diffChangeLog is generating redundant modifyDataType changes

adamcc · April 7, 2023, 12:47pm

Hi,

Context:

I have a Spring Boot project running off PostgreSQL.
My entities are defined using JPA annotations
I integrated Liquibase 4.20 in my gradle build using the plugin org.liquibase.gradle
I use generateChangeLog task to create the initial changelog, which I put in src/main/resources/db/changelog
I use gradle plugin docker-compose to start a new docker container running PostgreSQL
I use the existing changeset in src/main/resources/db/changelog, to get the current schema in to the new PostgreSQL instance
I run diffChangeLog task, to compare the JPA Entities in my source code with the fresh installed schema in PostgreSQL, and generate a diff changelog.

I would expect this diffChangeLog to be empty, because my entities have not changed since I generated the changeset in src/main/resources/db/changelog. Instead it generates a change for some of the columns in the schema, with an unnecessary modifyDataType operation.

JPA Entity: OrderAddressEntity.java

package com.acroteq.order.service.data.access.order.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.OneToOne;
import jakarta.persistence.Table;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.util.UUID;

import static jakarta.persistence.CascadeType.ALL;

@Getter
@Setter
@NoArgsConstructor
@EqualsAndHashCode(onlyExplicitlyIncluded = true)
@Table(name = "order_addresses")
@Entity
public class OrderAddressEntity {

  @EqualsAndHashCode.Include
  @Id
  private UUID id;

  @OneToOne(cascade = ALL)
  @JoinColumn(name="order_id")
  private OrderEntity order;

  private String street;

  private String postalCode;

  private String city;
}

Original changeset, generated from JPA entities using `generateChangeLog`

databaseChangeLog:
  - changeSet:
      id: 1680856586804-2
      author: adamcc (generated)
      changes:
        - createTable:
            columns:
              - column:
                  constraints:
                    nullable: false
                    primaryKey: true
                    primaryKeyName: order_addressesPK
                  name: id
                  type: BYTEA
              - column:
                  name: city
                  type: VARCHAR(255)
              - column:
                  name: postalCode
                  type: VARCHAR(255)
              - column:
                  name: street
                  type: VARCHAR(255)
              - column:
                  name: order_id
                  type: BYTEA
            tableName: order_addresses

