Advertisement
EyesOfAHawk

Char-purge

Jun 22nd, 2022 (edited)
592
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 4.50 KB | None
  1. -- Find chars last login before 2012 and export to a file  (to be used to create a webpage later)
  2. SELECT `name`
  3. INTO OUTFILE '/var/lib/mysql/purged-characters.csv'
  4. FIELDS TERMINATED BY ''
  5. ENCLOSED BY ''
  6. LINES TERMINATED BY '\n'
  7. FROM `char` WHERE
  8.     `last_login` = 0 AND
  9.     `account_id` IN (SELECT `account_id` FROM `login` WHERE `lastlogin` < '2012-01-01 00:00:00') AND
  10.     `class` = 0 AND `zeny` = 120000 AND
  11.     `str` = 1 AND `agi` = 1 AND `vit` = 1 AND `int` = 1 AND `dex` = 1 AND `luk` = 1 AND
  12.     `last_map` = 'amatsu' AND `last_x` = 225 AND `last_y` = 215
  13. ORDER BY `name` ASC;
  14.  
  15. -- Delete chars last login before 2012
  16. DELETE FROM `char` WHERE
  17.     `last_login` = 0 AND
  18.     `account_id` IN (SELECT `account_id` FROM `login` WHERE `lastlogin` < '2012-01-01 00:00:00') AND
  19.     `class` = 0 AND `zeny` = 120000 AND
  20.     `str` = 1 AND `agi` = 1 AND `vit` = 1 AND `int` = 1 AND `dex` = 1 AND `luk` = 1 AND
  21.     `last_map` = 'amatsu' AND `last_x` = 225 AND `last_y` = 215;
  22.  
  23.  
  24. -- Delete old accounts last login before 2012, no donations, no chars, no inventory AND no storage items
  25. DELETE FROM `login` WHERE
  26.     `lastlogin` < '2012-01-01 00:00:00' AND
  27.     `don_total` = 0 AND `last_unique_id` = 0 AND
  28.     `account_id` NOT IN (SELECT g.account_id FROM `storage` g) AND
  29.     `account_id` NOT IN (SELECT s.account_id FROM `char` s);
  30.  
  31. -- Delete chars with a non-existent account_id (i.e. they have no entry in login table)
  32. DELETE FROM `char` WHERE
  33.     `last_login` = 0 AND
  34.     `account_id` NOT IN (SELECT g.account_id FROM `login` g) AND
  35.     `class` = 0 AND `zeny` = 120000 AND
  36.     `str` = 1 AND `agi` = 1 AND `vit` = 1 AND `int` = 1 AND `dex` = 1 AND `luk` = 1 AND
  37.     `last_map` = 'amatsu' AND `last_x` = 225 AND `last_y` = 215;
  38.  
  39. -- Delete non-existent friends entries
  40. DELETE FROM `friends` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g) OR `friend_id` NOT IN (SELECT g.char_id FROM `char` g);
  41.  
  42. -- Delete non-existent inventory entries
  43. DELETE FROM `inventory` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);
  44.  
  45. -- Delete non-existent homunculus entries
  46. DELETE FROM `homunculus` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);
  47.  
  48. -- Delete non-existent hotkey entries
  49. DELETE FROM `hotkey` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);
  50.  
  51. -- Delete non-existent party entries
  52. DELETE FROM `party` WHERE `leader_char` NOT IN (SELECT g.char_id FROM `char` g);
  53. UPDATE `char` SET `party_id` = 0 WHERE `party_id` != 0 AND `party_id` NOT IN (SELECT g.party_id FROM `party` g);
  54.  
  55. -- Delete non-existent quest entries
  56. DELETE FROM `quest` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);
  57.  
  58. -- Delete non-existent sc_data entries
  59. DELETE FROM `sc_data` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);
  60.  
  61. -- Delete non-existent skill entries
  62. DELETE FROM `skill` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);
  63.  
  64. -- Delete non-existent skill_homunculus entries
  65. DELETE FROM `skill_homunculus` WHERE `homun_id` NOT IN (SELECT g.homun_id FROM `homunculus` g);
  66.  
  67. -- Delete non-existent char registry keys
  68. DELETE FROM `char_reg_num_db` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);
  69. DELETE FROM `char_reg_str_db` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);
  70.  
  71. -- Find the guild master char id
  72. SELECT `account_id` FROM `char` WHERE `char_id` IN (SELECT `char_id` FROM `guild` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g));
  73.  
  74. -- Export the above statement into a .sql for INSERT statements.
  75. -- Don't export 'id' column. Once exported, change guild_id to account_id
  76.  
  77. -- Then delete the storage
  78. DELETE FROM `guild_storage` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
  79.  
  80. -- Delete guild if guild master is now non-existent
  81. DELETE FROM `guild` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);
  82. UPDATE `char` SET `guild_id` = 0 WHERE `guild_id` != 0 AND `guild_id` NOT IN (SELECT g.guild_id FROM `guild` g);
  83.  
  84. -- Remove from guild if account is non-existent
  85. DELETE FROM `guild_member` WHERE `account_id` NOT IN (SELECT g.account_id FROM `login` g);
  86.  
  87. -- Delete any non-existent guild entries
  88. DELETE FROM `guild_alliance` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
  89. DELETE FROM `guild_castle` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
  90. DELETE FROM `guild_expulsion` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
  91. DELETE FROM `guild_member` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
  92. DELETE FROM `guild_position` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
  93. DELETE FROM `guild_skill` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
Advertisement
RAW Paste Data Copied
Advertisement