CleanUP + Fix DB

charlie · September 3, 2007

Tak vam tady postnu jeden SQL script, ktery my hodne usnadnil zivot. Nedelal jsem ho primo ja, ale sestavil jsem dohromady asi 3 scripty a patricne sem je upravil. Co udela ? Smaze vsechny zabanovane accounty, vsechny neaktivni accounty (dobu neaktivnosti lzde nastavit), vsechny IP Banovane accounty, vsechny accounty bez postav a opravi vasi DB predevsim teda looty, smaze charactery ktere nemaji accounty a smaze character data neexistujich characteru, takze spravi Itemy podle tabulek item_instance a character_inventory. Jak uspesny je, sem nedoufal ani ja. Meli jsme databazi na 1.12 serveru v dost odpornem stavu. Padalo to kazdou pul hodiny a dene byly Freezy ZE mangos zamrzl, a vyuzival CPU ze 100%. Takze se ani nedalo poradne pripojit pres SSH , VNC ..a nez se nahodilo trvalo asi pul hodiny. Pote jsem na chvili odstavil mangos - to doporucuji udelat pri pouziti scriptu, protoze sam o sobe vytizi mysql dost, takze mangos se sekne a vysledek je destruktivni. Takze spis za nulove zateze. No a vysledek po cleanupu ? pada jednou dene a freezy zadne, a lagy prakticky vymizeli. Treba se to bude nekomu hodit mno

-- Delete accounts without characters --
DELETE FROM `mangos`.`account` where `id` not in(select `account` from `character`);

-- Delete IP Banned accounts (comment this out if you wish)
DELETE FROM `mangos`.`account` where `last_ip` in (SELECT `ip` from `mangos`.`ip_banned`);

-- Delete banned accounts (comment this out if you wish)
DELETE FROM `mangos`.`account` WHERE `banned` = '1';

-- Delete all accounts which has not been online for some period of time
DELETE FROM `mangos`.`account` WHERE `last_login` < '2007-04-15 00:00:00';

-- Delete all characters which has not coresponding account
DELETE FROM `mangos`.`character` WHERE `account` NOT IN (SELECT `id` FROM `mangos`.`account`);

-- Fix Missing character_inventory table data missing from character
SELECT COUNT(guid) AS Missing_Char FROM `mangos`.`character_inventory` WHERE guid NOT IN (SELECT guid FROM `mangos`.`character`) AND guid!=0;
DELETE FROM `mangos`.`character_inventory` WHERE guid NOT IN (SELECT guid FROM `mangos`.`character`) AND guid!=0;

-- Fix Missing Items in item_instance missing from charater_inventory
SELECT COUNT(item) AS Missing_ItemInstance FROM `mangos`.`character_inventory` WHERE item NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND item!=0;
DELETE FROM `mangos`.`character_inventory` WHERE item NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND item!=0;

-- Fix Missing mail and auction data missing from item_instance
SELECT COUNT(guid) FROM `mangos`.`item_instance` WHERE guid NOT IN(SELECT item FROM `mangos`.`character_inventory`) AND guid NOT IN(SELECT item_guid FROM `mangos`.`mail`) AND guid NOT IN(SELECT itemguid FROM `mangos`.`auctionhouse`);
DELETE FROM `mangos`.`item_instance` WHERE guid NOT IN(SELECT item FROM `mangos`.`character_inventory`) AND guid NOT IN(SELECT item_guid FROM mail) AND guid NOT IN(SELECT itemguid FROM `mangos`.`auctionhouse`);

-- Fix Missing mail data from item_instance table
SELECT COUNT(item_guid) AS Missing_Mail FROM `mangos`.`mail` WHERE item_guid NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND item_guid!=0;
DELETE FROM `mangos`.`mail` WHERE item_guid NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND item_guid!=0;

-- Fix Missing auction data from item_instance
SELECT COUNT(itemguid) AS Missing_Auction FROM `mangos`.`auctionhouse` WHERE itemguid NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND itemguid!=0;
DELETE FROM `mangos`.`auctionhouse` WHERE itemguid NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND itemguid!=0;

DELETE FROM `character_inventory` WHERE `item_template` NOT IN (SELECT `entry` FROM `item_template`);

DELETE FROM `creature_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);

DELETE FROM `item_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);

DELETE FROM `gameobject_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);

DELETE FROM `gameobject` WHERE id NOT IN (SELECT `entry` FROM `gameobject_template`);

DELETE FROM `skinning_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);

DELETE FROM `pickpocketing_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);

DELETE FROM `prospecting_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);

DELETE FROM `creature` WHERE `id` NOT IN (SELECT `entry` FROM `creature_template`);

DELETE FROM `disenchant_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);

DELETE FROM `creature` WHERE `id` NOT IN (SELECT `entry` FROM `creature_template`);

DELETE FROM `gameobject` WHERE `id` NOT IN (SELECT `entry` FROM `gameobject_template`);

DELETE FROM `character_pet` WHERE `owner` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `character_queststatus` WHERE `quest` NOT IN (SELECT `entry` FROM `quest_template`);

DELETE FROM `character_reputation` WHERE `guid` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `character_social` WHERE `guid` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `character_tutorial` WHERE `guid` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `character_action` WHERE `guid` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `item_instance` WHERE `owner_guid` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `mail` WHERE sender NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `guild_member` WHERE `guid` NOT IN (SELECT `guid` FROM `character`);

-- Fix Loots
UPDATE `creature_template` SET `pickpocketloot` = `entry` WHERE (`pickpocketloot` = 0 AND `entry` IN (SELECT `entry` FROM `pickpocketing_loot_template`));

-- .
UPDATE `creature_template` SET `lootid` = `entry` WHERE (`lootid` = 0 AND `entry` IN (SELECT `entry` FROM `creature_loot_template`));

-- .
UPDATE `creature_template` SET `skinloot` = `entry` WHERE (`skinloot` = 0 AND `entry` IN (SELECT `entry` FROM `skinning_loot_template`));

-- Set to 0 for Missing Loots_Templates
UPDATE `creature_template` SET `pickpocketloot` = 0 WHERE (`pickpocketloot` != `entry` AND `pickpocketloot` NOT IN (SELECT `entry` FROM `pickpocketing_loot_template`)) OR (`pickpocketloot` != 0 AND `entry` NOT IN (SELECT `entry` FROM `pickpocketing_loot_template`));

-- .
UPDATE `creature_template` SET `lootid` = 0 WHERE (`lootid` != `entry` AND `lootid` NOT IN (SELECT `entry` FROM `creature_loot_template`)) OR (`lootid` != 0 AND `entry` NOT IN (SELECT `entry` FROM `creature_loot_template`));

-- .
UPDATE `creature_template` SET `skinloot` = 0 WHERE (`skinloot` != `entry` AND `skinloot` NOT IN (SELECT `entry` FROM `skinning_loot_template`)) OR (`skinloot` != 0 AND `entry` NOT IN (SELECT `entry` FROM `skinning_loot_template`));

-- Set to 0 for Missing Loots_Templates
UPDATE `creature_template` SET `pickpocketloot` = 0 WHERE (`pickpocketloot` != `entry` AND `pickpocketloot` NOT IN (SELECT `entry` FROM `pickpocketing_loot_template`)) OR (`pickpocketloot` != 0 AND `entry` NOT IN (SELECT `entry` FROM `pickpocketing_loot_template`));

-- .
UPDATE `creature_template` SET `lootid` = 0 WHERE (`lootid` != `entry` AND `lootid` NOT IN (SELECT `entry` FROM `creature_loot_template`)) OR (`lootid` != 0 AND `entry` NOT IN (SELECT `entry` FROM `creature_loot_template`));

-- .
UPDATE `creature_template` SET `skinloot` = 0 WHERE (`skinloot` != `entry` AND `skinloot` NOT IN (SELECT `entry` FROM `skinning_loot_template`)) OR (`skinloot` != 0 AND `entry` NOT IN (SELECT `entry` FROM `skinning_loot_template`));

-- Fix GameObject Loots
UPDATE `gameobject` SET `loot` = `id` WHERE (`loot` = 0 AND `id` IN (SELECT `entry` FROM `gameobject_loot_template`));

-- Fix Items Disenchat Loots
UPDATE `item_template` SET `DisenchantID` = `entry` WHERE (`DisenchantID` = 0 AND `entry` IN (SELECT `entry` FROM `disenchant_loot_template`));

Diky virnikovi za poskytnuti cleanup scriptu + dalsim par lidem z sdb fora

co se tyce, jeste preed pouzitim musite udelat nejake upravi. napriklad v ktere DB lezi mangos a account dataq.. ale to zvladnete. Take pro nejnovejsi mangos budou nutne upravi tabulky banovanych accountu. A nastavit dobu neaktivity

charlie · September 14, 2007

PRO NEJNOVEJSI REVIZE

-- Delete accounts without characters --
DELETE FROM `mangos`.`account` where `id` not in(select `account` from `character`);

-- Delete all accounts which has not been online for some period of time
DELETE FROM `mangos`.`account` WHERE `last_login` < '2007-04-15 00:00:00';

-- Delete all characters which has not coresponding account
DELETE FROM `mangos`.`character` WHERE `account` NOT IN (SELECT `id` FROM `mangos`.`account`);

TRUNCATE TABLE `group`;

TRUNCATE TABLE `group_member`;

-- Fix Missing character_inventory table data missing from character
SELECT COUNT(guid) AS Missing_Char FROM `mangos`.`character_inventory` WHERE guid NOT IN (SELECT guid FROM `mangos`.`character`) AND guid!=0;
DELETE FROM `mangos`.`character_inventory` WHERE guid NOT IN (SELECT guid FROM `mangos`.`character`) AND guid!=0;

-- Fix Missing Items in item_instance missing from charater_inventory
SELECT COUNT(item) AS Missing_ItemInstance FROM `mangos`.`character_inventory` WHERE item NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND item!=0;
DELETE FROM `mangos`.`character_inventory` WHERE item NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND item!=0;

-- Fix Missing mail and auction data missing from item_instance
SELECT COUNT(guid) FROM `mangos`.`item_instance` WHERE guid NOT IN(SELECT item FROM `mangos`.`character_inventory`) AND guid NOT IN(SELECT item_guid FROM `mangos`.`mail`) AND guid NOT IN(SELECT itemguid FROM `mangos`.`auctionhouse`);
DELETE FROM `mangos`.`item_instance` WHERE guid NOT IN(SELECT item FROM `mangos`.`character_inventory`) AND guid NOT IN(SELECT item_guid FROM mail) AND guid NOT IN(SELECT itemguid FROM `mangos`.`auctionhouse`);

-- Fix Missing mail data from item_instance table
SELECT COUNT(item_guid) AS Missing_Mail FROM `mangos`.`mail` WHERE item_guid NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND item_guid!=0;
DELETE FROM `mangos`.`mail` WHERE item_guid NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND item_guid!=0;

-- Fix Missing auction data from item_instance
SELECT COUNT(itemguid) AS Missing_Auction FROM `mangos`.`auctionhouse` WHERE itemguid NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND itemguid!=0;
DELETE FROM `mangos`.`auctionhouse` WHERE itemguid NOT IN (SELECT guid FROM `mangos`.`item_instance`) AND itemguid!=0;

DELETE FROM `character_inventory` WHERE `item_template` NOT IN (SELECT `entry` FROM `item_template`);

DELETE FROM `gameobject` WHERE id NOT IN (SELECT `entry` FROM `gameobject_template`);

DELETE FROM `skinning_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);

