Cleaning Up Your Database (Ascent derivatives - Aspire, Hearth, Arc, etc)

**TheSpidey** · 07-18-2009

If you're like me, you probably get your world-db info from various places, merging several releases together. I, for one, got my info from an old NCDB release coupled with WhyDB.
Even if you're not like me, you're probably using a db which is already a merge of other old projects.
The problem with it is, your db info gathers dust. You hold information that isn't right or relevant, mostly about spells - Trainers, items, AI agents and more - needlessly taking space and hindering performance.
In this guide I'll show you how to find out the amount of wrong info you're holding and remove it.

Prerequisite - The spell table
We'll start by obtaining the spell table. This is a must for most of the cleanup we're going to do.

Open your SpellFixes.cpp file, and locate the line //#define DUMP_SPELLS_SQL 1. It should be right after the license. Uncomment it (as seen in the pic) and recompile your core.

Start your server. Once started, feel free to shut it down, re-comment that line and recompile. You only need one startup of the server for it to dump the spells.
Check your binary folder, you should see a file called spells.sql which weighs a good amount of MBs.

Use your favorite MySQL client to import that sql file into a database of your choice. I chose to create a new database for this, called research, since this info is not world-db-specific.
(Note: SQLYog seemed to have a bit of an issue importing this sql file, so I opened it up with notepad, removed the DROP TABLE statement, executed the CREATE TABLE manually and removed that too from the file. Then the SQL had no issues being imported)

Cleaning up
In the following examples I'm going to assume you're executing the statements on your world db, with the spell table being in a different db called research. Modify the statements to reflect your structure.
Additionally, I recommend you to SELECT COUNT(*) before doing actual changes. Yes, most clients will tell you how many rows you've changed automatically, but it is good practice to check that before doing actual changes to your db.
For example, before executing the following statement:

Code:

DELETE FROM ai_agents
 WHERE spell NOT IN (SELECT entry FROM research.spells);

I recommend running this one:

Code:

SELECT COUNT(*)
  FROM ai_agents
 WHERE spell NOT IN (SELECT entry FROM research.spells);

To see exactly how many rows you're going to delete.

If such a need arises, I might write a small script to do these cleanups automatically

1) Creature names with missing proto

Code:

DELETE FROM creature_names
  WHERE entry NOT IN (SELECT entry FROM creature_proto);

2) Creature proto with no matching creature_names (Warning! Probable undesired behavior)
This will remove any proto that doesn't have a creature_name to match it.

Code:

DELETE FROM creature_proto
  WHERE entry NOT IN (SELECT entry FROM creature_names)

3) Invalid spawns

Code:

DELETE FROM creature_spawns
  WHERE entry NOT IN (SELECT entry FROM creature_names);

4) Waypoints of creatures that aren't spawned

Code:

DELETE FROM creature_waypoints
  WHERE spawnid NOT IN (SELECT id FROM creature_spawns);

5) Invalid static spawns

Code:

FROM creature_staticspawns
  WHERE entry NOT IN (SELECT entry FROM creature_names);

6) Invalid formations

Code:

DELETE FROM creature_formations
  WHERE spawn_id NOT IN (SELECT id FROM creature_spawns);

7) Missing quest starters

Code:

DELETE FROM creature_quest_starter
  WHERE id NOT IN (SELECT entry FROM creature_names);

Missing quest finishers

Code:

DELETE FROM creature_quest_finisher
  WHERE id NOT IN (SELECT entry FROM creature_names);

9) Gossip text not belonging to any creature

Code:

DELETE FROM npc_gossip_textid
  WHERE creatureid NOT IN (SELECT entry FROM creature_names);

10) Entries in monster say without a creature

Code:

DELETE FROM npc_monstersay
  WHERE entry NOT IN (SELECT entry FROM creature_names);
DELETE FROM npc_monstersay_localized
  WHERE entry NOT IN (SELECT entry FROM creature_names);

11) Entries in npc_text without an npc

Code:

DELETE FROM npc_text
  WHERE entry NOT IN (SELECT entry FROM creature_names);
DELETE FROM npc_text_localized
  WHERE entry NOT IN (SELECT entry FROM creature_names);

12) Pickpocketing and skinning loot that doesn't relate to a valid creature or a valid item

Code:

DELETE FROM pickpocketingloot
  WHERE entryid NOT IN (SELECT entry FROM creature_names)
     OR itemid NOT IN (SELECT entry FROM items);
DELETE FROM skinningloot
  WHERE entryid NOT IN (SELECT entry FROM creature_names)
     OR itemid NOT IN (SELECT entry FROM items);

13) On-kill Reputation for non-existent creatures

