Liquibase seems to ignore SQL errors on Snowflake

General Discussion
1

I added an SQL changeset that creates a table.
The SQL syntax is erroneous (the actual error doesn’t matter for this discussion).
In Snowflake Query History, I can see that the CREATE-statement fails (STATUS = Failed), as it correctly detects the SQL syntax error.
The very next SQL command is a commit, which succeeds.

Now the problems start.
Liquibase logs don’t show any issue → The user doesn’t know anything went wrong without logging in to Snowflake and verifying that the changes were applied.
Liquibase proceeds to add the changeset checksum to DATABASECHANGELOG → Even if the user found out about the error, they wouldn’t be able to fix the changeset, because it was added to the DATABASECHANGELOG despite being faulty.

We did not touch the failOnError flag, so we would expect Liquibase to

  1. detect that an SQL statement failed on Snowflake and stop the migration, or at least
  2. notify the user and not add the checksum to DATABASECHANGELOG, or at least
  3. show any of that information in the logs

Maybe the issue is that Liquibase relies on databases to reject a commit if any contained statements fail, and Snowflake doesn’t do that?

We would appreciate any help!

Our current idea of a workaround is to use a precondition for each changeset that uses a stored procedure that verifies the SQL before it is actually executed, as Snowflake does not offer that functionality out-of-the-box yet.

2

Can you provide the Liquibase version that you are using, and an example changelog?

3

Sure, thanks for reaching out!
We are using the latest Liquibase docker image, 4.20.
An example changelog would be:

-- liquibase formatted sql

--changeset morkunz:0
CREATE TABLE testtable (id THISDATATYPEDOESNOTEXIST);

Running “update” from a GitLab CICD pipeline then produces the following output:

image
image1202×209 35 KB

Snowflake’s Query History shows:

image
image724×134 6.73 KB

The error is:
image

And indeed, the table is not actually created.

Still, the appropriate DATABASECHANGELOG contains the following entry:

0 morkunz Docker/db1/schema1/changelog/liquibase-forum.sql 2023-03-17 07:25:39 11 EXECUTED 8:d619753c3cc8aebd8c9e4622d27d05e4 sql null 4.20.0 null null 9063139254

…which means that Liquibase inserted the changeset hash into the DATABASECHANGELOG.
This may be expected behavior, but the combination seems impractical to work with.

Thanks for any help!

4

Please reset everything and rerun the entire process with this added this to your liquibase execution so that the sql statements show in the output:

--sql-log-level=INFO
5

The “console” output is now:

image
image1232×509 91.5 KB

The rest is the same as before.

6

Very strange. I tried v4.19.1 (which uses 3.13.27 jdbc driver), and got the same results:

[2023-03-17 20:14:01] INFO [liquibase.executor] create table test_suite3 (column1 THISDATATYPEDOESNOTEXIST)
[2023-03-17 20:14:01] INFO [liquibase.changelog] Custom SQL executed

And I was also able to successfully rollback the changeset, even though the table didn’t exist.

So I also had v4.17.2 (which uses 3.13.22 jdbc driver) available to test, and it detects the error correctly:

Unsupported data type 'THISDATATYPEDOESNOTEXIST'. [Failed SQL: (2040) create table test_suite3 (column1 THISDATATYPEDOESNOTEXIST)]

So I guess one of us will need to open an issue on the Liquibase github.

UPDATE: I also tested v4.19.0 (which uses 3.13.26 jdbc driver), which also detects the error. Could this be possibly caused by the 3.13.27 jdbc driver?

7

This information already helps a lot, thank you so much!
I created a ticket here: Liquibase docker image 4.20 ignores SQL errors on Snowflake · Issue #3984 · liquibase/liquibase · GitHub
We will use 4.19.0 for now.

1 Like