DELETE FROM `creature` WHERE `id` NOT IN (SELECT `entry` FROM `creature_template`);

DELETE FROM `creature` WHERE `id` NOT IN (SELECT `entry` FROM `creature_template`);

DELETE FROM `gameobject` WHERE `id` NOT IN (SELECT `entry` FROM `gameobject_template`);

DELETE FROM `character_pet` WHERE `owner` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `character_queststatus` WHERE `quest` NOT IN (SELECT `entry` FROM `quest_template`);

DELETE FROM `character_reputation` WHERE `guid` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `character_social` WHERE `guid` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `character_tutorial` WHERE `guid` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `character_action` WHERE `guid` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `item_instance` WHERE `owner_guid` NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `mail` WHERE sender NOT IN (SELECT `guid` FROM `character`);

DELETE FROM `guild_member` WHERE `guid` NOT IN (SELECT `guid` FROM `character`);

Edited October 2, 2007 by charlie

charlie · October 1, 2007

Dale pridavam nejaky Spell Clean z MoDB fora.

/*blood elves*//*dranei*//*dranei & undead*//*dwarf*//*gnome*//*nigth elves*//*human*//*orc*//*tauren*//*troll*//*undead*/
DELETE FROM character_spell WHERE spell IN (28877, 25046, 28730, 822, 28734) AND guid IN (SELECT guid FROM `character` WHERE `race`<>10);
DELETE FROM character_spell WHERE spell IN (28875, 28880, 6562, 28878) AND guid IN (SELECT guid FROM `character` WHERE `race`<>11);
DELETE FROM character_spell WHERE spell IN (20579) AND guid IN (SELECT guid FROM `character` WHERE `race`<>11 AND `race`<>5);
DELETE FROM character_spell WHERE spell IN (2481, 20596,20595, 20594) AND guid IN (SELECT guid FROM `character` WHERE `race`<>3);
DELETE FROM character_spell WHERE spell IN (20592, 20593, 20589, 20591) AND guid IN (SELECT guid FROM `character` WHERE `race`<>7);
DELETE FROM character_spell WHERE spell IN (20583, 20582, 20580, 20585) AND guid IN (SELECT guid FROM `character` WHERE `race`<>4);
DELETE FROM character_spell WHERE spell IN (20599, 20864, 20600, 20597, 20598) AND guid IN (SELECT guid FROM `character` WHERE `race`<>1);
DELETE FROM character_spell WHERE spell IN (20574, 33702, 33697, 20572, 20575, 20573) AND guid IN (SELECT guid FROM `character` WHERE `race`<>2);
DELETE FROM character_spell WHERE spell IN (20552, 205550, 20551, 20549) AND guid IN (SELECT guid FROM `character` WHERE `race`<>6);
DELETE FROM character_spell WHERE spell IN (20557, 26297, 26296, 20554, 26290, 20555, 20558) AND guid IN (SELECT guid FROM `character` WHERE `race`<>8);
DELETE FROM character_spell WHERE spell IN (20577, 5227, 7744) AND guid IN (SELECT guid FROM `character` WHERE `race`<>5);

DELETE FROM `character_spell` WHERE `spell` IN (3563, 3566, 3561, 32272, 35715, 33690, 3567, 3562, 32271, 3565, 11419, 32266, 11416, 11417, 33691, 35717, 32267, 10059, 11420, 11418) AND `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 8);
DELETE FROM `character_action` WHERE `action` IN (3563, 3566, 3561, 32272, 35715, 33690, 3567, 3562, 32271, 3565, 11419, 32266, 11416, 11417, 33691, 35717, 32267, 10059, 11420, 11418) AND `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 8);
DELETE FROM `character_spell` WHERE `spell` IN (18960) AND `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 11);
DELETE FROM `character_action` WHERE `action` IN (18960) AND `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 11);
DELETE FROM `character_spell` WHERE `spell` IN (7386) AND `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 1);
DELETE FROM `character_action` WHERE `action` IN (7386) AND `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 1);

DELETE FROM `character_spell` WHERE spell IN ('33271', '3562', '3561', '3565', '32266', '11416', '10059', '11419', '33690', '33691') AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('1','3','4','7','11'));
DELETE FROM `character_spell` WHERE spell IN ('3567', '32272', '3563', '3566', '11417', '32267', '11418', '11420', '35715', '35717') AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('2','5','6','8','10'));
DELETE FROM `character_spell` WHERE spell IN ('13819', '23214', '31801') AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('1','3','11'));
DELETE FROM `character_spell` WHERE spell IN ('34769', '34767', '31892', '32676') AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('10'));
DELETE FROM `character_spell` WHERE spell IN ('13908', '19236', '19238', '19240', '19241', '19242', '19243', '25437') AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('1','3'));
DELETE FROM `character_spell` WHERE spell IN ('9035', '19281', '18137', '19282', '19283', '19284', '19285', '25470', '19308', '19309', '19310', '19311', '19312', '25477') AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('8'));
DELETE FROM `character_spell` WHERE spell IN ('10797', '19296', '2651', '19289', '19291', '19292', '19293', '25450', '19299', '19302', '19303', '19304', '19305', '25446') AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('4'));
DELETE FROM `character_spell` WHERE spell IN ('32548', '32182') AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('11'));
DELETE FROM `character_spell` WHERE spell IN ('2652', '19261', '19262', '19264', '19265', '19266', '25461') AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('5','10'));
DELETE FROM `character_spell` WHERE spell IN ('2944', '19276', '19277', '19278', '19279', '19280', '25467') AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('5'));
DELETE FROM `character_spell` WHERE spell IN ('25441', '19275', '19274', '19273', '19271', 13896') AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('1'));
DELETE FROM `character_spell` WHERE spell='6346' AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('3','11'));
DELETE FROM `character_spell` WHERE spell='2825' AND `guid` IN (SELECT guid FROM `character` Where `race` NOT IN ('2','6','8'));