Code:

DELETE FROM reputation_creature_onkill
  WHERE creature_id NOT IN (SELECT entry FROM creature_names);

14) Trainer definitions of non-existent creatures, and trainer spells belonging to no real trainers

Code:

DELETE FROM trainer_defs
  WHERE entry NOT IN (SELECT entry FROM creature_names);
DELETE FROM trainer_spells
  WHERE entry NOT IN (SELECT entry FROM trainer_defs);

15) Non existent creatures and items in the vendors table

Code:

DELETE FROM vendors
  WHERE entry NOT IN (SELECT entry FROM creature_names)
     OR item NOT IN (SELECT entry FROM items);

16) AI Agents with missing creatures

Code:

DELETE FROM ai_agents
  WHERE entry NOT IN (SELECT entry FROM creature_names);

17) AI Agents with missing spells

Code:

DELETE FROM ai_agents
  WHERE spell NOT IN (SELECT entry FROM research.spell);

1 Update non-existent spells from items

Code:

UPDATE items
   SET spellid_1 = 0
WHERE spellid_1 != 0 AND (spellid_1 IS NULL OR spellid_1 NOT IN (SELECT entry FROM research.spell));
UPDATE items
   SET spellid_2 = 0
WHERE spellid_2 != 0 AND (spellid_2 IS NULL OR spellid_2 NOT IN (SELECT entry FROM research.spell));
UPDATE items
   SET spellid_3 = 0
WHERE spellid_3 != 0 AND (spellid_3 IS NULL OR spellid_3 NOT IN (SELECT entry FROM research.spell));
UPDATE items
   SET spellid_4 = 0
WHERE spellid_4 != 0 AND (spellid_4 IS NULL OR spellid_4 NOT IN (SELECT entry FROM research.spell));
UPDATE items
   SET spellid_5 = 0
WHERE spellid_5 != 0 AND (spellid_5 IS NULL OR spellid_5 NOT IN (SELECT entry FROM research.spell));

19) Non-existent auctioneers

Code:

DELETE FROM auctionhouse
  WHERE creature_entry NOT IN (SELECT entry FROM creature_names);

20) Non-existent items in creature loot

Code:

DELETE FROM creatureloot
  WHERE itemid NOT IN (SELECT entry FROM items);

21) Non-existent items in disenchanting and fishing loot

Code:

DELETE FROM disenchantingloot
  WHERE entryid NOT IN (SELECT entry FROM items)
     OR itemid NOT IN (SELECT entry FROM items);
DELETE FROM fishingloot
  WHERE itemid NOT IN (SELECT entry FROM items);

22) Invalid data in item_quest_association

Code:

DELETE FROM item_quest_association
  WHERE item NOT IN (SELECT entry FROM items)
     OR quest NOT IN (SELECT entry FROM quests);

23) Non-existent items in randomprops and randomsuffixes

Code:

DELETE FROM item_randomprop_groups
  WHERE entry_id NOT IN (SELECT entry FROM items);
DELETE FROM item_randomsuffix_groups
  WHERE entry_id NOT IN (SELECT entry FROM items);

24) Invalid containers

Code:

DELETE FROM itemloot
  WHERE entryid NOT IN (SELECT entry FROM items)
     OR itemid NOT IN (SELECT entry FROM items);

25) Invalid playercreateinfo data

Code:

DELETE FROM playercreateinfo_items
  WHERE protoid NOT IN (SELECT entry FROM items);
DELETE FROM playercreateinfo_spells
  WHERE spellid NOT IN (SELECT entry FROM research.spell);

26) Spell specifics

Code:

DELETE FROM spell_coef_override
  WHERE id NOT IN (SELECT entry FROM research.spell);
DELETE FROM spell_disable
  WHERE spellid NOT IN (SELECT entry FROM research.spell);
DELETE FROM spell_disable_trainers
  WHERE spellid NOT IN (SELECT entry FROM research.spell);
DELETE FROM spell_forced_targets
  WHERE spellid NOT IN (SELECT entry FROM research.spell);
DELETE FROM spellfixes
  WHERE spellid NOT IN (SELECT entry FROM research.spell);
DELETE FROM spelloverride
  WHERE spellid NOT IN (SELECT entry FROM research.spell)

27) Invalid trainer spells

Code:

DELETE FROM trainer_spells
 WHERE (cast_spell != 0
        AND cast_spell NOT IN (SELECT entry FROM research.spell))
    OR (learn_spell != 0
        AND learn_spell NOT IN (SELECT entry FROM research.spell));
DELETE FROM trainerspelloverride
 WHERE spellid NOT IN (SELECT entry FROM research.spell);