Spring's JdbcDaoSupport (using MySQL Connector/J) fails after executing sql that adds FK
- by John
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:
tool executes create table script, works fine, table is created
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.
