Modify SQL changeset

Question

Liquibase provides a <validCheckSum> tag to allow for a new checksum to be specified in case we want to modify an existing changeset.

However, this tag is not a valid attribute for SQL-formatted changesets. There is runOnChange, but that's different.

Is there any way of achieving that?

Basically, I made a mistake on a changeset, and I can't even add a rollback command because liquibase detects the checksum change and spits an error, so I'm stuck.

EDIT

The concrete changeset that I'm trying to change is:

--liquibase formatted sql

--changeset myname:0
ALTER TABLE `customers`
CHANGE COLUMN `name` `firstName` VARCHAR(45) NULL;

--changeset myname:1
ALTER TABLE `customers`
ADD COLUMN `lastName` VARCHAR(45) NULL AFTER `firstName`;

And I keep it in a file changelog_1.05.sql. Finally, I include that file in my changelog.xml:

<include file="changelog_1.05.sql" relativeToChangelogFile="true"/>

I can't add <validCheckSum> because is a SQL-formatted file, so no xml tags can be added there.

score 3 · Answer 1 · edited Jun 03 '22 at 00:42

3

Even though it is not documented, looking at the source it appears that validCheckSum is a valid attribute in a formatted sql changelog. You can see line 105 in FormattedSqlChangelogParser.java has code to look for this attribute.

edited Jun 03 '22 at 00:42

msangel

9,895
3
50
69

answered Apr 11 '16 at 14:43

SteveDonie

8,700
3
43
43

Wow, nice. Unfortunately, it was added less than a month ago and hasn't been released yet. – garci560 Apr 11 '16 at 16:47
I think Liquibase version 3.5.0 is planned to be released by the end of April 2016. – SteveDonie Apr 12 '16 at 14:57
Good to hear. Thanks again – garci560 Apr 12 '16 at 15:19

score 0 · Answer 2 · answered Jun 17 '20 at 09:49

I ended up here trying to use the validCheckSum with SQL files in Liquibase 3.9.0. It works, but only when the "--validCheckSum" is in a new line without other attributes (as opposed to other attributes such as "--runAlways": --changeset me:test --runAlways:true --splitStatements:false --validCheckSum: 1:any

This seems to be due to the regex for parsing the attribute: https://github.com/liquibase/liquibase/blob/17fcfe4f8dae96ddb4caa6793051e47ce64ad933/liquibase-core/src/main/java/liquibase/parser/core/formattedsql/FormattedSqlChangeLogParser.java#L87

score 0 · Answer 3 · answered May 18 '23 at 13:17

For SQL grammar use syntax:

--validCheckSum VER:HASH

It is defined by pattern:

private static final String VALID_CHECK_SUM_REGEX = "\\-\\-[\\s]*validCheckSum:? (.*)";

You can find HASH by running:

gradle validate
...
Validation Error:
     1 change sets have changed since they were ran against the database
          liquibase/007-extra.sql::4::admin was: 8:c34ff9f5715de7a8fd548c608c0dfecb but is now: 8:1c13c7c5aec287a3eb1b32093d0b1f1c

Hash 1:any is magical: means ignore the check for the block ))

How do I specify validchecksum to any in liquibase formatted sql?

Modify SQL changeset

3 Answers3

Linked