Critical DB structure changes

[John Adams]

All,
I am implementing foreign keys and constraints to the eq2emulator database "spawn" structures. This will only effect you if you use the DB Update source on port 9102, as this is only for development builds of the server. Also, only if you have manually created your own spawn records *incorrectly*. Correct spawn entries should see no problems.
How this might effect you - if you have created spawns that have invalid references, your server might have problems starting or at least spawning what you expect. This change will allow us to insert, update, and delete data more efficiently, and maintain good referential integrity.
Basically what this means is that now, instead of deleting or updating 10 tables when something changes, you make the change to `spawns` (spawn_id changes) and they cascade through all tables that reference spawn_id, etc.
Effected tables are:

Code: Select all

lootdrop
loottable
npc_appearance
npc_appearance_equip
spawn
spawn_loot
spawn_npcs
spawn_objects
spawn_script_data
spawn_signs
spawn_widgets
zonespawngroup
zonespawnentry
zonespawns

I highly recommend you backup your local databases immediately before your next startup using the DB Updater on port 9102.
This is your only warning, developers.
It should not hurt anything unless as I said, you already have orphaned records or broken references. If these changes work out as I expect, they will go live (9101) during the next major release of the emulator server.
This is what we are hoping you see after todays update:

[Status] Connecting to Update Server..
[Status] Connected to Update Server:eq2emulator.net:9102
[Status] Checking for updates.
[Status] Found Updates.
[Status] Processing Updates.
[Status] Attempting to update database table 'items' ... SUCCESS!
[Status] Attempting to update database table 'npc_appearance' ... SUCCESS!
[Status] Attempting to update database table 'npc_appearance_equip' ... SUCCESS!
[Status] Attempting to update database table 'spawn_script_data' ... SUCCESS!
[Status] Attempting to update database table 'zonespawns' ... SUCCESS!
[Status] Attempting to update database table 'spawn_signs' ... SUCCESS!
[Status] Attempting to update database table 'spawn_npcs' ... SUCCESS!
[Status] Attempting to update database table 'spawn_objects' ... SUCCESS!
[Status] Attempting to update database table 'spawn_widgets' ... SUCCESS!
[Status] Attempting to update database table 'zonespawnentry' ... SUCCESS!
[Status] Attempting to update database table 'lootdrop' ... SUCCESS!
[Status] Attempting to update database table 'spawn_loot' ... SUCCESS!
[Status] Update complete.

Success!

[ZexisStryfe]

I am getting all errors John. They look like this for every table in your list:
"Error] Error in updating tables query 'DROP TABLE IF EXISTS 'items': #1217: Cannot Delete or update a parent row: a foreign key restraint fails
Error] Attempting to update database tabe 'items' ... FAILED!"
In addition, I'm getting this error:
"Error] Error in updating tables query 'CREATE TABLE 'visual_states' (
'id' int(10) unsigned NOT NULL auto_increment,
'name' varchar(64) NOT NULL default 'None',
PRIMARY KEY ('id')
ENGINE=MyISAM AUTO_INCREMENT=48398 DEFAULT CHARSET=latin1': #1050: Table 'visual_states' already exists."
Any Ideas?

[ZexisStryfe]

On a side note I also tried the update with a blank database, and that caused all sorts of problems due to the fact that the foreign keys needed to create some tables reference tables that haven't been created yet due to the order of operations.

[John Adams]

I have no idea about the table CREATE, since that table wasn't part of this update.
The DROP TABLE items scares me a little... The ALTER is only to change the engine type from MyISAM to InnoDB. The foreign key restraint usually means data is missing that is required for the constraint to be valid - ie., you have an item_id in lootdrops that does not exist in the items table (not that this is your exact problem, but an example).
So both of your problems do not seem normal. Can you paste another few errors maybe? I am not sure what's going on. But here are the ALTERs separately, maybe you can run each one individually and see which one is messing you up:

Code: Select all

