How To: Precise Rollback

Overview

Liquibase recommends using the standard rollback functionality which “rewinds” a database to a specified point. All changesets deployed after that point are rolled back. These rollbacks are documented here:

CLI Commands | CLICommands rollback

However, there is a newly supported command hammer <dbdef> precise command which allows the user to selectively rollback specific changesets.

WARNING! With this command Liquibase cannot guarantee that database dependencies will not be impacted by the rollback.

Take care not to remove changesets that later deployments built on.

For example:

Deploy a create table
Deploy a view on that table

If you now attempt to roll back only the changeset that created the table, then the view becomes invalid. It is up to the user to ensure that precise rollbacks include all the necessary database dependencies.

Syntax for Precise Rollback

Syntax for Precise Rollback is as follows:

hammer rollback <dbdef> precise [--preview] [--<search-option>=<value>, ...]

The search options determine which changesets to rollback.

Searches are not case-sensitive.

Multiple values in one statement are OR'ed together.
Multiple search option statements are AND'ed together.

Search option statements take one or a comma-separated list of values. They are provided for changeset attributes as follows:

searchIds - id
searchLabels - label
searchContexts - context
searchIgnore - ignore - true | false - Searches for false also return changesets where the ignore attribute is not set. true | false
searchOrigFilePaths - origFilePath
searchOrigFileNames - origFileName
searchVersion - version, an integer

Conditions for Precise Rollback

Changesets must meet the following criteria when evaluated for a precise rollback:

Changesets must be deployed on the specified databases
A rollback script is provided with the original SQL script for:
- DDL
- DATA_DML
- SQL_DIRECT
For stored logic:
- Must be using DaticalDB 7.18+ (There was an engineering ticket to support precise rollback with Stored Logic: https://datical.atlassian.net/browse/DAT-10150.)

How Precise Rollback Works

When using precise rollback, the associated -rollback script is run on the database. The corresponding row in the DATABASECHANGELOG table is removed.

Upon the next deployment the rollback changesets would be eligible for deployment again. To permanently rollback changes, the changesets must be marked with ignore status.

Example Scenarios

DDL and DIRECT and DATA_DML

A developer checks in a script along with a -rollback script. When packaging the file, the developer includes a work item/JIRA, eg. JIRA-7126. The changeset looks like:

    <changeSet appdba:scriptChecksum="49c3fa9cb08a2909882377a70754c5db" author="Amy Smith" created="2022-05-24 18:41-0500" datical:origFileName="alter_emp_081_add_uc.sql" datical:origFilePath="sql_direct" datical:version="1" datical:versionStrategy="DEPLOY_ALL" id="20220524184121367_alter_emp_081_add_uc" labels="jira-7126,dev,alter_emp_081_add_uc.sql,release">
        <appdba:sqlcmd path="sql/20220524184121367_alter_emp_081_add_uc.sql" removeOuts="First"/>
        <rollback>
            <appdba:sqlcmd path="sql/20220524184121367_alter_emp_081_add_uc-rollback.sql" removeOuts="First"/>
        </rollback>
        <comment>[JIRA-7126] add constraint</comment>
    </changeSet>

The following commands could be used to rollback:

hammer rollback DEV precise --searchLabels=JIRA-7126

To use multiple labels use:

hammer rollback DEV precise --searchLabels="rules_test_005.sql" --searchLabels="dev"

STORED LOGIC (functions, pkg, pkg body, procedures, triggers, and views)

On DaticalDB 7.18+

Stored logic script has been added but not modified (changelog only has version 0 and version 1)
- Using a rollback will drop the object
Stored logic script has been modified with a label JIRA-6544 (version 2)
- Using a rollback with --searchLabel=JIRA-6544 will return the object to its earlier version (version 1)
If you try to rollback to a stored logic version that is not the latest version, you will get an error
- Rollback of 1 change set to LiquibaseChangesetKey [changesetId='20220628221529316_test_new_proc_001', author='Amy Smith'] was not successful. The ChangeSet with id '20220628221529316_test_new_proc_001', author 'Amy Smith' and version '1' can not be rolled back. The ChangeLog contains the ChangeSet that has a higher version Failed on:changeSet: Id='20220628221529316_test_new_proc_001' author='Amy Smith' Did not attempt to rollback:changeSet: Id='20220628221529316_test_new_proc_001' author='Amy Smith'