DELETE FROM `character_spell` WHERE `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 1) AND `spell` IN (6673, 5242, 6192, 11549, 11550, 11551, 25289, 2048, 2457, 18499, 2458, 2687, 23881, 23892, 23893, 23894, 25251, 30335, 1161, 100, 6178, 11578, 845, 7369, 11608, 11609, 20569, 25231, 469, 71, 1160, 6190, 11554, 11555, 11556, 25202, 25203, 20243, 30016, 30022, 676, 5308, 20658, 20660, 20661, 20662, 25234, 25236, 1715, 7372, 7373, 25212, 78, 284, 285, 1608, 11564, 11565, 11566, 11567, 25286, 29707, 30324, 20252, 20616, 20617, 25272, 25275, 3411, 5246, 694, 7400, 7402, 20559, 20560, 25266, 12294, 21551, 21552, 21553, 25248, 30330, 7384, 7887, 11584, 11585, 6552, 6554, 29801, 30030, 30033, 1719, 772, 6546, 6547, 6548, 11572, 11573, 11574, 25208, 20230, 6572, 6574, 7379, 11600, 11601, 25288, 25269, 30357, 72, 1671, 1672, 29704, 2565, 23922, 23923, 23924, 23925, 25258, 30356, 871, 1464, 8820, 11604, 11605, 25241, 25242, 23920, 12678, 7386, 7405, 8380, 11596, 11597, 25225, 355, 6343, 8198, 8204, 8205, 11580, 11581, 25264, 34428, 1680, 12296, 12297, 12750, 12751, 12752, 12753, 16487, 16489, 16492, 29836, 29859, 12321, 12835, 12836, 12837, 12838, 12318, 12857, 12858, 12860, 12861, 12809, 12320, 12852, 12853, 12855, 12856, 12328, 12834, 12849, 12867, 12303, 12788, 12789, 16462, 16463, 16464, 16465, 16466, 23584, 23585, 23586, 23587, 23588, 29623, 12317, 13045, 13046, 13047, 13048, 12319, 12971, 12972, 12973, 12974, 29787, 29790, 29792, 16493, 16494, 20500, 20501, 29759, 29760, 29761, 29762, 29763, 12301, 12818, 12285, 12697, 12329, 12950, 20496, 29593, 29594, 29595, 12324, 12876, 12877, 12878, 12879, 12313, 12804, 12807, 29723, 29724, 29725, 20502, 20503, 12289, 12668, 23695, 12282, 12663, 12664, 20504, 20505, 35446, 35448, 35449, 35450, 35451, 12290, 12963, 12286, 12658, 12659, 12797, 12799, 12800, 12311, 12958, 12945, 12312, 12803, 12862, 12330, 12308, 12810, 12811, 12302, 12765, 12287, 12665, 12666, 29721, 29776, 12300, 12959, 12960, 12961, 12962, 12975, 12284, 12701, 12702, 12703, 12704, 16538, 16539, 16540, 16541, 16542, 12323, 12700, 12781, 12783, 12784, 12785, 29590, 29591, 29592, 29834, 29838, 29598, 29599, 29600, 12298, 12724, 12725, 12726, 12727, 12292, 12281, 12812, 12813, 12814, 12815, 12295, 12676, 12677, 12299, 12761, 12762, 12763, 12764, 12163, 12711, 12712, 12713, 12714, 12322, 12999, 13000, 13001, 13002, 29140, 29143, 29144, 29145, 29146, 29888, 29889);
DELETE FROM `character_spell` WHERE `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 2) AND `spell` IN (31935, 32699, 32700, 31884, 1044, 19977, 19978, 19979, 27144, 19740, 19834, 19835, 19836, 19837, 19838, 25291, 27140, 1022, 5599, 10278, 6940, 20729, 27147, 27148, 1038, 20911, 20912, 20913, 20914, 27168, 19742, 19850, 19852, 19853, 19854, 25290, 27142, 4987, 19746, 26573, 20116, 20922, 20923, 20924, 27173, 32223, 465, 10290, 643, 10291, 1032, 10292, 10293, 27149, 19752, 498, 5573, 642, 1020, 879, 5614, 5615, 10312, 10313, 10314, 27138, 19891, 19899, 19900, 27153, 19750, 19939, 19940, 19941, 19942, 19943, 27137, 19888, 19897, 19898, 27152, 25898, 25890, 27145, 25782, 25916, 27141, 25895, 25899, 27169, 25894, 25918, 27143, 853, 5588, 5589, 10308, 24275, 24274, 24239, 27180, 635, 639, 647, 1026, 1042, 3472, 10328, 10329, 25292, 27135, 27136, 20925, 20927, 20928, 27179, 20473, 20929, 20930, 27174, 33072, 2812, 10318, 27139, 20271, 633, 2800, 10310, 27154, 1152, 7328, 10322, 10324, 20772, 20773, 7294, 10298, 10299, 10300, 10301, 27150, 31789, 25780, 31892, 20375, 20915, 20918, 20919, 20920, 27170, 20164, 31895, 20165, 20347, 20348, 20349, 27160, 21084, 20287, 20288, 20289, 20290, 20291, 20292, 20293, 27155, 21082, 20162, 20305, 20306, 20307, 20308, 27158, 31801, 20166, 20356, 20357, 27166, 5502, 19876, 19895, 19896, 27151, 31785, 33776, 23214, 34767, 13819, 34769, 2878, 5627, 10326, 20096, 20097, 20098, 20099, 20100, 31850, 31851, 31852, 31853, 31854, 31821, 20101, 20102, 20103, 20104, 20105, 31828, 31829, 31830, 20217, 20117, 20118, 20119, 20120, 20121, 31866, 31867, 31868, 35395, 20060, 20061, 20062, 20063, 20064, 20216, 31842, 20257, 20258, 20259, 20260, 20261, 31871, 31872, 31873, 20262, 20263, 20264, 20265, 20266, 9799, 25988, 31879, 31880, 31881, 31882, 31883, 20174, 20175, 20237, 20238, 20239, 31837, 31838, 31839, 31840, 31841, 5923, 5924, 5925, 5926, 25829, 20210, 20212, 20213, 20214, 20215, 20042, 20045, 20046, 20047, 20048, 20244, 20245, 20254, 20255, 20256, 20138, 20139, 20140, 20141, 20142, 20487, 20488, 20489, 41021, 41026, 25956, 25957, 20234, 20235, 20091, 20092, 20468, 20469, 20470, 31869, 31870, 20224, 20225, 20330, 20331, 20332, 20335, 20336, 20337, 31833, 31835, 31836, 20196, 20197, 20198, 20199, 20200, 20189, 20192, 20193, 31822, 31823, 31824, 31825, 31826, 26022, 26023, 20177, 20179, 20181, 20180, 20182, 20127, 20130, 20135, 20136, 20137, 20066, 31848, 31849, 32043, 35396, 35397, 31876, 31877, 31878, 20359, 20360, 20361, 20218, 20148, 20149, 20150, 31846, 31847, 20205, 20206, 20207, 20209, 20208, 31844, 31845, 20143, 20144, 20145, 20146, 20147, 20111, 20112, 20113, 9453, 25836, 20049, 20056, 20057, 20058, 20059, 9452, 26016, 26021, 31858, 31859, 31860, 31861, 31862);
DELETE FROM `character_spell` WHERE `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 3) AND `spell` IN (19434, 20900, 20901, 20902, 20903, 20904, 27065, 27350, 3044, 14281, 14282, 14283, 14284, 14285, 14286, 14287, 27019, 13161, 5118, 13165, 14318, 14319, 14320, 14321, 14322, 25296, 27044, 13163, 13159, 34074, 20043, 20190, 27045, 75, 1462, 5149, 883, 5116, 19306, 20909, 20910, 27067, 781, 14272, 14273, 27015, 2641, 20736, 14274, 15629, 15630, 15631, 15632, 27020, 6197, 13813, 14316, 14317, 27025, 1002, 6991, 5384, 27351, 1543, 1499, 14310, 14311, 27352, 13809, 27364, 27344, 1130, 14323, 14324, 14325, 13795, 14302, 14303, 14304, 14305, 27023, 34026, 136, 3111, 3661, 3662, 13542, 13543, 13544, 27046, 34477, 1495, 14269, 14270, 14271, 36916, 2643, 14288, 14289, 14290, 25294, 27021, 27362, 27354, 3045, 2973, 14260, 14261, 14262, 14263, 14264, 14265, 14266, 27014, 982, 1513, 14326, 14327, 3043, 27349, 1978, 13549, 13550, 13551, 13552, 13553, 13554, 13555, 25295, 27016, 27353, 34600, 34120, 1515, 1494, 19878, 19879, 19880, 19882, 19885, 19883, 19884, 19801, 19506, 20905, 20906, 27066, 3034, 14279, 14280, 27018, 1510, 14294, 14295, 27022, 2974, 14267, 14268, 19386, 24132, 24133, 27068, 34453, 34454, 19461, 19462, 24691, 19590, 19592, 19596, 19574, 34482, 34483, 34484, 34462, 34464, 34465, 19239, 19245, 34475, 34476, 35100, 35102, 35103, 19295, 19297, 19298, 19301, 19300, 19263, 19416, 19417, 19418, 19419, 19420, 19583, 19584, 19585, 19586, 19587, 19184, 19387, 19388, 34500, 34502, 34503, 34455, 34459, 34460, 19598, 19599, 19600, 19601, 19602, 35029, 35030, 19621, 19622, 19623, 19624, 19625, 34950, 34954, 19498, 19499, 19500, 19151, 19152, 19153, 19454, 19455, 19456, 19457, 19458, 19552, 19553, 19554, 19555, 19556, 19549, 19550, 19551, 35104, 35110, 35111, 19407, 19412, 19413, 19414, 19415, 19286, 19287, 19421, 19422, 19423, 19424, 19425, 19572, 19573, 24443, 19575, 19464, 19465, 19466, 19467, 19468, 19228, 19232, 19233, 19577, 19370, 19371, 19373, 19426, 19427, 19429, 19430, 19431, 19168, 19180, 19181, 24296, 24297, 34485, 34486, 34487, 34488, 34489, 34506, 34507, 34508, 34838, 34839, 24293, 24294, 24295, 19485, 19487, 19488, 19489, 19490, 19559, 19560, 19507, 19508, 19509, 19510, 19511, 34948, 34949, 23989, 34491, 34492, 34493, 19159, 19160, 19503, 34466, 34467, 34468, 34469, 34470, 34490, 19578, 20895, 19290, 19294, 24283, 34494, 34496, 19255, 19256, 19257, 19258, 19259, 34692, 19609, 19610, 19612, 34497, 34498, 34499, 19376, 19377, 19616, 19617, 19618, 19619, 19620);
DELETE FROM `character_spell` WHERE `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 4) AND `spell` IN (8676, 8724, 8725, 11267, 11268, 11269, 27441, 26786, 53, 2589, 2590, 2591, 8721, 11279, 11280, 11281, 25300, 26863, 2094, 6510, 1833, 31224, 3420, 3421, 2835, 2837, 11357, 11358, 25347, 26969, 27282, 26679, 2836, 1842, 1725, 32645, 32684, 39967, 5277, 26669, 2098, 6760, 6761, 6762, 8623, 8624, 11299, 11300, 31016, 26865, 8647, 8649, 8650, 11197, 11198, 26866, 1966, 6768, 8637, 11303, 25302, 27448, 703, 8631, 8632, 8633, 11289, 11290, 26839, 26884, 1776, 1777, 8629, 11285, 11286, 38764, 16511, 17347, 17348, 26864, 8681, 8687, 8691, 11341, 11342, 11343, 26892, 24224, 27095, 27096, 27097, 27099, 1766, 1767, 1768, 1769, 38768, 408, 8643, 5763, 8694, 11400, 1329, 34411, 34412, 34413, 1804, 921, 2842, 1943, 8639, 8640, 11273, 11274, 11275, 26867, 1860, 6770, 2070, 11297, 5938, 1752, 1757, 1758, 1759, 1760, 8621, 11293, 11294, 26861, 26862, 5171, 6774, 2983, 8696, 11305, 1784, 1785, 1786, 1787, 1856, 1857, 26889, 13220, 13228, 13229, 13230, 27283, 13750, 18427, 18428, 18429, 13877, 31124, 31126, 13975, 14062, 14063, 14064, 14065, 31228, 31229, 31230, 14177, 35541, 35550, 35551, 35552, 35553, 13706, 13804, 13805, 13806, 13807, 31380, 31382, 31383, 31384, 31385, 30902, 30903, 30904, 30905, 30906, 13713, 13853, 13854, 13855, 13856, 14082, 14083, 14076, 14094, 13715, 13848, 13849, 13851, 13852, 13981, 14066, 13742, 13872, 31211, 31212, 31213, 31233, 31239, 31240, 31241, 31242, 13707, 13966, 13967, 13968, 13969, 31208, 31209, 14278, 30894, 30895, 14079, 14080, 14081, 13733, 13865, 13866, 14162, 14163, 14164, 14168, 14169, 13741, 13793, 13792, 13754, 13867, 14174, 14175, 14176, 14113, 14114, 14115, 14116, 14117, 13732, 13863, 14165, 14166, 14167, 13743, 13875, 13976, 13979, 13980, 14128, 14132, 14135, 14136, 14137, 13712, 13788, 13789, 13790, 13791, 13709, 13800, 13801, 13802, 13803, 14138, 14139, 14140, 14141, 14142, 13958, 13970, 13971, 13972, 13973, 31221, 31222, 31223, 31226, 31227, 14158, 14159, 31130, 31131, 14057, 14072, 14073, 14074, 14075, 13705, 13832, 13843, 13844, 13845, 14183, 14185, 31244, 31245, 14179, 14144, 14148, 14251, 14156, 14160, 14161, 14186, 14190, 14193, 14194, 14195, 14171, 14172, 14173, 13983, 14070, 14071, 36554, 31216, 31217, 31218, 31219, 31220, 30892, 30893, 32601, 13960, 13961, 13962, 13963, 13964, 14983, 16513, 16514, 16515, 16719, 16720, 31122, 31123, 30919, 30920);
DELETE FROM `character_spell` WHERE `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 5) AND `spell` IN (552, 32546, 34861, 34863, 34864, 34865, 34866, 32676, 528, 13908, 19236, 19238, 19240, 19241, 19242, 19243, 25437, 2944, 19276, 19277, 19278, 19279, 19280, 25467, 527, 988, 14752, 14818, 14819, 27841, 25312, 2651, 19289, 19291, 19292, 19293, 25450, 586, 9578, 9579, 9592, 10941, 10942, 25429, 6346, 13896, 19271, 19273, 19274, 19275, 25441, 2061, 9472, 9473, 9474, 10915, 10916, 10917, 25233, 25235, 2060, 10963, 10964, 10965, 25314, 25210, 25213, 2054, 2055, 6063, 6064, 9035, 19281, 19282, 19283, 19284, 19285, 25470, 14914, 15262, 15263, 15264, 15265, 15266, 15267, 15261, 25384, 15237, 15430, 15431, 27799, 27800, 27801, 25331, 588, 7128, 602, 1006, 10951, 10952, 25431, 2050, 2052, 2053, 1706, 724, 27870, 27871, 28275, 8129, 8131, 10874, 10875, 10876, 25379, 25380, 32375, 8092, 8102, 8103, 8104, 8105, 8106, 10945, 10946, 10947, 25372, 25375, 605, 10911, 10912, 15407, 17311, 17312, 17313, 17314, 18807, 25387, 453, 8192, 10953, 25596, 2096, 10909, 1243, 1244, 1245, 2791, 10937, 10938, 25389, 17, 592, 600, 3747, 6065, 6066, 10898, 10899, 10900, 10901, 25217, 25218, 21562, 21564, 25392, 596, 996, 10960, 10961, 25316, 25308, 33076, 27683, 39374, 27681, 32999, 8122, 8124, 10888, 10890, 139, 6074, 6075, 6076, 6077, 6078, 10927, 10928, 10929, 25315, 25221, 25222, 2006, 2010, 10880, 10881, 20770, 25435, 9484, 9485, 10955, 976, 10957, 10958, 25433, 32379, 32996, 589, 594, 970, 992, 2767, 10892, 10893, 10894, 25367, 25368, 34433, 18137, 19308, 19309, 19310, 19311, 19312, 25477, 585, 591, 598, 984, 1004, 6060, 10933, 10934, 25363, 25364, 10797, 19296, 19299, 19302, 19303, 19304, 19305, 25446, 32548, 2652, 19261, 19262, 19264, 19265, 19266, 25461, 34914, 34916, 34917, 33167, 33171, 33172, 15268, 15323, 15324, 15325, 15326, 27811, 27815, 27816, 33142, 33145, 33146, 15259, 15307, 15308, 15309, 15310, 18530, 18531, 18533, 18534, 18535, 33158, 33159, 33160, 33161, 33162, 34908, 34909, 34910, 34911, 34912, 33213, 33214, 33215, 33186, 33190, 18544, 18547, 18548, 18549, 18550, 14913, 15012, 14911, 15018, 34753, 34859, 34860, 27789, 27790, 14889, 15008, 15009, 15010, 15011, 33174, 33182, 15274, 15311, 14912, 15013, 15014, 14747, 14770, 14771, 14750, 14772, 15273, 15312, 15313, 15314, 15316, 14749, 14767, 14748, 14768, 14769, 15392, 15448, 14908, 15020, 17191, 15275, 15317, 27839, 27840, 14751, 14892, 15362, 15363, 14531, 14774, 14521, 14776, 14777, 14520, 14780, 14781, 14782, 14783, 18551, 18552, 18553, 18554, 18555, 33191, 33192, 33193, 33194, 33195, 33206, 10060, 33201, 33202, 33203, 33204, 33205, 14909, 15017, 15318, 15272, 15320, 15260, 15327, 15328, 15329, 15330, 33221, 33222, 33223, 33224, 33225, 17322, 17323, 14910, 33371, 15257, 15331, 15332, 15333, 15334, 15473, 15487, 14523, 14784, 14785, 14786, 14787, 27900, 27901, 27902, 27903, 27904, 20711, 15270, 15335, 15336, 15337, 15338, 14901, 15028, 15029, 15030, 15031, 14898, 15349, 15354, 15355, 15356, 33150, 33154, 14522, 14788, 14789, 14790, 14791, 15286, 14524, 14525, 14526, 14527, 14528);
DELETE FROM `character_spell` WHERE `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 7) AND `spell` IN (2008, 20609, 20610, 20776, 20777, 556, 2825, 1064, 10622, 10623, 25422, 25423, 421, 930, 2860, 10605, 25439, 25442, 2870, 526, 8170, 2062, 974, 32593, 32594, 8042, 8044, 8045, 8046, 10412, 10413, 10414, 25454, 2484, 6196, 2894, 1535, 8498, 8499, 11314, 11315, 25546, 25547, 8184, 10537, 10538, 25563, 8050, 8052, 8053, 10447, 10448, 29228, 25457, 8227, 8249, 10526, 16387, 25557, 8024, 8027, 8030, 16339, 16341, 16342, 25489, 8181, 10478, 10479, 25560, 8056, 8058, 10472, 10473, 25464, 8033, 8038, 10456, 16355, 16356, 25500, 2645, 8835, 10627, 25359, 8177, 5394, 6375, 6377, 10462, 10463, 25567, 331, 332, 547, 913, 939, 959, 8005, 10395, 10396, 25357, 25391, 25396, 32182, 8004, 8008, 8010, 10466, 10467, 10468, 25420, 403, 529, 548, 915, 943, 6041, 10391, 10392, 15207, 15208, 25448, 25449, 324, 325, 905, 945, 8134, 10431, 10432, 25469, 25472, 8190, 10585, 10586, 10587, 25552, 5675, 10495, 10496, 10497, 25570, 10595, 10600, 10601, 25574, 8166, 370, 8012, 20608, 8017, 8018, 8019, 10399, 16314, 16315, 16316, 25479, 25485, 3599, 6363, 6364, 6365, 10437, 10438, 25533, 6495, 36591, 5730, 6390, 6391, 6392, 10427, 10428, 25525, 8071, 8154, 8155, 10406, 10407, 10408, 25508, 25509, 8075, 8160, 8161, 10442, 25361, 25528, 36936, 25908, 8143, 131, 24398, 33736, 546, 8512, 10613, 10614, 25585, 255, 8232, 8235, 10486, 16362, 25505, 15107, 15111, 15112, 25577, 3738, 16176, 16235, 16240, 17485, 17486, 17487, 17488, 17489, 16254, 16271, 16272, 16273, 16274, 16038, 16160, 16161, 16041, 16117, 16118, 16119, 16120, 16035, 16105, 16106, 16107, 16108, 16039, 16109, 16110, 16111, 16112, 30798, 30816, 30818, 30819, 16043, 16130, 30160, 29179, 29180, 16164, 16089, 16166, 30672, 30673, 30674, 30669, 30670, 30671, 28996, 28997, 28998, 16266, 29079, 29080, 16259, 16295, 29062, 29064, 29065, 16256, 16281, 16282, 16283, 16284, 30864, 30865, 30866, 16258, 16293, 16181, 16230, 16232, 16233, 16234, 29187, 29189, 29191, 29206, 29205, 29202, 30872, 30873, 16086, 16544, 16262, 16287, 16182, 16226, 16227, 16228, 16229, 16261, 16290, 16291, 16184, 16209, 29192, 29193, 16578, 16579, 16580, 16581, 16582, 30675, 30678, 30679, 30680, 30681, 16190, 30812, 30813, 30814, 30867, 30868, 30869, 30881, 30883, 30884, 30885, 30886, 16180, 16196, 16198, 16188, 16178, 16210, 16211, 16212, 16213, 16187, 16205, 16206, 16207, 16208, 16040, 16113, 16114, 16115, 16116, 30823, 16253, 16298, 16299, 16300, 16301, 16268, 28999, 29000, 17364, 16255, 16302, 16303, 16304, 16305, 16179, 16214, 16215, 16216, 16217, 16194, 16218, 16219, 16220, 16221, 30706, 16173, 16222, 16223, 16224, 16225, 16189, 16252, 16306, 16307, 16308, 16309, 16269, 30802, 30808, 30809, 30810, 30811, 30664, 30665, 30666, 30667, 30668, 29082, 29084, 29086, 29087, 29088);
DELETE FROM `character_spell` WHERE `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 8) AND `spell` IN (1008, 8455, 10169, 10170, 27130, 33946, 30451, 23028, 27127, 1449, 8437, 8438, 8439, 10201, 10202, 27080, 27082, 1459, 1460, 1461, 10156, 10157, 27126, 7268, 5143, 5144, 7269, 5145, 7270, 8416, 8419, 8417, 8418, 10211, 10273, 10212, 10274, 25345, 25346, 27075, 27076, 38703, 38699, 38700, 38704, 11113, 13018, 13019, 13020, 13021, 27133, 33933, 1953, 10, 6141, 8427, 10185, 10186, 10187, 27085, 120, 8492, 10159, 10160, 10161, 27087, 587, 597, 990, 6129, 10144, 10145, 28612, 33717, 759, 10053, 27101, 3552, 10054, 5504, 5505, 5506, 6127, 10138, 10139, 10140, 37420, 27090, 2139, 604, 8450, 8451, 10173, 10174, 33944, 2855, 31661, 33041, 33042, 33043, 12051, 2136, 2137, 2138, 8412, 8413, 10197, 10199, 27078, 27079, 543, 8457, 8458, 10223, 10225, 27128, 133, 143, 145, 3140, 8400, 8401, 8402, 10148, 10149, 10150, 10151, 25306, 27070, 38692, 2120, 2121, 8422, 8423, 10215, 10216, 27086, 168, 7300, 7301, 122, 865, 6131, 10230, 27088, 6143, 8461, 8462, 10177, 28609, 32796, 116, 205, 837, 7322, 8406, 8407, 8408, 10179, 10180, 10181, 25304, 27071, 27072, 38697, 7302, 7320, 10219, 10220, 27124, 11426, 13031, 13032, 13033, 27134, 33405, 30455, 66, 6117, 22782, 22783, 27125, 1463, 8494, 8495, 10191, 10192, 10193, 27131, 30482, 118, 12824, 12825, 12826, 28272, 28271, 11419, 32266, 11416, 11417, 33691, 35717, 32267, 10059, 11420, 11418, 11366, 12505, 12522, 12523, 12524, 12525, 12526, 18809, 27132, 33938, 475, 2948, 8444, 8445, 8446, 10205, 10206, 10207, 27073, 27074, 130, 30449, 3565, 32271, 3562, 3567, 33690, 35715, 32272, 3561, 3566, 3563, 11213, 12574, 12575, 12576, 12577, 11222, 12839, 12840, 12841, 12842, 28574, 11242, 12467, 12469, 15058, 15059, 15060, 18462, 18463, 18464, 11232, 12500, 12501, 12502, 12503, 31571, 31572, 31573, 12042, 11210, 12592, 16757, 16758, 31674, 31675, 31676, 31677, 31678, 31641, 31642, 11083, 12351, 12472, 11129, 11115, 11367, 11368, 29438, 29439, 29440, 31579, 31582, 31583, 31656, 31657, 31658, 31659, 31660, 31682, 31683, 31684, 31685, 31686, 11124, 12378, 12398, 12399, 12400, 11100, 12353, 11160, 12518, 12519, 11189, 28332, 11071, 12496, 12497, 31667, 31668, 31669, 11958, 31670, 31672, 11207, 12672, 15047, 15052, 15053, 11119, 11120, 12846, 12847, 12848, 11103, 12357, 12358, 12359, 12360, 11237, 12463, 12464, 16769, 16770, 31569, 31570, 11185, 12487, 12488, 11190, 12489, 12490, 11255, 12598, 11078, 11080, 12342, 11094, 13043, 11069, 12338, 12339, 12340, 12341, 11108, 12349, 12350, 11165, 12475, 11070, 12473, 16763, 16765, 16766, 11252, 12605, 11095, 12872, 12873, 18459, 18460, 29441, 29444, 29445, 29446, 29447, 11247, 12606, 29074, 29075, 29076, 31584, 31585, 31586, 31587, 31588, 31679, 31680, 11175, 12569, 12571, 11151, 12952, 12953, 31638, 31639, 31640, 12043, 31574, 31575, 34293, 34295, 34296, 11170, 12982, 12983, 12984, 12985, 31589, 35578, 35581, 31687, 6057, 6085, 11180, 28592, 28593, 28594, 28595);
DELETE FROM `character_spell` WHERE `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 9) AND `spell` IN (710, 18647, 17962, 18930, 18931, 18932, 27266, 30912, 172, 6222, 6223, 7648, 11671, 11672, 25311, 27216, 6366, 17951, 17952, 17953, 27250, 6201, 6202, 5699, 11729, 11730, 27230, 693, 20752, 20755, 20756, 20757, 27238, 2362, 17727, 17728, 28172, 980, 1014, 6217, 11711, 11712, 11713, 27218, 603, 30910, 704, 7658, 7659, 11717, 27226, 17862, 17937, 27229, 1490, 11721, 11722, 27228, 1714, 11719, 702, 1108, 6205, 7646, 11707, 11708, 27224, 30909, 18220, 18937, 18938, 27265, 6789, 17925, 17926, 27223, 706, 1086, 11733, 11734, 11735, 27260, 687, 696, 132, 689, 699, 709, 7651, 11699, 11700, 27219, 27220, 5138, 6226, 11703, 11704, 27221, 30908, 1120, 8288, 8289, 11675, 27217, 1098, 11725, 11726, 126, 5782, 6213, 6215, 28176, 28189, 755, 3698, 3699, 3700, 11693, 11694, 11695, 27259, 1949, 11683, 11684, 27213, 5484, 17928, 348, 707, 1094, 2941, 11665, 11667, 11668, 25309, 27215, 29722, 32231, 1122, 1454, 1455, 1456, 11687, 11688, 11689, 27222, 5740, 6219, 11677, 11678, 27212, 18540, 29893, 698, 5676, 17919, 17920, 17921, 17922, 17923, 27210, 30459, 27243, 5500, 686, 695, 705, 1088, 1106, 7641, 11659, 11660, 11661, 25307, 27209, 6229, 11739, 11740, 28610, 17877, 18867, 18868, 18869, 18870, 18871, 27263, 30546, 30283, 30413, 30414, 18265, 18879, 18880, 18881, 27264, 30911, 6353, 17924, 27211, 30545, 29858, 23161, 691, 5784, 688, 712, 697, 5697, 30108, 30404, 30405, 18119, 18120, 18121, 18122, 18123, 18288, 34935, 34938, 34939, 17788, 17789, 17790, 17791, 17792, 17778, 17779, 17780, 17781, 17782, 30060, 30061, 30062, 30063, 30064, 18223, 30143, 30144, 30145, 18697, 18698, 18699, 18700, 18701, 35691, 35692, 35693, 30319, 30320, 30321, 18788, 30242, 30245, 30246, 30247, 30248, 17917, 17918, 18130, 18131, 18132, 18133, 18134, 17954, 17955, 17956, 17957, 17958, 32381, 32382, 32383, 17783, 17784, 17785, 17786, 17787, 18708, 18731, 18743, 18744, 18748, 18749, 18750, 18218, 18219, 17810, 17811, 17812, 17813, 17814, 18827, 18829, 18179, 18180, 18213, 18372, 18821, 18822, 18126, 18127, 18703, 18704, 18692, 18693, 30054, 30057, 17815, 17833, 17834, 17835, 17836, 18694, 18695, 18696, 18128, 18129, 18182, 18183, 17927, 17929, 17930, 17793, 17796, 17801, 17802, 17803, 18754, 18755, 18756, 18705, 18706, 18707, 18135, 18136, 32477, 32483, 32484, 30326, 30327, 30328, 18767, 18768, 23785, 23822, 23823, 23824, 23825, 18709, 18710, 30299, 30301, 30302, 18094, 18095, 18096, 18073, 17959, 30288, 30289, 30290, 30291, 30292, 32385, 32387, 32392, 32393, 32394, 18271, 18272, 18273, 18274, 18275, 30293, 30295, 30296, 19028, 17804, 17805, 30146, 18174, 18175, 18176, 18177, 18178, 18769, 18770, 18771, 18772, 18773);
DELETE FROM `character_spell` WHERE `guid` IN (SELECT `guid` FROM `character` WHERE `class` <> 11) AND `spell` IN (2893, 1066, 22812, 5211, 6798, 8983, 5487, 768, 5209, 1082, 3029, 5201, 9849, 9850, 27000, 8998, 9000, 9892, 31709, 27004, 8946, 33786, 1850, 9821, 33357, 99, 1735, 9490, 9747, 9898, 26998, 9634, 5229, 339, 1062, 5195, 5196, 9852, 9853, 26989, 770, 778, 9749, 9907, 26993, 16857, 17390, 17391, 17392, 27011, 20719, 22568, 22827, 22828, 22829, 31018, 24248, 33943, 22842, 22895, 22896, 26999, 21849, 21850, 26991, 6795, 5185, 5186, 5187, 5188, 5189, 6778, 8903, 9758, 9888, 9889, 25297, 26978, 26979, 2637, 18657, 18658, 16914, 17401, 17402, 27012, 29166, 5570, 24974, 24975, 24976, 24977, 27013, 33745, 33763, 22570, 33878, 33986, 33987, 33876, 33982, 33983, 1126, 5232, 6756, 5234, 8907, 9884, 9885, 26990, 6807, 6808, 6809, 8972, 9745, 9880, 9881, 26996, 8921, 8924, 8925, 8926, 8927, 8928, 8929, 9833, 9834, 9835, 26987, 26988, 16689, 16810, 16811, 16812, 16813, 17329, 27009, 9005, 9823, 9827, 27006, 5215, 6783, 9913, 1822, 1823, 1824, 9904, 27003, 6785, 6787, 9866, 9867, 27005, 20484, 20739, 20742, 20747, 20748, 26994, 8936, 8938, 8939, 8940, 8941, 9750, 9856, 9857, 9858, 26980, 774, 1058, 1430, 2090, 2091, 3627, 8910, 9839, 9840, 9841, 25299, 26981, 26982, 2782, 1079, 9492, 9493, 9752, 9894, 9896, 27008, 5221, 6800, 8992, 9829, 9830, 27001, 27002, 2908, 8955, 9901, 26995, 2912, 8949, 8950, 8951, 9875, 9876, 25298, 26986, 40120, 780, 769, 9754, 9908, 26997, 18960, 467, 782, 1075, 8914, 9756, 9910, 26992, 5217, 6793, 9845, 9846, 5225, 740, 8918, 9862, 9863, 26983, 783, 5176, 5177, 5178, 5179, 5180, 6780, 8905, 9912, 26984, 26985, 33592, 33596, 16836, 16839, 16840, 16940, 16941, 16850, 16923, 16924, 16918, 16919, 16920, 33597, 33599, 33956, 33886, 33887, 33888, 33889, 33890, 33879, 33880, 16858, 16859, 16860, 16861, 16862, 16979, 16947, 16948, 16949, 17002, 24866, 16934, 16935, 16936, 16937, 16938, 35363, 35364, 33831, 17056, 17058, 17059, 17060, 17061, 17104, 24943, 24944, 24945, 24946, 17003, 17004, 17005, 17006, 24894, 33600, 33601, 33602, 34297, 34300, 17050, 17051, 17053, 17054, 17055, 16821, 16822, 17245, 17247, 17248, 17249, 17074, 17075, 17076, 17077, 17078, 17111, 17112, 17113, 17123, 17124, 17106, 17107, 17108, 17007, 34151, 34152, 34153, 33589, 33590, 33591, 33917, 16896, 16897, 16899, 16900, 16901, 16845, 16846, 16847, 24858, 33881, 33882, 33883, 16833, 16834, 16835, 17069, 17070, 17071, 17072, 17073, 17063, 17065, 17066, 17067, 17068, 16880, 16819, 16820, 17116, 33872, 33873, 16864, 33859, 33866, 33867, 33868, 33869, 16972, 16974, 16975, 37116, 37117, 33851, 33852, 33957, 16998, 16999, 16942, 16943, 16944, 16966, 16968, 16814, 16815, 16816, 16817, 16818, 17118, 17119, 17120, 17121, 17122, 33853, 33855, 33856, 18562, 16929, 16930, 16931, 24968, 24969, 24970, 24971, 24972, 33891, 16909, 16910, 16911, 16912, 16913, 33603, 33604, 33605, 33606, 33607);

