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.