IMPORTANT: Database changes coming!

[John Adams]

In preparation for a database release (TBD) we have to make some dramatic changes to our existing DB structures. What this means to you is that soon, when you connect to our Login server, your databases will get many critical updates - including conversion to the DB engine to INNODB to support transactions, foreign keys, and a few other nice MySQL features.

After the InnoDB updates occur, each dynamic table will receive new foreign keys and relationships to make the database more stable and consistent.

THIS IS WHERE THE WARNING COMES IN!!!
Please pay attention to this, if nothing else.

If you have pre-existing data in your database that is INCONSISTENT, these FK/Relationships updates will fail!

This is not an error on the part of the updater, but in your data. This means you have to CLEAN OUT your bad data before you can get these updates applied.

My best advice for newer servers, delete everything and get an entirely new DB.

If you *must* preserve your user or custom data, I will instruct you (below) how to manually apply these updates _around_ your custom data.


First a little explanation of what Relationships mean in MySQL.

• Relationships between two tables generally mean, one table "relies" on another for consistency. Let's say you have a Character who is located in Zone ID 12. You cannot go to your Zones table and delete Zone ID 12 because a character is in that zone, thus keeping the database consistent.
  
  However, you can change the Zone ID of zone 12 to 112, and automatically, every Character that was in zone 12 is now updated to zone ID 112. This is called Cascading Updates.
  
  Now let's talk about another example. Say you have Character "Bob", and his char_id is 1. Bob has a dozen or more tables associated with his character data - like his details, his inventory, his spells, skills, etc. If you delete Bob, you'd also have to delete Bob's record from all those other tables manually (or in the server code).
  
  Using Relationships between `characters` and all the other `character_*` tables, and a Cascading Delete relationship, now when you delete Bob from the server, all his associated data is also deleted automatically. Like magic!

That is a quick and dirty explanation, and if you require more in-depth, google it


So using these examples above, you cannot setup a relationship between Characters and Zones *IF* there is a character sitting in a zone_id that does not exist. That is the biggest caveat of setting this stuff up right now. You have to go into your data and make sure you have ALL your data consistent, or the FK's will fail to update.

Likewise, if there is a deleted Characters record, but any of the other character_* tables still has an orphaned record for the deleted character, the relationship cannot be built. It is a tedious task, but once this is done, our database will be fairly solid.


I have chosen now to do this for many reasons; one being, I have promised to do this since new years of 2009 but also, our database is pretty much done being changed (constantly) so time to lock it down, and hopefully release some content.


Next post here will be the manual "HOW TO", so stay tuned while I write it up.


NOTE: You will not get these new DB updates if you do not update your EQ2Emu sources. You are safe to stay where you are and take your time cleaning up data first, then grab the new sources (version to be announced) to get these updates to apply.

[JCL]

Looking forward to some changes!

I was reading what you were saying about the consistency. An example of what I did for spawns/objects was made sure their ID's matched in every way (except for spawn_npc/spawn_object ID) and put the ID, model ID, model name, and entry name into an excel sheet so I can find them quickly and make deletions/modifications easily.

[John Adams]

Update: None of this post is needed. Use the awesome tool Scatman created below to check and clean up your inconsistent data before updating.

For most of you, you download the Binaries, setup your server, and connect to Login - World will download all the necessary tables to run your server, including any updates since release. Everything is relatively automatic, and works perfectly.


However, there are some of you who have custom data, or have existing servers with players on them (existing data) so updates of this magnitude require a little manual intervention. Below are a few queries you should run against your database to see if you have any "inconsistent data" that will prevent FK/Relationships from being applied.

Note: This should be done now, asap, immediately, before the updates are committed to the code, so you are sure to not have a problem on the day you get the updated code.

First, we have to ensure there are no orphaned records in any of the character* tables. There are a lot, so do these one at a time:
[code]SELECT COUNT(*) FROM char_colors WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_buyback WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_details WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_factions WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_factions WHERE faction_id NOT IN (SELECT id FROM factions);
SELECT COUNT(*) FROM character_friendlist WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_ignorelist WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_instances WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_items WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_items WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM character_language WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_language WHERE language_id NOT IN (SELECT language_id FROM languages);
SELECT COUNT(*) FROM character_macros WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_mail WHERE player_to_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_quest_progress WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_quest_progress WHERE quest_id NOT IN (SELECT quest_id FROM quests);
SELECT COUNT(*) FROM character_quests WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_quests WHERE quest_id NOT IN (SELECT quest_id FROM quests);
SELECT COUNT(*) FROM character_skillbar WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_skills WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_spells WHERE char_id NOT IN (SELECT id FROM characters);
SELECT COUNT(*) FROM character_spells WHERE spell_id NOT IN (SELECT id FROM spells);[/code]
Everyone of these must return 0 (zero)! If not, you have to delete the records from each table who's character ID does not exist in `characters`.


