Existing indexes and unique constraints are dropped then recreated again. #436

DejfCold · 2022-10-05T01:36:25Z

Environment

Liquibase Version: 4.16.1 (also 4.10.0)

Liquibase Integration & Version: Gradle

Liquibase Extension(s) & Version: org.liquibase.gradle:2.1.1, org.liquibase.ext:liquibase-hibernate5:4.16.1

Database Vendor & Version: PostgreSQL 14.2

Operating System Type & Version: Fedora Linux 36

Infrastructure Type/Provider: local PC & DB in Docker

Description

We've created some indexes using JPA's Index annotation. LB succesfully created those indexes in the DB. However when we run the diffChangelog Gradle task again (after the former changes are already in the DB), LB is trying to drop and recreate the indexes again. It does the same thing for unique constraints as well. I've found a related, but already resolved liquibase/liquibase#2069.

For example, in one CL it does this:

    <changeSet author="dejfcold (generated)" id="1664914383324-1">
        <createIndex indexName="table_name_column_name_idx" tableName="table_name" unique="false">
            <column name="column_name"/>
        </createIndex>
    </changeSet>
    
    <changeSet author="dejfcold (generated)" id="1664914383324-2">
        <addUniqueConstraint columnNames="id" constraintName="UC_TABLE_NAMEID_COL" tableName="table_name"/>
    </changeSet>

then in the next one it does this:

    <changeSet author="dejfcold (generated)" id="1664930693277-1">
        <dropIndex indexName="table_name_column_name_idx" tableName="table_name"/>
    </changeSet>
    <changeSet author="dejfcold (generated)" id="1664930693277-2">
        <createIndex indexName="table_name_column_name_idx" tableName="table_name" unique="false">
            <column name="column_name"/>
        </createIndex>
    </changeSet>

    <changeSet author="dejfcold (generated)" id="1664930693277-3">
        <dropUniqueConstraint constraintName="UC_TABLE_NAMEID_COL" tableName="table_name"/>
    </changeSet>
    <changeSet author="dejfcold (generated)" id="1664930693277-4">
        <addUniqueConstraint columnNames="id" constraintName="UC_TABLE_NAMEID_COL" tableName="table_name"/>
    </changeSet>

Steps To Reproduce

I have tried to create a sample app, but I couldn't reproduce it there.

Steps we do in our app:

have existing app
add the following entity:

package cz.dejfcold.demo

import java.util.*
import javax.persistence.*

@Entity
@Table(
    name = "some_table"
)
class SomeTable(
    @Id
    @Column(unique = true)
    var id: UUID,

    @Column
    var indexedColumn: String
)

build the app
run diffChangelog Gradle task
run the app so it applies the changeset onto the DB
apply the following change:

