Migration to jmix 1.2.3 fails due to foreign key constraint on multitenant table

tom.monnier · May 21, 2022, 2:28pm

this line in one of the liquibase files fails

<dropUniqueConstraint constraintName="IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID" tableName="MTEN_TENANT"/>`

[2022-05-21 16:16:40] INFO [liquibase.lockservice] Successfully released change log lock
Unexpected error running Liquibase: Migration failed for change set com/hysopt/pmc/liquibase/changelog/2022/05/21-010-95e104c2.xml::30::hysoptpmc:
     Reason: liquibase.exception.DatabaseException: (conn=40355) Cannot drop index 'IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID': needed in a foreign key constraint [Failed SQL: (1553) ALTER TABLE PMC_DEV.MTEN_TENANT DROP KEY IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID]
For more information, please use the --logLevel flag
[2022-05-21 16:16:40] SEVERE [liquibase.integration] Unexpected error running Liquibase: Migration failed for change set com/hysopt/pmc/liquibase/changelog/2022/05/21-010-95e104c2.xml::30::hysoptpmc:
     Reason: liquibase.exception.DatabaseException: (conn=40355) Cannot drop index 'IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID': needed in a foreign key constraint [Failed SQL: (1553) ALTER TABLE PMC_DEV.MTEN_TENANT DROP KEY IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID]
liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set com/hysopt/pmc/liquibase/changelog/2022/05/21-010-95e104c2.xml::30::hysoptpmc:
     Reason: liquibase.exception.DatabaseException: (conn=40355) Cannot drop index 'IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID': needed in a foreign key constraint [Failed SQL: (1553) ALTER TABLE PMC_DEV.MTEN_TENANT DROP KEY IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID]
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:124)
	at liquibase.Liquibase.lambda$null$0(Liquibase.java:265)
	at liquibase.Scope.lambda$child$0(Scope.java:177)
	at liquibase.Scope.child(Scope.java:186)
	at liquibase.Scope.child(Scope.java:176)
	at liquibase.Scope.child(Scope.java:155)
	at liquibase.Scope.child(Scope.java:239)
	at liquibase.Liquibase.lambda$update$1(Liquibase.java:264)
	at liquibase.Scope.lambda$child$0(Scope.java:177)
	at liquibase.Scope.child(Scope.java:186)
	at liquibase.Scope.child(Scope.java:176)
	at liquibase.Scope.child(Scope.java:155)
	at liquibase.Liquibase.runInScope(Liquibase.java:2404)
	at liquibase.Liquibase.update(Liquibase.java:211)
	at liquibase.Liquibase.update(Liquibase.java:197)
	at liquibase.integration.commandline.Main.doMigration(Main.java:1882)
	at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:402)
	at liquibase.Scope.lambda$child$0(Scope.java:177)
	at liquibase.Scope.child(Scope.java:186)
	at liquibase.Scope.child(Scope.java:176)
	at liquibase.Scope.child(Scope.java:155)
	at liquibase.integration.commandline.Main$1.run(Main.java:401)
	at liquibase.integration.commandline.Main$1.run(Main.java:225)
	at liquibase.Scope.child(Scope.java:186)
	at liquibase.Scope.child(Scope.java:162)
	at liquibase.integration.commandline.Main.run(Main.java:225)
	at liquibase.integration.commandline.Main.main(Main.java:168)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set com/hysopt/pmc/liquibase/changelog/2022/05/21-010-95e104c2.xml::30::hysoptpmc:
     Reason: liquibase.exception.DatabaseException: (conn=40355) Cannot drop index 'IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID': needed in a foreign key constraint [Failed SQL: (1553) ALTER TABLE PMC_DEV.MTEN_TENANT DROP KEY IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID]
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:695)
	at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:49)
	at liquibase.changelog.ChangeLogIterator$2.lambda$null$0(ChangeLogIterator.java:111)
	at liquibase.Scope.lambda$child$0(Scope.java:177)
	at liquibase.Scope.child(Scope.java:186)
	at liquibase.Scope.child(Scope.java:176)
	at liquibase.Scope.child(Scope.java:155)
	at liquibase.changelog.ChangeLogIterator$2.lambda$run$1(ChangeLogIterator.java:110)
	at liquibase.Scope.lambda$child$0(Scope.java:177)
	at liquibase.Scope.child(Scope.java:186)
	at liquibase.Scope.child(Scope.java:176)
	at liquibase.Scope.child(Scope.java:155)
	at liquibase.Scope.child(Scope.java:239)
	at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:94)
	at liquibase.Scope.lambda$child$0(Scope.java:177)
	at liquibase.Scope.child(Scope.java:186)
	at liquibase.Scope.child(Scope.java:176)
	at liquibase.Scope.child(Scope.java:155)
	at liquibase.Scope.child(Scope.java:239)
	at liquibase.Scope.child(Scope.java:243)
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:66)
	... 26 more
Caused by: liquibase.exception.DatabaseException: (conn=40355) Cannot drop index 'IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID': needed in a foreign key constraint [Failed SQL: (1553) ALTER TABLE PMC_DEV.MTEN_TENANT DROP KEY IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID]
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:393)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:82)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:150)
	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1279)
	at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1261)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:660)
	... 46 more
Caused by: java.sql.SQLTransientConnectionException: (conn=40355) Cannot drop index 'IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID': needed in a foreign key constraint
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:79)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:158)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:266)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:366)
	at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:504)
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:389)
	... 51 more
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Cannot drop index 'IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID': needed in a foreign key constraint
	at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:262)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:360)
	... 53 more
Caused by: java.sql.SQLException: Cannot drop index 'IDX_CUBASDBMT_TENANT_UK_ACCESS_GROUP_ID': needed in a foreign key constraint
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1695)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1557)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1520)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:256)
	... 54 more

y.konyashkina · May 23, 2022, 4:13pm

Hi @tom.monnier,
What version are you migrating from?

Regards,
Yulia

tom.monnier · May 23, 2022, 7:18pm

Hi Yulia,
I should have probably be more precise, im trying to migrate from a cuba application to a jmix application

cuba 7.2.19
multitenancy 2.0.2

tom.monnier · May 30, 2022, 7:13pm

@y.konyashkina any news or recommendations on this?

The application I want to upgrade is been running in production for 2 years, so I dont feel comforable fumbling with update scripts.

krivopustov · May 31, 2022, 3:57pm

Hi Tom,

We have improved the migration scripts for Multitenancy, the fix will be available in the next framework patch. See Cannot remove unique constraints from MTEN_TENANT table when migrating MySQL database · Issue #744 · jmix-framework/jmix · GitHub