ALTER TABLE `items` ENGINE = InnoDB;
ALTER TABLE `npc_appearance` ENGINE = InnoDB;
ALTER TABLE `npc_appearance_equip` ENGINE = InnoDB;
ALTER TABLE `spawn_script_data` ENGINE = InnoDB;
ALTER TABLE `spawn_npcs` ADD CONSTRAINT `FK_spawn_npcs_1` FOREIGN KEY `FK_spawn_npcs_1` (`spawn_id`)
    REFERENCES `spawn` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = FIXED;
ALTER TABLE `npc_appearance` ADD CONSTRAINT `FK_npc_appearance_1` FOREIGN KEY `FK_npc_appearance_1` (`spawn_id`)
    REFERENCES `spawn` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;
ALTER TABLE `npc_appearance_equip` ADD CONSTRAINT `FK_npc_appearance_equip_1` FOREIGN KEY `FK_npc_appearance_equip_1` (`spawn_id`)
    REFERENCES `spawn` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;
ALTER TABLE `spawn_loot` ADD CONSTRAINT `FK_spawn_loot_1` FOREIGN KEY `FK_spawn_loot_1` (`spawn_id`)
    REFERENCES `spawn` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;
ALTER TABLE `spawn_loot` ADD CONSTRAINT `FK_spawn_loot_tableid` FOREIGN KEY `FK_spawn_loot_tableid` (`loottable_id`)
    REFERENCES `loottable` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;
ALTER TABLE `lootdrop` ADD CONSTRAINT `FK_lootdrop_1` FOREIGN KEY `FK_lootdrop_1` (`loot_table_id`)
    REFERENCES `loottable` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;
ALTER TABLE `lootdrop` ADD CONSTRAINT `FK_lootdrop_items` FOREIGN KEY `FK_lootdrop_items` (`item_id`)
    REFERENCES `items` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;
ALTER TABLE `spawn_objects` ADD CONSTRAINT `FK_spawn_objects_1` FOREIGN KEY `FK_spawn_objects_1` (`spawn_id`)
    REFERENCES `spawn` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = FIXED;
ALTER TABLE `spawn_script_data` ADD CONSTRAINT `FK_spawn_script_data_1` FOREIGN KEY `FK_spawn_script_data_1` (`spawn_group_id`)
    REFERENCES `zonespawngroup` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;
ALTER TABLE `spawn_signs` ADD CONSTRAINT `FK_spawn_signs_1` FOREIGN KEY `FK_spawn_signs_1` (`spawn_id`)
    REFERENCES `spawn` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;
ALTER TABLE `spawn_widgets` ADD CONSTRAINT `FK_spawn_widgets_1` FOREIGN KEY `FK_spawn_widgets_1` (`spawn_id`)
    REFERENCES `spawn` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;
ALTER TABLE `zonespawnentry` ADD CONSTRAINT `FK_zonespawnentry_1` FOREIGN KEY `FK_zonespawnentry_1` (`spawn_id`)
    REFERENCES `spawn` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;
ALTER TABLE `zonespawnentry` ADD CONSTRAINT `FK_zonespawnentry_2` FOREIGN KEY `FK_zonespawnentry_2` (`spawngroupid`)
    REFERENCES `zonespawngroup` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;
ALTER TABLE `zonespawns` ADD CONSTRAINT `FK_zonespawns_1` FOREIGN KEY `FK_zonespawns_1` (`spawngroup_id`)
    REFERENCES `zonespawngroup` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC;

[John Adams]

On a side note I also tried the update with a blank database, and that caused all sorts of problems due to the fact that the foreign keys needed to create some tables reference tables that haven't been created yet due to the order of operations.

Ok, that is odd. I ran the update on both of my server databases with no problems. But then, the FKs were built on my my blank DB so maybe I am not considering something. If it cannot be repaired, or if anyone else has a problem, maybe LE should roll back my changes for now.
On a "blank" DB, there should be no reference issues since there is no data to reference. Something else might be wrong.

[ZexisStryfe]

I'd love to, but:
1.) I deleted the database to try with a blank.
2.) I have exceeded my allowed downloads

[John Adams]

Btw, I was careful to put the ALTER ENGINE first, because you cannot use FKs on MyISAM. I am not sure what order the update server runs things, but I assumed it was in the order of the update. Maybe not?

