Jakob Külzer Ninja Coding Monkey goes Canada

25Sep/082

LiquiBase, addForeignKeyConstraint and Column Types do not Match

Today I encountered some weird errors while migrating my database using LiquiBase. It took me quite some time to figure out what was wrong as no one has encountered this error before. So, here's the problem and the solution. ;-)

Ugly but simple example: I have two domain classes, say Book and Author and a Book has an Author. How Author looks like is not important. Book looks like this

class Book {
  Author author
}

Due to changes in the requirements the author needs to be optional thus nullable. With updated constraints the Book class looks like this:

class Book {
  Author author
  static constraints = { author(nullable:true) }
}

So upgrading the database with LiquiBase should be as easy as adding a couple of lines to the changelog file:

<changeSet author="jakob" id="foo-1">
  <dropForeignKeyConstraint baseTableName="BOOK" constraintName="FK2E3AE9CD85EDFA"/>
  <dropNotNullConstraint tableName="BOOK" columnName="AUTHOR_ID"/>
  <addForeignKeyConstraint baseColumnNames="AUTHOR_ID"
    baseTableName="BOOK" constraintName="FK2E3AE9CD85EDFA"
    deferrable="false" initiallyDeferred="false"
    referencedColumnNames="ID" referencedTableName="AUTHOR"/>
</changeSet>

Basically this changeset drops the foreign key constraint on AUTHOR_ID, drops the not-null-constraints and re-adds the foreign key constraint which is required to handle the association between the Book and the Author class.

Unfortunately the addForeignKeyConstraint will cause an error, something like this:

Caused by: java.sql.SQLException: Column types do not match in statement [ALTER TABLE...

The reason why this happens is that: when dropping the not-null constraint the type of the AUTHOR_ID column is re-set to something unusable. Inspecting the database with the HSQLDB Databasemanager showed that the type of the is set to NULL which is weird. The solution to this is easy however. All you need to do is to provide LiquiBase the type with the columnDataType attribute of the column in the dropNotNullConstraint element:

<changeSet author="jakob" id="foo-1">
  <dropForeignKeyConstraint baseTableName="BOOK" constraintName="FK2E3AE9CD85EDFA"/>
  <dropNotNullConstraint tableName="BOOK" columnName="AUTHOR_ID"/>
  <addForeignKeyConstraint baseColumnNames="AUTHOR_ID" columnDataType="BIGINT" 
    baseTableName="BOOK" constraintName="FK2E3AE9CD85EDFA"
    deferrable="false" initiallyDeferred="false"
    referencedColumnNames="ID" referencedTableName="AUTHOR"/>
</changeSet>

With this in place it will work like a charm.

23Sep/080

Updated Post about Grails and LiquiBase

If you have read my post about Grails and LiquiBase please note that I updated it today. I added new contents and corrected the section about db-diff. Make sure you have a look at it ;-) .

19Sep/0817

Grails and LiquiBase – How to use

In my current project I'm now at a point where the application has come pretty far but is still far from finished. The domain model is not perfect, but very stable at this point -- changes however, are still possible.

To get going with the application we are planning to perform tests with some real data. The problem at this point is that entering real data into a database that might be wiped due to changes in the domain model is a very tedious task, at least for the person responsible for entering the data. And the reason for running such a test is to find problems with the domain model which lead to the dreaded changes in the domain model. This cat bites her own tail...

As wiping out the database and re-entering the data is not an option at this point, I decided to look into LiquiBase which as been sitting on my delicious list for quite a while.

This post is an attempt to write down my experiences and learned lessons with LiquiBase. The documentation of LiquiBase is technically complete and extensive but lacks more information about how to use it in a real project. This is an attempt to create a guide on how to leverage LiquiBase in a real world project.