Database Clean

[kiova]

Hello, I am looking for a script to clean up accounts, characters, items, etc. of the database in the wake of a date.

Thank you very much.

[Patskie]

To cleanup a certain table you need to execute this command to your database :

TRUNCATE TABLE `table_name`;

 

EDITED : Misunderstood your request sorry for that 

DELETE FROM `char` WHERE `account_id` = ( SELECT `account_id` FROM `login` WHERE DATE_FORMAT(`lastlogin`, '%Y-%m-%d') < '2013-09-03' AND `account_id` != 1 );

DELETE FROM `login` WHERE DATE_FORMAT(`lastlogin`, '%Y-%m-%d') < '2013-09-03' AND `account_id` != 1;

This will delete all character and all account who are not online before 2013-09-03 ( YYYY/MM/DD ). Regarding the item i am not sure about it. In any case, do backup your database to prevent worst possible scenarios.

Edited September 3, 2013 by Patskie

[frenzmu06]

how about clean the whole ragnarok db in one command?

[Patskie]

how about clean the whole ragnarok db in one command?

 

Either you drop that database

DROP DATABASE `database_name`;

or :

TRUNCATE TABLE `auction`;
TRUNCATE TABLE `cart_inventory`;
TRUNCATE TABLE `char`;
TRUNCATE TABLE `charlog`;
TRUNCATE TABLE `friends`;
TRUNCATE TABLE `elemental`;
TRUNCATE TABLE `global_reg_value`;
TRUNCATE TABLE `guild`;
TRUNCATE TABLE `guild_alliance`;
TRUNCATE TABLE `guild_castle`;
TRUNCATE TABLE `guild_expulsion`;
TRUNCATE TABLE `guild_member`;
TRUNCATE TABLE `guild_position`;
TRUNCATE TABLE `guild_skill`;
TRUNCATE TABLE `guild_storage`;
TRUNCATE TABLE `homunculus`;
TRUNCATE TABLE `hotkey`;
TRUNCATE TABLE `interlog`;
TRUNCATE TABLE `inventory`;
TRUNCATE TABLE `ipbanlist`;
TRUNCATE TABLE `mail`;
TRUNCATE TABLE `mapreg`;
TRUNCATE TABLE `memo`;
TRUNCATE TABLE `mercenary`;
TRUNCATE TABLE `mercenary_owner`;
TRUNCATE TABLE `party`;
TRUNCATE TABLE `pet`;
TRUNCATE TABLE `quest`;
TRUNCATE TABLE `ragsrvinfo`;
TRUNCATE TABLE `sc_data`;
TRUNCATE TABLE `skill`;
TRUNCATE TABLE `skill_homunculus`;
TRUNCATE TABLE `sstatus`;
TRUNCATE TABLE `storage`;
TRUNCATE TABLE `login`;
INSERT INTO `login` (`account_id`, `userid`, `user_pass`, `sex`, `email`) VALUES ('1', 's1', 'p1', 'S','[email protected]');

[ccjosh]

Don't forget to re-create the 's' account

[Patskie]

Don't forget to re-create the 's' account

Thanks for the reminder. edited my last post

[frenzmu06]

@Patskie & ccjosh

ok tnx ty this help alot