Liquibase introduction into an existing project and MySQL

Question

We have an existing Java project, which is built upon a MySQL database and we rely on MySQL-specific setup and migration mechanisms for our ~ 100 tables and ~ 10 views. We don’t use stored procedures.

We implemented custom Java code (even featuring getRuntime().exec(…) ) to glue it all together and various Ant-Targets to invoke the database functionality. In favor of reduced database dependency and maintenance of code not related to our core business logic. We are currently evaluating liquibase to ease database deployment and migration.

Therefor we downloaded liquibase version 3.2.2 and used the “generateChangeLog” command to create a changelog-file for our current database schema.

Now we have some trouble with the generated changelog file:

It is not possible to deploy the generated changelog.xml file into the MySQL database which it was created from, because liquibase generates ‘DOUBLE(22)’ instead of a plain ‘DOUBLE’ keyword within the setup SQLs. By looking it up on the liquibase JIRA and some googling this seems to be a rather odd and old problem within liquibase, yet easy to fix but currently still unresolved.
Deploying the generated changelog-file into a H2 database raises another issue: Something about primary key statements generated by liquibase isn’t suitable for the H2 database.

Now some questions on this approach:

We expected database independency to be one of the main goals of liquibase, but it just doesn’t seem to be. Has anyone else encountered similar issues?
Is it somehow odd and not the intended usage to start from a generated changelog file from an existing database? Would starting from a manual changelog file somehow be a more suitable approach then?
is the command (mentioned in another post here) the appropriate way to deal with the found MySQL/H2 related issues?
Do you have any further best practices or resources of the integration of liquibase into an existing project in the above scenario?

Your advice is highly appreciated, thanks in advance!

Kind regards,

Steven

Mark O'Connor Mark O'Connor · Accepted Answer · 2015-03-09T18:00:28

The liquibase documentation states how it supports a small set of generic column types across databases.

http://www.liquibase.org/documentation/column.html

To help make scripts database-independent, the following “generic” data types will be converted to the correct database implementation:
BOOLEAN
CURRENCY
UUID
CLOB
BLOB
DATE
DATETIME
TIME
BIGINT
Also, specifying a java.sql.Types.* type will be converted to the correct type as well. If needed, precision can be included. Here are some examples:
java.sql.Types.TIMESTAMP
java.sql.Types.VARCHAR(255)

To support specific data types the best way is to declare DB specific properties in the changelog.

http://www.liquibase.org/documentation/changelog_parameters.html

<property name="double.type" value="DOUBLE"     dbms="h2"/>
<property name="double.type" value="DOUBLE(22)" dbms="mysql"/>

<changeSet id="1" author="joe">
     <createTable tableName="${table.name}">
         <column name="id" type="int"/>
         <column name="val1" type="${double.type}"/>
         <column name="val2" type="${double.type}"/>
     </createTable>
</changeSet>

Liquibase introduction into an existing project and MySQL

1 Answers