-- plate
DELETE FROM `character_spell` WHERE spell = 750 AND `guid` IN (SELECT guid FROM `character` Where `class` NOT IN ('1','2'));
-- Mail
DELETE FROM `character_spell` WHERE spell = 8737 AND `guid` IN (SELECT guid FROM `character` Where `class` NOT IN ('1','2', '3', '7'));
-- Leather
DELETE FROM `character_spell` WHERE spell = 9077 AND `guid` IN (SELECT guid FROM `character` Where `class` NOT IN ('1','2', '3', '4', '7', '11'));

DELETE FROM character_spell WHERE spell in (12774, 34082, 22838, 21866, 25201, 42004, 10347, 12348, 31517, 30227, 30684, 30677, 30685, 29945, 30676, 25107, 43450, 43492, 43755, 43663, 42424, 44368, 43714, 43660, 43662, 42146, 25861, 36795, 30417, 30418, 6119, 6123, 21268, 22108, 22109, 22110, 22111, 22112, 22113, 21338, 21356, 21731, 21368, 21078, 21372, 21465, 21464, 21542, 21541, 21732, 22114, 22115, 22116, 22117, 22118, 22119, 21734, 21735, 30473, 16510, 21654, 28287, 21398, 23059, 23019, 8609, 32094, 27583, 32260, 42010, 23107, 25846, 41560, 41457, 108, 32476, 23949, 23950, 2585, 25170, 2110, 36727, 39303, 43090, 21794, 39401, 33316, 25105, 8596, 32475, 44501, 23053, 27360, 29297, 29279, 21649, 39398, 39400, 20466, 24727, 3440, 38721, 31337, 31377, 31336, 21051, 25172, 28688, 21052, 27879, 27881, 27882, 29294, 29295, 24256, 24279, 24280, 24282, 24285, 24284, 24281, 24288, 24289, 24290, 22969, 33380, 28355, 25106, 43535, 27824, 20514, 6431, 10263, 6606, 14915, 16093, 6844, 24184, 29533, 40359, 40384, 39993, 25148, 31951, 31954, 25182, 30428, 22207, 23159, 27190, 27191, 27201, 28872, 27202, 27184, 23176, 32318, 27517, 31335, 27203, 21391, 21397, 23460, 29190, 25139, 29181, 24653, 32025, 36325, 30431, 25171, 31929, 30882, 32073, 29479, 21653, 20465, 20358, 30446, 21896, 43345, 43347, 43346, 38586, 38998, 38996, 38992, 39163, 38997, 39225, 39377, 39185,5301, 13358, 16092);