Index: src/main/kotlin/cz/dejfcold/demo/SomeTable.kt
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/main/kotlin/cz/dejfcold/demo/SomeTable.kt b/src/main/kotlin/cz/dejfcold/demo/SomeTable.kt
--- a/src/main/kotlin/cz/dejfcold/demo/SomeTable.kt	
+++ b/src/main/kotlin/cz/dejfcold/demo/SomeTable.kt
@@ -5,7 +5,10 @@
 
 @Entity
 @Table(
-    name = "some_table"
+    name = "some_table",
+    indexes = [
+        Index(name = "some_index", columnList = "indexedColumn", unique = false)
+    ]
 )
 class SomeTable(
     @Id

build the app
run diffChangelog Gradle task again
run the app so it applies the changeset onto the DB
run diffChangelog Gradle task yet again
observe the newest changeset where LB drops and recreates the index

Actual Behavior

Drops then creates the same new index and/or unique constraints.

Expected/Desired Behavior

If there was no other change, I'd expect an empty change set. If there was a change somewhere else, I'd expect it not touching unrelated things and only creating the changeset for the affected changes.

Additional Context

It doesn't do that with all indexes/unique constraints. Only to some. But both entities are made the same way and the only differences are the names and number of columns.

related issues:

The text was updated successfully, but these errors were encountered:

matej-staron · 2023-01-12T08:46:45Z

Seeing the same issue with Liquibase 4.16.1, MySQL 8 and Maven.

fleboulch · 2023-01-20T11:07:29Z

I have the same issue using liquibase 4.19.0 and maven.

Full descripton (configratuion, steps to reproduce, actual and expected behaviour) is written in PR description:

for postgre database
for H2 database

andreyBelov · 2023-02-12T20:43:29Z

I have same issue
<liquibase.version>4.18.0</liquibase.version>
<liquibase-hibernate6.version>4.18.0</liquibase-hibernate6.version>
It would be great to fix, because liquibase:diff with its changelog script generation is a fantastic tool

fleboulch · 2023-02-13T10:00:55Z

@filipelautert can you help on this? I opened an MR to highlight the current issue

MalloD12 · 2023-03-14T18:35:34Z

Hi @DejfCold , would you mind fetching the branch created in this PR and trying again?

@fleboulch I tried running the tests with the changes you made on User.hbm.xml and all the tests passed.

Thanks,
Daniel.

DejfCold · 2023-03-14T19:26:35Z

Hi @MalloD12 , I'd like to, but I don't have access to the project anymore. I'm also not sure (from top of my head at least) how would I do that, since we used it from Gradle and couldn't reproduce it on fresh project. I'll try mentioning @sitole and also ask him using some other channels if he can try it out instead.

MalloD12 · 2023-03-14T20:49:55Z

Hi @sitole - @matej-staron - @andreyBelov: would you mind trying again with these PR changes? I think this should be fine, but would like to know if there is any other use case which might require any additional change.

Thanks,
Daniel.

fleboulch · 2023-06-06T13:32:49Z

The issue is still here when we are using liquibase 4.22.0

filipelautert · 2023-06-21T12:45:52Z

As per @fleboulch comment, this still an issue.

filipelautert · 2023-10-03T17:13:26Z

@tati-qalified would you be able to help me to reproduce this issue? I'm not able to reproduce it using the sample project and the following properties:

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.1.2</version>
        <relativePath/>
    </parent>

    <properties>
        <liquibase.version>4.21.1</liquibase.version>
        <liquibase-hibernate6.version>4.21.1</liquibase-hibernate6.version>
        <hibernate6.version>6.2.7.Final</hibernate6.version>

tati-qalified · 2023-10-04T18:31:57Z

This bug is still an issue for Liquibase version 4.21.1 (as 4.22 has been deprecated), but is solved for 4.23.2.
From our point of view it's fixed, so I'll be closing the ticket, but if you're still having problems with this be sure to let us know and we'll reopen it.

filipelautert · 2023-10-04T19:55:25Z

@DejfCold could you test it with 4.23.2 ? Or PR #555 and see if it fixes the problem for you.

brachipa · 2023-11-02T19:55:42Z

There is a fix for it in Hibernate 6 only and not merged to hibernate 5:
In 2.24 tag:

liquibase-hibernate/src/main/java/liquibase/ext/hibernate/snapshot/UniqueConstraintSnapshotGenerator.java

Line 120 in e08f7c6

    
           index.setName(String.format("%s_%s_IX",hibernateTable.getName(), StringUtil.randomIdentifer(4)));

in hibernate 5 tag:

liquibase-hibernate/src/main/java/liquibase/ext/hibernate/snapshot/UniqueConstraintSnapshotGenerator.java

Line 119 in 25e7ff9

index.setName(hibernateTable.getName() + "_IX");

Can you help to release the fix also to hibernate5?

When entity has 2 unique constraints defined, the second one is always generated with drop and create change.
The old code gives both indexes same name index.setName(hibernateTable.getName() + "_IX");, and then later when it compares them it recognize a change, because it tries to compare the second one with the first one (because the naming clashes, only the first one appears in the cache -StandardDiffGenerator.compareObjectType)

Mapping looks liks:

@Table(
        name = "house",
        indexes = {},
        uniqueConstraints = {
                @UniqueConstraint(name = "abc", columnNames = {"owner", "abc", "f1"}),
                @UniqueConstraint(name = "xyzw", columnNames = {"f2", "f3", "f4"})
        }
)

filipelautert · 2023-12-22T13:57:18Z

@siddharthapd i wasn't able to find the problem yet. Do you have the same situation identified by TamasSzigeti (same set of columns had both an index and a unique index)? Otherwise could you share the element/attribute configuration that is facing the issue?

siddharthapd · 2023-12-25T06:28:29Z

No , in my case its primary keys only with sequence generator. So their index is dropped and recreated again… after FKs are dropped.

siddharthapd · 2023-12-25T06:29:19Z

No , in my case its primary keys only with sequence generator. So their index is dropped and recreated again… after FKs are dropped.

I am generating .oracle.sql files. Not xml files..

siddharthapd · 2023-12-25T06:30:03Z

I will share a snippet from my entity configuration.

siddharthapd · 2023-12-26T09:54:59Z

Hello @filipelautert ,

Employee.java

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Builder
@Table(name = "EMPLOYEE")
@JsonIgnoreProperties
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "EMPLOYEE_SEQ")
    @SequenceGenerator(
            sequenceName = "EMPLOYEE_SEQ",
            allocationSize = 1,
            name = "EMPLOYEE_SEQ")
    @Column(name = "EMPLOYEE_ID")
    private Long employeeId;

    @OneToOne
    @JoinColumn(name = "SALARY_ID")
    private Salary salary;
}

Salary.java

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Builder
@Table(name = "SALARY")
@JsonIgnoreProperties
public class Salary {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SALARY_SEQ")
    @SequenceGenerator(
            sequenceName = "SALARY_SEQ",
            allocationSize = 1,
            name = "SALARY_SEQ")
    @Column(name = "SALARY_ID")
    private Long salaryId;

    @Column(name = "VALUE")
    private String value;

    @Column(name = "DESCRIPTION")
    private String description;

}

