Update "2013_03_1_Merc_Rules_and Equipment" SQL Error

[PallbearerX]

MySQL is complaining about the foreign key definition in the 'CREATE TABLE merc_inventory' step.

/* SQL Error (1005): Can't create table 'peq.merc_inventory' (errno: 150)
Foreign key constraint is incorrectly formed */

I've been following the Windows Server Setup Guide. This is the first issue I've encountered.

The peqdb source is revision 2506 and I'm attempting to apply the required updates under the \EQ\Source\utils\sql\git\required directory.

This is the SQL statement from the source file.

CREATE TABLE merc_inventory (
merc_inventory_id int(10) unsigned NOT NULL auto_increment,
merc_subtype_id int(10) unsigned NOT NULL default '0',
item_id int(11) unsigned NOT NULL default '0',
min_level int(10) unsigned NOT NULL default '0',
max_level int(10) unsigned NOT NULL default '0',
PRIMARY KEY (merc_inventory_id),
KEY FK_merc_inventory_1 (merc_subtype_id),
CONSTRAINT FK_merc_inventory_1 FOREIGN KEY (merc_subtype_id) REFERENCES merc_subtypes (merc_subtype_id)
);

Normally, I think we'd see this error if the merc_subtype_id column in the merc_subtypes table did not have an index, but I've verified that it does. I'm not sure why SQL would be throwing this foreign key constaint error.

Has anyone else run into this?

PbX

[bad_captain]

If you have merc tables from the peq db, drop them and run the initial merc scripts from the svn folder. Or, just remove the foreign key constraint. I think the issue is that the peq creates the merc tables as MyISAM, instead of InnoDB. ( I didn't have this issue when I created the scripts).

I have plans to recreate the scripts, but have been quite busy. I hope to soon, though.

There's at least one thread that talks about this issue. I believe it is the 'Mercenaries' thread in the development forum.

[PallbearerX]

Ok, I'll give that a shot.

I guess this raises a question about the install instructions. It says to "'source in' those that have a higher number than the NNNN of your peqdb_revNNNN.sql file."

Since my peqdb source is 2506 and the highest revision in the svn folder is 2504, I didn't source any files from the svn directory; however, there are three files in that directory that don't have a revision number: bots, botsconvert, and mercs.

Previously, I hadn't run any of these since it wasn't called out in the instructions, but based on your comment I assume I'm supposed to run all three of these before applying any updates from the git sources, correct?

Thanks,
PbX

[Uleat]

If you're running bots, then yes, you will need to source in bots.sql.

You shouldn't run botsconvert...

This is possibly the thread that bad_captain spoke of for mercs: http://www.eqemulator.org/forums/showthread.php?t=36676

[PallbearerX]

Yea, it was my failure to 'source in' the merc.sql file from the svn folder that was causing my original issue.

Thanks the replies! I now have a functioning server install!

PbX