Liquibase does not rollback failed changesets with MySQL

Question

I am using Liquibase 3.4.1 with MySQL56 and run Liquibase via Spring Boot.

I have a changeset that includes adding a column to the existing table. The new column has valueComputed attribute with a simple select.

When I made a mistake in that select the changeset failed and migration stopped at that changeset. However the new column was commited to the database but without the proper value and, what's really bad, this changeset was not marked as run! The next time I ran migration Liquibase attempted to execute the changeset again, but failed because the column had been created already.

Why didn't Liquibase rollback a transaction when the changeset failed? How can I make my changeset transactional?

EDIT: Apparently, MySQL commits after every DDL command. If I specify rollback commands in rollback tag will Liquibase run then in case of failed changeset?

dbf dbf · Accepted Answer · 2015-10-27T10:56:55

Liquibase tries to run changeset in transaction, but I think that mysql commits after ddl (column added): https://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html

I'm not sure that it is possible to do it transactional, but you may split it into two changesets and if column population fails it will be rolled back and after rerun you will not have issues (column is already added in previous changeset).

EDIT by Ilya Novoseltsev:

Changesets involving DDL changes should be made atomic.

Bad:

<changeSet author="novoseltsevib (generated)" id="1445871764871-19">
    <addColumn tableName="account_range">
        <column name="cash2card_participation" type="BIT(1)" valueBoolean="false"/>
    </addColumn>
    <addColumn tableName="account_range_aud">
        <column name="cash2card_participation" type="BIT(1)"/>
    </addColumn>
</changeSet>

Good:

<changeSet author="novoseltsevib (generated)" id="1445871764871-19">
    <addColumn tableName="account_range">
        <column name="cash2card_participation" type="BIT(1)" valueBoolean="false"/>
    </addColumn>
</changeSet>
<changeSet author="novoseltsevib (generated)" id="1445871764871-20">
    <addColumn tableName="account_range_aud">
        <column name="cash2card_participation" type="BIT(1)"/>
    </addColumn>
</changeSet>

Writing your changesets this way allows to repeat a failed changeset without worrying about changes being partially commited.

One should be careful when using value = ..., especially valueComputed. These statements break addColumn atomicity. They are executed as a separate update command after the DDL command. The proper way is to move them to their own update tag in a separate changeset.

Liquibase does not rollback failed changesets with MySQL

1 Answers