Next up, Items. Same thing here, all COUNTs should be 0 (zero):
[code]SELECT COUNT(*) FROM item_appearances WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_armor WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_armorset WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_bag WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_bauble WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_book WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_food WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_house WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_house_container WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_pattern WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_range WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_recipe WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_recipe_items WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_shield WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_skill WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_skills WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_thrown WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_details_weapon WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_effects WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_levels_override WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM item_stats WHERE item_id NOT IN (SELECT id FROM items);[/code]


Check your Spawns records now:
[code]SELECT COUNT(*) FROM npc_appearance WHERE spawn_id NOT IN (SELECT id FROM spawn);
SELECT COUNT(*) FROM npc_appearance_equip WHERE spawn_id NOT IN (SELECT id FROM spawn);
SELECT COUNT(*) FROM spawn_ground WHERE spawn_id NOT IN (SELECT id FROM spawn);
SELECT COUNT(*) FROM spawn_location_entry WHERE spawn_id NOT IN (SELECT id FROM spawn);
SELECT COUNT(*) FROM spawn_location_entry WHERE spawn_location_id NOT IN (SELECT id FROM spawn_location_name);
SELECT COUNT(*) FROM spawn_location_group WHERE placement_id NOT IN (SELECT id FROM spawn_location_placement);
SELECT COUNT(*) FROM spawn_location_group_associations WHERE group_id1 NOT IN (SELECT group_id FROM spawn_location_group);
SELECT COUNT(*) FROM spawn_location_group_associations WHERE group_id2 NOT IN (SELECT group_id FROM spawn_location_group);
SELECT COUNT(*) FROM spawn_location_group_chances WHERE group_id NOT IN (SELECT group_id FROM spawn_location_group);
SELECT COUNT(*) FROM spawn_location_placement WHERE spawn_location_id NOT IN (SELECT id FROM spawn_location_name);
SELECT COUNT(*) FROM spawn_location_placement WHERE zone_id NOT IN (SELECT id FROM zones);
SELECT COUNT(*) FROM spawn_loot WHERE spawn_id NOT IN (SELECT id FROM spawn);
SELECT COUNT(*) FROM spawn_loot WHERE loottable_id NOT IN (SELECT id FROM loottable);
SELECT COUNT(*) FROM spawn_npcs WHERE spawn_id NOT IN (SELECT id FROM spawn);
SELECT COUNT(*) FROM spawn_npcs WHERE equipment_list_id NOT IN (SELECT equipment_list_id FROM spawn_npc_equipment);
SELECT COUNT(*) FROM spawn_npcs WHERE spell_list_id NOT IN (SELECT spell_list_id FROM spawn_npc_spells);
SELECT COUNT(*) FROM spawn_npcs WHERE secondary_spell_list_id NOT IN (SELECT spell_list_id FROM spawn_npc_spells);
SELECT COUNT(*) FROM spawn_npcs WHERE skill_list_id NOT IN (SELECT skill_list_id FROM spawn_npc_skills);
SELECT COUNT(*) FROM spawn_npcs WHERE secondary_skill_list_id NOT IN (SELECT skill_list_id FROM spawn_npc_skills);
SELECT COUNT(*) FROM spawn_npc_equipment WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM spawn_npc_skills WHERE skill_id NOT IN (SELECT id FROM skills);
SELECT COUNT(*) FROM spawn_npc_spells WHERE spell_id NOT IN (SELECT id FROM spells);
SELECT COUNT(*) FROM spawn_objects WHERE spawn_id NOT IN (SELECT id FROM spawn);
SELECT COUNT(*) FROM spawn_signs WHERE spawn_id NOT IN (SELECT id FROM spawn);
SELECT COUNT(*) FROM spawn_widgets WHERE spawn_id NOT IN (SELECT id FROM spawn);[/code]

Note about Spawns: Not all `spawn_*` records are related to the parent `spawn` table. Some are related to other spawn_* tables, or things like items, skills, and spells. It's a crazy, confusing world, but it works - trust me


