SQLFileChange calculates checksum after substituting properties

[jglass524]

Search first

• I searched and no similar issues were found

Description

According to https://docs.liquibase.com/concepts/changelogs/property-substitution.html:
Note that Liquibase obtains the checksum of a sql changeset after substituting any properties you specify. However, it obtains the checksum of a sqlFile changeset before substituting the properties in the external SQL file.
but this does not appear to be true in 4.22+. The following changeset has different checksums in different schemas:

<?xml version="1.0" encoding="utf-8"?>
<changeSet author="jglass" id="sqlFile">
    <sqlFile path="001_test.sql" relativeToChangelogFile="true" splitStatements="false"/>
</changeSet>

001_test.sql:

select * from ${database.liquibaseSchemaName}.customer;

Steps To Reproduce

A unit test can demonstrate the problem. Add this file to the liquibase source code at liquibase-standard/src/test/resources/com/example-2/fileWithSchemaNameProperty.sql:

select * from ${database.liquibaseSchemaName}.customer;

and add this function to liquibase-standard/src/test/groovy/liquibase/change/core/SQLFileChangeTest.groovy:

    def noReplacementOfPropertiesInChecksum() throws Exception {
        when:
        def changelog = new DatabaseChangeLog("com/example/changelog.xml")
        def change1 = new SQLFileChange()
        change1.path = "com/example-2/fileWithSchemaNameProperty.sql"
        change1.relativeToChangelogFile = false
        def changeLogParameters1 = new ChangeLogParameters()
        changeLogParameters1.set("database.liquibaseSchemaName", "schema1")
        def changeSet1 = new ChangeSet("x", "y", false, false, null, null, null, changelog)
        changeSet1.setChangeLogParameters(changeLogParameters1)
        change1.setChangeSet(changeSet1)
 
        def sqlForSchema1 = change1.getSql().trim()
        def checksum1 = change1.generateCheckSum()

        def change2 = new SQLFileChange()
        change2.path = "com/example-2/fileWithSchemaNameProperty.sql"
        change2.relativeToChangelogFile = false
        def changeLogParameters2 = new ChangeLogParameters()
        changeLogParameters2.set("database.liquibaseSchemaName", "schema2")
        def changeSet2 = new ChangeSet("x", "y", false, false, null, null, null, changelog)
        changeSet2.setChangeLogParameters(changeLogParameters2)
        change2.setChangeSet(changeSet2)

        def sqlForSchema2 = change2.getSql().trim()
        def checksum2 = change2.generateCheckSum()

        then:
        assertEquals("select * from schema1.customer;", sqlForSchema1)
        assertEquals("select * from schema2.customer;", sqlForSchema2)
        assertEquals(checksum1, checksum2)
    }

Expected/Desired Behavior

An SQLFileChange object with an external SQL file should return the same checksum value even if the properties referenced in that file are changed.

Liquibase Version

4.25.0

Database Vendor & Version

any

Liquibase Integration

any

Liquibase Extensions

none

OS and/or Infrastructure Type/Provider

any

Additional Context

No response

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[mwftapi]

We encountered an similar issue on one of our machines, which runs MySQL and the application itself on an Windows Server instance. After providing an updated version of our application, in which the liquibase-core dependency jumped from 4.21.1 to 4.25.1 the following changeset suddenly results in a new checksum, preventing startup of the machines:

<changeSet id="[REDACTED]" author="[REDACTED]"
    dbms="mysql, mariadb">
    <preConditions onFail="MARK_RAN">
      <and>
        <not>
          <primaryKeyExists tableName="DATABASECHANGELOG"/>
        </not>
        <sqlCheck expectedResult="1">SELECT @@version_comment LIKE '%[REDACTED]%';</sqlCheck>
      </and>
    </preConditions>
    <addPrimaryKey tableName="${database.databaseChangeLogTableName}"
      constraintName="PK_DATABASECHANGELOG" columnNames="ID, AUTHOR, FILENAME"/>
  </changeSet>

Application runs on Windows Server 2016, Java 17.0.8+7, Spring Boot 2.7.18, Liquibase 4.25.1. Observed against Mysql Version 8.0.25. Might be related to the above posted issue.

What is strange though is, that we do not encounter this "checksum changed, validation failed!" exception for this changeset on Ubuntu machines running our application with liquibase-core 4.25.1, solely observed on Windows Server 2016 until now.