[Sql] Auth/characters Database Cleanup

TrinityCore Development TrinityCore Code Discussion
1

Is your database starting to get that not-so-fresh feeling? Is your DBErrors.log file larger than your naughty movie collection? Look no further! Use these queries to make your database sparkle brighter than a Twilight cosplay convention.

[ol][li]First, make sure your worldserver.conf file is set to auto-clean the character DB during startup. To do this, browse into your Build\bin\Release folder and open “worldserver.conf”[/li]
[li]Set CleanCharacterDB = 1 (Optional: also set PersistentCharacterCleanFlags = 25 to continue fully cleaning the DB every time the server boots up - see CharacterDBCleanup.txt for more info. You may also need to edit this setting in your characters.[worldstates](http://www.trinitycore.info/Worldstates_tc2) table if the worldserver doesn’t change it automatically)[/li]
[li]Also set CharDelete.Method = 1, and modify CharDelete.MinLevel and CharDelete.KeepDays however you like (CharDelete.KeepDays will need to be greater than 0 for this cleaning to work)[/li]
[li]Choose one of the queries below:[/li]
[/ol]

[SIZE=12px]>[/SIZE] Clean Auth AND Characters:

[SPOILER]/*

  • Clean Auth/Characters DB (REMEMBER TO BACKUP!)
  • Set date to remove all accounts made BEFORE that time
  • Example shown will remove all accounts made before 2010
  • Remember to replace “auth” and “characters” with the names of your databases
    */

SET @date := “2010-01-01 00:00:00”; – Set date and time here

SET @timestamp := (SELECT UNIX_TIMESTAMP(@DATE));
DELETE FROM auth.account WHERE last_login < @date;
DELETE FROM characters.account_data WHERE NOT EXISTS (SELECT NULL FROM auth.account WHERE account.id = account_data.accountId);
UPDATE characters.characters SET deleteDate=@timestamp WHERE NOT EXISTS (SELECT NULL FROM auth.account WHERE account.id = characters.account);


[/SPOILER]

[SIZE=12px]>[/SIZE] Clean Characters Only:

[SPOILER]/*

  • Clean Characters DB (REMEMBER TO BACKUP!)
  • Set time stamp to remove all characters made BEFORE that time
  • Use unixtimestamp.com to find an exact Unix time stamp
  • Remember to replace “characters” with the name of your database
    */
    SET @deletedate := “1293861600”; – Set UNIX time stamp here

UPDATE characters SET deleteDate = @deletedate WHERE logout_time < @deletedate;


[/SPOILER]

According to src/server/game/Entities/Player/Player.cpp, the character cleaning method will run the following query:

SELECT guid, deleteInfos_Account FROM characters WHERE deleteDate IS NOT NULL AND deleteDate < '%u';

‘%u’ is the Unix timestamp of the current date minus CharDelete.KeepDays. This means that if you run a similar query against your database (using UnixTimestamp.com), you will find all of the characters that will be deleted.

Example:

SELECT COUNT(*) FROM characters WHERE deleteDate IS NOT NULL AND deleteDate < 1311051600;

This method of cleaning will effectively remove ALL character data, even data from other tables - character_social, character_pet, guild_eventlog, etc.

Don’t forget…

Optimize! Depending on the amount of data deleted from your database in the above steps, the optimization alone could reduce the size of your database by half!

[/CODE]mysqlcheck --optimize -u root -p db_name
The above steps could likely take hours to complete if your database is large, so plan ahead and have some scheduled maintenance. Another important note: because of the way the “optimize” function works, it will need at maximum double the disk space of your current largest table (not database - table), so make sure you have plenty of room on your hard drive.

Don’t forget to √ Like This this post if it helps!

2

You need to preserve item_instance.itemEntry

3

what you also can do ^^


SET @timestamp:=1288915200; -- put here Unixtimestamp that correspond with account date deletion




DELETE FROM `accountdb`.`account` WHERE `last_login` < "2010-11-05 00:00:00";

DELETE FROM `chardb`.`account_data` WHERE `account` NOT IN (SELECT id FROM `accountdb`.`account`);

UPDATE `chardb`.`characters` SET `deleteDate`=@timestamp WHERE `account` NOT IN (SELECT id FROM `accountdb`.`account`);

what above does is delete all accounts not used after november 5th.

delete all accountdata from accounts that are not present anymore ( the inactive once that got deleted )

then it will update the char database with chars that are not linked to account ( those where deleted ) with a timestamp that is november 5th, and then on next core startup it will delete all chars that have that timestamp ( if you have old char delete function active in config )

this way the core will do it for you, and the core will be started automaticly when done cleanup your rubish on the database.

Also data of account and chars that are active will not be touched bij this script.

Did a test and purged 50.000 chars in less then 6 hours, and that was not a fast machine, and basicly chars that where all good in their stuff, loads of quests and loads of items etc.etc.

4

Good times.

5

well i got the idea because the original database cleanup takes hours in my case, and then i mean 12 to 18 hours, because EVERY item needs to be checked in game if it is linked to a guild and or char and or deleted and or auction house.

im talkine about this item_instance table.

and now it only deletes and touches the item wich belongs or where involved in chars that are now deleted, and it left alone the items form the people that are kept.

The nice part is, maybe its possible to write a batch file, that does this automaticly in a cronjob.

So what the batchfile can do is, set a timeframe, lets say 60 days ( basicly 2 months .

then looks on wich accounts needs to be deleted.

then update the chars not linked to any accounts with a timestamp so that the core on next startup will deleted them.

if you do that cronjob every day, then you dont have to wait that long after a server restart, and you will always have a cleanup database of accounts that are active.

slick… yes, but i also liked the idea, did it on a testserver, and was very surprised that it indeed works.

But before testing always make a backup, because you never know.

it would be nice if such function would be inplemented into the core, deleted accounts not used within x or y days, then update chars not linked to a timestamp 1 year ago, then let the core deleted old deleted chars… bang voila.

well hopefully you get the point of the idea.

happy new year peepz.

6

i’ll try it

TY

7

OP updated with Maestro’s great idea, along with a slight alteration by yours truly.

Thanks, Maestro!

8

beautifull, it would be awesome if such feature would be inplemented by trinity in the core, but i dont think that will happen prolly…

9

This is really helpful!

10

Where did the fast database cleanup stored procedures go?

Anyway

[CODE]
/*

  • Clean Auth/Characters DB (REMEMBER TO BACKUP!)
  • Enter Unix timestamp (unixtimestamp.com) of date you want to use to remove all accounts BEFORE it
  • Noone should take credit for a forum script
  • Remember to change auth to your auth database and characters to your characters database
    */
    SET @date:=“2010-01-01 00:00:00”; – Set date and time here

SET @timestamp:=(SELECT UNIX_TIMESTAMP(@DATE));
DELETE FROM auth.account WHERE last_login < @date;
DELETE FROM characters.account_data WHERE NOT EXISTS (SELECT NULL FROM auth.account WHERE account.id = account_data.account);
UPDATE characters.characters SET deleteDate=@timestamp WHERE NOT EXISTS (SELECT NULL FROM auth.account WHERE account.id = characters.account);
[/sql][/CODE]

The query you were performing was poorly optimized. This takes the 40% of the time.

11

Thanks. OP updated, and I added 2 more variables to make it even simpler.

12

Unfortunately you can not add database (or tables) names in variables, so your code will not work.

13

Crap, I forgot about that. Thanks for the reminder.

OP fixed.

14

Aw, btw.

The optimize tables thing is not useful (on the contrary it tends to slow down performances).

Read some guides.

15

When you have a database that drops from 63 GB to 39 GB from just an optimization alone, let me know if you have the same opinion.

16

this can really help out, if your willing to hack&slash in your char/auth dbs

17

Obviously I’m speaking of trinitycore in particular. I doubt you had more than 50% recover in size with an optimization, or maybe you aren’t using innodb.

The biggest table in the db should be the achievement one, and it shouldn’t have any index, meaning that it has no optimization to be done (meaning no decrease in size).

Seriously. 39gb of db with a single optimization it means you really spammed it a lot and then deleted the crap you put on it.

18

not working for me, dunno why

/*

  • Clean Auth/Characters DB (REMEMBER TO BACKUP!)

  • Enter Unix timestamp (unixtimestamp.com) of date you want to use to remove all accounts BEFORE it

  • Noone should take credit for a forum script

  • Remember to change auth to your auth database and characters to your characters database

*/

SET @date:=“2010-01-01 00:00:00”; – Set date and time here

SET @timestamp:=(SELECT UNIX_TIMESTAMP(@DATE));

DELETE FROM auth.account WHERE last_login < @date;

DELETE FROM characters.account_data WHERE NOT EXISTS (SELECT NULL FROM auth.account WHERE account.id = account_data.account);

UPDATE characters.characters SET deleteDate=@timestamp WHERE NOT EXISTS (SELECT NULL FROM auth.account WHERE account.id = characters.account);

i changed to:

SET @date:=“2011-02-28 00:00:00”; – Set date and time here

SET @timestamp:=(SELECT UNIX_TIMESTAMP(@DATE));

DELETE FROM tri_auth_database.account WHERE last_login < @date;

DELETE FROM tri_characters_database.account_data WHERE NOT EXISTS (SELECT NULL FROM tri_auth_database.account WHERE account.id = account_data.account);

UPDATE tri_characters_database.characters SET deleteDate=@timestamp WHERE NOT EXISTS (SELECT NULL FROM tri_auth_database.account WHERE account.id = characters.account);

now i get this output

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1054 (42S22): Unknown column ‘account_data.account’ in ‘where clause’

Query OK, 0 rows affected (0.03 sec)

Rows matched: 410 Changed: 0 Warnings: 0

i hope u can help me

thx m8´s

19

Take a look here: http://www.trinitycore.org/f/index.php?/topic/2233-request-character-cleanup/page__view__findpost__p__14531

20

That method is outdated now.

i wanna use this methode