[John Adams]

2.) I have exceeded my allowed downloads

Heh, I think we need to make an allowance in this case. You might have exposed a weakness in my design.

[ZexisStryfe]

no, the problem with the blank database is it says create the table 'spawngroup' with a foreign key 'id' tied to table 'spawn', yet the table spawn isn't created until four tables later, so the foreign key is invalid immediately and the table isn't created.
(this is an illustration of the issue, not the actual tables involved)

[John Adams]

Ohhh, when you say "blank" you mean you are re-downloading everything? I gotcha. Yeah, I hadn't considered that so you are right, that won't work. Sorry, man. I'll have to work with LE to get the order worked out, since I do not know how to change that on the update server.
There are 3 primary parents in this case -
loottable
spawn
zonespawngroup
loottable parents anything with a loottable_id
spawn parents anything with a spawn_id
zonespawngroup parents anything with a spawngroupid
We'll get it sorted.

[ZexisStryfe]

Hey, that's what I am here for
I managed to almost get around it by updating several times in a row, since the second time through 'spawn' would have been created by the first pass. Unfortunately I hit my limit with just "npc_appearances" and "npc_appearances_equip" left ah well... I can wait till LE resets me...

[John Adams]

Thank you for being understanding and for reporting the problem right away. I asked LE to either remove the updates I made, or for us to re-organize the delivery of the raw data (I think it should build all structures first before applying updates, but we'll see what he thinks).
Either way, I apologize for biffing your DB.

[John Adams]

Ok, I just nuked my dev DB and captured verbose logging of a new DB deployment. I see the first error at npc_appearances, for exactly the reason Zexis mentioned - I am stuffing a reference from npc_appearance.spawn_id to spawn.id, when the `spawn` table does not yet exist.

ALTER TABLE `npc_appearance` ADD CONSTRAINT `FK_npc_appearance_1` FOREIGN KEY `FK_npc_appearance_1` (`spawn_id`)
REFERENCES `spawn` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC
[Error] Error in updating tables query 'ALTER TABLE `npc_appearance` ADD CONSTRAINT `FK_npc_appearance_1` FOREIGN KEY `FK_npc_appearance_1` (`spawn_id`)
REFERENCES `spawn` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC': #1005: Can't create table './eq2dev/#sql-7ba_1afd.frm' (errno: 150)
[Error] Attempting to update database table 'npc_appearance' ... FAILED!

I think we need to re-order the tables so the parent records are first (spawn, loottable, zonespawngroup) before their child tables.

[ZexisStryfe]

Don't worry about it John. Right now my database was empty anyway. I am only usually in game to catalog models anyway and I can still do that

[LethalEncounter]

Yah I it won't update correctly for me as well. It failed on the zonespawn* tables for me. I tried to recreate them and it still wont work:
DB Query Error #1005: Can't create table '.\eq2_test\#sql-2d8_118.frm' (errno: 121)
[Error] Error in updating tables query 'ALTER TABLE `zonespawns` ADD CONSTRAINT `FK_zonespawns_1` FOREIGN KEY `FK_zonesp
awns_1` (`spawngroup_id`)
REFERENCES `zonespawngroup` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC': #1005: Can't create table '.\eq2_test\#sql-2d8_118.frm' (errno: 121)
[Error] Attempting to update database table 'zonespawns' ... FAILED!
DB Query Error #1005: Can't create table '.\eq2_test\#sql-2d8_118.frm' (errno: 121)
[Error] Error in updating tables query 'ALTER TABLE `zonespawnentry` ADD CONSTRAINT `FK_zonespawnentry_1` FOREIGN KEY `F
K_zonespawnentry_1` (`spawn_id`)
REFERENCES `spawn` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE, ROW_FORMAT = DYNAMIC': #1005: Can't create table '.\eq2_test\#sql-2d8_118.frm' (errno: 121)
[Error] Attempting to update database table 'zonespawnentry' ... FAILED!
[Status] Update complete.
Any way to turn off the constraint when adding it and then enable it after everything is set up?