Rollback to tag not working if tag applied to runOnChange changeset

Question

I have been able to do rollback to tag with just schema changes, but I ran into a scenario that does not work when I mix in stored procedures.
I am using SQL changelogs against an Oracle database. Here is the scenario:

Release 1.0.0
I have a script r-1.0.0.sql that contains creates a table, and a script proc.sql that creates a stored procedure. The proc changeset is tagged as runOnChange=true.
I am happy with the changes, and I tag the database with tag 1.0.0
In the end the DATABASECHANGELOG table shows:
1 - r-1.0.0.sql-EXECUTED
2 - proc.sql-EXECUTED-(tag)1.0.0

Release 2.0.0
I have a script r-2.0.0 that renames a column, and I also updated proc.sql with the new column name. After running this, DATABASECHANGELOG is:
1 - r-1.0.0.sql-EXECUTED
4 - proc.sql-RERAN-(tag)1.0.0
3 - r-2.0.0.sql-EXECUTED

You notice that the re-ran proc script has a new number, but it still keeps the 1.0.0 tag

If now I want to rollback to tag 1.0.0, the rollback command does nothing, because tag 1.0.0 corresponds to the very latest change in the log.

This seems to be by design. Is there a different way to organize my changes to make this work?

According to this thread I should be able to specify a different change log table for the stored procedures. forum.liquibase.org/topic/…. I am still working on figuring out the command line. — Dan J

Dan J Dan J · Accepted Answer · 2017-10-18T21:26:17

I found a solution based on the article I linked above. Due to constraints in my environment I did not have an easy way to pass Java environment variables. I ended up installing a custom batch file (I named it Liquibase-sp.bat) with the following content:

@echo off

IF NOT DEFINED JAVA_OPTS set JAVA_OPTS=
set JAVA_OPTS=-Dliquibase.databaseChangeLogTableName=STOREDPROCCHANGELOG %JAVA_OPTS%

liquibase %*

It sets the parameter in a variable used by the Liquibase batch file, then calls the batch file passing the entire command line.
During my deployment I apply schema changes by calling "liquibase", and then apply stored proc changes by calling "liquibase-sp". The schema changes get logged in the default DATABASECHANGELOG table, while the proc changes get logged in a separate STOREDPROCCHANGELOG table. All tagging is done calling "liquibase" so it uses the default table, and only schema changes get tagged with a version.

Rollback works in the scenario I mentioned.

I expect to see an additional problem if I have a release 2.0.0 with no changes. When I tag the database with 2.0.0, the tag on the last changeset is modified from 1.0.0 to 2.0.0. This means any rollback to tag 1.0.0 would fail. I'm not worried though, there are procedural workarounds for this.

Rollback to tag not working if tag applied to runOnChange changeset

1 Answers