Generated PostgreSQL schema for table order_addresses`

Diff changeset, generated by comparing JPA entities to posgres schema, using `diffChangeLog`

databaseChangeLog:
- changeSet:
    id: 1680867980582-1
    author: micro (generated)
    changes:
    - modifyDataType:
        columnName: city
        newDataType: varchar(255)
        tableName: order_addresses
- changeSet:
    id: 1680867980582-5
    author: micro (generated)
    changes:
    - modifyDataType:
        columnName: postalCode
        newDataType: varchar(255)
        tableName: order_addresses
- changeSet:
    id: 1680867980582-7
    author: micro (generated)
    changes:
    - modifyDataType:
        columnName: street
        newDataType: varchar(255)
        tableName: order_addresses

As you can see, column “city” is data type “varchar(255)”, both in the original and the diff changesets, and also in the PostgreSQL schema. A modifyDataType change has been generated that does not change the type.

I have been trying to get this to work as expected for 2 days now, and I am stumped. I’m guessing that there is something missing or wrongly configured in a type map somewhere, and the diffChangeLog operation is reading different data types from postgreSQL than what is generated by the Hibernate dialect, so it is incorrectly identifying a change. It then generates a new change, and takes the changed data type from the JPA Entity, which is identical again.

Details of the build setup follow next.

I would appreciate any hints.

Thanks,

Adam

adamcc · April 7, 2023, 12:47pm

Here’s the details of the build and the liquibase calls:

liquibase.gradle (includes the Liquibase activities)

apply plugin: 'org.liquibase.gradle'

configurations {
    liquibaseRuntime.extendsFrom compileClasspath
}

dependencies {
    liquibaseRuntime libs.picocli
    liquibaseRuntime libs.snakeyaml
    liquibaseRuntime libs.liquibase.core
    liquibaseRuntime libs.liquibase.hibernate
    liquibaseRuntime libs.liquibase.groovy.dsl
    liquibaseRuntime libs.postgresql.driver
    liquibaseRuntime libs.spring.boot.data.jpa
    liquibaseRuntime sourceSets.main.output
}

dockerCompose {
    useComposeFiles = ["docker-compose.yaml"]
    startedServices = ['postgres'] //, 'pgadmin']
    dockerComposeWorkingDirectory = projectDir
}

composeUp {
    finalizedBy composeDown
    doLast {
        project.ext.set("postgresqlPort", dockerCompose.servicesInfos.postgres.ports[5432])
    }
}

liquibaseDropAll {
    dependsOn composeUp
    finalizedBy composeDown

    doFirst() {
        liquibase {
            jvmArgs "-Duser.dir=$projectDir"
            activities {
                dropSchema {
                    url "jdbc:postgresql://localhost:${project.postgresqlPort}/postgres"
                    driver "org.postgresql.Driver"
                    username "postgres"
                    password "password"
                    logFile "build/liquibase/liquibase_dropall.log"
                    logLevel "FINE"
                }
                runList = 'dropSchema'
            }
        }
    }
}

liquibaseUpdate {
    // Start up postgres docker container before running liquibaseDiffChangelog and shut it down after
    dependsOn liquibaseDropAll
    dependsOn composeUp
    finalizedBy composeDown

    doFirst() {
        liquibase {
            jvmArgs "-Duser.dir=$projectDir"
            activities {
                deployChangeLog {
                    changelogFile "src/main/resources/db/changelog/changelog_master.yaml"
                    url "jdbc:postgresql://localhost:${project.postgresqlPort}/postgres"
                    driver "org.postgresql.Driver"
                    username "postgres"
                    password "password"
                    logFile "build/liquibase/liquibase_update.log"
                    logLevel "FINE"
                }
                runList = 'deployChangeLog'
            }
        }
    }
}

liquibaseDiffChangelog {
    dependsOn liquibaseUpdate
    finalizedBy composeDown

    doFirst() {
        liquibase {
            jvmArgs "-Duser.dir=$projectDir"
            activities {
                generateDiffChangeLog {
                    changelogFile "./build/liquibase/changelog_diff.yaml"
                    overwriteOutputFile true
                    url "jdbc:postgresql://localhost:${project.postgresqlPort}/postgres"
                    driver "org.postgresql.Driver"
                    username "postgres"
                    password "password"
                    referenceUrl "hibernate:spring:com.acroteq.order.service.data.access?dialect=org.hibernate.dialect.PostgreSQLDialect&" +
                                     "hibernate.physical_naming_strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl&" +
                                     "hibernate.implicit_naming_strategy=org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy"
                    referenceDriver "liquibase.ext.hibernate.database.connection.HibernateDriver"
                    logFile "./build/liquibase/liquibase_diff.log"
                    logLevel "FINE"
                }
                runList = 'generateDiffChangeLog'
            }
        }
    }
}

liquibaseGenerateChangelog {
    dependsOn compileJava

    doFirst() {
        liquibase {
            jvmArgs "-Duser.dir=$projectDir"
            activities {
                generateChangeLog {
                    changelogFile "./build/liquibase/changelog.yaml"
                    overwriteOutputFile true
                    url "hibernate:spring:com.acroteq.order.service.data.access?dialect=org.hibernate.dialect.PostgreSQLDialect&" +
                             "hibernate.physical_naming_strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl&"
                             "hibernate.implicit_naming_strategy=org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy"
                    driver "liquibase.ext.hibernate.database.connection.HibernateDriver"
                    logFile "./build/liquibase/liquibase_gen.log"
                    logLevel "FINE"
                }
                runList = 'generateChangeLog'
            }
        }
    }
}

liquibase.properties

liquibase.logLevel=fine
liquibase.promptForNonLocalDatabase=true
liquibase.showBanner=false
liquibase.convertDataTypes=true
liquibase.diffColumnOrder=false
#liquibase.changelogParseMode=lax
#liquibase.command.diffChangelog.format=yaml

settings.gradle

dependencyResolutionManagement {
    versionCatalogs {
        libs {
            // Spring
            version('spring', '6.0.7')
            library('spring-core', 'org.springframework', 'spring-core').versionRef('spring')
            library('spring-beans', 'org.springframework', 'spring-beans').versionRef('spring')
            library('spring-context', 'org.springframework', 'spring-context').versionRef('spring')
            library('spring-tx', 'org.springframework', 'spring-tx').versionRef('spring')
            library('spring-test', 'org.springframework', 'spring-test').versionRef('spring')

            // Spring Boot
            version('springBoot', '3.0.5')
            library('spring-boot-core', 'org.springframework.boot', 'spring-boot').versionRef('springBoot')
            library('spring-boot-autoconfigure', 'org.springframework.boot', 'spring-boot-autoconfigure').versionRef('springBoot')
            library('spring-boot-web', 'org.springframework.boot', 'spring-boot-starter-web').versionRef('springBoot')
            library('spring-boot-validation', 'org.springframework.boot', 'spring-boot-starter-validation').versionRef('springBoot')
            library('spring-boot-thymeleaf', 'org.springframework.boot', 'spring-boot-starter-thymeleaf').versionRef('springBoot')
            library('spring-boot-data-jpa', 'org.springframework.boot', 'spring-boot-starter-data-jpa').versionRef('springBoot')
            library('spring-boot-configuration-processor', 'org.springframework.boot', 'spring-boot-configuration-processor').versionRef('springBoot')
            library('spring-boot-test', 'org.springframework.boot', 'spring-boot-test').versionRef('springBoot')

            // Postgres
            library('postgresql-driver', 'org.postgresql', 'postgresql').version('42.6.0')

            // Liquibase
            version('liquibase', '4.20.0')
            library('picocli', 'info.picocli', 'picocli').version('4.7.1')
            library('snakeyaml', 'org.yaml', 'snakeyaml').version('2.0')
            library('liquibase-core', 'org.liquibase', 'liquibase-core').versionRef('liquibase')
            library('liquibase-hibernate', 'org.liquibase.ext', 'liquibase-hibernate6').versionRef('liquibase')
            library('liquibase-groovy-dsl', 'org.liquibase', 'liquibase-groovy-dsl').version('3.0.3')
        }

        pluginLibs {
            plugin('liquibase', 'org.liquibase.gradle').version('2.2.0')
            plugin('docker-compose', 'com.avast.gradle.docker-compose').version('0.16.12')
            plugin('spring-boot', 'org.springframework.boot').version('3.0.5')
        }
    }
}

docker-compose.yaml

version: '3'
services:
  postgres:
    image: postgres:15.2-alpine
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: postgres
    volumes:
      - ./build/postgresql:/var/lib/postgresql/data
    ports:
      - "5432"
    networks:
      - app-tier
  pgadmin:
    image: dpage/pgadmin4:2023-04-04-1
    environment:
      PGADMIN_DEFAULT_EMAIL: pgadmin@adamcc.ch
      PGADMIN_DEFAULT_PASSWORD: pgadmin
      PGADMIN_LISTEN_PORT: 5050
    ports:
      - 5050:5050
    networks:
      - app-tier
networks:
  app-tier:
    driver: bridge

adamcc · April 7, 2023, 12:54pm

Log output from the `diffChangeLog` operation

[2023-04-07 14:53:16] INFO [liquibase.integration] Starting command execution.
[2023-04-07 14:53:17] INFO [liquibase.ext] Reading hibernate configuration hibernate:spring:com.acroteq.food.ordering.system.order.service.data.access?dialect=org.hibernate.dialect.PostgreSQLDialect&hibernate.physical_naming_strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl&hibernate.implicit_naming_strategy=org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
[2023-04-07 14:53:17] INFO [liquibase.ext] Found package com.acroteq.food.ordering.system.order.service.data.access
[2023-04-07 14:53:18] INFO [liquibase.ext] Using dialect org.hibernate.dialect.PostgreSQLDialect
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table order_addresses
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table order_addresses
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table order_items
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table order_items
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table orders
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table orders
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table order_items
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table order_items
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table OrderEntity_failureMessages
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table OrderEntity_failureMessages
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table order_items
[2023-04-07 14:53:18] INFO [liquibase.ext] Found primary key order_itemsPK
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column id bigint
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column productId bytea
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column quantity integer
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column order_id bytea
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column id bytea
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table orders
[2023-04-07 14:53:18] INFO [liquibase.ext] Found primary key ordersPK
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column id bytea
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column customerId bytea
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column orderStatus varchar(255)
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column priceAmount numeric(38, 2)
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column priceCurrencyId bytea
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column restaurantId bytea
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column trackingId bytea
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table order_addresses
[2023-04-07 14:53:18] INFO [liquibase.ext] Found primary key order_addressesPK
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column id bytea
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column city varchar(255)
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column postalCode varchar(255)
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column street varchar(255)
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column order_id bytea
[2023-04-07 14:53:18] INFO [liquibase.ext] Found table OrderEntity_failureMessages
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column OrderEntity_id bytea
[2023-04-07 14:53:18] INFO [liquibase.ext] Found column failureMessages varchar(255)
[2023-04-07 14:53:18] INFO [liquibase.database] Set default schema name to public
[2023-04-07 14:53:18] INFO [liquibase.diff] changeSets count: 7
[2023-04-07 14:53:18] INFO [liquibase.diff] ./build/liquibase/changelog_diff.yaml does not exist, creating and adding 7 changesets.
[2023-04-07 14:53:18] WARNING [liquibase.resource] Failed to create parent directories for file build\liquibase\changelog_diff.yaml

kotsy · April 10, 2023, 4:14pm

I don’t have the answer, but i wanted to let you know, that i have exact the same problem using “mvn liquibase:diff” in a maven spring boot solution using the latest liquibase 4.20 and hibernate6. in the changelog (.sql) it generates the following redundant column type changes for every property of the Entity:

ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(255) USING (username::VARCHAR(255));

Even after applying this changelog using mvn liquibase:update this changes are generated again

With version 4.19.0 of liquibase + maven plugin everythink works fine!

I think it’s a good idea to create an issue here: Issues · liquibase/liquibase · GitHub

Here is an issue, describing the same problem:
Unnecessary ALTER TABLE statements for PostgreSQL 15 · Issue #4047 · liquibase/liquibase · GitHub

adamcc · April 11, 2023, 7:21am

Turns out you do have the answer

This was my first Liquibase implementation (in this project), and I did not think to try a previous version. I rolled back to 4.19.0 as you suggested, and it works!

Thanks very much for the hint.

I will comment in the existing GitHub issue - I don’t thnk we need to make a new one.

Topic		Replies	Views
Liquibase Gradle Plugin General Discussion	1	2983	September 9, 2021
Liquibase generate JPA entities diff springboot kotlin General Discussion	1	1757	October 16, 2023
Maven-liquibase-plugin: diff does not recognize changes General Discussion	2	2048	March 23, 2021
Liquibase problems, API and customization General Discussion	2	410	August 30, 2010
Liquibase + Hibernate primary key name generation General Discussion	0	742	December 5, 2023

diffChangeLog is generating redundant modifyDataType changes

Related Topics