dbms-specific changes cause checksum mismatch for unknown reasons on 4.21.0+

[chadlwilson]

Search first

• I searched and no similar issues were found

Description

Similar to #4156 and #4457, checksum calculations seem broken for me on multiple changeSets in 4.23.1, 4.23.2 (and 4.21.1 although I skipped that due to #4156). While similar to these earlier issues, they seem different.

Caused by: liquibase.exception.CommandExecutionException: liquibase.exception.ValidationFailedException: Validation Failed:
     2 changesets check sum
          db-migration-scripts/initial/initialize.xml::0::gocd(generated) was: 8:84b408bb6848cef405f3e4420abfe5ac but is now: 8:d41d8cd98f00b204e9800998ecf8427e
          db-migration-scripts/initial/create-trigger.xml::107::gocd(generated) was: 8:126d525070c1cffacf1061fc4f021a65 but is now: 8:9790c3e9e279b99a5c4c96139fabe72b

I've no idea what the cause is, but two different files break.

https://github.com/gocd/gocd/blob/0f58107c851cf2df6ce7c6902eebde796dc1f742/db-support/db-migration/src/main/resources/db-migration-scripts/initial/initialize.xml#L18-L27

<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">
    <changeSet author="gocd(generated)" id="0">
        <sql dbms="postgresql">
            CREATE EXTENSION IF NOT EXISTS citext;
        </sql>
        <comment>This will load the case-insensitive text extension on PG.</comment>
    </changeSet>
</databaseChangeLog>

https://github.com/gocd/gocd/blob/0f58107c851cf2df6ce7c6902eebde796dc1f742/db-support/db-migration/src/main/resources/db-migration-scripts/initial/create-trigger.xml#L18-L58

<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">
    <changeSet author="gocd(generated)" id="107">
        <!-- prevent liquibase from splitting the statements on `;` -->
        <sql dbms="postgresql" splitStatements="false">
            <!-- @formatter:off -->
            CREATE FUNCTION update_stages_lastTransitionedTime() RETURNS TRIGGER AS $$
                BEGIN
                    UPDATE stages SET lastTransitionedTime = NEW.statechangetime WHERE stages.id = NEW.stageid;
                    RETURN NEW;
                END;
            $$ LANGUAGE plpgsql;

            CREATE TRIGGER lastTransitionedTimeUpdate
                AFTER INSERT ON buildStateTransitions
                FOR EACH ROW
                EXECUTE PROCEDURE update_stages_lastTransitionedTime();
            <!-- @formatter:on -->
        </sql>
        <sql dbms="h2">
            <!-- @formatter:off -->
            CREATE TRIGGER lastTransitionedTimeUpdate
                AFTER INSERT ON buildStateTransitions
                FOR EACH ROW
                CALL "com.thoughtworks.go.server.database.h2.UpdateLastTransitionedTime";
            <!-- @formatter:on -->
        </sql>
        <createProcedure dbms="mysql">
            <!-- @formatter:off -->
            CREATE TRIGGER lastTransitionedTimeUpdate
                AFTER INSERT ON buildStateTransitions
                FOR EACH ROW
                BEGIN
                    UPDATE stages SET lastTransitionedTime = NEW.statechangetime WHERE stages.id = NEW.stageid;
                END
            <!-- @formatter:on -->
        </createProcedure>
        <comment>Adds a trigger to update `stages.lastTransitionedTime`</comment>
    </changeSet>
</databaseChangeLog>

Steps To Reproduce

• Run the migrations at https://github.com/gocd/gocd/tree/master/db-support/db-migration/src/main/resources/db-migration-scripts on Liquibase 4.20.0 (with an H2 database, although not sure that matters, as one error is for a postgresql only changeset).
• Then try again with 4.23.2

I can possibly provide an easier reproducer (if this seems is not obviously a duplicate of another issue that I have missed).

Expected/Desired Behavior

Should be able to upgrade existing database.

Liquibase Version

4.21.1, 4.23.1, 4.23.2 (fail on all of these)

Database Vendor & Version

H2 1.4.200

Liquibase Integration

Direct java integration

Liquibase Extensions

none

OS and/or Infrastructure Type/Provider

MacOS Ventura

Additional Context

No response

Are you willing to submit a PR?

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

[kevin-atx]

