ArcEmu: [update] Database Maintenance - ArcEmu

Jump to content

Toggle shoutbox Lastest Announcements

dfighter  : (07 December 2014 - 12:06 PM) Arcemu is in hibernation mode, please read http://arcemu.org/fo...showtopic=26903
dfighter  : (01 January 2013 - 05:56 PM) Arcemu wishes you all a happy new year!
Hasbro  : (12 September 2012 - 10:01 AM) Please excuse our outage from the web! Our web host had a major malfunction!
dfighter  : (01 September 2012 - 04:05 PM) Since the spam bots just don't want to stop, I've enabled admin verification when registering.
dfighter  : (23 January 2012 - 09:56 PM) Please note that from now on you will need to confirm your email on the wiki in order to edit it!
Hasbro  : (31 December 2011 - 12:50 PM) Happy New Years all!
Navid  : (26 December 2011 - 04:09 AM) Merry Christmas !!!!!! Happy holidays all :)
WAmadeus  : (24 December 2011 - 03:54 PM) Merry Christmas to all!
dfighter  : (24 December 2011 - 11:05 AM) The Arcemu team wishes y'all a Merry Christmukkah!
Hasbro  : (05 October 2011 - 12:53 PM) Looking for web designers for upcoming web related project. If you're interested in designing user interfaces contact me
dfighter  : (02 September 2011 - 03:47 PM) So who here wants vehicles in Arcemu? :P http://arcemu.org/fo...showtopic=25440
Hasbro  : (14 August 2011 - 03:25 PM) Join us on irc, grab an irc client and connect to irc.freenode.net join channel #arcemu /server irc.freenode.net:6667 /join #arcemu
jackpoz  : (03 August 2011 - 05:33 AM) to all Lua Engine (old one) users: please check http://arcemu.org/fo...showtopic=25274
Hasbro  : (20 May 2011 - 05:27 PM) Looking for people experienced with CMake configuration and setup! Contact me asap
Hasbro  : (15 May 2011 - 05:03 PM) ArcEmu is recruiting C++ programmers, contact Hasbro if interested.
paroxysm  : (03 May 2011 - 06:26 PM) Updated luabridge gossip example to describe the whole gossip creation process rather than just how to create menu. Gossip tutorial
paroxysm  : (23 April 2011 - 11:35 AM) Lua writers can refer to the Luabridge Tutorials section in the Wiki to learn how to write gossip code correctly.
Hasbro  : (20 April 2011 - 05:22 PM) Thank you for your continuous contribution of bug reports, we are working on them.
Hasbro  : (17 April 2011 - 03:20 AM) Please consider donating to support our bills. Donations can be sent using PayPal to donations@arcemu.org - Thank you for your support.
paroxysm  : (10 April 2011 - 12:43 AM) Refer to the Luabridge Tutorials section in the Wiki to learn the new syntax of luabridge.
Resize Shouts Area

Read:

When submitting patches - READ: http://arcemu.org/fo...?showtopic=2355 -
Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

[update] Database Maintenance

#1 User is offline   Marforius 

  • Permanantly on leave
  • PipPipPipPip
  • Group: Members
  • Posts: 597
  • Joined: 07-August 08
  • IRC:Marforius
  • Gender:Male
  • Location:Rome
  • Interests:ArcEmulatorius
  • Server OS:Linux

Posted 01 May 2011 - 03:08 AM

Fixes: Updates database maintenance to latest database structures. "Recycles" GUIDs. Cleans duplicate GUIDs. Cleans nonexistent entries (Creature_names with missing proto etc). Cleans double spawned NPCs(X,Y,Z) and GOs(X,Y). Sorts tables where possible ascending. Optimizes all tables.

