Changing a property value doesn't rerun a changeSet

lowtra2 · March 31, 2023, 3:13pm

It seems that if I define a stored procedure with an embedded property value, then it doesn’t rerun the changeSet when the property value changes. Is this expected behavior?

Helene · April 11, 2023, 7:43am

We had a similar case, and have added the property to the changeset Id to achieve what we wanted. This way the Id is new if the property value is changed, and the changeset is rerun.

Something like this:

	<changeSet author="..." id="...-${propertyName}">
             <....>
	</changeSet>

daryldoak · April 11, 2023, 1:29pm

Do you have runOnChange set to “true”?

PJatLiquibase · April 11, 2023, 6:51pm

This could point to an issue in the checksum logic. Out of curiosity, do you have an example changeset where you are seeing the problem that you could share?

lowtra2 · April 12, 2023, 9:00am

Yes, that would cover where the property value was changed to be a new value as an additional changeSet entry would be created in the database changeLog. However, if the property value was later changed back to its original value, then the changeSet would not be rerun based on what I have seen when using the tag.

Helene · April 12, 2023, 9:22am

Our case is not identical then, as in our case it should never re-execute if the property value is changed back to its original value. That would actually fail.

Does it still not work for you, even if you set runonchange=true?

lowtra2 · April 12, 2023, 9:23am

I have created a test that shows the issue (using a SQL Server database).

<?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-3.8.xsd">

  <property name="property1" value="value1" global="true"/>

  <changeSet author="x" id="1" runOnChange="true">
    <sql>
      drop synonym if exists synonym1;
      CREATE SYNONYM synonym1 FOR [${property1}].[dbo].[TEST1]
    </sql>
    <rollback>
    </rollback>
  </changeSet>

  <changeSet author="x" id="2" runOnChange="true">
    <sql>
      drop synonym if exists synonym2;
    </sql>
    <sqlFile
      path="synonym2.sql"/>
    <rollback>
    </rollback>
  </changeSet>

</databaseChangeLog>

synonym2.sql

CREATE SYNONYM synonym2 FOR [${property1}].[dbo].[TEST2];

First deployment;