I generated initial changelog file as db-changelog-CHANGE-0001.oracle.sql

-- liquibase formatted sql

-- changeset siddharthapd:1703583314879-1
CREATE SEQUENCE EMPLOYEE_SEQ START WITH 1 INCREMENT BY 1;

-- changeset siddharthapd:1703583314879-2
CREATE SEQUENCE SALARY_SEQ START WITH 1 INCREMENT BY 1;

-- changeset siddharthapd:1703583314879-3
CREATE TABLE EMPLOYEE (EMPLOYEE_ID NUMBER(19, 0) NOT NULL, SALARY_ID NUMBER(19, 0), CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPLOYEE_ID));

-- changeset siddharthapd:1703583314879-4
CREATE TABLE SALARY (SALARY_ID NUMBER(19, 0) NOT NULL, DESCRIPTION VARCHAR2(255 CHAR), VALUE VARCHAR2(255 CHAR), CONSTRAINT SALARYPK PRIMARY KEY (SALARY_ID));

-- changeset siddharthapd:1703583314879-5
CREATE UNIQUE INDEX EMPLOYEE_LQZP_IX ON EMPLOYEE(SALARY_ID);

-- changeset siddharthapd:1703583314879-6
ALTER TABLE EMPLOYEE ADD CONSTRAINT UC_EMPLOYEESALARY_ID_COL UNIQUE (SALARY_ID) USING INDEX EMPLOYEE_LQZP_IX;

-- changeset siddharthapd:1703583314879-7
ALTER TABLE EMPLOYEE ADD CONSTRAINT FKt5fw46ovhv0k12amptk6kua1r FOREIGN KEY (SALARY_ID) REFERENCES SALARY (SALARY_ID);

I executed above changelog in Database and then again ran diffchangelog in order to generate 2nd changelog named as db-changelog-CHANGE-0002.oracle.sql

Ideally this changelog file should be empty, but i am getting below new changelog with drops and creation. (the behaviour is same for both XML and SQL)

-- liquibase formatted sql

-- changeset siddharthapd:1703583667115-1
ALTER TABLE EMPLOYEE DROP CONSTRAINT FKt5fw46ovhv0k12amptk6kua1r;

-- changeset siddharthapd:1703583667115-2
ALTER TABLE EMPLOYEE ADD CONSTRAINT FKt5fw46ovhv0k12amptk6kua1r FOREIGN KEY (SALARY_ID) REFERENCES SALARY (SALARY_ID);

-- changeset siddharthapd:1703583667115-3
ALTER SEQUENCE EMPLOYEE_SEQ INCREMENT BY 1;

-- changeset siddharthapd:1703583667115-4
ALTER SEQUENCE SALARY_SEQ INCREMENT BY 1;

-- changeset siddharthapd:1703583667115-5
ALTER TABLE EMPLOYEE DROP PRIMARY KEY DROP INDEX;

-- changeset siddharthapd:1703583667115-6
CREATE UNIQUE INDEX IX_EMPLOYEEPK ON EMPLOYEE(EMPLOYEE_ID);

-- changeset siddharthapd:1703583667115-7
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPLOYEE_ID) USING INDEX IX_EMPLOYEEPK;

-- changeset siddharthapd:1703583667115-8
ALTER TABLE SALARY DROP PRIMARY KEY DROP INDEX;

-- changeset siddharthapd:1703583667115-9
CREATE UNIQUE INDEX IX_SALARYPK ON SALARY(SALARY_ID);

-- changeset siddharthapd:1703583667115-10
ALTER TABLE SALARY ADD CONSTRAINT SALARYPK PRIMARY KEY (SALARY_ID) USING INDEX IX_SALARYPK;

below liquibase config in my gradle

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
    liquibase
    liquibaseRuntime.extendsFrom runtime
}

ext {
    diffChangeLogVersion = "CHANGE-0002"
    rollbackTagVersion = "CHANGE-0002"
    diffChangeLogFile = "src/main/resources/XXXX/db-changelog-${diffChangeLogVersion}.oracle.sql"
    entitiesPackage =  XXX.XXX.XXX.XXX"
    hibernateGenericDialect = "org.hibernate.dialect.OracleDialect"
    springCoreVersion = "6.1.2"
    springDataVersion = "3.2.1"
}

dependencies {
    // Liquibase
    implementation "io.quarkus:quarkus-liquibase"
    implementation "org.liquibase:liquibase-core:4.25.1"
    liquibaseRuntime "org.liquibase:liquibase-core:4.25.1"
    liquibaseRuntime "org.liquibase.ext:liquibase-hibernate6:4.25.1"
    //liquibaseRuntime "org.liquibase:liquibase-groovy-dsl:3.0.2"
    liquibaseRuntime "info.picocli:picocli:4.7.5"
    liquibaseRuntime "com.oracle.database.jdbc:ojdbc11-production:23.2.0.0"
    liquibaseRuntime "javax.xml.bind:jaxb-api:2.3.1"
    liquibaseRuntime "ch.qos.logback:logback-core:1.2.9"
    liquibaseRuntime "ch.qos.logback:logback-classic:1.2.9"

    liquibaseRuntime "org.springframework:spring-core:${springCoreVersion}"
    liquibaseRuntime "org.springframework.data:spring-data-jpa:${springDataVersion}"
    liquibaseRuntime "org.springframework.data:spring-data-envers:${springDataVersion}"
    liquibaseRuntime sourceSets.main.output
}

