liquibase : error with malformed foreign key

Question

I have this create statement :

    <addPrimaryKey 
        constraintName="pk_sitetv_consultant"
        columnNames="consultants_id, sitetv_id" 
        tableName="sitetv_consultant"/>

    <addForeignKeyConstraint baseColumnNames="sitetv_id"
                             baseTableName="sitetv_consultant"
                             constraintName="fk_sitetv_consultant_sitetv_id"
                             referencedColumnNames="id"
                             referencedTableName="site_tv"/>

    <addForeignKeyConstraint baseColumnNames="consultants_id"
                             baseTableName="sitetv_consultant"
                             constraintName="fk_sitetv_consultant_consultant_id"
                             referencedColumnNames="id"
                             referencedTableName="consultant"/>

But when I run the webapp I am gettin this error :

2016-03-24 10:34:31.537 ERROR 7322 --- [lley-Executor-1] c.t.r.c.liquibase.AsyncSpringLiquibase   : Liquibase could not start correctly, your database is NOT ready: Migration failed for change set classpath:config/liquibase/changelog/20160324092108_changelog.xml::1458811279836-12::tv (generated):
     Reason: liquibase.exception.DatabaseException: Error on rename of './m/#sql-522_bc' to './myApp/sitetv_consultant' (errno: 150 - Foreign key constraint is incorrectly formed) [Failed SQL: ALTER TABLE myApp.sitetv_consultant DROP PRIMARY KEY]

liquibase.exception.MigrationFailedException: Migration failed for change set classpath:config/liquibase/changelog/20160324092108_changelog.xml::1458811279836-12::tv (generated):
     Reason: liquibase.exception.DatabaseException: Error on rename of './myApp/#sql-522_bc' to './myApp/sitetv_consultant' (errno: 150 - Foreign key constraint is incorrectly formed) [Failed SQL: ALTER TABLE myApp.sitetv_consultant DROP PRIMARY KEY]
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:590) ~[liquibase-core-3.4.2.jar:na]
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51) ~[liquibase-core-3.4.2.jar:na]
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:73) ~[liquibase-core-3.4.2.jar:na]
    at liquibase.Liquibase.update(Liquibase.java:212) ~[liquibase-core-3.4.2.jar:na]
    at liquibase.Liquibase.update(Liquibase.java:192) ~[liquibase-core-3.4.2.jar:na]
    at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:434) ~[liquibase-core-3.4.2.jar:na]
    at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:391) ~[liquibase-core-3.4.2.jar:na]
    at com.tv.myApp.config.liquibase.AsyncSpringLiquibase.initDb(AsyncSpringLiquibase.java:63) ~[classes/:na]
    at com.tv.myApp.config.liquibase.AsyncSpringLiquibase.lambda$0(AsyncSpringLiquibase.java:49) ~[classes/:na]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[na:1.8.0_74]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[na:1.8.0_74]
    at java.lang.Thread.run(Thread.java:745) ~[na:1.8.0_74]
Caused by: liquibase.exception.DatabaseException: Error on rename of './myApp/#sql-522_bc' to './myApp/sitetv_consultant' (errno: 150 - Foreign key constraint is incorrectly formed) [Failed SQL: ALTER TABLE myApp.sitetv_consultant DROP PRIMARY KEY]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:301) ~[liquibase-core-3.4.2.jar:na]
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55) ~[liquibase-core-3.4.2.jar:na]
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:107) ~[liquibase-core-3.4.2.jar:na]
    at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1251) ~[liquibase-core-3.4.2.jar:na]
    at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1234) ~[liquibase-core-3.4.2.jar:na]
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:554) ~[liquibase-core-3.4.2.jar:na]
    ... 11 common frames omitted
Caused by: java.sql.SQLException: Error on rename of './myApp/#sql-522_bc' to './myApp/sitetv_consultant' (errno: 150 - Foreign key constraint is incorrectly formed)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2505) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:840) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:740) ~[mysql-connector-java-5.1.38.jar:5.1.38]
    at com.zaxxer.hikari.proxy.StatementProxy.execute(StatementProxy.java:94) ~[HikariCP-2.4.1.jar:na]
    at com.zaxxer.hikari.proxy.HikariStatementProxy.execute(HikariStatementProxy.java) ~[HikariCP-2.4.1.jar:na]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:299) ~[liquibase-core-3.4.2.jar:na]
    ... 16 common frames omitted

What is wrong with my xml file?

Answer 1

It may be that the way Liquibase is generating multi-column primary keys is incorrect. It appears that MySQL does support multi-column primary keys (see Multi-Column Primary Key in MySQL 5) but that the SQL genberated is incorrect. You might try running the updateSQL command rather than the update command to see what SQL is being generated.