Starting Liquibase at 10:11:19 (version 4.9.1 #1978 built at 2022-03-28 19:39+0000)
Liquibase Version: 4.9.1
Liquibase Community 4.9.1 by Liquibase
[2023-04-12 10:11:21] INFO [liquibase.database] Set default schema name to dbo
[2023-04-12 10:11:22] INFO [liquibase.lockservice] Successfully acquired change log lock
[2023-04-12 10:11:23] INFO [liquibase.changelog] Creating database history table with name: DATABASECHANGELOG
[2023-04-12 10:11:23] INFO [liquibase.changelog] Reading from DATABASECHANGELOG
Running Changeset: test.xml::1::x
[2023-04-12 10:11:24] INFO [liquibase.changelog] Custom SQL executed
[2023-04-12 10:11:24] INFO [liquibase.changelog] ChangeSet test.xml::1::x ran successfully in 79ms
Running Changeset: test.xml::2::x
[2023-04-12 10:11:24] INFO [liquibase.changelog] Custom SQL executed
[2023-04-12 10:11:24] INFO [liquibase.changelog] SQL in file synonym2.sql executed
[2023-04-12 10:11:24] INFO [liquibase.changelog] ChangeSet test.xml::2::x ran successfully in 85ms
[2023-04-12 10:11:24] INFO [liquibase.lockservice] Successfully released change log lock
Liquibase command 'update' was executed successfully.

Second deployment after updating the property values to “value2”;

Starting Liquibase at 10:13:27 (version 4.9.1 #1978 built at 2022-03-28 19:39+0000)
Liquibase Version: 4.9.1
Liquibase Community 4.9.1 by Liquibase
[2023-04-12 10:13:28] INFO [liquibase.database] Set default schema name to dbo
[2023-04-12 10:13:29] INFO [liquibase.lockservice] Successfully acquired change log lock
[2023-04-12 10:13:32] INFO [liquibase.changelog] Reading from DATABASECHANGELOG
Running Changeset: test.xml::1::x
[2023-04-12 10:13:33] INFO [liquibase.changelog] Custom SQL executed
[2023-04-12 10:13:33] INFO [liquibase.changelog] ChangeSet test.xml::1::x ran successfully in 278ms
[2023-04-12 10:13:35] INFO [liquibase.lockservice] Successfully released change log lock
Liquibase command 'update' was executed successfully.

In the second run, the second changeSet did not get rerun. Interestingly, the behavior works as expected for the <sql> tag, but not for <sqlFile>. In this example, I could workaround the issue by placing the synonym definition within the changeSet, but the real world scenario is where there are property values embedded in stored procedure code, so the preference is to use a separate SQL script for each stored procedure and reference the script in the changeSet.

Same behavior seen in Liquibase 4.20.0.

lowtra2 · April 12, 2023, 9:33am

Yes, I think that our scenario is different. In our case, we use using properties to manage the differences in link server and database names that are embedded within various database objects (mainly stored procedures), where we are also using contexts to manage environment differences. Within our development environments things get changed from time to time, so we expect any update in a property value to trigger a rerun of a changeSet where it has been defined with runOnChange=true.

lowtra2 · June 6, 2023, 8:18am

This appears to be a bug where the only current workaround I think can be used is to set the affected changeSets to runAlways=“true”. This is not ideal as we would really want to define them as runOnChange=“true” and then for Liquibase to correctly detect the change which includes any change in property substitution.

veda-tschoening · May 23, 2024, 7:08am

The described issue reads to me like the following:

github.com/liquibase/liquibase

SQLFileChange calculates checksum after substituting properties

opened 08:32PM - 14 Dec 23 UTC

closed 06:38PM - 24 Jan 24 UTC

jglass524

### Search first - [X] I searched and no similar issues were found ### Descrip…tion 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? - [X] I'm willing to submit a PR (Thank you!)

lowtra2 · October 18, 2024, 12:48pm

It would appear that the issue reported here and fixed in release 4.23 (possibly earlier) has now resurfaced in 4.29.2 (possibly earlier).

The scenario is where the a sqlFile contains a property value that has changed from when it was last run and the changeSet is defined as runOnChange="true". In this situation, the checksum should be calculated after substituting parameters, so that the change can be detected and the changeSet rerun. I accept that for changeSets that are defined with runOnChange="false" it may be best to calculate the checksum before property substitution. However, for those changeSets that are defined as runOnChange="true", then we need to calculate the checksum after property substitution in order to detect the change.

lowtra2 · October 21, 2024, 7:33am

It looks like this issue was originally addressed in #4192. but then reverted as part of #5389.

The original fix for the issue in #4192 appears to have had an impact on existing changeSets that contained a reference to a property value with runOnChange="false". This resulted in checksum issues on those changeSets where the calculation move from before substitution to after. This could probably have been addressed by a checksum migration step, where if the checksum matched with the before substitution calculation, then update it to be to new checksum based on after substitution. Another option would be use clear-checksums to force the recalculation of checksums based on the latest implementation.

I believe that the implementation for sql and sqlFile must be the same, so that the deployment pattern in consistent.

lowtra2 · November 27, 2024, 5:41pm

I have a workaround for this, which is to select the property value select '${property1}'; within the same changeSet that contains the <sqlFile> reference. Whilst I do not like this workaround, it does ensure that the changeSet is rerun in both scenarios, so long as the same property references exist in both the <sql> and <sqlFile> tag within the changeSet.

<?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-3.8.xsd">

  <property name="property1" value="value1" global="true"/>

  <changeSet author="x" id="1" runOnChange="true">
    <sql>
      drop synonym if exists synonym1;
      CREATE SYNONYM synonym1 FOR [${property1}].[dbo].[TEST1];
    </sql>
    <rollback>
    </rollback>
  </changeSet>

  <changeSet author="x" id="2" runOnChange="true">
    <sql>
      drop synonym if exists synonym2;
      select '${property1}'; -- *** Workaround ***
    </sql>
    <sqlFile
      path="synonym2.sql"/>
    <rollback>
    </rollback>
  </changeSet>

</databaseChangeLog>

lowtra2 · December 12, 2024, 6:20pm

I would consider this to be a bug, as a changeSet that is defined as runOnChange=“true” should always RERUN when there is a significant change to the changeSet itself or any files referenced within the changeSet. There are many use-cases for having property substitutions present in SQL scripts () and the fact that changes to property values are not being considered when a changeSet is defined as runOnChange=“true” is currently preventing us from upgrading from 4.23.0.

Thoughts?

Helene · January 20, 2025, 1:18pm

We add the property to the id of the changeset. So that if the property is changed, the id is also changed. This way the changeset is considered by liquibase to be a new changeset and is executed.

If for example the username is set as a property, the id of the changeset which creates the user could be

id="createuser ${userName}"

lowtra2 · March 10, 2025, 7:49am

It seems wrong to have to modify the "id" of the changeSet via property substitution or to provide a custom <sql> statement that selects a property value in order to “force” detection of a change, when the content in a referenced <sqlFile> has the same property substitution. One of our use cases relates to the use of Linked Servers referenced either by a Synonym, or directly inside a Stored Procedure. These can change from time to time (especially in test environments), so we need the ability to redeploy the same definitions, but with the latest Link Server references . When there is need to change these references, we want the ability to deploy the same definitions, but with the latest property substitution values by changeSets defined as runOnChange=“True”`.

We are currently running Liquibase 4.23.0 where this is working as expected, but in the latest version it is not.

Example
root.xml

<?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-3.8.xsd">

  <property name="link_server_1" value="another_server" global="true"/>
  <property name="database_1" value="another_database" global="true"/>

  <changeSet author="x" id="1" runOnChange="true">
    <sql>
      drop synonym if exists synonym1;
      CREATE SYNONYM synonym1 FOR [${link_server_1}].[${database_1}].[dbo].[TEST1];
    </sql>
    <rollback>
    </rollback>
  </changeSet>

  <changeSet author="x" id="2" runOnChange="true">
    <sql>
      drop synonym if exists synonym2;
    </sql>
    <sqlFile
      path="synonym2.sql"/>
    <rollback>
    </rollback>
  </changeSet>

  <changeSet author="x" id="3" runOnChange="true">
    <comment>
      detect [${link_server_1}] and/or [${database_1}] change
    </comment>
    <sql>
      select '${link_server_1}';
      select '${database_1}';
    </sql>
    <sql>
      drop synonym if exists synonym2;
    </sql>
    <sqlFile
      path="synonym2.sql"/>
    <rollback>
    </rollback>
  </changeSet>

</databaseChangeLog>

synonym2.sql

CREATE SYNONYM synonym2 FOR [${link_server_1}].[${database_1}].[dbo].[TEST2];

First run (all 3 changeSets are run)

Running Changeset: root.xml::1::x
[2025-02-10 10:48:03] INFO [liquibase.ui] Running Changeset: root.xml::1::x
[2025-02-10 10:48:03] INFO [liquibase.changelog] Custom SQL executed
[2025-02-10 10:48:03] INFO [liquibase.changelog] ChangeSet root.xml::1::x ran successfully in 45ms
Running Changeset: root.xml::2::x
[2025-02-10 10:48:03] INFO [liquibase.ui] Running Changeset: root.xml::2::x
[2025-02-10 10:48:03] INFO [liquibase.changelog] Custom SQL executed
[2025-02-10 10:48:03] INFO [liquibase.changelog] SQL in file synonym2.sql executed
[2025-02-10 10:48:03] INFO [liquibase.changelog] ChangeSet root.xml::2::x ran successfully in 31ms
Running Changeset: root.xml::3::x
[2025-02-10 10:48:03] INFO [liquibase.ui] Running Changeset: root.xml::3::x
[2025-02-10 10:48:03] INFO [liquibase.changelog] Custom SQL executed
[2025-02-10 10:48:03] INFO [liquibase.changelog] Custom SQL executed
[2025-02-10 10:48:03] INFO [liquibase.changelog] SQL in file synonym2.sql executed
[2025-02-10 10:48:03] INFO [liquibase.changelog] ChangeSet root.xml::3::x ran successfully in 60ms

Second run (all 3 changeSets are run)

Database is up to date, no changesets to execute
[2025-02-10 10:49:04] INFO [liquibase.ui] Database is up to date, no changesets to execute

Third run (change one of the property values)

Starting Liquibase at 10:51:08 (version 4.23.0 #10709 built at 2023-06-23 21:18+0000)
Liquibase Version: 4.23.0
[2025-02-10 10:51:08] INFO [liquibase.ui] Liquibase Version: 4.23.0
Liquibase Open Source 4.23.0 by Liquibase
[2025-02-10 10:51:08] INFO [liquibase.ui] Liquibase Open Source 4.23.0 by Liquibase
...
Running Changeset: root.xml::1::x
[2025-02-10 10:51:10] INFO [liquibase.ui] Running Changeset: root.xml::1::x
[2025-02-10 10:51:10] INFO [liquibase.changelog] Custom SQL executed
[2025-02-10 10:51:10] INFO [liquibase.changelog] ChangeSet root.xml::1::x ran successfully in 47ms
Running Changeset: root.xml::2::x
[2025-02-10 10:51:10] INFO [liquibase.ui] Running Changeset: root.xml::2::x
[2025-02-10 10:51:10] INFO [liquibase.changelog] Custom SQL executed
[2025-02-10 10:51:10] INFO [liquibase.changelog] SQL in file synonym2.sql executed
[2025-02-10 10:51:10] INFO [liquibase.changelog] ChangeSet root.xml::2::x ran successfully in 40ms
Running Changeset: root.xml::3::x
[2025-02-10 10:51:10] INFO [liquibase.ui] Running Changeset: root.xml::3::x
[2025-02-10 10:51:10] INFO [liquibase.changelog] Custom SQL executed
[2025-02-10 10:51:10] INFO [liquibase.changelog] Custom SQL executed
[2025-02-10 10:51:10] INFO [liquibase.changelog] SQL in file synonym2.sql executed
[2025-02-10 10:51:10] INFO [liquibase.changelog] ChangeSet root.xml::3::x ran successfully in 60ms
...
UPDATE SUMMARY
Run:                          3
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            3

Repeat the same, but using Liquibase 4.30.0 (changeSet id=2 is skipped)

Starting Liquibase at 10:52:55 using Java 21.0.2 (version 4.30.0 #4943 built at 2024-10-31 17:00+0000)
Liquibase Version: 4.30.0
[2025-02-10 10:52:55] INFO [liquibase.ui] Liquibase Version: 4.30.0
Liquibase Open Source 4.30.0 by Liquibase
[2025-02-10 10:52:55] INFO [liquibase.ui] Liquibase Open Source 4.30.0 by Liquibase
...
Running Changeset: root.xml::1::x
[2025-02-10 10:52:59] INFO [liquibase.ui] Running Changeset: root.xml::1::x
[2025-02-10 10:52:59] INFO [liquibase.changelog] Custom SQL executed
[2025-02-10 10:52:59] INFO [liquibase.changelog] ChangeSet root.xml::1::x ran successfully in 84ms
Running Changeset: root.xml::3::x
[2025-02-10 10:52:59] INFO [liquibase.ui] Running Changeset: root.xml::3::x
[2025-02-10 10:52:59] INFO [liquibase.changelog] Custom SQL executed
[2025-02-10 10:52:59] INFO [liquibase.changelog] Custom SQL executed
[2025-02-10 10:52:59] INFO [liquibase.changelog] SQL in file synonym2.sql executed
[2025-02-10 10:52:59] INFO [liquibase.changelog] ChangeSet root.xml::3::x ran successfully in 61ms
...
UPDATE SUMMARY
Run:                          2
Previously run:               1
Filtered out:                 0
-------------------------------
Total change sets:            3

Topic		Replies	Views
Loosing changeset info when rerunning it General Discussion	1	333	November 30, 2022
changeSet runOnChange="true" not detected in RC7 General Discussion	7	1041	November 10, 2010
Drop and recreate table if changeset of create table changes General Discussion	4	7519	October 30, 2023
Need a precondition/check to detect if a changeset is RE-executed? General Discussion	1	399	March 28, 2013
Oracle "Create or replace" change set is not registered in databasechangelog table General Discussion	2	289	January 23, 2023

Changing a property value doesn't rerun a changeSet

Related topics