task deleteDiffChangeLog(type: Delete) {
    delete diffChangeLogFile
}

task liquibaseEntitiesToDbDiffChangelog(type: JavaExec) {
    dependsOn deleteDiffChangeLog
    group = "liquibase"
    classpath sourceSets.main.runtimeClasspath
    classpath configurations.liquibaseRuntime
    mainClass = "liquibase.integration.commandline.LiquibaseCommandLine"
    args "--logLevel=FINE"
    args "--changeLogFile=${diffChangeLogFile}"
    args "--url=${dbURL}"
    args "--username=${dbUser}"
    args "--password=${dbPassword}"
    args "--defaultSchemaName=${dbSchema}"
    args "--driver=${dbDriver}"
    args "--referenceUrl=hibernate:spring:${entitiesPackage}?dialect=${hibernateGenericDialect}"
    args "diffChangeLog"
}

siddharthapd · 2024-01-02T06:02:27Z

@filipelautert were you able to reproduce this ?

siddharthapd · 2024-01-25T09:45:54Z

@filipelautert Did you had time to check this?

filipelautert · 2024-01-29T20:35:41Z

@siddharthapd sorry, not yet.

jonenst · 2024-02-09T17:44:14Z

hi @filipelautert , I think I have the same problem, reproduced here:
liquibasebug_diffchangelog_unique_changeset_order.zip

In this repro we diff the source code to the current chngelog (using a dummy h2 database). There should be no generated changesets because they match. But for some equivalent changesets configurations it wronlgy generates DropUniqueConstraint AddUniqueConstraint (the same one) pairs. You can even add those changesets to the changelog and it still generates the same ones over and over again.

# the bad one does createTable, addColumn (in a separate changeset), addForeignKeyConstraint, addUniqueConstraint
$ mvn  clean compile liquibase:update liquibase:diff
[INFO] changeSets count: 2

# edit  src/main/resources/db/changelog/db.changelog-master.yaml to use changelog_good instead changelog_bad
# the good one does addUniqueConstraint before addForeignKeyConstraint instead of after
$ mvn  clean compile liquibase:update liquibase:diff
[INFO] changeSets count: 0

my setup is

@Entity
@Table
class ParentEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column
    private UUID id;

    @OneToOne(cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
    @JoinColumn(name = "fooId_id",
            referencedColumnName = "id",
            foreignKey = @ForeignKey(
                    name = "fooId_id_fk"
            ), nullable = true)
    ChildEntity foo;

    @OneToOne(cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
    @JoinColumn(name = "barId_id",
            referencedColumnName = "id",
            foreignKey = @ForeignKey(
                    name = "barId_id_fk"
            ), nullable = true)
    ChildEntity bar;

}

@Entity
@Table
class ChildEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column
    private UUID id;
}

the bad changelog:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" 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/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
    <changeSet author="harperjon (generated)" id="1707499031054-1">
        <createTable tableName="child_entity">
            <column name="id" type="UUID">
                <constraints nullable="false" primaryKey="true" primaryKeyName="child_entityPK"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet author="harperjon (generated)" id="1707499031054-2">
        <createTable tableName="parent_entity">
            <column name="id" type="UUID">
                <constraints nullable="false" primaryKey="true" primaryKeyName="parent_entityPK"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet author="harperjon (generated)" id="1707499067649-1">
        <addColumn tableName="parent_entity">
            <column name="bar_id_id" type="uuid"/>
        </addColumn>
    </changeSet>
    <changeSet author="harperjon (generated)" id="1707499067649-2">
        <addColumn tableName="parent_entity">
            <column name="foo_id_id" type="uuid"/>
        </addColumn>
    </changeSet>
    <changeSet author="harperjon (generated)" id="1707499067649-5">
        <addForeignKeyConstraint baseColumnNames="bar_id_id" baseTableName="parent_entity" constraintName="barId_id_fk" deferrable="false" initiallyDeferred="false" referencedColumnNames="id" referencedTableName="child_entity" validate="true"/>
    </changeSet>
    <changeSet author="harperjon (generated)" id="1707499067649-6">
        <addForeignKeyConstraint baseColumnNames="foo_id_id" baseTableName="parent_entity" constraintName="fooId_id_fk" deferrable="false" initiallyDeferred="false" referencedColumnNames="id" referencedTableName="child_entity" validate="true"/>
    </changeSet>
    <changeSet author="harperjon (generated)" id="1707499067649-3">
        <addUniqueConstraint columnNames="bar_id_id" constraintName="UC_PARENT_ENTITYBAR_ID_ID_COL" tableName="parent_entity"/>
    </changeSet>
    <changeSet author="harperjon (generated)" id="1707499067649-4">
        <addUniqueConstraint columnNames="foo_id_id" constraintName="UC_PARENT_ENTITYFOO_ID_ID_COL" tableName="parent_entity"/>
    </changeSet>
