Executing SQL by Liquibase on MariaDB

Liquibase is an excellent and open source framework for managing database changes. In most cases these changes can be expressed in a database-independent way (XML/JSON/YAML instead of SQL). However sometime it’s necessary to use SQL code (‘sql’ tag) or SQL file (‘sqlFile’ tag) in a Liquibase change-set. But SQL in general is not database-independent. Luckily Liquibase supports different SQLs for different databases:

<changeSet author="someone" id="some-change">
    <sqlFile dbms="h2" path="file_for_h2.sql"/>
    <sqlFile dbms="mysql" path="file_for_mysql.sql"/>
</changeSet>

The problem

The above change-set worked fine when my application was configured to use H2 database, however when the same application was reconfigured to use MariaDB it turned out the SQL from “file_for_mysql.sql” was not executed. It was really surprising as:

  1. MariaDB is a replacement of MySQL and was once forked from MySQL.
  2. In the documentation for Liquibase, in section “Supported Databases” there is nothing about MariaDB, but MySQL is listed and “mysql” is given as database identifier for MySQL.
  3. In all materials I’ve found there was an information that “mysql” is the proper value for dbms parameter used in ‘sql’ and ‘sqlFile’ tags to mark it’s for MariaDB.
  4. In application logs there was a message from Liquibase that the change set was executed successfully. No errors reported by Liquibase. However no changes recorded in a database as well.

The solution

The key step in my investigation was to add a precondition to my change-set:

<changeSet author="someone" id="some-change">
    <preConditions>
        <or>
            <dbms type="h2"/>
            <dbms type="mysql"/>
        </or>
    </preConditions>
    <sqlFile dbms="h2" path="file_for_h2.sql"/>
    <sqlFile dbms="mysql" path="file_for_mysql.sql"/>
</changeSet>

After this modification Liquibase signalled an error and revealed that identifier for MariaDB is… “mariadb”. Something not mentioned in official Liquibase documentation! Knowing this I’ve modified my change-set to this:

<changeSet author="someone" id="some-change">
    <preConditions>
        <or>
            <dbms type="h2"/>
            <dbms type="mysql"/>
            <dbms type="mariadb"/>
        </or>
    </preConditions>
    <sqlFile dbms="h2" path="file_for_h2.sql"/>
    <sqlFile dbms="mariadb,mysql" path="file_for_mysql.sql"/>
</changeSet>

…and then things worked like a charm! 🙂

Conclusion

When using ‘sql’ or ‘sqlFile’ tag in a Liquibase change-set with assumption that some variant of SQL must be executed it’s a very good idea to add a proper precondition to the change-set as well. Such precondition as given above makes sure that a database being in use has one of listed identifiers.

About krzysztoftomaszewski

I've got M.Sc. in software engineering. I graduated in 2005 at Institute of Computer Science, Warsaw University of Technology, Faculty of Electronics and Information Technology. I'm working on computer software design and engineering continuously since 2004.
This entry was posted in database and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s