ůdržba MaNGOS databáze
Z WoWResource Wiki
MaNGOS popužívá k uchování dat databázi. Jednou z podporovaných databází je zdarma dostupná databáze MySQL. Jedná se o relační databázi, která uchovává data v tabulkách, které dále obsahují řádky a sloupce. Tabulky jsou různě vzájemně provázané a vazby jsou zde řešeny pomocí identifikačních čísel zkráceně označovaných jako ID. Při selhání emulátoru nebo celého serveru však může, a taky dochází k porušení těchto vazeb. Po delší době provozu je pak databáze plná vadných odkazů na položky, které už neexistují nebo naopak obsahuje samostatné položky, na které není odnikud ukazováno. Mimo tyto základní chyby v integritě daty může databáze obsahovat také řádky, které obsahují data jako např. čísla, které jsou mimo rozsah povolených hodnot nebo jsou jinak porušené přímo obsahy buněk.
Všechny tyto problémy nelze jednoduše řešit, protože emulátor MaNGOS se neustále vyvíjí a většina free serverů nepoužívá oficiálně uvolněné verze, které vycházejí jednou za čas, ale přímo verze stahované z SVN repozitáře. Pak dochází k dost častým změnám ve struktuře tabulek a je problém udržovat aktualizovaný nástroj pro číštění databáze.
Většina těchto problémů nemá přímý vliv na funkčnost emulátoru, ale přinejmenším způsobují opakované výpisování mnoha chyb do log souboru. Ovšem po po roce provozu serveru je databáze plná už nepoužívaných dat a pročištění databáze může zlepšit v malé míře i rychlost načítání dat z databáze.
Blacks database backup & cleaning tool
K promazání nadbytečných položek v databází lze s výhodou použít připravené SQL skripty. Tento skript se postará o následující:
- Smaže všechny herní účty starší než zvolený datum (potřeba zadat).
- Smaže všechny prázdné herní účty.
- Maže nežádoucí kouzla a řeší chyby "WORLD: unknown spell id xxx"
- Maže porušené vazby mezi tabulkami
Ke stažení verze pro Windows v podobě BAT souboru: Clean_Backup_tool.rar
K dispozici také varianta jako SQL soubor. Před provedením vždy nejdřív databázi zazálohujte. Tento skript nemusí fungovat správně pro nové verze MaNGOSu, jelikož se může změnit struktura a vazby tabulek.
DELETE FROM `realmd`.`account` WHERE `locked` = '1';
DELETE FROM `realmd`.`account` WHERE `last_login` < '2006-12-31 00:00:00';
DELETE FROM `realmd`.`account` where `id` NOT IN (SELECT `account` FROM `characters`.`characters`);
DELETE FROM `characters`.`characters` WHERE `account` NOT IN (SELECT `id` FROM `realmd`.`account`);
DELETE FROM `characters`.`character_spell` WHERE spell in (12774, 15569, 16313, 18350, 34082, 18350, 16311, 11920, 9127, 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, 27251, 18350, 5302, 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, 11920, 18350, 10400, 34084, 18350, 24948, 23192, 9127, 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);
DELETE FROM `characters`.`character_action` WHERE `characters`.`character_action`.guid NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.characters );
DELETE FROM `characters`.`character_homebind` WHERE `characters`.`character_homebind`.guid NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.characters );
DELETE FROM `characters`.`character_aura` WHERE `characters`.`character_aura`.guid NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.characters );
DELETE FROM `characters`.`character_instance` WHERE `characters`.`character_instance`.guid NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.characters );
DELETE FROM `characters`.`character_inventory` WHERE `characters`.`character_inventory`.guid NOT IN ( SELECT `characters`.`characters`.`guid` FROM `characters`.characters );
DELETE FROM `characters`.`character_inventory` WHERE `characters`.`character_inventory`.`item_template` NOT IN ( SELECT `mangos`.`item_template`.`entry` FROM `mangos`.`item_template`);
DELETE FROM `characters`.`character_inventory` WHERE `characters`.`character_inventory`.`item` NOT IN (SELECT `characters`.`item_instance`.`guid` FROM `characters`.`item_instance`);
DELETE FROM `characters`.`character_queststatus` WHERE `characters`.`character_queststatus`.`guid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`character_reputation` WHERE `characters`.`character_reputation`.`guid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`character_social` WHERE `characters`.`character_social`.`guid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`character_spell` WHERE `characters`.`character_spell`.`guid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`character_spell_cooldown` WHERE `characters`.`character_spell_cooldown`.`guid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`character_tutorial` WHERE `characters`.`character_tutorial`.`guid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`character_kill` WHERE `characters`.`character_kill`.`guid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`character_pet` WHERE `characters`.`character_pet`.`owner` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`character_inventory` WHERE guid NOT IN (SELECT guid FROM `characters`.`characters`) AND guid!=0;
DELETE FROM `characters`.`character_inventory` WHERE item NOT IN (SELECT guid FROM `characters`.`item_instance`) AND item!=0;
DELETE FROM `characters`.`character_spell` WHERE spell = '107' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','2','7'));
DELETE FROM `characters`.`character_spell` WHERE spell = '264' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','3','4'));
DELETE FROM `characters`.`character_spell` WHERE spell = '5011' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','3','4'));
DELETE FROM `characters`.`character_spell` WHERE spell = '1180' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','3','4','5','7','8','9','11'));
DELETE FROM `characters`.`character_spell` WHERE spell = '674' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','3','4','7'));
DELETE FROM `characters`.`character_spell` WHERE spell = '15590' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','3','4','7','11'));
DELETE FROM `characters`.`character_spell` WHERE spell = '266' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','3','4'));
DELETE FROM `characters`.`character_spell` WHERE spell = '196' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','2','3','7'));DELETE FROM `characters`.`character_spell` WHERE spell = '198' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','2','4','5','7','11'));
DELETE FROM `characters`.`character_spell` WHERE spell = '201' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','2','3','4','8','9'));
DELETE FROM `characters`.`character_spell` WHERE spell = '200' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','2','3'));
DELETE FROM `characters`.`character_spell` WHERE spell = '3018' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','3','4'));
DELETE FROM `characters`.`character_spell` WHERE spell = '5019' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('5','8','9'));
DELETE FROM `characters`.`character_spell` WHERE spell = '227' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','3','5','7','8','9','11'));
DELETE FROM `characters`.`character_spell` WHERE spell = '2567' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','3','4'));
DELETE FROM `characters`.`character_spell` WHERE spell = '197' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','2','3','7'));
DELETE FROM `characters`.`character_spell` WHERE spell = '199' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','2','7','11'));
DELETE FROM `characters`.`character_spell` WHERE spell = '202' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('1','2','3'));DELETE FROM `characters`.`character_spell` WHERE spell = '5009' AND `guid` IN (SELECT guid FROM `characters`.`characters` Where `class` NOT IN ('5','8','9'));
DELETE FROM `characters`.`item_instance` WHERE `characters`.`item_instance`.`owner_guid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`item_instance` WHERE guid NOT IN(SELECT item FROM `characters`.`character_inventory`) AND guid NOT IN(SELECT item_guid FROM `characters`.`mail_items`) AND guid NOT IN(SELECT itemguid FROM `characters`.`auctionhouse`);
DELETE FROM `characters`.`arena_team` WHERE `characters`.`arena_team`.`captainguid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`arena_team_member` WHERE `characters`.`arena_team_member`.`arenateamid` NOT IN (SELECT `characters`.`arena_team`.`arenateamid` FROM `characters`.`arena_team`);
DELETE FROM `characters`.`arena_team_member` WHERE `characters`.`arena_team_member`.`guid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`arena_team_stats` WHERE `characters`.`arena_team_stats`.`arenateamid` NOT IN (SELECT `characters`.`arena_team`.`arenateamid` FROM `characters`.`arena_team`);
DELETE FROM `characters`.`guild` WHERE `characters`.`guild`.`leaderguid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`guild_rank` WHERE `characters`.`guild_rank`.`guildid` NOT IN (SELECT `characters`.`guild`.`guildid` FROM `characters`.`guild`);
DELETE FROM `characters`.`guild_member` WHERE `characters`.`guild_member`.`guid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
DELETE FROM `characters`.`guild_member` WHERE `characters`.`guild_member`.`guid` NOT IN (SELECT `characters`.`characters`.`guid` FROM `characters`.`characters`);
TRUNCATE TABLE `characters`.`group`;
TRUNCATE TABLE `characters`.`group_member`;
DELETE FROM `characters`.`mail_items` WHERE item_guid NOT IN (SELECT guid FROM `characters`.`item_instance`) AND item_guid!=0;
DELETE FROM `characters`.`mail_items` WHERE item_guid NOT IN (SELECT guid FROM `characters`.`item_instance`) AND item_guid!=0;
DELETE FROM `characters`.`auctionhouse` WHERE itemguid NOT IN (SELECT guid FROM `characters`.`item_instance`) AND itemguid!=0;
DELETE FROM `mangos`.`creature` WHERE `id` NOT IN (SELECT `entry` FROM `mangos`.`creature_template`);
DELETE FROM `mangos`.`gameobject` WHERE `id` NOT IN (SELECT `entry` FROM `mangos`.`gameobject_template`);
DELETE FROM `mangos`.`creature_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `mangos`.`item_template`);
DELETE FROM `mangos`.`item_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `mangos`.`item_template`);
DELETE FROM `mangos`.`gameobject_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `mangos`.`item_template`);
DELETE FROM `mangos`.`skinning_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `mangos`.`item_template`);
DELETE FROM `mangos`.`pickpocketing_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `mangos`.`item_template`);
DELETE FROM `mangos`.`prospecting_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `mangos`.`item_template`);
DELETE FROM `mangos`.`disenchant_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `mangos`.`item_template`);
DROP TABLE IF EXISTS `characters`.`spelltemp`;
CREATE TABLE `characters`.`spelltemp` SELECT * FROM `characters`.`character_spell` WHERE `spell`=1515;
INSERT IGNORE INTO `characters`.`character_spell` (`guid`,`spell`) SELECT `guid`, '883' FROM `characters`.`spelltemp`;
INSERT IGNORE INTO `characters`.`character_spell` (`guid`,`spell`) SELECT `guid`, '2641' FROM `characters`.`spelltemp`;
INSERT IGNORE INTO `characters`.`character_spell` (`guid`,`spell`) SELECT `guid`, '5149' FROM `characters`.`spelltemp`;
INSERT IGNORE INTO `characters`.`character_spell` (`guid`,`spell`) SELECT `guid`, '982' FROM `characters`.`spelltemp`;
INSERT IGNORE INTO `characters`.`character_spell` (`guid`,`spell`) SELECT `guid`, '6991' FROM `characters`.`spelltemp`;
DROP TABLE IF EXISTS `characters`.`spelltemp`;
DROP TABLE IF EXISTS `characters`.`spelltemp`;
CREATE TABLE `characters`.`spelltemp` SELECT * FROM `characters`.`character_spell` WHERE `spell`=2458;
INSERT IGNORE INTO `characters`.`character_spell` (`guid`,`spell`) SELECT `guid`, '20252' FROM `characters`.`spelltemp`;
DROP TABLE IF EXISTS `characters`.`spelltemp`;
