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.

SELECT * FROM characters WHERE NOT EXISTS (
    SELECT null FROM rpobject WHERE characters.object_id=rpobject.object_id
) ORDER BY timedate;

Consider:

SELECT * FROM characters 
LEFT JOIN rpobject ON characters.object_id=rpobject.object_id
WHERE rpobject.object_id IS NULL 
ORDER BY timedate;

Accounts without characters

SELECT account.* FROM account WHERE NOT EXISTS (
    SELECT null FROM characters WHERE player_id=account.id
) AND status='active';

Consider:

SELECT account.* FROM account LEFT JOIN characters
ON player_id=account.id
where account.STATUS='active' and player_id is null;

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

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');

Delete

• reached_achievement
• postman.source
• postman.target
• halloffame.charname
• halloffame_archive.charname
• halloffame_archive_alltimes.charname
• halloffame_archive_recent.charname
• cid.charname
• characters.charname
• character_stats.name
• buddy.charname
• buddy.buddy