Spells tables:
[code]SELECT COUNT(*) FROM spell_classes WHERE spell_id NOT IN (SELECT id FROM spells);
SELECT COUNT(*) FROM spell_data WHERE spell_id NOT IN (SELECT id FROM spells);
SELECT COUNT(*) FROM spell_display_effects WHERE spell_id NOT IN (SELECT id FROM spells);
SELECT COUNT(*) FROM spell_tiers WHERE spell_id NOT IN (SELECT id FROM spells);[/code]


Those are our major systems tables. Now we have to check a few miscellaneous tables for consistency as well --


Faction Alliances must have a valid faction_id value in `factions` table:
[code]SELECT COUNT(*) FROM faction_alliances WHERE faction_id NOT IN (SELECT id FROM factions);[/code]


Groundspawn_* tables must be consistent with each other, and Items:
[code]SELECT COUNT(*) FROM groundspawn_bonus WHERE groundspawn_entry_id NOT IN (SELECT groundspawn_entry_id FROM groundspawn_items);
SELECT COUNT(*) FROM groundspawn_bonus WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM groundspawn_items WHERE item_id NOT IN (SELECT id FROM items);[/code]


Loot drops with Loot Tables and Items:
[code]SELECT COUNT(*) FROM lootdrop WHERE loot_table_id NOT IN (SELECT id FROM loottable);
SELECT COUNT(*) FROM lootdrop WHERE item_id NOT IN (SELECT id FROM items);[/code]


Merchant Inventory with Merchants table and items:
[code]SELECT COUNT(*) FROM merchant_inventory WHERE inventory_id NOT IN (SELECT inventory_id FROM merchants);
SELECT COUNT(*) FROM merchant_inventory WHERE item_id NOT IN (SELECT id FROM items);[/code]


Revive Points and Zones:
[code]SELECT COUNT(*) FROM revive_points WHERE zone_id NOT IN (SELECT id FROM zones);
SELECT COUNT(*) FROM revive_points WHERE respawn_zone_id NOT IN (SELECT id FROM zones);[/code]


Server "Starting" tables (stuff that players get when they create a character):
[code]SELECT COUNT(*) FROM starting_factions WHERE faction_id NOT IN (SELECT id FROM factions);
SELECT COUNT(*) FROM starting_items WHERE item_id NOT IN (SELECT id FROM items);
SELECT COUNT(*) FROM starting_skills WHERE skill_id NOT IN (SELECT id FROM skills);
SELECT COUNT(*) FROM starting_spells WHERE spell_id NOT IN (SELECT id FROM spells);
SELECT COUNT(*) FROM starting_zones WHERE zone_id NOT IN (SELECT id FROM zones);[/code]


So, if any of the above queries result in > 0 records found, it is your responsibility to find the orphaned records and delete them. The easiest way to do this is simply replace "SELECT COUNT(*)" with "DELETE" and re-run the queries. Backup your data first, of course

Example using the first COUNT above:
[code]DELETE FROM char_colors WHERE char_id NOT IN (SELECT id FROM characters);[/code]


Next up, how you can MANUALLY update your World tables to support these foreign key constraints and relationships, after you have verified your data is consistent Hopefully only a small handful of you have to do this entire process... or don't care about your data and can blow it away and start over. This entire Post/HowTo is for servers like Sytherian Legends and Parallax. Hope they are greatful. This took a lot of effort on my part

[Arremis]

Excellent work John. It's Miller Time!

[Scatman]

I am almost finished writing an application to automate this process. I'll post it once it's finished.

[Scatman]

Should be pretty straight forward to use:

[ZexisStryfe]

Well now, first off... thanks for the tool Scat- very helpful.

Now onto the bad news.

Code: Select all

Found 130 inconsistenies! Run the following query to clean:
DELETE FROM spawn_npcs WHERE equipment_list_id NOT IN (SELECT equipment_list_id FROM spawn_npc_equipment)
Found 130 inconsistenies! Run the following query to clean:
DELETE FROM spawn_npcs WHERE spell_list_id NOT IN (SELECT spell_list_id FROM spawn_npc_spells)
Found 130 inconsistenies! Run the following query to clean:
DELETE FROM spawn_npcs WHERE secondary_spell_list_id NOT IN (SELECT spell_list_id FROM spawn_npc_spells)
Found 130 inconsistenies! Run the following query to clean:
DELETE FROM spawn_npcs WHERE skill_list_id NOT IN (SELECT skill_list_id FROM spawn_npc_skills)
Found 130 inconsistenies! Run the following query to clean:
DELETE FROM spawn_npcs WHERE secondary_skill_list_id NOT IN (SELECT skill_list_id FROM spawn_npc_skills)

