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.