"/spawn save new" bug, whoops!

[John Adams]

I think someone tried telling me about this weeks ago, but because I didn't see it on our dev servers, I figured it was a partially updated DB problem. Fact is, it was a geniune bug, afterall.

When you use /spawn create to make a new NPC, then use /spawn add new "name", the NPC does not get properly saved, and the admin is told to view the error in the console:

Code: Select all

   0: 01 00 1A 00 63 72 65 61 - 74 65 20 6E 70 63 20 32  | ....create npc 2
  16: 30 33 20 31 20 35 30 20 - 27 74 65 73 74 27        | 03 1 50 'test'
   0: 09 01 00 00                                        | ....
   0: 01 00 0D 00 61 64 64 20 - 6E 65 77 20 22 56 6F 78  | ....add new "Vox
  16: 22                                                 | "
DB Query Error #1452: Cannot add or update a child row: a foreign key constraint fails (`eq2world/spawn_npcs`, CONSTRAINT `FK_spawn_skill_list` FOREIGN KEY (`skill_list_id`) REFERENCES `spawn_npc_skills` (`skill_list_id`) ON DELETE CASCADE ON UPDATE CASCADE)
[20100323 13:09:29] [Error] Error in SaveSpawnInfo query 'insert into spawn_npcs (spawn_id, min_level, max_level, enc_level, class_, gender, min_group_size, max_group_size, hair_type_id, facial_hair_type_id, wing_type_id, chest_type_id, legs_type_id, soga_hair_type_id, soga_facial_hair_type_id, soga_model_type, heroic_flag, action_state, mood_state, initial_state, activity_status) values(1, 50, 50, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 203, 0, 0, 0, 0, 0)': #1452: Cannot add or update a child row: a foreign key constraint fails (`eq2world/spawn_npcs`, CONSTRAINT `FK_spawn_skill_list` FOREIGN KEY (`skill_list_id`) REFERENCES `spawn_npc_skills` (`skill_list_id`) ON DELETE CASCADE ON UPDATE CASCADE)

The reason this happens is because there is a FK constraint between tables `spawn_npcs` and the 3 configurable spawn tables for equipment, skills and spells. Since the latter 3 have no default data (no PK), there is nothing to reference when the spawn_npcs record is trying to be inserted, and the constraint fails.

You DO get a valid spawn_location_name record, but the spawn will never load due to the missing spawn_npcs data.

Without getting into a long-winded explanation, this came about due to the order in which our DB Updater executes commands on the DB. It should be by date applied, not all_table_updates+date applied. No expectation to fix that broken feature now, because we might be changing data delivery functions soon anyway.


[center]WHAT YOU SHOULD LOOK FOR[/center]
After tonights SVN update, you will either successfully update your tables, or you will get a failure. Love that 50/50, eh?

If you get a FAILED! while updating, read on. Otherwise, you're golden, have a nice day, sorry for the confusion.

Here's the failure, what it might look like:

[20100323 14:19:20] [Status] Processing Updates.
[20100323 14:19:20] [Error] Error in updating tables query '-- DROP in here due to key not existing til this table is created!
ALTER TABLE `spawn_npcs` DROP FOREIGN KEY `FK_spawn_sec_skill` ': #1025: Error on rename of './eq2dev/spawn_npcs' to './eq2dev/#sql2-4770-2415' (errno: 152)
[20100323 14:19:20] [Error] Attempting to update database table 'spawn_npc_skills' ... FAILED!
[20100323 14:19:20] [Error] Error in updating tables query '-- DROP in here due to key not existing til this table is created!
ALTER TABLE `spawn_npcs` DROP FOREIGN KEY `FK_spawn_sec_spell` ': #1025: Erroron rename of './eq2dev/spawn_npcs' to './eq2dev/#sql2-4770-2415' (errno: 152)
[20100323 14:19:20] [Error] Attempting to update database table 'spawn_npc_spells' ... FAILED!
[20100323 14:19:20] [Error] Error in updating tables query '-- DROP in here due to key not existing til this table is created!
ALTER TABLE `spawn_npcs` DROP FOREIGN KEY `FK_spawn_equipment_list` ': #1025: Error on rename of './eq2dev/spawn_npcs' to './eq2dev/#sql2-4770-2415' (errno: 152)
[20100323 14:19:20] [Error] Attempting to update database table 'spawn_npc_equipment' ... FAILED!