The reason for this is because when creating a spawn using "spawn create" and "spawn add", entries in these tables are not generated. Could we get this fixed before the DB changes or else creating a new spawn is gonna cause hella errors?

Also what are we supposed to do if we aren't giving creatures one of these (spells, equipment, items, skills)? None of them are required (there is no point to giving a chatty NPC a spell list or skills) so is it really necessary to make these entries mandatory?

Sorry to muck everything up John... evidently today I get to wear the asshat, er hat.

[John Adams]

The reason for this is because when creating a spawn using "spawn create" and "spawn add", entries in these tables are not generated. Could we get this fixed before the DB changes or else creating a new spawn is gonna cause hella errors?

Simple fix on the way! I haven't posted my epic post yet... the "manual way for you goofy people" post.

In it, it's going to tell you that at least for now, make an entry in those tables (our data will supply a generic one), and call it "equipment_list_id = 0", so all your "0"s in spawn_npcs will have a valid reference.

Let me shoot out my next post and have you look it over. You don't have to do this work now, but it won't hurt Emu if you do. TessEQ2 has been running InnoDB and the FK's since mid 2008.

[John Adams]

HOW TO MANUALLY UPDATE YOUR TABLES!
You custom server creators, you...

Ok, now that all your tables are consistent and there should be no errors creating constraints, here's how you can do it and preserve your data (ie., no DROP table, CREATE table). Again, this is only for those who wish to preserve their data! The rest of you, drop your DB and start over


First, in order for constraints/foreign keys to work at all on MySQL, the table engine must be INNODB - which hopefully yours should be by getting the table modifications from us. If you do not allow us to mod your tables, here's the command:

Code: Select all

ALTER TABLE `characters`  ENGINE=INNODB;

You have to do that for every table that shows as Engine = MyISAM. Here's the full script I used:

Code: Select all

ALTER TABLE `appearances` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `bugs` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `characters`  ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `char_colors` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `character_access` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `character_details` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `character_factions` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `character_friendlist` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `character_items` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `character_language` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `character_macros` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `character_skillbar` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `character_skills` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `character_spells` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `commands` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `entity_commands` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `factions` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `item_appearances` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `item_details_armor` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `item_details_bag` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `item_details_food` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `item_details_range` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `item_details_weapon` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `item_effects` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `item_stats` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `languages` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `name_filter` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `opcodes` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `revive_points` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `skills` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `spell_data` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `spell_display_effects` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `spells` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `starting_items` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `starting_skillbar` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `starting_skills` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `starting_spells` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `starting_zones` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `table_versions` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `variables` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `visual_states` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `zones` ENGINE=INNODB CHECKSUM=1 AUTO_INCREMENT=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC CHARSET=latin1 COLLATE=latin1_swedish_ci;

If you re-check your database table list, you should now see every Engine as "InnoDB". If not, change it manually before proceeding.


Let's get the Character-related tables updated with the relationships (constraints/FKs). You will notice some of these have 2 constraints, and I tried to comment so you know what the difference is. ALL character-related tables have `characters` as a parent record. Some tables use other resources, which must also become related.

Code: Select all

