3 Replies Latest reply on Oct 21, 2003 9:06 AM by mike andrews

    new mysql/innodb foreign key problem in 3.2.2

    mike andrews Newbie

      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.

        • 1. Re: new mysql/innodb foreign key problem in 3.2.2
          mike andrews Newbie

          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.

          • 2. Re: new mysql/innodb foreign key problem in 3.2.2
            Jochen Cordes Novice

            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.

            • 3. Re: new mysql/innodb foreign key problem in 3.2.2
              mike andrews Newbie

              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