Liquibase: modify a change set

eXo Platform uses Liquibase to manage its database evolutions. It makes it easy to update database schema (add/remove table, add/remove column, add an index,etc) or database content (update rows, etc).

These changes are defined as a list of change sets that Liquibase will apply when it is executed (at startup in the case of eXo Platform). Once a change set has been applied, Liquibase remembers it and does not apply it again on the next startup.

It also checks that the applied change sets have not been modified in the changelog file. And this is what we are going to talk about here.

While it is good practice not to modify these already applied change sets, it is sometimes necessary.

Here we will tell you some of the reasons you might need to modify a change set and how to do it. But first let’s look at the mechanics of Liquibase.

How does it work?

Database changes are defined as a set of change sets in XML files. Here is an example from the eXo Wiki application:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                  	http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd">

  <!-- Managing both DB that use sequences and db that use auto increment -->
  <property name="autoIncrement" value="true" dbms="mysql,mssql,h2,sybase,db2,hsqldb"/>
  <property name="autoIncrement" value="false" dbms="oracle,postgresql"/>

  <!-- Managing auto generation of timestamp by Database -->
  <property name="now" value="now()" dbms="mysql,hsqldb,postgresql,h2"/>
  <property name="now" value="sysdate" dbms="oracle"/>
  <property name="now" value="CURRENT_TIMESTAMP" dbms="mssql"/>

  <changeSet author="wiki" id="1.0.0-1">
    <createTable tableName="WIKI_WIKIS">
      <column name="WIKI_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_WIKI_WIKIS_ID"/>
      </column>
      <column name="NAME" type="NVARCHAR(550)"/>
      <column name="OWNER" type="NVARCHAR(200)">
        <constraints nullable="false"/>
      </column>
      <column name="TYPE" type="NVARCHAR(50)">
        <constraints nullable="false"/>
      </column>
      <column name="WIKI_HOME" type="BIGINT"/>
      <column name="SYNTAX" type="NVARCHAR(30)"/>
      <column name="ALLOW_MULTI_SYNTAX" type="BOOLEAN"/>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>
  ...
  <changeSet author="wiki" id="1.0.0-31" dbms="oracle,postgresql">
    <createSequence sequenceName="SEQ_WIKI_WIKIS_WIKI_ID" startValue="1"/>
  </changeSet>
  …
</databaseChangeLog>

When Liquibase runs these change sets, it stores the ID of all the successfully applied change sets in its own database, along with the checksum of these change sets.

For example, for the first change set of the above changelog (id=ᐦ1.0.0-1ᐦ), the following entry is inserted in the Liquibase table (DATABASECHANGELOG):

ID: 1.0.0-1
AUTHOR: wiki
FILENAME: db/changelog/wiki.db.changelog-1.0.0.xml
DATEEXECUTED: 2016-04-13 07:14:29
ORDEREXECUTED: 22
EXECTYPE: EXECUTED
MD5SUM: 7:4714e3fc16bfa2cac4dde34703a4f58f
DESCRIPTION: createTable
COMMENTS:
TAG: NULL
LIQUIBASE: 3.4.1
CONTEXTS: NULL
LABELS: NULL

The MD5SUM column contains the checksum of the change set, so any change in the change set in the XML file will result in a different checksum.

At each execution, for each change set defined in the XML, Liquibase checks if its ID/author (in the example: 1.0.0-1/wiki) is stored in the database:

• If not, the change set is applied and the entry is inserted in the Liquibase table.
• If so, Liquibase calculates the checksum of the XML change set and checks if this is the same as the stored checksum.
  • If the checksums are the same, the change set is ignored (already applied and no change in XML change set).
  • If the checksums are different, an error is raised.

This ensures that a change set is only applied once and that an already applied change set has not been modified.

But there are cases where modifying a change set is necessary.

When should I modify a change set?

The main reason to modify a change set is when the change set fails to apply, for whatever reason. For example, because of a bug, or because you want to support a new database vendor or a new database version that is not compatible with this change set.

Let’s take a real example with an eXo Platform issue: Data initialization issues at startup with MySQL 5.7.14+.

Starting from MySQL 5.7.14, the eXo Social change sets fail to apply on a fresh database because of the error ‘Invalid default value for ‘CREATED_DATE’’, whereas they apply successfully on previous MySQL versions. In such a situation, there is no way to avoid modifying the change set.

Another valid reason to modify a change set is when you want to optimise the change set. After several versions of your application, you will probably carry out some unnecessary operations. For example, a first change set creates a table, then another change set changes the default value of one of the columns of that table, and a final change set removes this column because the application no longer needs. If you had designed your application now, you would not have created this column at all. But because of Liquibase, when your application starts on a fresh database, the column will be created, updated, then deleted. In this case, modifying the initial change set that creates the table in order to remove the column is interesting.

A useful article on this topic is available in the Liquibase documentation.

You may also like: 

• Safely manage the database for your eXo add-ons with Liquibase Maven Plugin
• How to integrate Angular business applications in eXo Platform
• Definition and Types of collaboration in business
• What is a digital workplace and where did the term originate?

How do I modify a change set?

We suppose that you will want to modify a change set because it fails to apply. There are two steps to the process:

• Modify the change set to make it apply successfully on a database where it has never been applied.
• Make sure the change set will still work correctly on a database where it has already been applied.

If you just try to fix the change set in the changelog file, it will fulfil the first objective but not the second. Liquibase will detect this update (the checksum of the change set in the changelog XML file is different to the one in the Liquibase data) for already initialised instances and will raise an error.

The solution is to add into the XML file the new checksum (the one of the change set after the modification), using the tag validCheckSum.

Therefore, the process to modify a change set is as follows:

• Start eXo Platform in an environment where it starts successfully.
• Stop it.
• Modify the change set in the changelog file.
• Restart eXo Platform. Liquibase should raise an error, something like:

2017-05-06 10:52:15,082 | ERROR | Error while applying liquibase changelogs db/changelog/wiki.db.changelog-1.0.0.xml - Cause : Validation Failed:
     1 change sets check sum
          db/changelog/wiki.db.changelog-1.0.0.xml::1.0.0-42::wiki is now: 7:21d239448c33f39a9300ea433349d470

• Copy the checksum reported in this error (here : 7:21d239448c33f39a9300ea433349d470). This is the checksum of the updated changeset (which is not equal anymore to the one in the Liquibase database).
• Add a tag <validChecksum> containing this checksum in the modified changeset :

<changeSet id="1.0.0-42" author="wiki">
    <validCheckSum>7:21d239448c33f39a9300ea433349d470</validCheckSum>
    … // content of the changeset (with the update)
</changeSet>

You are basically telling Liquibase that 7:21d239448c33f39a9300ea433349d470 is also a valid checksum (besides the one in the database) when checking the equality of checksums for instances that have already run this change set.

Your eXo Platform instance should now run successfully in any case (with or without a populated database).