new mysql/innodb foreign key problem in 3.2.2

[mandrews-flarion]

hi folks,

i've been using the 3.2.2RCx series for a while now, and i just upgraded to 3.2.2 today.

unfortunately, compared to 3.2.2RC3 (the one i used till this morning), 3.2.2 seems to have changed its behaviour when creating a foreign key constraint, and this breaks mysql when using innodb tables.

in 3.2.2RC3, the jboss-generated statement that worked fine was:

ALTER TABLE FmdmInfo ADD INDEX ind_fk_fmdm (fk_fmdm), ADD CONSTRAINT fk_FmdmInfo_fmdm FOREIGN KEY (fk_fmdm) REFERENCES Fmdm (id);

and now, the "same" statement does not include the index phrase, which is required for innodb tables (see http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html ):

ALTER TABLE FmdmInfo ADD CONSTRAINT fk_FmdmInfo_fmdm FOREIGN KEY (fk_fmdm) REFERENCES Fmdm (id);

causing the dreaded mysql error --- "can't create table error, number 150". again, this is simply as a result to upgrading from 3.2.2RC3 to 3.2.2 this morning.

am i doing something wrong here? the single *.ear file i've been deploying simply doesn't work any more as a result.

[mandrews-flarion]

just verified that 3.2.2RC4 behaves the same way as 3.2.2RC3 with regards to adding the column index before declaring the foreign key constraint, as it should. thus, i now have more evidence for considering the absence of this behaviour in 3.2.2 a bug.

[jcordes]

Hi !

Have a look at the release notes for JBoss-3.2.2:


server/src/etc/conf/default/standardjbosscmp-jdbc.xml
comments:

Removed ADD INDEX from FK constraint template for mySQL. It does not work for compound keys.


Maybe you could fix it for yourself ;-).

HTH,

Jochen.

[mandrews-flarion]

thanks jochen,

my bad for not consulting the release notes :-) [was a bit difficult finding them however, once i knew of their existence].

but your explanation points the way for me to temporarily fix my problem while still using 3.2.2 --- creating a modified mySQL type-mapping in my app's META-INF/jbosscmp-jdbc.xml, which essentially reverts back to the old 3.2.2RC4-style foreign-key constraint template. i tested that this works, so that's what i'll continue doing for now.

to fix things so that the server's default jbosscmp-jdbc.xml works for both composite keys and my simple integer keys may require some slight architecture change in jboss, right? i'd be glad to discuss and look into it when i have some more free time.

best regards,
mike