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

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

Delete

<source lang="sql"> DELETE FROM reached_achievement USING reached_achievement, del2 WHERE reached_achievement.charname = del2.charname; DELETE FROM postman USING postman, del2 WHERE source = del2.charname; DELETE FROM postman USING postman, del2 WHERE target = del2.charname; DELETE FROM buddy USING buddy, del2 WHERE buddy.charname = del2.charname; DELETE FROM buddy USING buddy, del2 WHERE buddy.buddy = del2.charname; DELETE FROM character_stats USING character_stats, del2 WHERE name = del2.charname;

UPDATE halloffame, del2 SET halloffame.charname = concat('del.2012-05-06.', halloffame.charname) WHERE del2.charname=halloffame.charname; UPDATE halloffame_archive_alltimes, del2 SET halloffame_archive_alltimes.charname = concat('del.2012-05-06.', halloffame_archive_alltimes.charname) WHERE del2.charname=halloffame_archive_alltimes.charname; UPDATE halloffame_archive_recent, del2 SET halloffame_archive_recent.charname = concat('del.2012-05-06.', halloffame_archive_recent.charname) WHERE del2.charname=halloffame_archive_recent.charname; UPDATE halloffame_archive, del2 SET halloffame_archive.charname = concat('del.2012-05-06.', halloffame_archive.charname) WHERE del2.charname=halloffame_archive.charname;

UPDATE cid, del2 SET cid.charname = concat('del.2012-05-06.', cid.charname) WHERE del2.charname=cid.charname;

-- TODO account

UPDATE characters, del2 SET characters.status='deleted' AND characters.charname = concat('del.2012-05-06.', characters.charname) WHERE del2.charname=characters.charname; </source>