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:
- MariaDB is a replacement of MySQL and was once forked from MySQL.
- 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.
- 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.
- 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.