How to fail Liquibase changeset when prerequisites are missing

Arkadiusz Fronc Liquibase 04 September 2018

Index creation should not start when there is not enough disk space. An old table should not be dropped until it is completely empty. A database upgrade may happen only if a DBA has changed a default value of some setting. Many database upgrades have some prerequisites. If they are not met, the whole change should not even start. You may write a checklist during development and follow it on the upgrade but there is a better way - preconditions in Liquibase.

Precondition 1 - enough disk space

Data migration estimated for 10 hours that fails after 8 hours because of running out of disk space is a disaster. It would be very inconvenient even if rolling back was easy. Otherwise it could be catastrophic.

Assuming that a changeset that does the migration should not even start without at least 10 GB of free space in a datafile, the following Liquibase file presents a solution.

<?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"
         xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
         xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
         http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
 
     <changeSet id="Data migration" author="DBA presents">
         <preConditions onFailMessage="Preconditions failed - at least 10GB of free space">
             <sqlCheck expectedResult="1">
                 select case when size/128.0 - cast(fileproperty(name, 'SpaceUsed') as int)/128.0 > 10000 then 1 else 0 end
                 from sys.database_files
                 where name = 'MyDatabase'
             </sqlCheck>
         </preConditions>
         <sql>
             insert into NEWTABLE (ID, NAME, VALUE)
             select ID, NAME, VALUE
             from OLDTABLE;
         </sql>
     </changeSet>
 
 </databaseChangeLog>

When the data file has more than 10GB of free space, the changeset executes, but when the precondition fails, you can see the following in the command line.

liquibase --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --classpath="c:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar" --changeLogFile=databaseChangeLog.xml --url="jdbc:sqlserver://localhost\DEVSQL01;databaseName=MyDatabase" --username=sa --password=xyz --logLevel="severe" update
SEVERE 29.07.2018, 14:18: liquibase: databaseChangeLog.xml: databaseChangeLog.xml::Data migration::DBA presents: Change Set databaseChangeLog.xml::Data migration::DBA presents failed. Error: Migration failed for change set databaseChangeLog.xml::Data migration::DBA presents:
Reason:
databaseChangeLog.xml : Preconditions failed - at least 10GB of free space

liquibase.exception.MigrationFailedException: Migration failed for change set databaseChangeLog.xml::Data migration::DBA presents:
Reason:
databaseChangeLog.xml : Preconditions failed - at least 10GB of free space

at liquibase.changelog.ChangeSet.execute(ChangeSet.java:463)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:43)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:70)
at liquibase.Liquibase.update(Liquibase.java:195)
at liquibase.Liquibase.update(Liquibase.java:174)
at liquibase.integration.commandline.Main.doMigration(Main.java:997)
at liquibase.integration.commandline.Main.run(Main.java:170)
at liquibase.integration.commandline.Main.main(Main.java:89)
Caused by: liquibase.exception.PreconditionFailedException: Preconditions Failed
at liquibase.precondition.core.PreconditionContainer.check(PreconditionContainer.java:220)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:449)
... 7 more
Unexpected error running Liquibase: Preconditions Failed

SEVERE 29.07.2018, 14:18: liquibase: databaseChangeLog.xml::Data migration::DBA presents: Preconditions Failed
liquibase.exception.MigrationFailedException: Migration failed for change set databaseChangeLog.xml::Data migration::DBA presents:
Reason:
databaseChangeLog.xml : Preconditions failed - at least 10GB of free space

For more information, use the --logLevel flag

Remember about setting logLevel, otherwise you will not see a meaningful error.

Precondition 2 - specific user

A database upgrade may require special privileges. Running it without them will fail so you have already created a special user on all environments and you would like to make sure they are used for all upgrades done by Liquibase. A key is all upgrades. This time the precondition applies to the whole upgrade not only a specific changeset. That is easy because preConditions tag can be applied inside a changeset but also inside a databaseChangeLog.

<?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"
         xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
         xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
         http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
 
     <preConditions onFailMessage="Preconditions failed - dba user is required">
         <runningAs username="dba" />
     </preConditions>
 
     <changeSet id="Data migration" author="DBA presents">
         <sql>
             insert into NEWTABLE (ID, NAME, VALUE)
             select ID, NAME, VALUE
             from OLDTABLE;
         </sql>
     </changeSet>
 
 </databaseChangeLog>

SEVERE 29.07.2018, 14:50: liquibase: Validation Failed:
1 preconditions failed
databaseChangeLog.xml : Preconditions failed - dba user is required

liquibase.exception.ValidationFailedException: Validation Failed:
1 preconditions failed
databaseChangeLog.xml : Preconditions failed - dba user is required

at liquibase.changelog.DatabaseChangeLog.validate(DatabaseChangeLog.java:181)
at liquibase.Liquibase.update(Liquibase.java:191)
at liquibase.Liquibase.update(Liquibase.java:174)
at liquibase.integration.commandline.Main.doMigration(Main.java:997)
at liquibase.integration.commandline.Main.run(Main.java:170)
at liquibase.integration.commandline.Main.main(Main.java:89)

For more information, use the --logLevel flag

In that case, the precondition is verified before any changeset is attempted to apply. It becomes a prerequisite for the whole database upgrade.

Notice also that there is a predefined tag runningAs which checks the name of the database user who is running the upgrade. There are more such predefined conditions listed in the Liquibase manual.

Precondition 3 - complex case

One precondition per changeset would be too easy if it was always enough. Real life scenarios often require a little bit more. Fortunately, preConditions tag may contain multiple prerequisites joined together with logical operators.

<?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"
         xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
         xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
         http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
 
     <preConditions onFailMessage="Preconditions failed - dba user, 10GB of free space and MSSQL or Oracle db engine">
         <and>
             <runningAs username="dba" />
             <sqlCheck expectedResult="1">
                 select case when size/128.0 - cast(fileproperty(name, 'SpaceUsed') as int)/128.0 > 10000 then 1 else 0 end
                 from sys.database_files
                 where name = 'MyDatabase'
             </sqlCheck>
             <or>
                 <dbms type="mssql" />
                 <dbms type="oracle" />
             </or>
         </and>
 
     </preConditions>
 
     <changeSet id="Data migration" author="DBA presents">
         <sql>
             insert into NEWTABLE (ID, NAME, VALUE)
             select ID, NAME, VALUE
             from OLDTABLE;
         </sql>
     </changeSet>
 
 </databaseChangeLog>

The above example will allow the upgrade only when it is run by dba user, there is at least 10GB of space in the datafile and the upgrade is executed on SQL Server or Oracle.

Summary

Prerequisites are very common in database upgrades. We usually create checklists and manually verify them which is error prone. At some point it makes sense to automate at least some of them. The less items you have to remember about, the more creative your work can become. Preconditions are there to help you.

If anything goes wrong, the upgrade can be rolled back with autorollback feature.

If you are new to Liquibase watch my introduction to Liquibase and find more Liquibase articles.

If you like what I do, consider buying me a coffee :)

Buy me a coffee