If you get a FAILED! trying to update your DB relating to tables `spawn_npc_equipment`, `spawn_npc_skills`, or `spawn_npc_spells`, you need to do 2 things:

1) Run this command in your SQL editor:

Code: Select all

SHOW CREATE TABLE `spawn_npcs`;

If it DOES NOT look like this:

CREATE TABLE `spawn_npcs` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`spawn_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`min_level` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`max_level` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`enc_level` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`class_` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`gender` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`min_group_size` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`max_group_size` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`hair_type_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`facial_hair_type_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`wing_type_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`chest_type_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`legs_type_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`soga_hair_type_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`soga_facial_hair_type_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`soga_model_type` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`heroic_flag` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`action_state` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`mood_state` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`initial_state` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`activity_status` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`attack_type` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`aggro_radius` FLOAT UNSIGNED NOT NULL DEFAULT '20',
`ai_strategy` ENUM('BALANCED','OFFENSIVE','DEFENSIVE') NOT NULL DEFAULT 'BALANCED',
`spell_list_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`secondary_spell_list_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`skill_list_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`secondary_skill_list_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`equipment_list_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`str` SMALLINT(6) NOT NULL DEFAULT '25',
`sta` SMALLINT(6) NOT NULL DEFAULT '25',
`wis` SMALLINT(6) NOT NULL DEFAULT '25',
`intel` SMALLINT(6) NOT NULL DEFAULT '25',
`agi` SMALLINT(6) NOT NULL DEFAULT '25',
`heat` SMALLINT(6) NOT NULL DEFAULT '0',
`cold` SMALLINT(6) NOT NULL DEFAULT '0',
`magic` SMALLINT(6) NOT NULL DEFAULT '0',
`mental` SMALLINT(6) NOT NULL DEFAULT '0',
`divine` SMALLINT(6) NOT NULL DEFAULT '0',
`disease` SMALLINT(6) NOT NULL DEFAULT '0',
`poison` SMALLINT(6) NOT NULL DEFAULT '0',
`cast_percentage` TINYINT(3) UNSIGNED NOT NULL DEFAULT '20',
`randomize` INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `SpawnIDX` (`spawn_id`),
KEY `FK_spawn_skill_list` (`skill_list_id`),
KEY `FK_spawn_sec_skill` (`secondary_skill_list_id`),
KEY `FK_spawn_spell_list` (`spell_list_id`),
KEY `FK_spawn_sec_spell` (`secondary_spell_list_id`),
KEY `FK_spawn_equipment_list` (`equipment_list_id`),
CONSTRAINT `FK_npcs_spawn` FOREIGN KEY (`spawn_id`) REFERENCES `spawn` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=latin1

... that is your problem. You are already fixed, but the updater is trying to drop those FKs again. If that's the case, run these queries:

Code: Select all

UPDATE `table_versions` SET `version` = 242 WHERE `name` = 'spawn_npc_equipment';
UPDATE `table_versions` SET `version` = 243 WHERE `name` = 'spawn_npc_skills';
UPDATE `table_versions` SET `version` = 244 WHERE `name` = 'spawn_npc_spells';

These queries will tell the Updater to stop trying to fix that `spawn_npcs` table, since it's already fixed.


You can also opt to drop the 5 FK's yourself (but do not drop the FK called FK_npc_spawn!!), then run the table_versions update and you're all set.


Final option, if you have no data you care about, then drop the DB and fetch a whole new one.


Believe me, all this hellish nightmare is an attempt to bring you a very solid, functional database. Eventually the problems will stop surfacing

[Scatman]

Oh oh, I think I reported that one!