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

However, there is a newly supported command hammer <dbdef> precise command which allows the user to selectively rollback specific changesets. With this command Liquibase cannot guarantee that database dependencies will not be impacted by the rollback. 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: DAT-10150 - Getting issue details... STATUS .)

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: (did open DAT-10546 to handle AND within the same searchLabels entry.)

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'