Uploaded image for project: 'OpenMRS Core'
  1. OpenMRS Core
  2. TRUNK-1646

Bug in Liquibase leads to failing changeset if you have repeated foreign keys (with different names)

    Details

    • Type: Bug
    • Status: Design
    • Priority: Could
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • Complexity:
      Low

      Description

      I tried to update one of my databases to the latest, and I got the stacktrace below.

      My database has some unnecessarily-repeated foreign key constraints, that refer to the same tables and columns, but have different names. (I don't know how they got there.)

      The precondition on this liquibase update should fail, but it isn't failing.

      Liquibase has a bug where it considers foreign keys to be the same if the tables and columns match (even if the names differ).

      A temporary workaround is for me to delete the extra unnecessary keys (FKFE02B5FA89CF3AF8 in my case).

      The long-term solution is to propose a patch to liquibase (testing it first!) that changes the ForeignKey.equals(Object) method so that this:
      return getForeignKeyColumns().equalsIgnoreCase(that.getForeignKeyColumns())
      && foreignKeyTable.equals(that.foreignKeyTable)
      && getPrimaryKeyColumns().equalsIgnoreCase(that.getPrimaryKeyColumns())
      && primaryKeyTable.equals(that.primaryKeyTable);
      turns into this:
      return getForeignKeyColumns().equalsIgnoreCase(that.getForeignKeyColumns())
      && foreignKeyTable.equals(that.foreignKeyTable)
      && getPrimaryKeyColumns().equalsIgnoreCase(that.getPrimaryKeyColumns())
      && primaryKeyTable.equals(that.primaryKeyTable)
      && name.equalsIgnoreCase(that.getName()); // <-- ADD THIS LINE

      (This is the stack trace)
      ERROR - UpdateFilter$UpdateFilterCompletion$1.run(591) |2010-07-28 11:56:37,302| Unable to update the database
      org.openmrs.util.DatabaseUpdateException: There was an error while updating the database to the latest. file: liquibase-update-to-latest.xml. Error: Migration failed for change set liquibase-update-to-latest.xml::20100607-1550i::wyclif:
      Reason: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE `concept_name_tag_map` ADD CONSTRAINT `mapped_concept_name_tag` FOREIGN KEY (`concept_name_tag_id`) REFERENCES `concept_name_tag`(`concept_name_tag_id`):
      Caused By: Error executing SQL ALTER TABLE `concept_name_tag_map` ADD CONSTRAINT `mapped_concept_name_tag` FOREIGN KEY (`concept_name_tag_id`) REFERENCES `concept_name_tag`(`concept_name_tag_id`):
      Caused By: Can't create table 'haitimdr.#sql-384_9a' (errno: 121)
      at org.openmrs.util.DatabaseUpdater.executeChangelog(DatabaseUpdater.java:150)
      at org.openmrs.web.filter.update.UpdateFilter$UpdateFilterCompletion$1.run(UpdateFilter.java:573)
      at java.lang.Thread.run(Thread.java:619)
      Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set liquibase-update-to-latest.xml::20100607-1550i::wyclif:
      Reason: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE `concept_name_tag_map` ADD CONSTRAINT `mapped_concept_name_tag` FOREIGN KEY (`concept_name_tag_id`) REFERENCES `concept_name_tag`(`concept_name_tag_id`):
      Caused By: Error executing SQL ALTER TABLE `concept_name_tag_map` ADD CONSTRAINT `mapped_concept_name_tag` FOREIGN KEY (`concept_name_tag_id`) REFERENCES `concept_name_tag`(`concept_name_tag_id`):
      Caused By: Can't create table 'haitimdr.#sql-384_9a' (errno: 121)
      at liquibase.ChangeSet.execute(ChangeSet.java:260)
      at liquibase.parser.visitor.UpdateVisitor.visit(UpdateVisitor.java:26)
      at org.openmrs.util.DatabaseUpdater$1OpenmrsUpdateVisitor.visit(DatabaseUpdater.java:196)
      at liquibase.parser.ChangeLogIterator.run(ChangeLogIterator.java:41)
      at org.openmrs.util.DatabaseUpdater.executeChangelog(DatabaseUpdater.java:223)
      at org.openmrs.util.DatabaseUpdater.executeChangelog(DatabaseUpdater.java:162)
      at org.openmrs.util.DatabaseUpdater.executeChangelog(DatabaseUpdater.java:147)
      ... 2 more
      Caused by: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE `concept_name_tag_map` ADD CONSTRAINT `mapped_concept_name_tag` FOREIGN KEY (`concept_name_tag_id`) REFERENCES `concept_name_tag`(`concept_name_tag_id`)
      at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:55)
      at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:86)
      at liquibase.change.AbstractChange.execute(AbstractChange.java:247)
      at liquibase.change.AbstractChange.executeStatements(AbstractChange.java:93)
      at liquibase.ChangeSet.execute(ChangeSet.java:236)
      ... 8 more
      Caused by: java.sql.SQLException: Can't create table 'haitimdr.#sql-384_9a' (errno: 121)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
      at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:741)
      at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:587)
      at liquibase.database.template.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:78)
      at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:48)
      ... 12 more

      (This is what my table looks like.)
      CREATE TABLE `haitimdr`.`concept_name_tag_map` (
      `concept_name_id` int(11) NOT NULL,
      `concept_name_tag_id` int(11) NOT NULL,
      KEY `map_name` (`concept_name_id`),
      KEY `map_name_tag` (`concept_name_tag_id`),
      KEY `FKFE02B5FA89CF3AF8` (`concept_name_tag_id`),
      KEY `FKFE02B5FA5D4A7D57` (`concept_name_id`),
      CONSTRAINT `FKFE02B5FA5D4A7D57` FOREIGN KEY (`concept_name_id`) REFERENCES `concept_name` (`concept_name_id`),
      CONSTRAINT `FKFE02B5FA89CF3AF8` FOREIGN KEY (`concept_name_tag_id`) REFERENCES `concept_name_tag` (`concept_name_tag_id`),
      CONSTRAINT `mapped_concept_name` FOREIGN KEY (`concept_name_id`) REFERENCES `concept_name` (`concept_name_id`),
      CONSTRAINT `mapped_concept_name_tag` FOREIGN KEY (`concept_name_tag_id`) REFERENCES `concept_name_tag` (`concept_name_tag_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8

        Gliffy Diagrams

          Attachments

            Attachments-Category-Modification

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  darius Darius Jazayeri
                  Watchers:
                  Burke Mamlin, Darius Jazayeri, Filip Spiridonov, Saumya Gurtu
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated: