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?

Answer 1

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.

Answer 2

Mysql (and many other relational databases) has implicit commit concept. Most of database trigger the commit implicitly (just like you call COMMIT yourself) to end the current active transaction before(or after) executing the DDL statements.

Liquibase tries to apply specified changes of one changeset under single transaction. In your case, there are two changes and both are DDL statements (RENAME TABLE and ALTER TABLE), under one change set. Both statements will trigger the implicit commit which would leave the database inconsistent state if later statement fails.

More information on mysql implicit commit on their website including the comprehensive list of SQL statements which trigger the implicit commits.

Hope it helps.