DELETE FROM character_spell WHERE spell in (2916, 4810, 4811, 4644, 4637, 4659, 4660, 4651, 4652, 4675, 4676, 4667, 4668, 4694, 4695, 4687, 4688, 5948, 5944, 4712, 4713, 4705, 4706, 42507, 8298, 32420, 25697, 40154, 40890, 40905, 2598, 2381, 3653, 3652, 27035, 4730, 4723, 3, 1, 50);

A

http://filebeam.com/96f3c4b94871c78da96d0aa5751c4609

http://filebeam.com/763aedd8e5e1e570b8bf074b43e8fa9d

charlie · October 1, 2007

Smaze veskera Data Character neexistujich charu atd.. proste udela poradek v characterech

DELETE FROM `character` WHERE ( `account` ) NOT IN ( SELECT id FROM `account` );

/*Cleaning not needed Guid character (delete or other bug)*/
DELETE FROM `auctionhouse` WHERE auctioneerguid NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_action` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_homebind` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_aura` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_instance` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_inventory` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_queststatus` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_reputation` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_social` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_spell` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_spell_cooldown` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_tutorial` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_kill` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_pet` WHERE ( owner ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `corpse` WHERE ( player ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `guild` WHERE ( leaderguid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `guild_rank` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_member` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `guild_member` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `petition` WHERE (ownerguid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `petition_sign` WHERE (ownerguid ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `character_inventory` WHERE item_template NOT IN (SELECT entry FROM item_template );
DELETE FROM `character_inventory` WHERE item NOT IN (SELECT guid FROM item_instance );
DELETE FROM `petition` WHERE ownerguid NOT IN (SELECT guid FROM `character` );
DELETE FROM `petition_sign` WHERE ownerguid NOT IN (SELECT guid FROM `character` );
DELETE FROM `petition_sign` WHERE playerguid NOT IN (SELECT guid FROM `character` );
DELETE FROM `petition_sign` WHERE petitionguid NOT IN (SELECT petitionguid FROM `petition` );
DELETE FROM `group` WHERE leaderguid NOT IN (SELECT guid FROM `character` );
DELETE FROM `group_member` WHERE leaderguid NOT IN (SELECT guid FROM `character` );
DELETE FROM `group_member` WHERE memberguid NOT IN (SELECT guid FROM `character` );
DELETE FROM `arena_team` WHERE captainguid NOT IN (SELECT guid FROM `character` );
DELETE FROM `arena_team_member` WHERE arenateamid NOT IN (SELECT arenateamid FROM `arena_team` );
DELETE FROM `arena_team_member` WHERE guid NOT IN (SELECT guid FROM `character` );
DELETE FROM `arena_team_stats` WHERE arenateamid NOT IN (SELECT arenateamid FROM `arena_team` );
DELETE FROM `looking_for_group` WHERE ( guid ) NOT IN ( SELECT guid FROM `character` );

DELETE FROM `mail` WHERE ( receiver ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `mail` WHERE ( sender ) NOT IN ( SELECT guid FROM `character` );
DELETE FROM `item_instance` WHERE ( owner_guid ) NOT IN ( SELECT guid FROM `character` );

/*Remove all BIG GUID FROM character and update the DATA row with a lower guid*/
ALTER TABLE `character` CHANGE `guid` `guid` INT( 11 ) UNSIGNED NOT NULL COMMENT 'Global Unique Identifier';
ALTER TABLE `character` DROP PRIMARY KEY;
ALTER TABLE `character` AUTO_INCREMENT = 1;
ALTER TABLE `character` ADD `guid_new` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Global Unique Identifier FIRST;
UPDATE `character` SET `data`=CONCAT(`guid_new`,' ',right(data,length(data)-length(substring_index(data,' ',1))-1));

/*Auction House*/
UPDATE `auctionhouse`, `character` SET `auctionhouse`.`buyguid` = `character`.`guid_new` WHERE `auctionhouse`.`buyguid` = `character`.`guid`;
UPDATE `auctionhouse`, `character` SET `auctionhouse`.`itemowner` = `character`.`guid_new` WHERE `auctionhouse`.`itemowner` = `character`.`guid`;

/*Character Action*/
ALTER TABLE `character_action` ADD COLUMN `guid2` INTEGER AFTER `guid`;
UPDATE `character_action` SET guid2=guid;
UPDATE `character_action`, `character` SET `character_action`.`guid2` = `character`.`guid_new` WHERE `character_action`.`guid` = `character`.`guid`;
ALTER TABLE `character_action`
DROP PRIMARY KEY,
DROP COLUMN `guid`,
CHANGE COLUMN `guid2` `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
ADD PRIMARY KEY(`guid`,`button`);

/*Character Aura*/
ALTER TABLE `character_aura` ADD COLUMN `guid2` INTEGER AFTER `guid`;
UPDATE `character_aura` SET guid2=guid;
UPDATE `character_aura`, `character` SET `character_aura`.`guid2` = `character`.`guid_new` WHERE `character_aura`.`guid` = `character`.`guid`;
UPDATE `character_aura`, `character` SET `character_aura`.`caster_guid` = `character`.`guid_new` WHERE `character_aura`.`caster_guid` = `character`.`guid`;
ALTER TABLE `character_aura`
DROP PRIMARY KEY,
DROP COLUMN `guid`,
CHANGE COLUMN `guid2` `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
ADD PRIMARY KEY (`guid`,`spell`,`effect_index`);

/*Character Gift*/
UPDATE `character_gifts`, `character` SET `character_gifts`.`guid` = `character`.`guid_new` WHERE `character_gifts`.`guid` = `character`.`guid`;

/*Character Homebind*/
ALTER TABLE `character_homebind` ADD COLUMN `guid2` INTEGER AFTER `guid`;
UPDATE `character_homebind` SET guid2=guid;
UPDATE `character_homebind`, `character` SET `character_homebind`.`guid2` = `character`.`guid_new` WHERE `character_homebind`.`guid` = `character`.`guid`;
ALTER TABLE `character_homebind`
DROP PRIMARY KEY,
DROP COLUMN `guid`,
CHANGE COLUMN `guid2` `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
ADD PRIMARY KEY(`guid`);


/*Character Instance*/
UPDATE `character_instance`, `character` SET `character_instance`.`leader` = `character`.`guid_new` WHERE `character_instance`.`leader` = `character`.`guid`;
ALTER TABLE `character_instance` ADD COLUMN `guid2` INTEGER AFTER `guid`;
UPDATE `character_instance` SET guid2=guid;
UPDATE `character_instance`, `character` SET `character_instance`.`guid2` = `character`.`guid_new` WHERE `character_instance`.`guid` = `character`.`guid`;
ALTER TABLE `character_instance`
DROP PRIMARY KEY,
DROP COLUMN `guid`,
CHANGE COLUMN `guid2` `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
ADD PRIMARY KEY(`guid`,`map`);

/*Character Inventory*/
UPDATE `character_inventory`, `character` SET `character_inventory`.`guid` = `character`.`guid_new` WHERE `character_inventory`.`guid` = `character`.`guid`;

/*Character Quest Status*/
ALTER TABLE `character_queststatus` ADD COLUMN `guid2` INTEGER AFTER `guid`;
UPDATE `character_queststatus` SET guid2=guid;
UPDATE `character_queststatus`, `character` SET `character_queststatus`.`guid2` = `character`.`guid_new` WHERE `character_queststatus`.`guid` = `character`.`guid`;
ALTER TABLE `character_queststatus`
DROP PRIMARY KEY,
DROP COLUMN `guid`,
CHANGE COLUMN `guid2` `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
ADD PRIMARY KEY(`guid`,`quest`);

/*Character Spell*/
ALTER TABLE `character_spell` ADD COLUMN `guid2` INTEGER AFTER `guid`;
UPDATE `character_spell` SET guid2=guid;
UPDATE `character_spell`, `character` SET `character_spell`.`guid2` = `character`.`guid_new` WHERE `character_spell`.`guid` = `character`.`guid`;
ALTER TABLE `character_spell`
DROP PRIMARY KEY,
DROP COLUMN `guid`,
CHANGE COLUMN `guid2` `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
ADD PRIMARY KEY(`guid`,`spell`);

/*Character Spell Cooldown*/
ALTER TABLE `character_spell_cooldown` ADD COLUMN `guid2` INTEGER AFTER `guid`;
UPDATE `character_spell_cooldown` SET guid2=guid;
UPDATE `character_spell_cooldown`, `character` SET `character_spell_cooldown`.`guid2` = `character`.`guid_new` WHERE `character_spell_cooldown`.`guid` = `character`.`guid`;
ALTER TABLE `character_spell_cooldown`
DROP PRIMARY KEY,
DROP COLUMN `guid`,
CHANGE COLUMN `guid2` `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
ADD PRIMARY KEY(`guid`,`spell`);

/*Character Ticket*/
UPDATE `character_ticket`, `character` SET `character_ticket`.`guid` = `character`.`guid_new` WHERE `character_ticket`.`guid` = `character`.`guid`;

/*Character Tutorial*/
ALTER TABLE `character_tutorial` ADD COLUMN `guid2` INTEGER AFTER `guid`;
UPDATE `character_tutorial` SET guid2=guid;
UPDATE `character_tutorial`, `character` SET `character_tutorial`.`guid2` = `character`.`guid_new` WHERE `character_tutorial`.`guid` = `character`.`guid`;ALTER TABLE `character_tutorial`
DROP PRIMARY KEY,
DROP COLUMN `guid`,
CHANGE COLUMN `guid2` `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
ADD PRIMARY KEY(`guid`);

/*Character Guild Member*/
UPDATE `guild_member`, `character` SET `guild_member`.`guid` = `character`.`guid_new` WHERE `guild_member`.`guid` = `character`.`guid`;

/*Character Arena Team member*/
UPDATE `arena_team_member`, `character` SET `arena_team_member`.`guid` = `character`.`guid_new` WHERE `arena_team_member`.`guid` = `character`.`guid`;

/*Character Social*/
ALTER TABLE `character_social` ADD COLUMN `guid2` INTEGER AFTER `guid`;
ALTER TABLE `character_social` ADD COLUMN `friend2` INTEGER AFTER `friend`;
UPDATE `character_social` SET guid2=guid;
UPDATE `character_social` SET friend2=friend;
UPDATE `character_social`, `character` SET `character_social`.`guid2` = `character`.`guid_new` WHERE `character_social`.`guid` = `character`.`guid`;
UPDATE `character_social`, `character` SET `character_social`.`friend2` = `character`.`guid_new` WHERE `character_social`.`friend` = `character`.`guid`;
ALTER TABLE `character_social`
DROP PRIMARY KEY,
DROP COLUMN `guid`,
DROP COLUMN `friend`,
CHANGE COLUMN `guid2` `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
CHANGE COLUMN `friend2` `friend` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Character Unique Identifier',
ADD PRIMARY KEY(`guid`,`friend`,`flags`);

/*Character Kill*/
ALTER TABLE `character_kill` ADD COLUMN `guid2` INTEGER AFTER `guid`;
ALTER TABLE `character_kill` ADD COLUMN `victim_guid2` INTEGER AFTER `victim_guid`;
UPDATE `character_kill` SET guid2=guid;
UPDATE `character_kill` SET victim_guid2=victim_guid;
ALTER TABLE `character_kill` DROP PRIMARY KEY;
UPDATE `character_kill`, `character` SET `character_kill`.`guid2` = `character`.`guid_new` WHERE `character_kill`.`guid` = `character`.`guid`;
UPDATE `character_kill`, `character` SET `character_kill`.`victim_guid2` = `character`.`guid_new` WHERE `character_kill`.`victim_guid` = `character`.`guid`;
ALTER TABLE `character_kill` DROP COLUMN `guid`;
ALTER TABLE `character_kill` DROP COLUMN `victim_guid`;
ALTER TABLE `character_kill` CHANGE COLUMN `victim_guid2` `victim_guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier';
ALTER TABLE `character_kill` CHANGE COLUMN `guid2` `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier';
ALTER TABLE `character_kill` ADD PRIMARY KEY(`guid`,`victim_guid`);

/*Character Pet*/
UPDATE `character_pet`, `character` SET `character_pet`.`owner` = `character`.`guid_new` WHERE `character_pet`.`owner` = `character`.`guid`;

/*Character Corpse*/
UPDATE `corpse`, `character` SET `corpse`.`player` = `character`.`guid_new` WHERE `corpse`.`player` = `character`.`guid`;

/*Character Guild*/
UPDATE `guild`, `character` SET `guild`.`leaderguid` = `character`.`guid_new` WHERE `guild`.`leaderguid` = `character`.`guid`;

/*Character Mail*/
UPDATE `mail`, `character` SET `mail`.`sender` = `character`.`guid_new` WHERE `mail`.`sender` = `character`.`guid`;
UPDATE `mail`, `character` SET `mail`.`receiver` = `character`.`guid_new` WHERE `mail`.`receiver` = `character`.`guid`;

/*Character Petition*/
ALTER TABLE `petition` ADD COLUMN `ownerguid2` INTEGER AFTER `ownerguid`;
UPDATE `petition` SET ownerguid2=ownerguid;
UPDATE `petition`, `character` SET `petition`.`ownerguid2` = `character`.`guid_new` WHERE `petition`.`ownerguid` = `character`.`guid`;
ALTER TABLE `petition`
DROP PRIMARY KEY,
DROP COLUMN `ownerguid`,
CHANGE COLUMN `ownerguid2` `ownerguid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
ADD PRIMARY KEY(`ownerguid`);

/*Character Petition Sign*/
UPDATE `petition_sign`, `character` SET `petition_sign`.`ownerguid` = `character`.`guid_new` WHERE `petition_sign`.`ownerguid` = `character`.`guid`;
ALTER TABLE `petition_sign` ADD COLUMN `playerguid2` INTEGER AFTER `playerguid`;
UPDATE `petition_sign` SET playerguid2=playerguid;
UPDATE `petition_sign`, `character` SET `petition_sign`.`playerguid2` = `character`.`guid_new` WHERE `petition_sign`.`playerguid` = `character`.`guid`;
ALTER TABLE `petition_sign`
DROP PRIMARY KEY,
DROP COLUMN `playerguid`,
CHANGE COLUMN `playerguid2` `playerguid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
ADD PRIMARY KEY(`petitionguid`,`playerguid`);

/*Character Arena Team*/
UPDATE `arena_team`, `character` SET `arena_team`.`captainguid` = `character`.`guid_new` WHERE `arena_team`.`captainguid` = `character`.`guid`;

/*Character Group*/
ALTER TABLE `group` ADD COLUMN `leaderguid2` INTEGER AFTER `leaderguid`;
UPDATE `group` SET leaderguid2=leaderguid;
UPDATE `group`, `character` SET `group`.`leaderguid2` = `character`.`guid_new` WHERE `group`.`leaderguid` = `character`.`guid`;
ALTER TABLE `group`
DROP PRIMARY KEY,
DROP COLUMN `leaderguid`,
CHANGE COLUMN `leaderguid2` `leaderguid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
ADD PRIMARY KEY(`leaderguid`);

/*Character Group Member*/
ALTER TABLE `group_member` ADD COLUMN `leaderGuid2` INTEGER AFTER `leaderGuid`;
ALTER TABLE `group_member` ADD COLUMN `memberGuid2` INTEGER AFTER `memberGuid`;
UPDATE `group_member` SET leaderGuid2=leaderGuid;
UPDATE `group_member` SET memberGuid2=memberGuid;
ALTER TABLE `group_member` DROP PRIMARY KEY;
UPDATE `group_member`, `character` SET `group_member`.`leaderGuid2` = `character`.`guid_new` WHERE `group_member`.`leaderGuid` = `character`.`guid`;
UPDATE `group_member`, `character` SET `group_member`.`memberGuid2` = `character`.`guid_new` WHERE `group_member`.`memberGuid` = `character`.`guid`;
ALTER TABLE `group_member` DROP COLUMN `leaderGuid`;
ALTER TABLE `group_member` DROP COLUMN `memberGuid`;
ALTER TABLE `group_member` CHANGE COLUMN `leaderGuid2` `leaderGuid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier';
ALTER TABLE `group_member` CHANGE COLUMN `memberGuid2` `memberGuid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier';
ALTER TABLE `group_member` ADD PRIMARY KEY(`leaderGuid`,`memberGuid`);

/*Item instance*/
UPDATE `item_instance`, `character` SET `item_instance`.`owner_guid` = `character`.`guid_new` WHERE `item_instance`.`owner_guid` = `character`.`guid`;
UPDATE `item_instance` SET `data`=CONCAT(substring_index(data,' ',6),' ',`owner_guid`,' ',right(data,length(data)-length(substring_index(data,' ',7))-1));

/*recover GUID character*/
ALTER TABLE `character` DROP `guid`;
ALTER TABLE `character` CHANGE `guid_new` `guid` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Global Unique Identifier';

Netestovano, pouzivejte na vasi zodpovednost... testnu to a pak napisu report

Benny · October 2, 2007

Ja uz to testoval, smazlo to par kouzel co nemelo ale jinak v pohode. Neni problem se je doucit znova u traineru

charlie · October 2, 2007

Takze, k reportum : ten spell clean funguje dost dobre. Ale ten posledni to precislovani funguje strasne.. nejen ze sou chyby primo v SQL syntaxi toho cleanupu, ale vseobecne to precislovani neni dokonaly. Myslenka je dobra, ale kdyz sem po tomto cleanu zapnul server vsici byly bez vseho jestli chapete.

Ibalgin7756 · January 16, 2010

nemate nekdo fix na grouding . ?

STONERSVK · January 16, 2010

nemate nekdo fix na grouding . ?

proc ozivujes 3 roky starej post :lol:

Wladass · January 16, 2010

proc ozivujes 3 roky starej post

Aby měl 5 postů a mohl spamovat hulákadlo

Sign In

Announcements

Pravidlo pro postování v TTT

Seznam návodů a důležitých témat v této sekci

Příspěvky tam, kde nemají co dělat

Používání spoilerů

CleanUP + Fix DB

Recommended Posts

charlie 13

Share this post

Link to post

Share on other sites

charlie 13

Share this post

Link to post

Share on other sites

charlie 13

Share this post

Link to post

Share on other sites

charlie 13

Share this post

Link to post

Share on other sites

Benny 1

Share this post

Link to post

Share on other sites

charlie 13

Share this post

Link to post

Share on other sites

Ibalgin7756 3

Share this post

Link to post

Share on other sites

STONERSVK 899

Share this post

Link to post

Share on other sites

Wladass 1216

Share this post

Link to post

Share on other sites

Browse

More