How to continue execution of Liquibase script even though there is error thrown?

Question

I'm using MySQL database. I'm giving call to an stored procedure for 3 times to insert 3 records. following is the example for the same.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.10.xsd
   http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-3.10.xsd">
    <changeSet id="Demo" author="rambo" runOnChange="true">
        <sql splitStatements="true">            
            CALL insert_animal(NULL, 'Cat', 'Cat', NULL, 0, '', '');
            CALL insert_animal(NULL, 'Mouse', 'Mouse', NULL, 0, '', '');
            CALL insert_animal(NULL, 'Dog', 'Dog', NULL, 0, '', '');            
        </sql>
        <rollback/>
    </changeSet>
</databaseChangeLog>

Now when I update this above script for addition of 4th animal lets say, then I get error and basically it says that record exist with the name Cat and script execution is halted.

What I want to do is, I want to continue the execution even if the SP call to insert any animal fails because its duplicate.

I'm new to liquibase and seeking for some advise on this.

score 0 · Accepted Answer · answered Mar 28 '22 at 07:13

You trying to use liquibase wrong way. The changeset aimed be written only once and never changed after you apply it to database. If you need to add something new to existing changeset - just create new one. No exception from this rule, especially if you already commit the changes. Actually, there is only one exception - if you test the changeset on your own database and it is not satisfy the requirements. In this case you should remove corresponding row from databasechangelog table, manually return database schema to previous state, update the changeset and apply it to database again. In addition, you should NEVER use liquibase to add new data to tables. Liquibase is aimed to change schema state, not database data. If you break this rule, you will get a lot of problems all the way, because the data is a subject for frequently changes. Use plain sql for data migration after you apply liquibase changes.

I'm getting you Gleb. The thing is, in my above example i want those 3 animals name to be dafult present on client side. You are right as plain migration script would solve this, but my company's team is using this to do so. So, what if i used BEGIN END constructs for each SP call in it ? would that be fine ? — Aԃιƚყα Gυɾαʋ, Mar 28 '22 at 08:21
Nope. The error you got is not from liquibase. The database told you that you already have the row. To fix you issue, just add DELETE IF EXISTS statement before you CALL statement for any animal that you add. Thus you return database to empty state before you add new columns. Another option, use REPLACE INTO instead of CALL. If the record exists, it will be overwritten; if it does not yet exist, it will be created. — Gleb Yan, Mar 28 '22 at 20:10

How to continue execution of Liquibase script even though there is error thrown?

1 Answers1