liquibase 4.25.0 works differently for migration and first time execution

[kirangodishala]

Search first

• I searched and no similar issues were found

Description

We have been using liquibase 3.10.3 and we tried to migrate to 4.13.0 but it introduced addAfterColumn is not allowed on postgresql issue. We worked with you to fix this issue and rased this PR- #3978.
And this was included in liquibase 4.23.2. But for this fix to work, we needed to include the below yaml snippet in our changelog file under databaseChangeLog node.

- removeChangeSetProperty:
    change: addColumn
    dbms: postgresql
    remove: afterColumn

So we made the changes and migrated the liquibase to 4.24.0 (next version after 4.23.2). But we are getting the below checksum errors(spinnaker/spinnaker#6941):

Caused by: liquibase.exception.ValidationFailedException: Validation Failed:
     3 changesets check sum
          db/changelog/20180919-initial-schema.yml::mysql-change-state-stauts-to-enum-type::robzienert was: 8:f0bfebd55de9168e38a8ef9c7217c610 but is now: 8:d41d8cd98f00b204e9800998ecf8427e
          db/changelog/20180919-initial-schema.yml::mysql-revert-change-state-stauts-to-enum-type::afeldman was: 8:d6f5eedc195011826620cc0355e8352d but is now: 8:d41d8cd98f00b204e9800998ecf8427e
          db/changelog/20190913-task-sagaids.yml::mysql-update-state-enum-values::robzienert was: 8:9601af668599fbc12e338b9b84c66f56 but is now: 8:d41d8cd98f00b204e9800998ecf8427e

	at liquibase.changelog.DatabaseChangeLog.validate(DatabaseChangeLog.java:353)
	at liquibase.command.core.helpers.DatabaseChangelogCommandStep.run(DatabaseChangelogCommandStep.java:92)

Out of two changelog files, the second file(20190913-task-sagaids.yml) was added with removeChangeSetProperty node to fix addAfterColumn is not allowed on postgresql issue and the first file(20180919-initial-schema.yml) was not changed at all.

After going through the checksum issues list, I tried to test with liquibase 4.25.0 which has a different behaviour.
It accepted the migration of changelog files from 3.10.3 to 4.25.0 in postgresql but without any changes to the files i.e.,
without adding the removeChangeSetProperty node to those changelog files that have changeSets with "afterColumn" feature.
But when I tested the same set of files directly(not migrating) with 4.25.0, addAfterColumn is not allowed on postgresql error resurfaced.

So liquibase 4.25.0 works differently for migration and first time use. We can't proceed with this kind of behaviour. Do you have any solution for this?

Steps To Reproduce

Scenario 1:

1. Run the liquibase update using liquibase 3.10.0 on the following set of files - https://github.com/spinnaker/clouddriver/tree/release-1.32.x/clouddriver-sql/src/main/resources/db
2. And then run the update using 4.25.0 on the following set of files(some got updated with removeChangeSetProperty node) - https://github.com/spinnaker/clouddriver/tree/release-1.33.x/clouddriver-sql/src/main/resources/db. This gives checksum error on a couple of files.
3. Now run the update using 4.25.0 on the first set of files - https://github.com/spinnaker/clouddriver/tree/release-1.32.x/clouddriver-sql/src/main/resources/db. This update goes successful.

Scenario 2:

Run the liquibase update using liquibase 4.25.0 on the first set of files - https://github.com/spinnaker/clouddriver/tree/release-1.32.x/clouddriver-sql/src/main/resources/db. This gives addAfterColumn is not allowed on postgresql error.

Expected/Desired Behavior

1. When we migrate from 3.10.0 to a higher version, we want to get rid of addAfterColumn is not allowed on postgresql error and at the same time we don't want checksum errors.
2. Whichever highr version you suggest, should work for the first time execution as well.

Liquibase Version

4.25.0

[tati-qalified]

Hi @kirangodishala, I've tested the scenarios you've presented, but I'm not getting the same behaviour in all of them.

I can confirm that running directly with 4.25.0 the files in your 1.32 version, the execution breaks with addAfterColumn is not allowed on postgresql. This is also replicable with the latest 4.27.0 Liquibase version.

We'd appreciate it if you could propose a fix for this bug, though it will remain open for the community to fix if not.

However, when I follow the steps of
1. run your 1.32 files with 3.10.3
2. upgrade to 4.25.0 and 1.33
3. run the 1.33 files

I don't get any checksum errors - the checksums are automatically upgraded to version 9. The same thing happens with 4.27.0. Could you provide the error message and/or the stack trace?