Renaming UniqueConstraint causes incomplete ChangeLog and Liquibase exception

chrisbeaham · November 20, 2022, 11:40am

Jmix version: 1.4.0
Jmix Studio plugin version: 1.4.0-221
IntelliJ IDEA 2022.1.4 (Community Edition)
Build #IC-221.6008.13, built on July 19, 2022
Runtime version: 11.0.15+10-b2043.56 x86_64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o.
Kotlin: 221-1.7.20-release-201-IJ5591.52
Java 17.0.4 2022-07-19 LTS
Java™ SE Runtime Environment (build 17.0.4+11-LTS-179)
Java HotSpot™ 64-Bit Server VM (build 17.0.4+11-LTS-179, mixed mode, sharing)
Operating System: macOS 13.0.1 (22A400)
File System: Case-Sensitive Journaled HFS+ (APFS)
Datebase: PostgreSQL 13

Hello Everyone

When I try to rename a UniqueConstraint Liquibase throws an exception. I was using the current Jmix “stored-procedure-sample” application and first added the IDX_USER__UNQ_EMAIL UniqueConstraint with the following code…

@JmixEntity
@Entity
@Table(name = "USER_", indexes = {
        @Index(name = "IDX_USER__ON_USERNAME", columnList = "USERNAME", unique = true)
}, uniqueConstraints = {
        @UniqueConstraint(name = "IDX_USER__UNQ_EMAIL", columnNames = {"EMAIL"})
})
public class User implements JmixUserDetails, HasTimeZone {```

This caused the following ChangeLog to be generated…

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.11.xsd"
        objectQuotingStrategy="QUOTE_ONLY_RESERVED_WORDS">
    <changeSet id="1" author="stored-procedures-sample">
        <addUniqueConstraint columnNames="EMAIL" constraintName="IDX_USER__UNQ_EMAIL" tableName="USER_"/>
    </changeSet>
</databaseChangeLog>```

I compiled and ran the application and then renamed the UniqueConstraint to USER__UNQ_EMAIL as follows…

@JmixEntity
@Entity
@Table(name = "USER_", indexes = {
        @Index(name = "IDX_USER__ON_USERNAME", columnList = "USERNAME", unique = true)
}, uniqueConstraints = {
        @UniqueConstraint(name = "USER__UNQ_EMAIL", columnNames = {"EMAIL"})
})
public class User implements JmixUserDetails, HasTimeZone {```

This caused the following ChangeLog to be generated…

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
                   xmlns:pro="http://www.liquibase.org/xml/ns/pro"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.1.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
    <changeSet author="stored-procedures-sample"
               id="1">
        <dropIndex indexName="idx_user__unq_email"
                   tableName="user_"/>
    </changeSet>
</databaseChangeLog>```

I was also expecting an “addUniqueConstraint” to replace the dropped IDX_USER__UNQ_EMAIL UniqueConstraint with the renamed one USER__UNQ_EMAIL. I assume that this is a bug.

What do you think?

And then while compiling, I receive the following exception…


/Library/Java/JavaVirtualMachines/jdk-11.0.2.jdk/Contents/Home/bin/java liquibase.integration.commandline.Main --driver org.postgresql.Driver --changeLogFile liquibase-change-log-1668853310506.xml --url jdbc:postgresql://localhost/jmixstoredprocedures --username jmix --password jmixfirth5A$ --logLevel INFO update
[2022-11-19 11:21:50] INFO [liquibase.integration] No Liquibase Pro license key supplied. Please set liquibaseProLicenseKey on command line or in liquibase.properties to use Liquibase Pro features.
Liquibase Community 4.11.0 by Liquibase
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ## 
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ## 
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 11:21:50 (version 4.11.0 #2708 built at 2022-05-23 15:17+0000)
[2022-11-19 11:21:51] INFO [liquibase.database] Set default schema name to public
[2022-11-19 11:21:51] INFO [liquibase.lockservice] Successfully acquired change log lock
[2022-11-19 11:21:51] INFO [liquibase.changelog] Reading resource: com/company/storedprocedures/liquibase/changelog/010-init-user.xml
[2022-11-19 11:21:51] INFO [liquibase.changelog] Reading resource: com/company/storedprocedures/liquibase/changelog/2022/05/25-193109-08e24d17.xml
[2022-11-19 11:21:51] INFO [liquibase.changelog] Reading resource: com/company/storedprocedures/liquibase/changelog/2022/05/26-102743-08e24d17.xml
[2022-11-19 11:21:51] INFO [liquibase.changelog] Reading resource: com/company/storedprocedures/liquibase/changelog/2022/11/09-190426-2d7d9ec3.xml
[2022-11-19 11:21:51] INFO [liquibase.changelog] Reading resource: com/company/storedprocedures/liquibase/changelog/2022/11/19-111843-50849d54.xml
[2022-11-19 11:21:51] INFO [liquibase.changelog] Reading resource: com/company/storedprocedures/liquibase/changelog/2022/11/19-112122-50849d54.xml
[2022-11-19 11:21:51] INFO [liquibase.changelog] Reading from public.databasechangelog
Running Changeset: com/company/storedprocedures/liquibase/changelog/2022/11/19-112122-50849d54.xml::1::stored-procedures-sample
[2022-11-19 11:21:51] INFO [liquibase.lockservice] Successfully released change log lock
Unexpected error running Liquibase: Migration failed for change set com/company/storedprocedures/liquibase/changelog/2022/11/19-112122-50849d54.xml::1::stored-procedures-sample:
     Reason: liquibase.exception.DatabaseException: ERROR: cannot drop index idx_user__unq_email because constraint idx_user__unq_email on table user_ requires it
  Hint: You can drop constraint idx_user__unq_email on table user_ instead. [Failed SQL: (0) DROP INDEX public.idx_user__unq_email]
For more information, please use the --logLevel flag
[2022-11-19 11:21:52] SEVERE [liquibase.integration] Unexpected error running Liquibase: Migration failed for change set com/company/storedprocedures/liquibase/changelog/2022/11/19-112122-50849d54.xml::1::stored-procedures-sample:
     Reason: liquibase.exception.DatabaseException: ERROR: cannot drop index idx_user__unq_email because constraint idx_user__unq_email on table user_ requires it
  Hint: You can drop constraint idx_user__unq_email on table user_ instead. [Failed SQL: (0) DROP INDEX public.idx_user__unq_email]
liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set com/company/storedprocedures/liquibase/changelog/2022/11/19-112122-50849d54.xml::1::stored-procedures-sample:
     Reason: liquibase.exception.DatabaseException: ERROR: cannot drop index idx_user__unq_email because constraint idx_user__unq_email on table user_ requires it
  Hint: You can drop constraint idx_user__unq_email on table user_ instead. [Failed SQL: (0) DROP INDEX public.idx_user__unq_email]
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:126)
	at liquibase.Liquibase.lambda$null$0(Liquibase.java:265)
	at liquibase.Scope.lambda$child$0(Scope.java:180)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:179)
	at liquibase.Scope.child(Scope.java:158)
	at liquibase.Scope.child(Scope.java:243)
	at liquibase.Liquibase.lambda$update$1(Liquibase.java:264)
	at liquibase.Scope.lambda$child$0(Scope.java:180)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:179)
	at liquibase.Scope.child(Scope.java:158)
	at liquibase.Liquibase.runInScope(Liquibase.java:2405)
	at liquibase.Liquibase.update(Liquibase.java:211)
	at liquibase.Liquibase.update(Liquibase.java:197)
	at liquibase.integration.commandline.Main.doMigration(Main.java:1869)
	at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:398)
	at liquibase.Scope.lambda$child$0(Scope.java:180)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:179)
	at liquibase.Scope.child(Scope.java:158)
	at liquibase.integration.commandline.Main$1.run(Main.java:397)
	at liquibase.integration.commandline.Main$1.run(Main.java:221)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:165)
	at liquibase.integration.commandline.Main.run(Main.java:221)
	at liquibase.integration.commandline.Main.main(Main.java:164)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set com/company/storedprocedures/liquibase/changelog/2022/11/19-112122-50849d54.xml::1::stored-procedures-sample:
     Reason: liquibase.exception.DatabaseException: ERROR: cannot drop index idx_user__unq_email because constraint idx_user__unq_email on table user_ requires it
  Hint: You can drop constraint idx_user__unq_email on table user_ instead. [Failed SQL: (0) DROP INDEX public.idx_user__unq_email]
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:696)
	at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:56)
	at liquibase.changelog.ChangeLogIterator$2.lambda$null$0(ChangeLogIterator.java:113)
	at liquibase.Scope.lambda$child$0(Scope.java:180)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:179)
	at liquibase.Scope.child(Scope.java:158)
	at liquibase.changelog.ChangeLogIterator$2.lambda$run$1(ChangeLogIterator.java:112)
	at liquibase.Scope.lambda$child$0(Scope.java:180)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:179)
	at liquibase.Scope.child(Scope.java:158)
	at liquibase.Scope.child(Scope.java:243)
	at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:93)
	at liquibase.Scope.lambda$child$0(Scope.java:180)
	at liquibase.Scope.child(Scope.java:189)
	at liquibase.Scope.child(Scope.java:179)
	at liquibase.Scope.child(Scope.java:158)
	at liquibase.Scope.child(Scope.java:243)
	at liquibase.Scope.child(Scope.java:247)
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:65)
	... 26 more
Caused by: liquibase.exception.DatabaseException: ERROR: cannot drop index idx_user__unq_email because constraint idx_user__unq_email on table user_ requires it
  Hint: You can drop constraint idx_user__unq_email on table user_ instead. [Failed SQL: (0) DROP INDEX public.idx_user__unq_email]
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:397)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:83)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:151)
	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1279)
	at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1261)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:661)
	... 46 more
Caused by: org.postgresql.util.PSQLException: ERROR: cannot drop index idx_user__unq_email because constraint idx_user__unq_email on table user_ requires it
  Hint: You can drop constraint idx_user__unq_email on table user_ instead.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286)
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:393)
	... 51 more

Is there a different procedure for renaming a UniqueConstraint? What do you recommend? Thanks in advance for your feedback.

Best regards
Chris

gaslov · November 24, 2022, 8:42am

Hi

Thanks for your feedback!
You are right, there is a bug in Jmix Studio. YouTrack issue created, so you can follow the progress.

There is no different procedure for constraints renaming. So until the bug is fixed you need to create changelog manually. It will look like this:

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
                   xmlns:pro="http://www.liquibase.org/xml/ns/pro"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.1.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
    <changeSet id="1" author="stored-procedures-sample">
        <dropUniqueConstraint constraintName="IDX_USER__UNQ_EMAIL" tableName="USER_"/>
    </changeSet>
    <changeSet id="2" author="stored-procedures-sample">
        <addUniqueConstraint columnNames="EMAIL" constraintName="USER__UNQ_EMAIL" tableName="USER_"/>
    </changeSet>
</databaseChangeLog>