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>
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 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 candidatesMulti 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);
</source>