</databaseChangeLog>

In my changelog the columns are added in separate changesets because in my case they were added after the entity was created.

Can you reproduce the issue ? Can I do something to help ?

I saw in the debugger in stepping in liquibase code that some indexes were considered readonly but couldn't understand enough of what was happening to find the root cause of the problem
Cheers

Guschtel · 2024-02-12T19:07:51Z

I can confirm we still have such an issue with liquibase-hibernate 4.25.0

Basically it drops all PK and FK Constraints and creates them again.

Examples of generated Sql (we add the dbms:oracle) afterwards:

-- changeset hermchr:1707764571097-1 dbms:oracle
ALTER TABLE FALL_GNR DROP CONSTRAINT FK_FALL_ID;

-- changeset hermchr:1707764571097-3 dbms:oracle
ALTER TABLE FALL_GNR DROP CONSTRAINT FK_GNR_ID;

-- changeset hermchr:1707764571097-2 dbms:oracle
ALTER TABLE FALL_GNR ADD CONSTRAINT FK_FALL_ID FOREIGN KEY (FALL_ID) REFERENCES FALL (ID);

-- changeset hermchr:1707764571097-4 dbms:oracle
ALTER TABLE FALL_GNR ADD CONSTRAINT FK_GNR_ID FOREIGN KEY (GNR_ID) REFERENCES GNR (ID);

-- changeset hermchr:1707764571097-5 dbms:oracle
ALTER SEQUENCE SEQ_FALL INCREMENT BY 1;

-- changeset hermchr:1707764571097-6 dbms:oracle
ALTER SEQUENCE SEQ_FALL_GNR INCREMENT BY 1;

-- changeset hermchr:1707764571097-7 dbms:oracle
ALTER SEQUENCE SEQ_GNR INCREMENT BY 1;

-- changeset hermchr:1707764571097-8 dbms:oracle
ALTER SEQUENCE SEQ_ICD_CODE_GEN INCREMENT BY 1;

-- changeset hermchr:1707764571097-9 dbms:oracle
ALTER TABLE FALL DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1707764571097-10 dbms:oracle
CREATE INDEX FALLPK ON FALL(ID);

-- changeset hermchr:1707764571097-11 dbms:oracle
ALTER TABLE FALL ADD CONSTRAINT FALLPK PRIMARY KEY (ID) USING INDEX FALLPK;

-- changeset hermchr:1707764571097-12 dbms:oracle
ALTER TABLE FALL_GNR DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1707764571097-13 dbms:oracle
CREATE INDEX FALL_GNRPK ON FALL_GNR(ID);

-- changeset hermchr:1707764571097-14 dbms:oracle
ALTER TABLE FALL_GNR ADD CONSTRAINT FALL_GNRPK PRIMARY KEY (ID) USING INDEX FALL_GNRPK;

-- changeset hermchr:1707764571097-15 dbms:oracle
ALTER TABLE GNR DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1707764571097-16 dbms:oracle
CREATE INDEX GNRPK ON GNR(ID);

-- changeset hermchr:1707764571097-17 dbms:oracle
ALTER TABLE GNR ADD CONSTRAINT GNRPK PRIMARY KEY (ID) USING INDEX GNRPK;

-- changeset hermchr:1707764571097-18 dbms:oracle
ALTER TABLE ICD_CODE DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1707764571097-19 dbms:oracle
CREATE INDEX ICD_CODEPK ON ICD_CODE(ID);

-- changeset hermchr:1707764571097-20 dbms:oracle
ALTER TABLE ICD_CODE ADD CONSTRAINT ICD_CODEPK PRIMARY KEY (ID) USING INDEX ICD_CODEPK;

The Entities look like this:

@Entity
@Table(
        name = "FALL",
        uniqueConstraints = {
                @UniqueConstraint(
                        name = "UC_FALL_KEY_QUARTAL",
                        columnNames = {"FALL_KEY", "QUARTAL"}
                )
        },
        indexes = {
                @Index(name = "FALLPK", columnList = "ID"),
                @Index(name = "IXUC_FALL_KEY_QUARTAL", columnList = "FALL_KEY, QUARTAL")
        }
)
public class FallEntity extends ImportEntityBase {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_FALL")
  @SequenceGenerator(name = "SEQ_FALL", allocationSize = 1)
  @Column(name = "ID", nullable = false, updatable = false)
  private Long id;
...

vsprabhakaran · 2024-04-03T11:58:42Z

The liquibase-hibernate is an excellent tool but this bug makes it cumbersome to use it effectively.

Hoping for quick resolution here.

I can confirm we still have such an issue with liquibase-hibernate 4.25.0

Basically it drops all PK and FK Constraints and creates them again.

Examples of generated Sql (we add the dbms:oracle) afterwards:

-- changeset hermchr:1707764571097-1 dbms:oracle
ALTER TABLE FALL_GNR DROP CONSTRAINT FK_FALL_ID;

-- changeset hermchr:1707764571097-3 dbms:oracle
ALTER TABLE FALL_GNR DROP CONSTRAINT FK_GNR_ID;

-- changeset hermchr:1707764571097-2 dbms:oracle
ALTER TABLE FALL_GNR ADD CONSTRAINT FK_FALL_ID FOREIGN KEY (FALL_ID) REFERENCES FALL (ID);

-- changeset hermchr:1707764571097-4 dbms:oracle
ALTER TABLE FALL_GNR ADD CONSTRAINT FK_GNR_ID FOREIGN KEY (GNR_ID) REFERENCES GNR (ID);

-- changeset hermchr:1707764571097-5 dbms:oracle
ALTER SEQUENCE SEQ_FALL INCREMENT BY 1;

-- changeset hermchr:1707764571097-6 dbms:oracle
ALTER SEQUENCE SEQ_FALL_GNR INCREMENT BY 1;

-- changeset hermchr:1707764571097-7 dbms:oracle
ALTER SEQUENCE SEQ_GNR INCREMENT BY 1;

-- changeset hermchr:1707764571097-8 dbms:oracle
ALTER SEQUENCE SEQ_ICD_CODE_GEN INCREMENT BY 1;

-- changeset hermchr:1707764571097-9 dbms:oracle
ALTER TABLE FALL DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1707764571097-10 dbms:oracle
CREATE INDEX FALLPK ON FALL(ID);

-- changeset hermchr:1707764571097-11 dbms:oracle
ALTER TABLE FALL ADD CONSTRAINT FALLPK PRIMARY KEY (ID) USING INDEX FALLPK;

-- changeset hermchr:1707764571097-12 dbms:oracle
ALTER TABLE FALL_GNR DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1707764571097-13 dbms:oracle
CREATE INDEX FALL_GNRPK ON FALL_GNR(ID);

-- changeset hermchr:1707764571097-14 dbms:oracle
ALTER TABLE FALL_GNR ADD CONSTRAINT FALL_GNRPK PRIMARY KEY (ID) USING INDEX FALL_GNRPK;

-- changeset hermchr:1707764571097-15 dbms:oracle
ALTER TABLE GNR DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1707764571097-16 dbms:oracle
CREATE INDEX GNRPK ON GNR(ID);

-- changeset hermchr:1707764571097-17 dbms:oracle
ALTER TABLE GNR ADD CONSTRAINT GNRPK PRIMARY KEY (ID) USING INDEX GNRPK;

-- changeset hermchr:1707764571097-18 dbms:oracle
ALTER TABLE ICD_CODE DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1707764571097-19 dbms:oracle
CREATE INDEX ICD_CODEPK ON ICD_CODE(ID);

-- changeset hermchr:1707764571097-20 dbms:oracle
ALTER TABLE ICD_CODE ADD CONSTRAINT ICD_CODEPK PRIMARY KEY (ID) USING INDEX ICD_CODEPK;

The Entities look like this:

@Entity
@Table(
        name = "FALL",
        uniqueConstraints = {
                @UniqueConstraint(
                        name = "UC_FALL_KEY_QUARTAL",
                        columnNames = {"FALL_KEY", "QUARTAL"}
                )
        },
        indexes = {
                @Index(name = "FALLPK", columnList = "ID"),
                @Index(name = "IXUC_FALL_KEY_QUARTAL", columnList = "FALL_KEY, QUARTAL")
        }
)
public class FallEntity extends ImportEntityBase {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_FALL")
  @SequenceGenerator(name = "SEQ_FALL", allocationSize = 1)
  @Column(name = "ID", nullable = false, updatable = false)
  private Long id;
...

fuitattila · 2024-04-10T13:44:58Z

We are still encountering an issue with liquibase-hibernate (using maven diff generation) version 4.27.0.

The generated xml look like that:

<?xml version="1.0" encoding="UTF-8"?><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-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
<changeSet author="attila (generated)" id="1712755908477-1">
<dropUniqueConstraint constraintName="UC_EMAILSUBSCRIPTION_KEY_COL" tableName="email"/>
</changeSet>
<changeSet author="attila (generated)" id="1712755908477-2">
<addUniqueConstraint columnNames="subscription_key" constraintName="UC_EMAILSUBSCRIPTION_KEY_COL" tableName="email"/>
</changeSet>
</databaseChangeLog>

tati-qalified · 2024-04-10T14:11:55Z

Hi @vsprabhakaran and @fuitattila, thank you for letting us know. We'll look into this again and get back to you.

filipelautert · 2024-04-10T17:37:04Z

More related issues and PR's:

Old fixes:

filipelautert · 2024-04-12T13:55:12Z

Hello @tati-qalified @jonenst - are you able to help testing the fix on PR #679 ( https://github.com/liquibase/liquibase-hibernate/actions/runs/8656431018/artifacts/1407347218) ?

Guschtel · 2024-04-19T07:41:14Z

@filipelautert that did not fix it for me or I did something wrong:

In my build.gradle I loaded the jar and hibernate-core as a replacement to the normal dependency (liquibaseRuntime "org.liquibase.ext:liquibase-hibernate6:${liquibaseVersion}")