ALTER TABLE `char_colors` ADD CONSTRAINT `FK_char_colors` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `character_buyback` ADD CONSTRAINT `FK_character_buyback` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `character_details` ADD CONSTRAINT `FK_character_details` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `character_factions` ADD CONSTRAINT `FK_character_factions` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
-- if factions.id changes / do not allow faction delete unless no characters have that faction
ALTER TABLE `character_factions` ADD CONSTRAINT `FK_factions` FOREIGN KEY (`faction_id`) REFERENCES `factions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;

ALTER TABLE `character_friendlist` ADD CONSTRAINT `FK_character_friendlist` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `character_items` ADD CONSTRAINT `FK_character_items` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
-- if items.id changes / do not allow item delete unless no characters have that item
ALTER TABLE `character_items` ADD CONSTRAINT `FK_char_items` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `character_language` ADD CONSTRAINT `FK_character_language` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
-- if language.id changes / do not allow language delete unless no characters have that language
ALTER TABLE `character_language` ADD CONSTRAINT `FK_languages` FOREIGN KEY (`language_id`) REFERENCES `languages` (`language_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `character_macros` ADD CONSTRAINT `FK_character_macros` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `character_quest_progress` ADD CONSTRAINT `FK_character_quest_progress` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
-- if quest.quest_id changes / do not allow quest delete unless no characters have that quest
ALTER TABLE `character_quest_progress` ADD CONSTRAINT `FK_quest_id` FOREIGN KEY (`quest_id`) REFERENCES `quests` (`quest_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `character_quests` ADD CONSTRAINT `FK_character_quests` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
-- if quest.quest_id changes / do not allow quest delete unless no characters have that quest
ALTER TABLE `character_quests` ADD CONSTRAINT `FK_quest_quests` FOREIGN KEY (`quest_id`) REFERENCES `quests` (`quest_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `character_skillbar` ADD CONSTRAINT `FK_character_skillbar` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `character_skills` ADD CONSTRAINT `FK_character_skills` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `character_spells` ADD CONSTRAINT `FK_character_spells` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
-- if spells.id changes / do not allow spell delete unless no characters have that spell
ALTER TABLE `character_spells` ADD CONSTRAINT `FK_char_spells` FOREIGN KEY (`spell_id`) REFERENCES `spells` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

-- if zones.id changes / do not allow zone delete unless no characters exist in that zone
ALTER TABLE `characters` ADD CONSTRAINT `FK_char_zone` FOREIGN KEY (`current_zone_id`) REFERENCES `zones` (`id`) ON DELETE RESTRICT  ON UPDATE CASCADE ;

That's it for Characters. Now, when a record from `characters` gets deleted, all associated data for that characters ID (char_id) also gets removed from all other tables - nice and clean. Server doesn't have to work so hard, and that's the point. Putting the work on MySQL.


Moving on, we have Items:

Code: Select all

ALTER TABLE `item_appearances` ADD CONSTRAINT `FK_item_appearances` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_armor` ADD CONSTRAINT `FK_item_details_armor` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_armorset` ADD CONSTRAINT `FK_item_details_armorset` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_bag` ADD CONSTRAINT `FK_item_details_bag` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_bauble` ADD CONSTRAINT `FK_item_details_bauble` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_book` ADD CONSTRAINT `FK_item_details_book` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_food` ADD CONSTRAINT `FK_item_details_food` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_house` ADD CONSTRAINT `FK_item_details_house` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_house_container` ADD CONSTRAINT `FK_item_details_house_container` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_pattern` ADD CONSTRAINT `FK_item_details_pattern` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_range` ADD CONSTRAINT `FK_item_details_range` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_recipe` ADD CONSTRAINT `FK_item_details_recipe` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_recipe_items` ADD CONSTRAINT `FK_item_details_recipe_items` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_shield` ADD CONSTRAINT `FK_item_details_shield` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_skill` ADD CONSTRAINT `FK_item_details_skill` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_skills` ADD CONSTRAINT `FK_item_details_skills` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_thrown` ADD CONSTRAINT `FK_item_details_thrown` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_details_weapon` ADD CONSTRAINT `FK_item_details_weapon` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_effects` ADD CONSTRAINT `FK_item_effects` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_levels_override` ADD CONSTRAINT `FK_item_levels_override` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `item_stats` ADD CONSTRAINT `FK_item_stats` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

Items are straight-forward, as they do not reference out to any other tables (currently).


Next are Spawns structures. Before we fly through them however, there are a couple of important data entries we need in order for the references to work. As long as your spawn_npc_equipment, spawn_npc_skills, spawn_npc_spells tables are empty, do the following:

Code: Select all

INSERT INTO `items`(`id`,`name`,`item_type`,`icon`,`count`,`tier`,`skill_id_req`,`skill_id_req2`,`skill_min`,`skill_max`,`weight`,`description`,`show_name`,`attuneable`,`artifact`,`lore`,`temporary`,`notrade`,`novalue`,`nozone`,`nodestroy`,`crafted`,`good_only`,`evil_only`,`ornate`,`heirloom`,`usable`,`slots`,`set_name`,`sell_price`,`stack_count`,`collectable`,`adornment_description`,`offers_quest_id`,`part_of_quest_id`,`quest_unknown`,`max_charges`,`recommended_level`,`adventure_default_level`,`tradeskill_default_level`,`adventure_classes`,`tradeskill_classes`,`soe_id`,`lua_script`) VALUES ( 1,'empty','Normal','0','0','1','0','0','0','0','0',NULL,'1','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0',NULL,'0','1','0',NULL,'0','0','0','0','0','1','1','0','0','0',NULL);
    INSERT INTO `spells`(`id`,`type`,`cast_type`,`name`,`description`,`icon`,`icon2`,`icontype`,`class_skill`,`mastery_skill`,`min_class_skill_req`,`duration_until_cancel`,`target_type`,`success_message`,`fade_message`,`interruptable`,`lua_script`,`spell_visual`,`effect_message`,`spell_book_type`,`can_effect_raid`,`affect_only_group_members`,`display_spell_tier`,`friendly_spell`,`group_spell`,`is_active`) VALUES ( '3','0','0','NoName','reference for spawn_npc_spells table sets - DO NOT DELETE!','0','0','0','0','0','0','0','0',NULL,NULL,'1',NULL,'0',NULL,'0','0','0','0','0','0','0');
    INSERT INTO `spawn_npc_equipment`(`id`,`equipment_list_id`,`item_id`) VALUES ( 1,'0','1');
    INSERT INTO `spawn_npc_skills`(`id`,`skill_list_id`,`skill_id`,`starting_value`) VALUES ( '1','0','1','0');
    INSERT INTO `spawn_npc_spells`(`id`,`spell_list_id`,`spell_id`,`spell_tier`) VALUES ( '1','0','3','1');

Since the spawn_npcs table defaults to '0' for these 5 settings (3 primary lists and 2 secondary lists), unfortunately we have to set a bogus equip, skill and spell entry in our tables. Insert a single record into Items, and Spells (we can use skills.id 1 for now). If they are all ID 1, the above insert should be fine for you.

Now the Spawn references:

Code: Select all

-- if spawn.id changes
ALTER TABLE `spawn_ground` ADD CONSTRAINT `FK_groundspawn` FOREIGN KEY (`spawn_id`) REFERENCES `spawn` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `spawn_loot` ADD CONSTRAINT `FK_loot_spawn` FOREIGN KEY (`spawn_id`) REFERENCES `spawn` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
-- if loottable.id changes
ALTER TABLE `spawn_loot` ADD CONSTRAINT `FK_spawnloot_loottable` FOREIGN KEY (`loottable_id`) REFERENCES `loottable` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE;

ALTER TABLE `spawn_npc_equipment` ADD CONSTRAINT `FK_spawn_npc_equipment` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_npc_skills` ADD CONSTRAINT `FK_spawn_skills` FOREIGN KEY (`skill_id`) REFERENCES `skills` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_npc_spells` ADD CONSTRAINT `FK_spawn_npc_spells` FOREIGN KEY (`spell_id`) REFERENCES `spells` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `spawn_npcs` ADD CONSTRAINT `FK_npcs_spawn` FOREIGN KEY (`spawn_id`) REFERENCES `spawn` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_npcs` ADD CONSTRAINT `FK_spawn_equipment_list` FOREIGN KEY (`equipment_list_id`) REFERENCES `spawn_npc_equipment` (`equipment_list_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_npcs` ADD CONSTRAINT `FK_spawn_skill_list` FOREIGN KEY (`skill_list_id`) REFERENCES `spawn_npc_skills` (`skill_list_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_npcs` ADD CONSTRAINT `FK_spawn_sec_skill` FOREIGN KEY (`secondary_skill_list_id`) REFERENCES `spawn_npc_skills` (`skill_list_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_npcs` ADD CONSTRAINT `FK_spawn_spell_list` FOREIGN KEY (`spell_list_id`) REFERENCES `spawn_npc_spells` (`spell_list_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_npcs` ADD CONSTRAINT `FK_spawn_sec_spell` FOREIGN KEY (`secondary_spell_list_id`) REFERENCES `spawn_npc_spells` (`spell_list_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

ALTER TABLE `spawn_objects` ADD CONSTRAINT `FK_objects_spawn` FOREIGN KEY (`spawn_id`) REFERENCES `spawn` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_signs` ADD CONSTRAINT `FK_signs_spawn` FOREIGN KEY (`spawn_id`) REFERENCES `spawn` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_widgets` ADD CONSTRAINT `FK_widgets_spawn` FOREIGN KEY (`spawn_id`) REFERENCES `spawn` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

-- Spawn location data
ALTER TABLE `spawn_location_entry` ADD CONSTRAINT `FK_spawn_entry1` FOREIGN KEY (`spawn_location_id`) REFERENCES `spawn_location_name` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_location_entry` ADD CONSTRAINT `FK_spawn_entry2` FOREIGN KEY (`spawn_id`) REFERENCES `spawn` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_location_placement` ADD CONSTRAINT `FK_placement1` FOREIGN KEY (`spawn_location_id`) REFERENCES `spawn_location_name` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_location_placement` ADD CONSTRAINT `FK_placement2` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_location_group` ADD CONSTRAINT `FK_group_placement` FOREIGN KEY (`placement_id`) REFERENCES `spawn_location_placement` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spawn_location_group_associations` ADD CONSTRAINT `FK_group_association1` FOREIGN KEY (`group_id1`) REFERENCES `spawn_location_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `spawn_location_group_associations` ADD CONSTRAINT `FK_group_association2` FOREIGN KEY (`group_id2`) REFERENCES `spawn_location_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `spawn_location_group_chances` ADD CONSTRAINT `FK_group_chances` FOREIGN KEY (`group_id`) REFERENCES `spawn_location_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Spawn data is quite complex, so everything needs to be perfect for this to work. First, you are tying all spawn_* tables to the parent table `spawn`, except for some that tie back to Items, Spells, and Skills, or within each subset of tables (like the spawn_location_* stuff).


Last major system is Spells, and it is much simpler than the others:

Code: Select all

ALTER TABLE `spell_classes` ADD CONSTRAINT `FK_spell_classes` FOREIGN KEY (`spell_id`) REFERENCES `spells` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spell_data` ADD CONSTRAINT `FK_spell_data` FOREIGN KEY (`spell_id`) REFERENCES `spells` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spell_display_effects` ADD CONSTRAINT `FK_spell_display_effects` FOREIGN KEY (`spell_id`) REFERENCES `spells` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `spell_tiers` ADD CONSTRAINT `FK_spell_tiers` FOREIGN KEY (`spell_id`) REFERENCES `spells` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

See? Simple.


Now the remaining constraints:

Faction Alliances:

Code: Select all

-- if factions.id changes
ALTER TABLE `faction_alliances` ADD CONSTRAINT `FK_faction_alliances` FOREIGN KEY (`faction_id`) REFERENCES `factions` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

GroundSpawns:

Code: Select all

-- if items.id changes
ALTER TABLE `groundspawn_items` ADD CONSTRAINT `FK_groundspawn_items` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

-- if groundspawn_items.groundspawn_entry_id changes
ALTER TABLE `groundspawn_bonus` ADD CONSTRAINT `FK_groundspawn_bonus` FOREIGN KEY (`groundspawn_entry_id`) REFERENCES `groundspawn_items` (`groundspawn_entry_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

Loot Tables:

Code: Select all

-- if items.id changes
ALTER TABLE `lootdrop` ADD CONSTRAINT `FK_lootdrop` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

-- if loottable.id changes
ALTER TABLE `lootdrop` ADD CONSTRAINT `FK_loottable` FOREIGN KEY (`loot_table_id`) REFERENCES `loottable` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

Merchant Inventory (this one's a little goofy for some reason):

Code: Select all

ALTER TABLE `merchants` DROP INDEX `MerchantsIDX`;
    ALTER TABLE `merchant_inventory` ADD CONSTRAINT `FK_merchant_inventory1` FOREIGN KEY (`inventory_id`) REFERENCES `merchants` (`inventory_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
    ALTER TABLE `merchant_inventory` ADD CONSTRAINT `FK_merchant_inventory2` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
    ALTER TABLE `merchant_inventory` ADD UNIQUE `InventoryItemIDX` (`inventory_id`, `item_id`);
    ALTER TABLE `merchants` ADD UNIQUE `MerchantInvIDX` (`merchant_id`, `inventory_id`);

Quest Details:

Code: Select all

ALTER TABLE `quest_details` ADD CONSTRAINT `FK_quest_details` FOREIGN KEY (`quest_id`) REFERENCES `quests` (`quest_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Revive Points:

Code: Select all

ALTER TABLE `revive_points` ADD CONSTRAINT `FK_respawn_points` FOREIGN KEY (`respawn_zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `revive_points` ADD CONSTRAINT `FK_revive_zone` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

Starting Tables:

Code: Select all

-- if factions.id changes
ALTER TABLE `starting_factions` ADD CONSTRAINT `FK_starting_factions` FOREIGN KEY (`faction_id`) REFERENCES `factions` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE;

-- if items.id changes
ALTER TABLE `starting_items` ADD CONSTRAINT `FK_starting_items` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE;

-- if skills.id changes
ALTER TABLE `starting_skills` ADD CONSTRAINT `FK_starting_skills` FOREIGN KEY (`skill_id`) REFERENCES `skills` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE;

-- if spells.id changes
ALTER TABLE `starting_spells` ADD CONSTRAINT `FK_starting_spells` FOREIGN KEY (`spell_id`) REFERENCES `spells` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE;

-- if zones.id changes
ALTER TABLE `starting_zones` ADD CONSTRAINT `FK_starting_zones` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE;

Transporters:

Code: Select all

ALTER TABLE `transporters` ADD CONSTRAINT `FK_transporters` FOREIGN KEY (`destination_zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
ALTER TABLE `transporters` ADD CONSTRAINT `FK_transporters2` FOREIGN KEY (`trigger_location_zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

WHEW!! I think that's it. Don't be too intimidated by these queries. In most cases if your data is cleaned up, the queries will run without a hitch. If you find a hitch (Zexis.........) just have a backup on hand for worse-case-scenarios... and post your problems here, I'll be happy to help customites get through this one.

[John Adams]

First, in order for constraints/foreign keys to work at all on MySQL, the table engine must be INNODB - which hopefully yours should be by getting the table modifications from us. If you do not allow us to mod your tables, here's the command

This is actually a question I've been meaning to ask for a long time. Zexis, Bolly, JCL... for your custom servers, I figure you do probably still get your updates by running World, since there really is no Content to come down and clobber your custom data.

What are your plans when we DO provide "official eq2emu content"? Will you stop getting Table AND data updates from us? If so, how will your emulators maintain compatibility with our new code changes/DB changes?

I think if you still get Structural DB changes (Tables) and not Data, your data could be ok... except in those cases (like tonight) when I made a change to guild_ranks_defaults, and dropped the table to create a new one.

Let's talk. I need to know what you guys do, want, need, etc.

[ZexisStryfe]

What I have been doing as of late is just pulling down table structs. Well, that's not true... I pull down data too, but all the tables containing custom stuff I have manually kicked their data version up to like 100,000 so it won't overwrite that info.

I think with me just pulling down structs is the best option, however there is stuff like opcode data that I still need. Another big problem I am gonna have is widgets (doors in particular) but that is a whole other issue I think.

[John Adams]

Not to derail my own thread, but for widgets, you could probably keep a copy of the zone data we do, and just snag our widgets, doors, and signs data wherever you need it. As for placing your own that SOE hasn't, I am not sure if that'll work. Widget_ID eludes me still.

[John Adams]

Should be pretty straight forward to use:

Scatman, that is literally the most wonderful thing I have seen in months

So very helpful. It will eliminate most of the pre-checking I detailed above. USE THIS TOOL! It's awesome.

[Astal]

Should be pretty straight forward to use:

Scatman, that is literally the most wonderful thing I have seen in months

So very helpful. It will eliminate most of the pre-checking I detailed above. USE THIS TOOL! It's awesome.

John at the moment my server is still working. Ive used scats tool, it fixed everything but I followed your steps and im having a few problems.

what does

If you re-check your database table list, you should now see every Engine as "InnoDB". If not, change it manually before proceeding.

mean, i use navicat and it has Table Information and under General a field says Table Type InnoDB, is that what your talking about. All but character_house character_house_access character_house_deposit and character_house_spawn say InnoDB, those 4 say MyISAM as the type.

Ok i see u said any that the Engine says MyISAM must be changed, They dont change with that query and i have no idea how to change those 4

When i try to do the other queries im getting errors then.

[John Adams]

All but character_house character_house_access character_house_deposit and character_house_spawn say InnoDB, those 4 say MyISAM as the type.

Those 3 tables should be dropped. Not sure if I've released those queries, but if those are the only ones with MyISAM, you're in good shape.


Now to brag just a little. I ran the Consistency Checker on the TessEQ2 DB and have 3 inconsistencies! THREE! I am happy.

tess-clean!.jpg

I've hit a lull in updating the constraints (listed above), but soon as I have a chance to talk with LE about the update process, I'll commit the rest and we can test it out. Meanwhile, just make sure your custom databases are nice and consistent.

Remember (as Zexis pointed out), if it is spawn_npc_equipment, spawn_npc_skills or spawn_npc_spells giving you issues, you have to create BOGUS records with *_list_id = 0 in order to get through this change. For those of you not wanting this headache, just wait til we release 0.7.0 + DB, and your worries will be solved.

All this nightmare is just for those silly people who are already building custom server content while the emulator is in Alpha