ForeignKeyConstraintExists Precondition Not Working?

General Discussion
1

Is the foreign key constraint exists precondition broken on MySQL, or am I doing something wrong?

Snippet from output:

May 5, 2009 1:05:14 PM liquibase.ChangeSet execute
INFO: Marking ChangeSet: classpath:crystal-database.xml::Delete FK_IDEAS_USER::Michael::(MD5Sum: 423890c9a874d7fdb0c577f06146d57a) ran due to precondition failure: 
          Foreign Key FK_IDEAS_USER does not exist

May 5, 2009 1:05:14 PM liquibase.database.template.JdbcTemplate comment
INFO: Changeset classpath:crystal-database.xml::Dropping old USER-IDEA link::Michael::(MD5Sum: 1a567d20fd79ea5e311966219854fe5e)

Failure:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [spring-hibernate.xml]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set classpath:crystal-database.xml::Dropping old USER-IDEA link::Michael:
     Reason: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE `IDEAS` DROP COLUMN `SUBMITTING_USER_ID`:
          Caused By: Error executing SQL ALTER TABLE `IDEAS` DROP COLUMN `SUBMITTING_USER_ID`:
          Caused By: Error on rename of './crystal/#sql-b60_5' to './crystal/IDEAS' (errno: 150)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1338)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
	at java.security.AccessController.doPrivileged(Native Method)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380)
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:429)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:728)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:380)
	at org.springframework.context.support.ClassPathXmlApplicationContext.(ClassPathXmlApplicationContext.java:139)
	at org.springframework.context.support.ClassPathXmlApplicationContext.(ClassPathXmlApplicationContext.java:93)
	at com.tsg.crystal.jobs.nudging.JobIntegrationTest.setUp(JobIntegrationTest.java:17)
	at junit.framework.TestCase.runBare(TestCase.java:125)
	at junit.framework.TestResult$1.protect(TestResult.java:106)
	at junit.framework.TestResult.runProtected(TestResult.java:124)
	at junit.framework.TestResult.run(TestResult.java:109)
	at junit.framework.TestCase.run(TestCase.java:118)
	at junit.framework.TestSuite.runTest(TestSuite.java:208)
	at junit.framework.TestSuite.run(TestSuite.java:203)
	at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set classpath:crystal-database.xml::Dropping old USER-IDEA link::Michael:
     Reason: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE `IDEAS` DROP COLUMN `SUBMITTING_USER_ID`:
          Caused By: Error executing SQL ALTER TABLE `IDEAS` DROP COLUMN `SUBMITTING_USER_ID`:
          Caused By: Error on rename of './crystal/#sql-b60_5' to './crystal/IDEAS' (errno: 150)

Investigation:

:

show innodb status;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
090505 12:57:18 Error in foreign key constraint of table crystal/ideas:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT "FK_IDEAS_USER" FOREIGN KEY ("SUBMITTING_USER_ID") REFERENCES "users" ("USER_ID")

mysql> select * from information_schema.referential_constraints WHERE constraint_name='FK_IDEAS_USER';
+--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME |
+--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
| NULL               | crystal           | FK_IDEAS_USER   | NULL                      | crystal                  | PRIMARY                | NONE         | RESTRICT    | RESTRICT    | IDEAS      | users                 | 
+--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
1 row in set (0.06 sec)

mysql> select * from information_schema.table_constraints WHERE constraint_name='FK_IDEAS_USER';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| NULL               | crystal           | FK_IDEAS_USER   | crystal      | IDEAS      | FOREIGN KEY     | 
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
1 row in set (0.06 sec)

So … near as I can tell, the constraint exists.  The precondition doesn’t see it, so the changeset doesn’t drop it.  The subsequent column-drop fails because the foreign key is present.

I can work around this by taking out the precondition and just marking the changeset as continuing despite failure, but … I’d rather use the precondition.

2

Ah, I see I’m not the only one to encounter this: http://liquibase.jira.com/browse/CORE-171

3

It does look like a bug.  I’ll look into it for the next release