Support:Database Cleanup

This article suggests methods to determine accounts and characters that are candidates for deletion.

Inconsistent data

Characters without rpobject

This was caused by a bug cause by storing gameEvents in an other thread before the database access was thread-safe. The bug was fixed in July 2009.

<source lang="sql"> SELECT * FROM characters WHERE NOT EXISTS (

   SELECT null FROM rpobject WHERE characters.object_id=rpobject.object_id

) ORDER BY timedate; </source> Consider: <source lang="sql"> SELECT * FROM characters LEFT JOIN rpobject ON characters.object_id=rpobject.object_id WHERE rpobject.object_id IS NULL ORDER BY timedate; </source>

Accounts without characters

<source lang="sql"> SELECT account.* FROM account WHERE NOT EXISTS (

   SELECT null FROM characters WHERE player_id=account.id

) AND status='active'; </source> Consider: <source lang="sql"> SELECT account.* FROM account LEFT JOIN characters ON player_id=account.id where account.STATUS='active' and player_id is null; </source>

Metrics for discontinuted accounts

• Must have no entries in gameEvents (2008-06-30, 2009-08-17), itemlog (2008-03-24), kills (2010-01-08), accountban
• Remember to clean additional tables, character_stats, accountLink, ...
• Criteria
  • Level < 5
  • Age < 5, if it exist
  • no valuable items
  • No other characters on that account that are not candidates Multi character support is younger than 2008-06-30.

Cleanup Script

<source lang="sql"> INSERT INTO del2 SELECT charname FROM del WHERE items='0 leather_armor, 0 club'; INSERT INTO del2 SELECT charname FROM del WHERE items='0 club, 0 leather_armor'; INSERT INTO del2 SELECT charname FROM del WHERE items='0 leather armor, 0 club'; INSERT INTO del2 SELECT charname FROM del WHERE items='0 club, 0 leather armor';

DELETE FROM del2 WHERE EXISTS (SELECT null FROM gameEvents WHERE charname=source); DELETE FROM del2 WHERE EXISTS (SELECT null FROM gameEvents_2008_08_21 WHERE charname=source); DELETE FROM del2 WHERE EXISTS (SELECT null FROM gameEvents_2009_02_19 WHERE charname=source); DELETE FROM del2 WHERE EXISTS (SELECT null FROM gameEvents_2009_08_17 WHERE charname=source); DELETE FROM del2 WHERE EXISTS (SELECT null FROM itemlog WHERE charname = source); DELETE FROM del2 WHERE EXISTS (SELECT null FROM kills WHERE kills.killed = charname AND killed_type='P'); DELETE FROM del2 WHERE EXISTS (SELECT null FROM kills WHERE kills.killer = charname AND killer_type='P');

-- check SELECT username, count(*) FROM account, del2, characters WHERE account.username=del2.charname AND account.id=player_id AND NOT EXISTS (SELECT null FROM del2 As t WHERE t.charname = characters.charname) GROUP BY username HAVING count(*) > 1;

SELECT username, count(*) FROM account, del2, characters As c1, characters As c2 WHERE account.id=c1.player_id AND c1.charname=del2.charname AND account.id=c2.player_id AND NOT EXISTS (SELECT null FROM del2 As t WHERE t.charname = c2.charname) GROUP BY username HAVING count(*) > 1; </source>

Delete

<source lang="sql">

</source>

• reached_achievement
• postman.source
• postman.target
• buddy.charname
• buddy.buddy
• character_stats.name

• halloffame.charname
• halloffame_archive.charname
• halloffame_archive_alltimes.charname
• halloffame_archive_recent.charname
• characters.charname