Liquibase transactions not working in Spring environment

Question

I'm using Liquibase in a project with a Spring environment. However the rollback functionality doesn't seem to work.

I suspect rollback functionality isn't supported when using Spring to run Liquibase since the manual says:

LiquiBase allows you to undo changes you have made to your database, either automatically or via custom rollback SQL. Rollback support is available in command line, Ant, Maven, and Grails.

However find it hard to believe rollbacks aren't possible in this setup. So the question is: "Why are rollbacks not working and how can I get rollbacks working?"

---

EDIT:

I now realize I should have asked the question a bit different. The kind of rollbacks I would like to have are the rollbacks that happen if something goes wrong when migrating.

I tried to use transactions (as these are there for I assume), but this didn't have the wanted effect.

Example of a change set that will go wrong:

<changeSet runInTransaction="true">
    <createTable tableName="table1">
        <column name="a" type="int"/>
    </createTable>
    <createTable tableName="table1">
        <column name="a" type="int"/>
    </createTable>
    <createTable tableName="table2">
        <column name="a" type="int"/>
    </createTable>
</changeSet>

When I run the example change set, Liquibase manages to create table1 but when it tried to create another table with the same name it fails (obviously). The created table doesn't get deleted and is persisted to the database.

Looking at the Liquibase manual I notice that:

runInTransaction

Should the changeSet be ran as a single transaction (if possible)? Defaults to true. Warning: be careful with this attribute. If set to false and an error occurs part way through running a changeSet containing multiple statements, the LiquiBase databasechangelog table will be left in an invalid state Since 1.9

I'm using MySQL, but the change set has to work with MSSQL and Oracle too.

I'm also using Spring's transactions, and these work perfect, any ideas on how to get both the awesomeness of Liquibase and those awesome (database independent) transactions?

Answer 1

According to the doco the spring integration appears designed to support database migrations.

To perform a rollback, you could use liquibase jar as follows (See command-line docs):

java -jar liquibase.jar \
      --driver=oracle.jdbc.OracleDriver \
      --classpath=website.war \
      --changeLogFile=com/example/db.changelog.xml \
      --url=jdbc:oracle:thin:@localhost:1521:oracle \
      --username=scott \
      --password=tiger \
      rollbackCount ?

The database JDBC jar and change logs should all be available from the WAR file.

Answer 2

From what I recall the spring integration is for migrating a database as a post-processing step. Since there's no interactive step, you wouldn't have the opportunity to specify a command, like 'rollback one changeset'.

For interactive steps like rollbacking n-changes, you would need to use the CLI, or a build script via Ant or Maven.

POST EDIT

Transaction-level rollbacks depend on the underlying database implementation. Some, like Oracle, do not allow DDL commands (such as create table) to be rolled back during a transaction rollback. I've found that for these cases its best to put each DDL statement in its own changeset, in case one fails. In fact, if you run the generateChangeLog command against an existing database, you'll find that Liquibase generates a changeset for each DDL command. You should still have transaction-level recovery for other types of commands though, such as inserts, etc.