For CleanNonExistantSpells to work correctly, spelltosql needs to be ran and imported in the world database or it will error out. More importantly if you do not want 25+MB of dead weight in your database all you actually need is the first row from the table it creates. (You can find a preextracted 3.3.5 version of this row here: http://subversion.as...e/dbc_spell.sql) Also.. if this isn't extracted it will just skip spell cleanup, as it will the logon database if it cannot be found.

Some parts of this may need to be removed. Vendor fix, common ghost spawn deletion for NCDB & other databases, logon database cleanup (accounts from pre 2010 will be purged).
This wasn't meant to be so much of a release update as it is a resource if anyone wants to actually update the maintenance I was having fun figuring how to do most of this out. Although this query worked for me fine.
Some discussion was had as to weather to grab the data from the database and process it in the core : or to just run the query directly. I went with direct to save bandwith, time, and resources inside the world. My logic may be flawed :P


Index: configs/world.conf
===================================================================
--- configs/world.conf	(revision 4187)
+++ configs/world.conf	(working copy)
@@ -12,10 +12,15 @@
 *   Database.Password  - The password used for the mysql connection
 *   Database.Name      - The database name
 *   Database.Port      - Port that MySQL listens on. Usually 3306.
+*
+* MaintenanceExtendedData
+*	LogonDatabaseName  - The name of the Logon server's database
+* If logon database is on another server maintenance will not be ran on the logon database.
 *******************************************************/
 
 <WorldDatabase Hostname = "host" Username = "username" Password = "passwd" Name = "database" Port = "3306">
 <CharacterDatabase Hostname = "host" Username = "username" Password = "passwd" Name = "database" Port = "3306">
+<MaintenanceExtendedData LogonDatabaseName = "logon">
 
 /******************************************************
 * Listen Config
Index: src/arcemu-world/DatabaseCleaner.cpp
===================================================================
--- src/arcemu-world/DatabaseCleaner.cpp	(revision 4187)
+++ src/arcemu-world/DatabaseCleaner.cpp	(working copy)
@@ -21,350 +21,431 @@
 #include "StdAfx.h"
 initialiseSingleton(DatabaseCleaner);
 
+/* This will need examination every time a table index or key entry is added/changed to prevent unneeded rows. */
 void DatabaseCleaner::Run()
 {
-	Log.Notice("DatabaseCleaner", "Stage 1 of 3: Cleaning characters...");
-	CleanCharacters();
+		Log.Notice("DatabaseCleaner", "Stage 1 of 5: Cleaning NonExistant Spells \nREQUIRES DBC_SPELL BE EXTRACTED WITH SPELLTOSQL AND BE IN THE WORLD DATABASE");
+        CleanNonExistantSpells();
 
-	Log.Notice("DatabaseCleaner", "Stage 2 of 3: Cleaning world...");
-	CleanWorld();
+        Log.Notice("DatabaseCleaner", "Stage 2 of 4: Cleaning Logon");
+        CleanLogon();
+ 
+        Log.Notice("DatabaseCleaner", "Stage 3 of 4: Cleaning World");
+        CleanWorld();
+ 
+        Log.Notice("DatabaseCleaner", "Stage 4 of 5: Cleaning Characters");
+        CleanCharacters();
 
-	Log.Notice("DatabaseCleaner", "Stage 3 of 3: Optimizing databases...");
-	Optimize();
+        Log.Notice("DatabaseCleaner", "Stage 5 of 5: Optimizing Tables");
+        Optimize();
 }
 
-void DatabaseCleaner::CleanWorld()
+void DatabaseCleaner::CleanNonExistantSpells()
 {
+	string worlddatabasename;
+	string characterdatabasename;
+	bool WorldDBName = Config.MainConfig.GetString( "WorldDatabase", "Name", &worlddatabasename );
+	bool CharacterDBName = Config.MainConfig.GetString( "CharacterDatabase", "Name", &characterdatabasename );
+	/* needs updating if world config files change */
+	WorldDatabase.Query("DELETE FROM `SpellTargetConstraints` WHERE `SpellID` NOT IN ( SELECT `dbc_spell`.`Id` FROM `dbc_spell` );");
+	WorldDatabase.Query("DELETE FROM `ai_agents` WHERE `spell` NOT IN ( SELECT `dbc_spell`.`Id` FROM `dbc_spell` );");
+	WorldDatabase.Query("DELETE FROM `ai_threattospellid` WHERE `spell` NOT IN ( SELECT `dbc_spell`.`Id` FROM `dbc_spell` );");
+	CharacterDatabase.Query("DELETE FROM `playerpetspells` WHERE `spellid` NOT IN ( SELECT `dbc_spell`.`Id` FROM `%s`.`dbc_spell` );", worlddatabasename);
+	WorldDatabase.Query("DELETE FROM `playercreateinfo_spells` WHERE `spellid` NOT IN ( SELECT `dbc_spell`.`Id` FROM `dbc_spell` );");
+	WorldDatabase.Query("DELETE FROM `spell_coef_override` WHERE `id` NOT IN ( SELECT `dbc_spell`.`Id` FROM `dbc_spell` );");
+	WorldDatabase.Query("DELETE FROM `spell_effects_override` WHERE `spellId` NOT IN ( SELECT `dbc_spell`.`Id` FROM `dbc_spell` );");
+	WorldDatabase.Query("DELETE FROM `spell_proc` WHERE `spellID` NOT IN ( SELECT `dbc_spell`.`Id` FROM `dbc_spell` );");
+	WorldDatabase.Query("DELETE FROM `trainer_spells` WHERE `learn_spell` NOT IN ( SELECT `dbc_spell`.`Id` FROM `dbc_spell` );");
+}
 
+void DatabaseCleaner::CleanLogon()
+{	 
+	string logondatabasename;
+	bool LogonDBName = Config.MainConfig.GetString( "MaintenanceExtendedData", "LogonDatabaseName", &logondatabasename );
+	string characterdatabasename;
+	bool CharacterDBName = Config.MainConfig.GetString( "CharacterDatabase", "Name", &characterdatabasename );
+	/*
+	* needs updating if world config files change 
+	* Reason for a new entry in the config files is that logon's database name doesnt get loaded in the world
+	*/
+	WorldDatabase.Query("DELETE FROM `%s`.`accounts` WHERE (`lastlogin`<'2010-00-00 00:00:00');", logondatabasename); /* like this is going to trigger :) */
+	WorldDatabase.Query("DELETE FROM `%s`.`characters` WHERE `acct` NOT IN (SELECT `accounts`.`acct` FROM `%s`.`accounts`);", characterdatabasename, logondatabasename);
 }
 
-void DatabaseCleaner::Optimize()
+void DatabaseCleaner::CleanWorld()
 {
-
+	/* vendor bugfix, deletes very common ghost spawn in NCDB based databases */
+	WorldDatabase.Query("DELETE FROM `creature_spawns` WHERE `entry` = 29238;");
+	WorldDatabase.Query("UPDATE `vendors` SET amount = 1 WHERE amount < 1;");
+	/*****************************LOLSEPERATOR***********************************/
+	WorldDatabase.Query("DELETE FROM `creature_names` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_proto`);");
+	WorldDatabase.Query("DELETE FROM `creature_proto` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);");
+	WorldDatabase.Query("DELETE FROM `creature_quest_finisher` WHERE `quest` NOT IN ( SELECT `entry` FROM `quests` );");
+	WorldDatabase.Query("DELETE FROM `creature_quest_starter` WHERE `quest` NOT IN ( SELECT `entry` FROM `quests` );");
+	WorldDatabase.Query("DELETE FROM `creature_spawns` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);");
+	WorldDatabase.Query("DELETE FROM `creature_waypoints` WHERE `spawnid` NOT IN (SELECT `id` FROM `creature_spawns`);");
+	WorldDatabase.Query("DELETE FROM `gameobject_spawns` WHERE `entry` NOT IN (SELECT `entry` FROM `gameobject_names`);");
+	WorldDatabase.Query("DELETE FROM `loot_creatures` WHERE `itemid` NOT IN (SELECT `entry` FROM `items`);");
+	WorldDatabase.Query("DELETE FROM `loot_gameobjects` WHERE `itemid` NOT IN (SELECT `entry` FROM `items`);");
+	WorldDatabase.Query("DELETE FROM `npc_gossip_textid` WHERE `creatureid` NOT IN (SELECT `entry` FROM `creature_names`);");
+	WorldDatabase.Query("DELETE FROM `npc_monstersay` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);");
+	WorldDatabase.Query("DELETE FROM `vendors` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);");
+	WorldDatabase.Query("DELETE FROM `vendors` WHERE `item` NOT IN (SELECT `entry` FROM `items`);");
+	WorldDatabase.Query("DELETE FROM `creature_proto` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `creature_quest_starter` WHERE `id` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `creature_quest_finisher` WHERE `id` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `ai_agents` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `battlemasters` WHERE `creature_entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `loot_creatures` WHERE `entryid` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `loot_skinning` WHERE `entryid` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `loot_pickpocketing` WHERE `entryid` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `npc_gossip_textid` WHERE `creatureid` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `npc_monstersay` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `reputation_creature_onkill` WHERE `creature_id` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `trainer_defs` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `trainer_spells` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `vendor_restrictions` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `vendors` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `zoneguards` WHERE `horde_entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `zoneguards` WHERE `alliance_entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `creature_spawns` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `creature_formations` WHERE `spawn_id` NOT IN ( SELECT `id` FROM `creature_spawns` );");
+	WorldDatabase.Query("DELETE FROM `creature_waypoints` WHERE `spawnid` NOT IN ( SELECT `id` FROM `creature_spawns` );");
+	WorldDatabase.Query("DELETE FROM `creature_timed_emotes` WHERE `spawnid` NOT IN ( SELECT `id` FROM `creature_spawns` );");
+	WorldDatabase.Query("DELETE FROM `gameobject_quest_starter` WHERE `id` NOT IN ( SELECT `entry` FROM `gameobject_names` );");
+	WorldDatabase.Query("DELETE FROM `gameobject_quest_finisher` WHERE `id` NOT IN ( SELECT `entry` FROM `gameobject_names` );");
+	WorldDatabase.Query("DELETE FROM `gameobject_quest_item_binding` WHERE `entry` NOT IN ( SELECT `entry` FROM `gameobject_names` );");
+	WorldDatabase.Query("DELETE FROM `gameobject_quest_pickup_binding` WHERE `entry` NOT IN ( SELECT `entry` FROM `gameobject_names` );");
+	WorldDatabase.Query("DELETE FROM `gameobject_teleports` WHERE `entry` NOT IN ( SELECT `entry` FROM `gameobject_names` );");
+	WorldDatabase.Query("DELETE FROM `gameobject_spawns` WHERE `entry` NOT IN ( SELECT `entry` FROM `gameobject_names` );");
+	WorldDatabase.Query("DELETE FROM `loot_gameobjects` WHERE `entryid` NOT IN ( SELECT `entry` FROM `gameobject_names` );");
+	WorldDatabase.Query("DELETE FROM `creature_proto` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `creature_quest_starter` WHERE `id` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `creature_quest_finisher` WHERE `id` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `ai_agents` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `battlemasters` WHERE `creature_entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `loot_creatures` WHERE `entryid` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `loot_skinning` WHERE `entryid` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `loot_pickpocketing` WHERE `entryid` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `npc_gossip_textid` WHERE `creatureid` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `npc_monstersay` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `reputation_creature_onkill` WHERE `creature_id` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `trainer_defs` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `trainer_spells` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `vendor_restrictions` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `vendors` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `zoneguards` WHERE `horde_entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `zoneguards` WHERE `alliance_entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `creature_spawns` WHERE `entry` NOT IN ( SELECT `entry` FROM `creature_names` );");
+	WorldDatabase.Query("DELETE FROM `creature_formations` WHERE `spawn_id` NOT IN ( SELECT `id` FROM `creature_spawns` );");
+	WorldDatabase.Query("DELETE FROM `creature_waypoints` WHERE `spawnid` NOT IN ( SELECT `id` FROM `creature_spawns` );");
+	WorldDatabase.Query("DELETE FROM `creature_timed_emotes` WHERE `spawnid` NOT IN ( SELECT `id` FROM `creature_spawns` );");
+	WorldDatabase.Query("DELETE FROM `loot_creatures` WHERE `entryid` NOT IN (SELECT `entry` FROM `creature_names`);");
+	WorldDatabase.Query("DELETE FROM `loot_fishing` WHERE `entryid` NOT IN (SELECT `entry` FROM `items`);");
+	WorldDatabase.Query("DELETE FROM `loot_gameobjects` WHERE `entryid` NOT IN (SELECT `entry` FROM `creature_names`);");
+	WorldDatabase.Query("DELETE FROM `loot_items` WHERE `entryid` NOT IN (SELECT `entry` FROM `items`);");
+	WorldDatabase.Query("DELETE FROM `loot_pickpocketing` WHERE `entryid` NOT IN (SELECT `entry` FROM `creature_names`);");
+	WorldDatabase.Query("DELETE FROM `loot_skinning` WHERE `entryid` NOT IN (SELECT `entry` FROM `creature_names`);");
+	WorldDatabase.Query("DELETE FROM `trainer_defs` WHERE `entry` NOT IN (SELECT `entry` FROM `trainer_spells`);");
+	WorldDatabase.Query("DELETE FROM `quest_poi` WHERE `questid` NOT IN (SELECT `entry` FROM `quests`);");
+	WorldDatabase.Query("DELETE FROM `quest_poi_points` WHERE `questid` NOT IN (SELECT `entry` FROM `quests`);");
+	WorldDatabase.Query("CREATE TABLE `creature_spawns1` AS SELECT * FROM `creature_spawns` WHERE 1 GROUP BY `position_x`,`position_y`;");
+	WorldDatabase.Query("CREATE TABLE `creature_spawns2` AS SELECT * FROM `creature_spawns1` WHERE 1 GROUP BY `id`;");
+	WorldDatabase.Query("CREATE TABLE `gameobject_spawns1` AS SELECT * FROM `gameobject_spawns` WHERE 1 GROUP BY `position_x`,`position_y`,`position_z`;");
+	WorldDatabase.Query("CREATE TABLE `gameobject_spawns2` AS SELECT * FROM `gameobject_spawns1` WHERE 1 GROUP BY `id`;");
+	WorldDatabase.Query("CREATE TABLE `creature_proto1` AS SELECT * FROM `creature_proto` WHERE 1 GROUP BY `entry`;");
+	WorldDatabase.Query("DROP TABLE IF EXISTS `creature_spawns`,`creature_spawns1`,`gameobject_spawns`,`gameobject_spawns1`,`creature_proto`;");
+	WorldDatabase.Query("RENAME TABLE `creature_proto1` TO `creature_proto`;");
+	WorldDatabase.Query("RENAME TABLE `creature_spawns2` TO `creature_spawns`;");
+	WorldDatabase.Query("RENAME TABLE `gameobject_spawns2` TO `gameobject_spawns`;");
+	WorldDatabase.Query("ALTER TABLE `creature_proto` ADD PRIMARY KEY (`entry`);");
+	WorldDatabase.Query("ALTER TABLE `creature_spawns` ADD PRIMARY KEY (`id`);");
+	WorldDatabase.Query("ALTER TABLE `gameobject_spawns` ADD PRIMARY KEY (`id`);");
+	WorldDatabase.Query("DELETE FROM `creature_waypoints` WHERE `spawnid` NOT IN (SELECT `id` FROM `creature_spawns`);");
+	WorldDatabase.Query("ALTER TABLE `creature_spawns` ORDER BY `id` ASC;");
+	WorldDatabase.Query("ALTER TABLE `creature_spawns` ADD COLUMN `temp` INTEGER AFTER `phase`;");
+	WorldDatabase.Query("UPDATE `creature_spawns` SET `temp`=`id`;");
+	WorldDatabase.Query("ALTER TABLE `creature_spawns` DROP `id`;");
+	WorldDatabase.Query("ALTER TABLE `creature_spawns` ADD COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);");
+	WorldDatabase.Query("UPDATE `creature_waypoints`,`creature_spawns` SET `creature_waypoints`.`spawnid` = `creature_spawns`.`id` WHERE `creature_waypoints`.`spawnid` = `creature_spawns`.`temp`;");
+	WorldDatabase.Query("UPDATE `creature_formations`,`creature_spawns` SET `creature_formations`.`spawn_id` = `creature_spawns`.`id` WHERE `creature_formations`.`spawn_id` = `creature_spawns`.`temp`;");
+	WorldDatabase.Query("UPDATE `creature_formations`,`creature_spawns` SET `creature_formations`.`target_spawn_id` = `creature_spawns`.`id` WHERE `creature_formations`.`target_spawn_id` = `creature_spawns`.`temp`;");
+	WorldDatabase.Query("UPDATE `creature_timed_emotes`,`creature_spawns` SET `creature_timed_emotes`.`spawnid` = `creature_spawns`.`id` WHERE `creature_timed_emotes`.`spawnid` = `creature_spawns`.`temp`;");
+	WorldDatabase.Query("ALTER TABLE `creature_spawns` DROP `temp`;");
+	WorldDatabase.Query("ALTER TABLE `gameobject_spawns` ORDER BY `Entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `gameobject_spawns` DROP `id`;");
+	WorldDatabase.Query("ALTER TABLE `gameobject_spawns` ADD COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);");
+	WorldDatabase.Query("ALTER TABLE `gameobject_staticspawns` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `gameobject_teleports` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `graveyards` ORDER BY `id` ASC;");
+	WorldDatabase.Query("ALTER TABLE `graveyards` DROP `id`;");
+	WorldDatabase.Query("ALTER TABLE `graveyards` ADD COLUMN `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);");
+	WorldDatabase.Query("ALTER TABLE `loot_creatures` ORDER BY `entryid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `loot_fishing` ORDER BY `entryid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `loot_gameobjects` ORDER BY `entryid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `loot_items` ORDER BY `entryid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `loot_pickpocketing` ORDER BY `entryid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `loot_skinning` ORDER BY `entryid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `recall` ORDER BY `name` ASC;");
+	WorldDatabase.Query("ALTER TABLE `recall` DROP `id`;");
+	WorldDatabase.Query("ALTER TABLE `recall` ADD COLUMN `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`), ADD UNIQUE KEY (`id`);");
+	WorldDatabase.Query("ALTER TABLE `SpellTargetConstraints` ORDER BY `SpellID` ASC;");
+	WorldDatabase.Query("ALTER TABLE `ai_agents` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `ai_threattospellid` ORDER BY `spell` ASC;");
+	WorldDatabase.Query("ALTER TABLE `areatriggers` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `auctionhouse` ORDER BY `id` ASC;");
+	WorldDatabase.Query("ALTER TABLE `banned_phrases` ORDER BY `phrase` ASC;");
+	WorldDatabase.Query("ALTER TABLE `battlemasters` ORDER BY `creature_entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `clientaddons` ORDER BY `id`;");
+	WorldDatabase.Query("ALTER TABLE `command_overrides` ORDER BY `command_name` ASC;");
+	WorldDatabase.Query("ALTER TABLE `creature_formations` ORDER BY `spawn_id` ASC;");
+	WorldDatabase.Query("ALTER TABLE `creature_names_localized` ORDER BY `id` ASC;");
+	WorldDatabase.Query("ALTER TABLE `creature_names` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `creature_proto` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `creature_quest_finisher` ORDER BY `quest` ASC;");
+	WorldDatabase.Query("ALTER TABLE `creature_quest_starter` ORDER BY `quest` ASC;");
+	WorldDatabase.Query("ALTER TABLE `creature_spawns` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `creature_staticspawns` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `creature_timed_emotes` ORDER BY `spawnid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `creature_waypoints` ORDER BY `spawnid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `display_bounding_boxes` ORDER BY `displayid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `fishing` ORDER BY `Zone` ASC;");
+	WorldDatabase.Query("ALTER TABLE `gameobject_names_localized` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `gameobject_names` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `gameobject_quest_finisher` ORDER BY `quest` ASC;");
+	WorldDatabase.Query("ALTER TABLE `gameobject_quest_item_binding` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `gameobject_quest_pickup_binding` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `gameobject_quest_starter` ORDER BY `quest` ASC;");
+	WorldDatabase.Query("ALTER TABLE `instance_bosses` ORDER BY `creatureid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `item_quest_association` ORDER BY `item` ASC;");
+	WorldDatabase.Query("ALTER TABLE `item_randomprop_groups` ORDER BY `entry_id` ASC;");
+	WorldDatabase.Query("ALTER TABLE `item_randomsuffix_groups` ORDER BY `entry_id` ASC;");
+	WorldDatabase.Query("ALTER TABLE `itemnames` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `itempages_localized` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `itempages` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `itempetfood` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `items_localized` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `items` ORDER BY `entry`;");
+	WorldDatabase.Query("ALTER TABLE `map_checkpoint` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `npc_gossip_textid` ORDER BY `creatureid`;");
+	WorldDatabase.Query("ALTER TABLE `npc_monstersay` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `npc_text_localized` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `npc_text` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `petdefaultspells` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `playercreateinfo_bars` ORDER BY `race` ASC;");
+	WorldDatabase.Query("ALTER TABLE `playercreateinfo_items` ORDER BY `indexid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `playercreateinfo_skills` ORDER BY `indexid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `playercreateinfo_spells` ORDER BY `indexid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `playercreateinfo` ORDER BY `race` ASC;");
+	WorldDatabase.Query("ALTER TABLE `professiondiscoveries` ORDER BY `SpellId` ASC;");
+	WorldDatabase.Query("ALTER TABLE `quests_localized` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `quests` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `reputation_creature_onkill` ORDER BY `creature_id` ASC;");
+	WorldDatabase.Query("ALTER TABLE `reputation_faction_onkill` ORDER BY `faction_id` ASC;");
+	WorldDatabase.Query("ALTER TABLE `reputation_instance_onkill` ORDER BY `mapid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `spell_coef_override` ORDER BY `id` ASC;");
+	WorldDatabase.Query("ALTER TABLE `spell_disable_trainers` ORDER BY `spellid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `spell_disable` ORDER BY `spellid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `spell_effects_override` ORDER BY `spellId` ASC;");
+	WorldDatabase.Query("ALTER TABLE `spell_proc` ORDER BY `spellID` ASC;");
+	WorldDatabase.Query("ALTER TABLE `spelloverride` ORDER BY `overrideId` ASC;");
+	WorldDatabase.Query("ALTER TABLE `teleport_coords` ORDER BY `id` ASC;");
+	WorldDatabase.Query("ALTER TABLE `totemspells` ORDER BY `spell` ASC;");
+	WorldDatabase.Query("ALTER TABLE `trainer_defs` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `trainer_spells` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `trainerspelloverride` ORDER BY `spellid` ASC;");
+	WorldDatabase.Query("ALTER TABLE `transport_creatures` ORDER BY `transport_entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `transport_data` ORDER BY `name` ASC;");
+	WorldDatabase.Query("ALTER TABLE `unit_display_sizes` ORDER BY `DisplayID` ASC;");
+	WorldDatabase.Query("ALTER TABLE `vendor_restrictions` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `vendors` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `weather` ORDER BY `zoneId` ASC;");
+	WorldDatabase.Query("ALTER TABLE `wordfilter_character_names` ORDER BY `regex_match` ASC;");
+	WorldDatabase.Query("ALTER TABLE `wordfilter_chat` ORDER BY `regex_match` ASC;");
+	WorldDatabase.Query("ALTER TABLE `worldbroadcast_localized` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `worldbroadcast` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `worldmap_info_localized` ORDER BY `text` ASC;");
+	WorldDatabase.Query("ALTER TABLE `worldmap_info` ORDER BY `area_name` ASC;");
+	WorldDatabase.Query("ALTER TABLE `worldstring_tables_localized` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `worldstring_tables` ORDER BY `entry` ASC;");
+	WorldDatabase.Query("ALTER TABLE `zoneguards` ORDER BY `zone` ASC;");
 }
 
 void DatabaseCleaner::CleanCharacters()
 {
-	set<uint32> chr_guids;
-	set<uint32> chr_guilds;
-	set<uint32> chr_charters;
-	Log.Notice("DatabaseCleaner", "Loading guids...");
-	QueryResult * result = CharacterDatabase.Query("SELECT guid, guildid, charterId FROM characters");
-	if(result)
-	{
-		do {
-			chr_guids.insert(result->Fetch()[0].GetUInt32());
-			if(result->Fetch()[1].GetUInt32() != 0)
-				chr_guilds.insert(result->Fetch()[1].GetUInt32());
-			if(result->Fetch()[2].GetUInt32() != 0)
-				chr_guilds.insert(result->Fetch()[2].GetUInt32());
-		} while(result->NextRow());
-		delete result;
-	}
-	Log.Notice("DatabaseCleaner", "Got %u guids.", chr_guids.size());
-	Log.Notice("DatabaseCleaner", "Cleaning playeritems...");
-	result = CharacterDatabase.Query("SELECT ownerguid, guid FROM playeritems");
-	vector<uint64> tokill_items;
-	if(result)
-	{
-		do {
-			if(result->Fetch()[0].GetUInt32()!= 0 && chr_guids.find(result->Fetch()[0].GetUInt32()) == chr_guids.end())
-			{
-				tokill_items.push_back(result->Fetch()[1].GetUInt64());
-			}
-		}while(result->NextRow());
-		delete result;
-	}
+	string worlddatabasename;
+	bool WorldDBName = Config.MainConfig.GetString( "WorldDatabase", "Name", &worlddatabasename );
+	
+	CharacterDatabase.Query("DELETE FROM `questlog` WHERE `player_guid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `social_friends` WHERE `character_guid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `social_ignores` WHERE `character_guid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `gm_tickets` WHERE (`deleted`='1');");
+	CharacterDatabase.Query("DELETE FROM `gm_tickets` WHERE `playerGuid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `corpses` WHERE `guid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `character_achievement` WHERE `guid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `character_achievement_progress` WHERE `guid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `arenateams` WHERE `leader` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `auctions` WHERE `owner` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `charters` WHERE `leaderGuid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `tutorials` WHERE `playerId` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `character_achievement` WHERE `guid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `playeritems` WHERE `ownerguid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `playeritems` WHERE `entry` NOT IN (SELECT `items`.`entry` FROM `%s`.`items`);", worlddatabasename);
+	CharacterDatabase.Query("DELETE FROM `mailbox` WHERE `player_guid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `playercooldowns` WHERE `player_guid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `playerpets` WHERE `ownerguid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `playerpetspells` WHERE `ownerguid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `playersummons` WHERE `ownerguid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `playersummonspells` WHERE `ownerguid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `guilds` WHERE `leaderGuid` NOT IN (SELECT `characters`.`guid` FROM `characters`);");
+	CharacterDatabase.Query("DELETE FROM `guild_bankitems` WHERE `guildId` NOT IN (SELECT `guilds`.`guildId` FROM `guilds`);");
+	CharacterDatabase.Query("DELETE FROM `guild_banklogs` WHERE `guildid` NOT IN (SELECT `guilds`.`guildId` FROM `guilds`);");
+	CharacterDatabase.Query("DELETE FROM `guild_banktabs` WHERE `guildId` NOT IN (SELECT `guilds`.`guildId` FROM `guilds`);");
+	CharacterDatabase.Query("DELETE FROM `guild_data` WHERE `guildid` NOT IN (SELECT `guilds`.`guildId` FROM `guilds`);");
+	CharacterDatabase.Query("DELETE FROM `guild_logs` WHERE `guildid` NOT IN (SELECT `guilds`.`guildId` FROM `guilds`);");
+	CharacterDatabase.Query("DELETE FROM `guild_ranks` WHERE `guildId` NOT IN (SELECT `guilds`.`guildId` FROM `guilds`);");
+}
 
-	for(vector<uint64>::iterator itr = tokill_items.begin(); itr != tokill_items.end(); ++itr)
-	{
-		CharacterDatabase.WaitExecute("DELETE FROM playeritems WHERE guid = "I64FMTD, *itr);
-	}
-
-	Log.Notice("DatabaseCleaner", "Deleted %u item instances.", tokill_items.size());
-	Log.Notice("DatabaseCleaner", "Cleaning questlog...");
-	result = CharacterDatabase.Query("SELECT index, player_guid FROM questlog");
-	vector<uint32> tokill_quests;
-	if(result)
-	{
-		do 
-		{
-			if(chr_guids.find(result->Fetch()[1].GetUInt32()) == chr_guids.end())
-				tokill_quests.push_back(result->Fetch()[0].GetUInt32());
-		} while(result->NextRow());
-		delete result;
-	}
-
-	for(vector<uint32>::iterator itr = tokill_quests.begin(); itr != tokill_quests.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM questlog WHERE index = %u", *itr);
-
-	Log.Notice("DatabaseCleaner", "Deleted %u questlog entries.", tokill_quests.size());
-	Log.Notice("DatabaseCleaner", "Cleaning corpses...");
-	vector<uint32> tokill_corpses;
-	result = CharacterDatabase.Query("SELECT * FROM corpses");
-	if(result)
-	{
-		do 
-		{
-			Corpse * pCorpse = new Corpse(0, result->Fetch()[0].GetUInt32());
-			pCorpse->LoadValues(result->Fetch()[8].GetString());
-            pCorpse->SetLowGUID( 0 );
-			if(pCorpse->GetDisplayId() == 0 ||
-				GET_LOWGUID_PART(pCorpse->GetOwner()) == 0 ||
-				chr_guids.find(GET_LOWGUID_PART(pCorpse->GetOwner())) == chr_guids.end())
-			{
-				tokill_corpses.push_back(pCorpse->GetLowGUID());
-			}
-			delete pCorpse;
-		} while(result->NextRow());
-		delete result;
-	}
-
-	for(vector<uint32>::iterator itr = tokill_corpses.begin(); itr != tokill_corpses.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM corpses WHERE guid = %u", *itr);
-
-	Log.Notice("DatabaseCleaner", "Removed %u corpses.", tokill_corpses.size());
-	Log.Notice("DatabaseCleaner", "Cleaning mailbox...");
-	result = CharacterDatabase.Query("SELECT message_id, player_guid FROM mailbox");
-	vector<uint32> tokill_mail;
-	if(result)
-	{
-		do 
-		{
-			if(chr_guids.find(result->Fetch()[1].GetUInt32()) == chr_guids.end())
-				tokill_mail.push_back(result->Fetch()[0].GetUInt32());
-
-		} while(result->NextRow());
-		delete result;
-	}
-
-	for(vector<uint32>::iterator itr = tokill_mail.begin(); itr != tokill_mail.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM mailbox WHERE message_id = %u", *itr);
-	Log.Notice("DatabaseCleaner", "Deleted %u mail messages.", tokill_mail.size());
-	Log.Notice("DatabaseCleaner", "Cleaning guilds table...");
-	result = CharacterDatabase.Query("SELECT guildId FROM guilds");
-	vector<uint32> tokill_guilds;
-	if(result)
-	{
-		do 
-		{
-			if(chr_guilds.find(result->Fetch()[0].GetUInt32()) == chr_guilds.end())
-			{
-				tokill_guilds.push_back(result->Fetch()[0].GetUInt32());
-			}
-		} while(result->NextRow());
-		delete result;
-	}
-
-	for(vector<uint32>::iterator itr = tokill_guilds.begin(); itr != tokill_guilds.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM guilds WHERE guildId = %u", *itr);
-
-	Log.Notice("DatabaseCleaner", "Deleted %u guilds.", tokill_guilds.size());
-	Log.Notice("DatabaseCleaner", "Cleaning guild_ranks table...");
-	result = CharacterDatabase.Query("SELECT guildId FROM guild_ranks");
-	set<uint32> tokill_guildranks;
-	if(result)
-	{
-		do 
-		{
-			if(chr_guilds.find(result->Fetch()[0].GetUInt32()) == chr_guilds.end())
-			{
-				tokill_guildranks.insert(result->Fetch()[0].GetUInt32());
-			}
-		} while(result->NextRow());
-		delete result;
-	}
-	for(set<uint32>::iterator itr = tokill_guildranks.begin(); itr != tokill_guildranks.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM guild_ranks WHERE guildId = %u", *itr);
-
-	Log.Notice("DatabaseCleaner", "Deleted %u guild rank rows.", tokill_guildranks.size());
-	Log.Notice("DatabaseCleaner", "Cleaning social table...");
-	result = CharacterDatabase.Query("SELECT * FROM social");
-	vector<pair<uint32,uint32> > tokill_social;
-	if(result)
-	{
-		do 
-		{
-			uint32 g1 = result->Fetch()[0].GetUInt32();
-			uint32 g2 = result->Fetch()[1].GetUInt32();
-			if(chr_guids.find(g1) == chr_guids.end() || chr_guids.find(g2) == chr_guids.end())
-			{
-				pair<uint32,uint32> x;
-				x.first = g1;
-				x.second = g2;
-				tokill_social.push_back(x);
-			}
-		} while(result->NextRow());
-		delete result;
-	}
-
-	for(vector<pair<uint32,uint32> >::iterator itr = tokill_social.begin(); itr != tokill_social.end(); ++itr)
-	{
-		CharacterDatabase.WaitExecute("DELETE FROM social WHERE guid = %u and socialguid = %u", itr->first, itr->second);
-	}
-
-	Log.Notice("DatabaseCleaner", "Deleted %u social entries.", tokill_social.size());
-	Log.Notice("DatabaseCleaner", "Cleaning cooldown tables...");
-	set<uint32> tokill_cool;
-	vector<pair<uint32,uint32> > tokill_cool2;
-	result = CharacterDatabase.Query("SELECT OwnerGuid, CooldownTimeStamp FROM playercooldownitems");
-	if(result)
-	{
-		uint32 t = getMSTime();
-		do 
-		{
-			uint32 guid = result->Fetch()[0].GetUInt32();
-			uint32 cool = result->Fetch()[1].GetUInt32();
-			if(chr_guids.find(guid) == chr_guids.end())
-				tokill_cool.insert(guid);
-			else if(t >= cool)
-				tokill_cool2.push_back(make_pair(guid,cool));
-		} while(result->NextRow());
-		delete result;
-	}
-
-	for(vector<pair<uint32,uint32> >::iterator itr = tokill_cool2.begin(); itr != tokill_cool2.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM playercooldownitems WHERE OwnerGuid = %u AND CooldownTimeStamp = %u", itr->first, itr->second);
-	for(set<uint32>::iterator itr = tokill_cool.begin(); itr != tokill_cool.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM playercooldownitems WHERE OwnerGuid = %u", *itr);
-
-	Log.Notice("DatabaseCleaner", "Deleted %u playercooldownitems.", tokill_cool.size() + tokill_cool2.size());
-	tokill_cool.clear();
-	tokill_cool2.clear();
-	result = CharacterDatabase.Query("SELECT OwnerGuid, TimeStamp FROM playercooldownsecurity");
-	if(result)
-	{
-		uint32 t = getMSTime();
-		do 
-		{
-			uint32 guid = result->Fetch()[0].GetUInt32();
-			uint32 cool = result->Fetch()[1].GetUInt32();
-			if(chr_guids.find(guid) == chr_guids.end())
-				tokill_cool.insert(guid);
-			else if(t >= cool)
-				tokill_cool2.push_back(make_pair(guid,cool));
-		} while(result->NextRow());
-		delete result;
-	}
-
-	for(vector<pair<uint32,uint32> >::iterator itr = tokill_cool2.begin(); itr != tokill_cool2.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM playercooldownsecurity WHERE OwnerGuid = %u AND TimeStamp = %u", itr->first, itr->second);
-	for(set<uint32>::iterator itr = tokill_cool.begin(); itr != tokill_cool.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM playercooldownsecurity WHERE OwnerGuid = %u", *itr);
-
-    Log.Notice("DatabaseCleaner", "Deleted %u playercooldownsecurities.", tokill_cool.size() + tokill_cool2.size());
-	Log.Notice("DatabaseCleaner", "Cleaning tutorials...");
-	vector<uint32> tokill_tutorials;
-	result = CharacterDatabase.Query("SELECT playerId FROM tutorials");
-	if(result)
-	{
-		do 
-		{
-			uint32 pi = result->Fetch()[0].GetUInt32();
-			if(chr_guids.find(pi) == chr_guids.end())
-				tokill_tutorials.push_back(pi);
-		} while(result->NextRow());
-		delete result;
-	}
-
-	for(vector<uint32>::iterator itr = tokill_tutorials.begin(); itr != tokill_tutorials.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM tutorials WHERE playerId = %u", *itr);
-	Log.Notice("DatabaseCleaner", "Deleted %u tutorials.", tokill_tutorials.size());
-	Log.Notice("DatabaseCleaner", "Cleaning playerpets...");
-	set<uint32> tokill_pet;
-	result = CharacterDatabase.Query("SELECT ownerguid, petnumber FROM playerpets");
-	if(result)
-	{
-		do 
-		{
-			if(chr_guids.find( result->Fetch()[0].GetUInt32() ) == chr_guids.end())
-				tokill_pet.insert(result->Fetch()[0].GetUInt32());
-		} while(result->NextRow());
-		delete result;
-	}
-	for(set<uint32>::iterator itr = tokill_pet.begin(); itr != tokill_pet.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM playerpets WHERE ownerguid = %u", *itr);
-	Log.Notice("DatabaseCleaner", "Deleted %u pets.", tokill_pet.size());
-	Log.Notice("DatabaseCleaner", "Cleaning playersummonspells...");
-	set<uint32> tokill_ss;
-	result = CharacterDatabase.Query("SELECT ownerguid FROM playersummonspells");
-	if(result)
-	{
-		do 
-		{
-			if(chr_guids.find( result->Fetch()[0].GetUInt32() ) == chr_guids.end())
-				tokill_ss.insert(result->Fetch()[0].GetUInt32());
-		} while(result->NextRow());
-		delete result;
-	}
-	for(set<uint32>::iterator itr = tokill_ss.begin(); itr != tokill_ss.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM playersummonspells WHERE ownerguid = %u", *itr);
-	Log.Notice("DatabaseCleaner", "Deleted %u summonspells.", tokill_ss.size());
-	Log.Notice("DatabaseCleaner", "Cleaning playerpetspells...");
-	set<uint32> tokill_ps;
-	result = CharacterDatabase.Query("SELECT ownerguid FROM playerpetspells");
-	if(result)
-	{
-		do 
-		{
-			if(chr_guids.find( result->Fetch()[0].GetUInt32() ) == chr_guids.end())
-				tokill_ps.insert(result->Fetch()[0].GetUInt32());
-		} while(result->NextRow());
-		delete result;
-	}
-	for(set<uint32>::iterator itr = tokill_ps.begin(); itr != tokill_ps.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM playerpetspells WHERE ownerguid = %u", *itr);
-	Log.Notice("DatabaseCleaner", "Deleted %u petspells.", tokill_ps.size());
-	Log.Notice("DatabaseCleaner", "Cleaning gm_tickets...");
-	set<uint32> tokill_gm;
-	result = CharacterDatabase.Query("SELECT guid FROM gm_tickets");
-	if(result)
-	{
-		do 
-		{
-			if(chr_guids.find( result->Fetch()[0].GetUInt32() ) == chr_guids.end())
-				tokill_gm.insert(result->Fetch()[0].GetUInt32());
-		} while(result->NextRow());
-		delete result;
-	}
-	for(set<uint32>::iterator itr = tokill_gm.begin(); itr != tokill_gm.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM gm_tickets WHERE guid = %u", *itr);
-	Log.Notice("DatabaseCleaner", "Deleted %u gm tickets.", tokill_gm.size());
-	Log.Notice("DatabaseCleaner", "Cleaning charters...");
-	vector<uint32> tokill_charters;
-	result = CharacterDatabase.Query("SELECT * FROM charters");
-	if(result)
-	{
-		do 
-		{
-			if(chr_charters.find(result->Fetch()[0].GetUInt32()) == chr_charters.end() ||
-				chr_guids.find(result->Fetch()[1].GetUInt32()) == chr_guids.end())
-			{
-				tokill_charters.push_back(result->Fetch()[0].GetUInt32());
-			}
-		} while(result->NextRow());
-		delete result;
-	}
-
-	for(vector<uint32>::iterator itr = tokill_charters.begin(); itr != tokill_charters.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM charters WHERE charterId = %u", *itr);
-	Log.Notice("DatabaseCleaner", "Deleted %u charters.", tokill_charters.size());
-	Log.Notice("DatabaseCleaner", "Cleaning charters...");
-	result = CharacterDatabase.Query("SELECT auctionId, owner FROM auctions");
-	vector<uint32> tokill_auct;
-	if(result)
-	{
-		do 
-		{
-			if(chr_guids.find(result->Fetch()[1].GetUInt32()) == chr_guids.end())
-                tokill_auct.push_back(result->Fetch()[0].GetUInt32());
-
-		} while(result->NextRow());
-		delete result;
-	}
-
-	for(vector<uint32>::iterator itr = tokill_auct.begin(); itr != tokill_auct.end(); ++itr)
-		CharacterDatabase.WaitExecute("DELETE FROM auctions WHERE auctionId = %u", *itr);
-	Log.Notice("DatabaseCleaner", "Deleted %u auctions.", tokill_auct.size());
-	Log.Notice("DatabaseCleaner", "Ending...");
-}
+void DatabaseCleaner::Optimize()
+{
+	string logondatabasename;
+	bool LogonDBName = Config.MainConfig.GetString( "MaintenanceExtendedData", "LogonDatabaseName", &logondatabasename );
+	
+	WorldDatabase.Query("OPTIMIZE TABLE `SpellTargetConstraints`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `ai_agents`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `ai_threattospellid`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `areatriggers`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `auctionhouse`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `banned_phrases`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `battlemasters`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `clientaddons`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `command_overrides`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `creature_formations`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `creature_names_localized`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `creature_names`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `creature_proto`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `creature_quest_finisher`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `creature_quest_starter`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `creature_spawns`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `creature_staticspawns`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `creature_timed_emotes`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `creature_waypoints`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `display_bounding_boxes`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `fishing`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `gameobject_names_localized`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `gameobject_names`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `gameobject_quest_finisher`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `gameobject_quest_item_binding`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `gameobject_quest_pickup_binding`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `gameobject_quest_starter`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `instance_bosses`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `item_quest_association`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `item_randomprop_groups`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `item_randomsuffix_groups`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `itemnames`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `itempages_localized`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `itempages`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `itempetfood`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `items_localized`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `items`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `map_checkpoint`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `npc_gossip_textid`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `npc_monstersay`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `npc_text_localized`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `npc_text`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `petdefaultspells`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `playercreateinfo_bars`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `playercreateinfo_items`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `playercreateinfo_skills`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `playercreateinfo_spells`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `playercreateinfo`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `professiondiscoveries`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `quests_localized`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `quests`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `reputation_creature_onkill`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `reputation_faction_onkill`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `reputation_instance_onkill`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `spell_coef_override`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `spell_disable_trainers`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `spell_disable`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `spell_effects_override`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `spell_proc`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `spelloverride`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `teleport_coords`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `totemspells`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `trainer_defs`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `trainer_spells`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `trainerspelloverride`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `transport_creatures`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `transport_data`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `unit_display_sizes`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `vendor_restrictions`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `vendors`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `weather`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `wordfilter_character_names`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `wordfilter_chat`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `worldbroadcast_localized`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `worldbroadcast`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `worldmap_info_localized`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `worldmap_info`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `worldstring_tables_localized`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `worldstring_tables`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `zoneguards`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `recall`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `loot_skinning`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `loot_pickpocketing`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `loot_items`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `loot_gameobjects`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `loot_fishing`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `loot_creatures`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `graveyards`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `gameobject_teleports`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `gameobject_staticspawns`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `gameobject_spawns`;");
+	WorldDatabase.Query("OPTIMIZE TABLE `%s`.`accounts`;", logondatabasename);
+	WorldDatabase.Query("OPTIMIZE TABLE `%s`.`ipbans`;", logondatabasename);
+	CharacterDatabase.Query("OPTIMIZE TABLE `account_data`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `account_forced_permissions`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `arcemu_db_version`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `arenateams`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `auctions`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `banned_names`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `character_achievement`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `character_achievement_progress`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `characters`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `characters_insert_queue`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `charters`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `clientaddons`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `command_overrides`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `corpses`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `equipmentsets`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `gm_tickets`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `groups`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `guild_bankitems`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `guild_banklogs`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `guild_banktabs`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `guild_data`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `guild_logs`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `guild_ranks`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `guilds`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `instanceids`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `instances`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `mailbox`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `mailbox_insert_queue`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `playerbugreports`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `playercooldowns`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `playerdeletedspells`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `playeritems`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `playeritems_insert_queue`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `playerpets`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `playerpetspells`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `playerskills`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `playerspells`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `playersummons`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `playersummonspells`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `questlog`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `server_settings`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `social_friends`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `social_ignores`;");
+	CharacterDatabase.Query("OPTIMIZE TABLE `tutorials`;");
+}
\ No newline at end of file
Index: src/arcemu-world/DatabaseCleaner.h
===================================================================
--- src/arcemu-world/DatabaseCleaner.h	(revision 4187)
+++ src/arcemu-world/DatabaseCleaner.h	(working copy)
@@ -27,8 +27,10 @@
 	void Run();
 
 protected:
+	void CleanNonExistantSpells();
 	void CleanCharacters();
 	void CleanWorld();
+	void CleanLogon();
 	void Optimize();
 };
 


Attached File  databasecleaner.patch (48.62K)
Number of downloads: 18

This post has been edited by Marforius: 01 May 2011 - 03:22 AM

1

#2 User is offline   Marforius 

  • Permanantly on leave
  • PipPipPipPip
  • Group: Members
  • Posts: 597
  • Joined: 07-August 08
  • IRC:Marforius
  • Gender:Male
  • Location:Rome
  • Interests:ArcEmulatorius
  • Server OS:Linux

Posted 06 February 2012 - 08:26 PM

This has been updated, here:
https://github.com/M...152ffb1afd410ed
0

#3 User is offline   Magissia 

  • Member
  • Pip
  • Group: Members
  • Posts: 13
  • Joined: 24-August 08

Posted 04 April 2012 - 11:21 AM

Sorry for the dumb question :

This is suposed to make worldserver autoclean the database ?
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users