    liquibaseRuntime "org.hibernate.orm:hibernate-core:6.4.4.Final"
    liquibaseRuntime files("../../libs/liquibase-hibernate6-4.27.0-SNAPSHOT.jar")

It did improve the situation.

But the primary keys were still dropped:

filipelautert · 2024-04-19T17:20:17Z

Thanks for the testing @Guschtel ! I pushed more fixes for PK and Sequences, could you try again?

Tristan-WorkGH · 2024-04-23T08:25:52Z

@filipelautert
I tested the artifact 1407347218, and it resolved the problem for uniques constraints for us.

Guschtel · 2024-04-29T10:14:27Z

@filipelautert it fixes a few cases, but not all of them.

I still have issues with sequences that all get an alter table statement:

-- changeset hermchr:1714384768211-1 dbms:oracle
ALTER SEQUENCE SEQ_FALL INCREMENT BY 1;

-- changeset hermchr:1714384768211-2 dbms:oracle
ALTER SEQUENCE SEQ_FALL_GNR INCREMENT BY 1;

-- changeset hermchr:1714384768211-3 dbms:oracle
ALTER SEQUENCE SEQ_GNR INCREMENT BY 1;

-- changeset hermchr:1714384768211-4 dbms:oracle
ALTER SEQUENCE SEQ_ICD_CODE_GEN INCREMENT BY 1;

-- changeset hermchr:1714384768211-5 dbms:oracle
ALTER SEQUENCE SEQ_KK INCREMENT BY 1;

It also drops the primary Keys:

-- changeset hermchr:1714384768211-10 dbms:oracle
ALTER TABLE FALL DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1714384768211-11 dbms:oracle
CREATE INDEX FALLPK ON FALL(ID);

-- changeset hermchr:1714384768211-12 dbms:oracle
ALTER TABLE FALL ADD CONSTRAINT FALLPK PRIMARY KEY (ID) USING INDEX FALLPK;

-- changeset hermchr:1714384768211-13 dbms:oracle
ALTER TABLE FALL_GNR DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1714384768211-14 dbms:oracle
CREATE INDEX FALL_GNRPK ON FALL_GNR(ID);

-- changeset hermchr:1714384768211-15 dbms:oracle
ALTER TABLE FALL_GNR ADD CONSTRAINT FALL_GNRPK PRIMARY KEY (ID) USING INDEX FALL_GNRPK;

They are defined like this:

Entity
@Table(
  name = "FALL",
  uniqueConstraints = {
    @UniqueConstraint(name = "UC_FALL_KEY_QUARTAL", columnNames = { "FALL_KEY", "QUARTAL" }),
  },
  indexes = {
    @Index(name = "FALLPK", columnList = "ID"),
    @Index(name = "IXUC_FALL_KEY_QUARTAL", columnList = "FALL_KEY, QUARTAL"),
  }
)
public class FallEntity extends ImportEntityBase {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_FALL_GEN")
  @SequenceGenerator(name = "SEQ_FALL_GEN", allocationSize = 1, sequenceName = "SEQ_FALL")
  @Column(name = "ID", nullable = false, updatable = false)
  private Long id;

...

The resulting SQL was:

-- changeset hermchr:1707828645796-5 dbms:oracle
CREATE TABLE FALL
(
    ID                 NUMBER(19, 0) GENERATED BY DEFAULT AS IDENTITY NOT NULL,
...
    CONSTRAINT FALLPK PRIMARY KEY (ID)
);

The sequence was initially created by this sql:

CREATE SEQUENCE SEQ_FALL START WITH 1 INCREMENT BY 1;

Guschtel · 2024-04-29T11:58:21Z

I just tested the latest nightly build https://github.com/liquibase/liquibase-hibernate/actions/runs/8874569931

With that it looks better, the problems with the sequences are gone 👍

Remaining are only the problems with the primary key:

-- changeset hermchr:1714391751631-8 dbms:oracle
ALTER TABLE FALL DROP PRIMARY KEY DROP INDEX;

-- changeset hermchr:1714391751631-9 dbms:oracle
CREATE INDEX FALLPK ON FALL(ID);

-- changeset hermchr:1714391751631-10 dbms:oracle
ALTER TABLE FALL ADD CONSTRAINT FALLPK PRIMARY KEY (ID) USING INDEX FALLPK;

filipelautert · 2024-04-29T12:23:54Z

@Guschtel almost there!
Are you using the mappings from comment #436 (comment) ? And what database are you using, Oracle?

Guschtel · 2024-04-29T14:28:30Z

@filipelautert I wrote down the latest mappings in #436 (comment), yes.

But they should be the same as before, i just added the sequence name with sequenceName = "SEQ_FALL" to match the name in the database (compared to my try in #436 (comment)).

Our database is Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

FBurguer added FeatureDiffChangeLog labels Oct 5, 2022

kevin-atx transferred this issue from liquibase/liquibase Oct 5, 2022

kevin-atx removed the daily_review label Oct 5, 2022

fleboulch mentioned this issue Jan 20, 2023

feat: Make test failing with unique constraints #455

Merged

fleboulch mentioned this issue Feb 7, 2023

diffChangeLog constantly creating and dropping indicies/primary keys since Spring Boot 3/Liquibase 4.19 #456

Closed

filipelautert self-assigned this Feb 13, 2023

filipelautert removed their assignment Mar 7, 2023

MalloD12 mentioned this issue Mar 14, 2023

UniqueConstraintSnapshotGenerator removed to avoid issue of index/constraint recreation #468

Merged

filipelautert closed this as completed in #468 Mar 17, 2023

fleboulch mentioned this issue Apr 19, 2023

diff changelog is removing unique constraint since 4.21.0 (hibernate6 + postgresql) #479

Closed

filipelautert reopened this Jun 21, 2023

kevin-atx added the needs_guidance label Jun 30, 2023

fleboulch mentioned this issue Aug 14, 2023

Duplicated add/drop index SQL statements for adding/removing unique constraints #500

Closed

filipelautert self-assigned this Sep 4, 2023

chrstnbrn mentioned this issue Oct 1, 2023

use unique name for backing index of unique constraints #555

Merged

MalloD12 reopened this Feb 19, 2024

filipelautert removed their assignment Mar 7, 2024

filipelautert self-assigned this Apr 10, 2024

This was referenced Apr 11, 2024

fix diff changelog generating undesired changes #679

Merged

fix: keep indexes list in the order returned by database liquibase/liquibase#5792

Merged

filipelautert closed this as completed in #679 Apr 24, 2024

Guschtel mentioned this issue Sep 20, 2024

Existing primary keys and foreign keys are dropped and recreated again #718

Closed

siddharthapd mentioned this issue Oct 7, 2024

Generate Database Migration from hibernate model quarkusio/quarkus#43723

Open

Existing indexes and unique constraints are dropped then recreated again. #436

Existing indexes and unique constraints are dropped then recreated again. #436

Comments

DejfCold commented Oct 5, 2022 • edited Loading

Environment

Description

Steps To Reproduce

Actual Behavior

Expected/Desired Behavior

Additional Context

matej-staron commented Jan 12, 2023

fleboulch commented Jan 20, 2023 • edited Loading

andreyBelov commented Feb 12, 2023 • edited Loading

fleboulch commented Feb 13, 2023

MalloD12 commented Mar 14, 2023

DejfCold commented Mar 14, 2023

MalloD12 commented Mar 14, 2023

fleboulch commented Jun 6, 2023

filipelautert commented Jun 21, 2023

filipelautert commented Oct 3, 2023

tati-qalified commented Oct 4, 2023 • edited Loading

filipelautert commented Oct 4, 2023 • edited Loading

brachipa commented Nov 2, 2023 • edited Loading

filipelautert commented Dec 22, 2023

siddharthapd commented Dec 25, 2023

siddharthapd commented Dec 25, 2023

siddharthapd commented Dec 25, 2023

siddharthapd commented Dec 26, 2023 • edited Loading

siddharthapd commented Jan 2, 2024

siddharthapd commented Jan 25, 2024

filipelautert commented Jan 29, 2024

jonenst commented Feb 9, 2024 • edited Loading

Guschtel commented Feb 12, 2024

vsprabhakaran commented Apr 3, 2024

fuitattila commented Apr 10, 2024

tati-qalified commented Apr 10, 2024

filipelautert commented Apr 10, 2024 • edited Loading

filipelautert commented Apr 12, 2024

Guschtel commented Apr 19, 2024 • edited Loading

filipelautert commented Apr 19, 2024

Tristan-WorkGH commented Apr 23, 2024

Guschtel commented Apr 29, 2024

Guschtel commented Apr 29, 2024

filipelautert commented Apr 29, 2024

Guschtel commented Apr 29, 2024 • edited Loading

DejfCold commented Oct 5, 2022 •

edited

Loading

fleboulch commented Jan 20, 2023 •

edited

Loading

andreyBelov commented Feb 12, 2023 •

edited

Loading

tati-qalified commented Oct 4, 2023 •

edited

Loading

filipelautert commented Oct 4, 2023 •

edited

Loading

brachipa commented Nov 2, 2023 •

edited

Loading

siddharthapd commented Dec 26, 2023 •

edited

Loading

jonenst commented Feb 9, 2024 •

edited

Loading

filipelautert commented Apr 10, 2024 •

edited

Loading

Guschtel commented Apr 19, 2024 •

edited

Loading

Guschtel commented Apr 29, 2024 •

edited

Loading