Spring's JdbcDaoSupport (using MySQL Connector/J) fails after executing sql that adds FK

Question

I am using Spring's JdbcDaoSupport class with a DriverManagerDataSource using the MySQL Connector/J 5.0 driver (driverClassName=com.mysql.jdbc.driver). allowMultiQueries is set to true in the url.

My application is an in-house tool we recently developed that executes sql scripts in a directory one-by-one (allows us to re-create our schema and reference table data for a given date, etc, but I digress). The sql scripts sometime contain multiple statements (hence allowMultiQueries), so one script can create a table, add indexes for that table, etc.

The problem happens when including a statement to add a foreign key constraint in one of these files. If I have a file that looks like...

--(column/constraint names are examples)
CREATE TABLE myTable (
 fk1 BIGINT(19) NOT NULL,
 fk2 BIGINT(19) NOT NULL,
 PRIMARY KEY (fk1, fk2)
);

ALTER TABLE myTable ADD CONSTRAINT myTable_fk1
FOREIGN KEY (fk1)
REFERENCES myOtherTable (id)
;
ALTER TABLE myTable ADD CONSTRAINT myTable_fk2
FOREIGN KEY (fk2)
REFERENCES myOtherOtherTable (id)
;

then JdbcTemplate.execute throws an UncategorizedSqlException with the following error message and stack trace:

Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [ THE SQL YOU SEE ABOVE LISTED HERE ];
SQL state [HY000]; error code [1005]; Can't create table 'myDatabase.myTable' (errno: 150); nested exception is java.sql.SQLException: Can't create table 'myDatabase.myTable' (errno: 150)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

and the table and foreign keys are not inserted.

Also, especially weird: if I take the foreign key statements out of the script I showed above and then place them in their own script that executes after (so I now have 1 script with just the create table statement, and 1 script with the add foreign key statements that executes after that) then what happens is:

1. tool executes create table script, works fine, table is created
2. tool executes add fk script, throws the same exception as seen above (except errno=121 this time), but the FKs actually get added (!!!)

In other words, when the create table/FK statements are in the same script then the exception is thrown and nothing is created, but when they are different scripts a nearly identical exception is thrown but both things get created.

Any help on this would be greatly appreciated. Please let me know if you'd like me to clarify anything more.

---

Some more info:

1) This only happens on my box. My coworker does not get the same problem. 2) The script that forces the tool to error works fine when executed from the mysql command line using the "script" command

Answer 1

My God.

http://bugs.mysql.com/bug.php?id=41635

and

[2nd link removed because spam filter isn't letting me add 2 links. Search Google for "mysql connector / j errno 150" and it's the 3rd result]

...

Looks like mySql5.1 has a bug with its jdbc connector where it bombs where an alter statement to add a FK is in a script with any other statement.

When I broke out my 3 statements into 3 scripts, it worked (the way I was trying before with the 2 fk statements in their own script still bombed because they were sharing a script!!). Also, my coworker is using MySql5.0, so it didn't affect him.

Holy Cow, that was a fun 5 hours.