-
Notifications
You must be signed in to change notification settings - Fork 157
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Existing indexes and unique constraints are dropped then recreated again. #436
Comments
Seeing the same issue with Liquibase 4.16.1, MySQL 8 and Maven. |
I have the same issue using liquibase Full descripton (configratuion, steps to reproduce, actual and expected behaviour) is written in PR description:
|
I have same issue |
@filipelautert can you help on this? I opened an MR to highlight the current issue |
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 Thanks, |
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. |
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, |
The issue is still here when we are using liquibase 4.22.0 |
As per @fleboulch comment, this still an issue. |
@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:
|
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. |
There is a fix for it in Hibernate 6 only and not merged to hibernate 5: Line 120 in e08f7c6
in hibernate 5 tag: Line 119 in 25e7ff9
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. Mapping looks liks:
|
@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? |
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.. |
I will share a snippet from my entity configuration. |
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"
} |
@filipelautert were you able to reproduce this ? |
@filipelautert Did you had time to check this? |
@siddharthapd sorry, not yet. |
hi @filipelautert , I think I have the same problem, reproduced here: 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.
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 |
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:
The Entities look like this:
|
The liquibase-hibernate is an excellent tool but this bug makes it cumbersome to use it effectively. Hoping for quick resolution here.
|
We are still encountering an issue with liquibase-hibernate (using maven diff generation) version 4.27.0. The generated xml look like that:
|
Hi @vsprabhakaran and @fuitattila, thank you for letting us know. We'll look into this again and get back to you. |
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) ? |
@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 (
It did improve the situation. But the primary keys were still dropped: |
Thanks for the testing @Guschtel ! I pushed more fixes for PK and Sequences, could you try again? |
@filipelautert |
@filipelautert it fixes a few cases, but not all of them. I still have issues with sequences that all get an alter table statement:
It also drops the primary Keys:
They are defined like this:
The resulting SQL was:
The sequence was initially created by this sql:
|
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:
|
@Guschtel almost there! |
@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 Our database is Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 |
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:
then in the next one it does this:
Steps To Reproduce
I have tried to create a sample app, but I couldn't reproduce it there.
Steps we do in our app:
diffChangelog
Gradle taskdiffChangelog
Gradle task againdiffChangelog
Gradle task yet againActual 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: