Run remaining commands in a single .sql file, even if one command fails

Community Answers
1

Hi! I have stumbled upon an interesting issue. First of all, I am well aware that this is an issue on the developer’s side, that they should simply give us one sql command per .sql file, but this is what we have to live with, so no chance to change this.

Our developer is giving us an .sql files which contain often multiple commands, for example
CREATE TABLE
GRANT
INSERT

And I have noticed, that if I will use the failOnError:false parameter, and there is a database issue, for example, that the table already exists, when it tries to create it, that it will not execute remaining commands in the .sql file, and it will move to another .sql file.

And my question is, is there any way how to tell Liquibase, that in case of error, it should move to the next command in the .sql file, after the “/” separator?

Thank you!

2

Hello @DarrianCZE,

Using this attribute in that form is what you need, yes, but also something else: if not already, converting the .sql files into liquibase formatted sql (files).
Then you need to make each individual DDL statement a separate changeset (--changeset author:id attribute1:value1…) and each of these can/should have the failOnError attribute set accordingly (to false).
While I am not sure this is a good practice (to silently continue in case of an error), I know each project/case has its “specific” flavors. this setup should ensure that if a particular statement fails, execution (still) continues.

Hope this helps,
Eduard

3

Hi Eduard,

So you mean, that I should include this metadata in ONE .sql file with every command, like this?

– liquibase formatted sql
– changeset author:datalite failOnError:failse
CREATE TABLE
/
– liquibase formatted sql
– changeset author:datalite failOnError:failse
GRANT
/
– liquibase formatted sql
– changeset author:datalite failOnError:failse
INSERT
/

4

Hi @DarrianCZE ,

No, that setting (liquibase formatted sql) is only set once per .sql file, as below:

--liquibase formatted sql

--changeset author:id1 failOnError:false
CREATE TABLE
/

--changeset author:id2 failOnError:false
GRANT
/

--changeset author:id3 failOnError:false
INSERT
/

Regards,
Eduard

5

I see! Thank you very much!

1 Like
6

Hi Eduard, a followup question!

Those changeset authors (id1, id2, id3, etc…) have to be unique throughout the entire deployment, or only within the single .sql file?

For example

1.sql
id1
id2
id3

2.sql
id4
id5
id6

or

1.sql
id1
id2
id3

2.sql
id1
id2
id3

?

Thank you!

7

Definitely at changelog (file) level (so each changelog can have similar id-ing… i.e id:1, id:2 or id1, id2 or whatever project agrees). There isn’t a requirement that ids need to be unique across entire files/changelogs base. This translates into Option B as you mentioned in the above example.
As long as you have the combination id, author, filename (logicalFilePath) unique at changelog (file) level, you’re set.

Regards,
Eduard

1 Like