Liquibase commits changeset partially

Question

Using liquibase v3.6.3 on MySQL. If I understood correctly, every CHANGESET is run into a SQL transaction by default. However, seems to me that transactions are being commites in a CHANGE basis. When running this script

databaseChangeLog:
  - changeSet:
      id: changeset-
      changes:        
         - renameTable:
            oldTableName: old_table
            newTableName: new_table
        - addColumn:
            columns:
              - column:
                  name: test_column_name
                  type: varchar(255)
                  tableName: other_table

If the addColumn tag fails because some SQL exception (i.e constraint check or other), then the databasechangelog table won't be updated, which I don't expect to, as the changeset failed. However, the firs statement DID pass and my table is now called new_table.

Of course, if I correct the problem causing the second one to fail and retry the update, it will fail because old_table doesn't exist anymore.

I'm aware of this paragraph in the liquibase documentation

Liquibase attempts to execute each changeSet in a transaction that is committed at the end, or rolled back if there is an error. Some databases will auto-commit statements which interferes with this transaction setup and could lead to an unexpected database state. Therefore, it is usually best to have just one change per changeSet unless there is a group of non-auto-committing changes that you want applied as a transaction such as inserting data.

https://www.liquibase.org/documentation/changeset.html

but I don't really understand it. Auto-commit means auto commiting A TRANSACTION. If all the changeset is wrapped in a transaction, why are there only some changes passing? Should liquibase rollback the whole transaction?

Any best practices for this? Can't we manually set transactions in liquibase?

Are you sure that your db is not in auto commit mode? Did your try running your SQL statements manually (without liquibase) to make sure the statements really work in transactions? — Jens
yes, IT IS in autocommit. I don't want to change that though. What I don't understand is, if the WHOLE changeset is wrapped in a Tx, autocommit should commit the WHOLE changeset, or rollback it. Can't figure out why the Changeset is commited partially. So, Liquibase does NOT open a Tx for the changeset? — luso
I don't know exactly how liquibase works in terms of changesets. What I am trying to say is: Maybe the database is the reason for your observed behaviour. Hence the suggestion to try it manually (without liquibase). — Jens
I think the issue is basically nested transactions. Yes, Liquibase opens a transaction for each changeset, but if the database is in autocommit, it is basically adding an inner transaction, and that is what is committed. When that happens, it is not possible to roll back the outer transaction. — SteveDonie

Stefan Mondelaers Stefan Mondelaers · Accepted Answer · 2019-06-21T14:17:59

It is not Liquibase that is committing a changeset partially. I have worked with many databases and a basic concept for all the databases I used, is that a transaction combines data modifications (DML) only.

DDL is never part of a transaction. It is always executed immediately and an open transaction is automatically committed before it is executed. The reason for that is that the rollback command of a database can handle data modifications only. It can't handle DDL. And if a rollback is not possible anymore then keeping the transaction open becomes useless.

So, Liquibase does create a transaction and commits all changes at the end as the doucmentation states. But that works only if the changeset contains DML only, no DDL.

And because of that DDL and DML should never be mixed in one changeset and every DDL statement should be in a separate changeset. Otherwise there is no way that Liquibase can prevent a changeset from partially succeeding and causing trouble when trying to rollback.

Liquibase commits changeset partially

2 Answers