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

CREATE TABLE del3 (charname VARCHAR(50), PRIMARY KEY(charname));

-- check
INSERT INTO del3
SELECT c3.charname FROM characters As c3, 
    (SELECT account.id As account_id 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) As x
WHERE x.account_id = c3.player_id;

INSERT INTO del3
SELECT c3.charname FROM characters As c3, 
    (SELECT account.id As account_id, 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 account_id
     HAVING count(*) > 1) As x
WHERE x.account_id = c3.player_id;

DELETE FROM del2 USING del2, del3 WHERE del2.charname = del3.charname;
DROP TABLE del3;

Delete

• 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