@chadlwilson - the Liquibase team updated the checksum calculation in Liquibase 4.22.0. In Liquibase 4.23.0, they added logic to auto-upgrade the checksums so the upgrade is transparent to people using the Maven, CLI interfaces and the primary direct Java interface.

So, if you are using the command framework in the direct Java interface, it should work correctly. If you don't use it, then you will need to add the code that performs the auto-update to the new checksum version.

Here is the page with the details on updating your code (if needed):
https://contribute.liquibase.com/extensions-integrations/extensions-overview/upgrade-guides/lb-4.23-upgrade-guide/

[chadlwilson]

If that were the problem, would it not complain about every change set rather than 2 specific ones?

Also note that the checksum version in the error message is V8. I understand it doesn't blindly upgrade checksum versions if they do not match with the old version checksums, so I think it's not due to checksum upgrade.

There's nothing special being done here, only liquibase.update.
https://github.com/gocd/gocd/blob/9a9a12047df94036b92106d4296555278eb7bce6/db-support/db-migration/src/main/java/com/thoughtworks/go/server/database/migration/DatabaseMigrator.java#L58

[chadlwilson]

Hi @kevin-atx - I commented above that I don't believe this issue is related to the checksum migration since it was failing in earlier releases also and for V8 checksums which no longer match how they were applied by Liquibase 4.20 and earlier.

Let me know if there is additional info needed.

[da-snap]

I see the same problem in our code base. We are currently at version 4.21.1 and cannot upgrade to version 4.23.2 because we are getting some checksum errors. The error only occurs with change sets with type sql as you can see in the original post. I would be glad to provide additional information if needed.

[filipelautert]

Seems similar to #4457 . The common thing I see is that those changes have the dbms="postgresql" in the sql change instead of changeset.
@chadlwilson @da-snap could you confirm if this is happening only to changes (not changesets) that have this attribute set?

[da-snap]

Yes I can confirm. This only affects, changesets which contains a <sql dbms="..."> change.

[chadlwilson]

For me too, it seems sql changes are affected, across our changesets. However the same changeset that has sql for postgresql and h2 has a createProcedure for mysql (as above), so not sure if that is also part of the problem.

We don't have any changesets that are dbms-specific.

I've also only actually specifically validated these failures when trying to apply against h2, so not sure if the behaviour is different if trying to apply against mysql or postgresql, but assume it's similar since the complaint here is about the entire changeset regardless of whether it needs to be applied to h2.

Could be the same as #4457 I guess. Can't recall why I felt my case was different to #4457 when opening the issue separately here now - but maybe it was the separate discussion on variable replacement , which we are not actually using or that the linked PR hadn't fixed this for us in 4.23.2.

[chadlwilson]

For what it's worth, GoCD is an open-source product so you can see the entire changesets in the repo linked above, if necessary.

The other workarounds in comments on #4457 seemed not appropriate, as GoCD is a packaged open-source product which manages database migrations for users installing in their own environments on start-up; so I can't easily suggest workarounds/hacks to regenerate checksums for users to apply - they may also have strictly controlled environments without direct DB access and force-regenerating checksums sounds rather risky from a support perspective given disparate database deployments and environments of users.

I'd have to automate that for users pre-migration, and then maintain that code until the end of time to support version upgrades from an arbitrary point in the product history, which is not really realistic/desirable.

[filipelautert]

For me too, it seems sql changes are affected, across our changesets. However the same changeset that has sql for postgresql and h2 has a createProcedure for mysql (as above), so not sure if that is also part of the problem.

You are right. For 4.21.1 the problem was createProcedure. I downloaded gocd scripts and if I remove only that everything works.

Now going from 4.20.0 or 4.21.1 to 4.23.x it always fails at change 107, having the createProcedure or not - so the current problem is for the dbms flag.

[filipelautert]

@chadlwilson are you able to test if the artifacts from PR #4995 fix it for you?

[chadlwilson]

Hello @filipelautert - thanks for the effort here!

I can confirm that liquibase-core-DAT-16006.jar from #4995 here resolved this for gocd when

• applied on top of a schema previously applied via 4.20.0 (as-is checksums = v8)
• H2 target is what I have validated (assume I don't need to check other targets for this purpose)

The checksums are upgraded to v9 on apply with no obvious issues. 🎉