Liquibase rollback-sql not producing SQL statements

1

I’m attempting to run the following liquibase rollback-sql statement in order to produce the required SQL or DDL in order to reverse my changes in Snowflake. I am also using the Liquibase docker container in Jenkins. Version is 4.19.

The following are the changes I’d like reversed in DATBASECHANGELOG. These were new changes put into the database through Liquibase update.

33_123 M11X ./…/com/idrc/impl/CMS_FCT_CLM_IMPL/CMS_FCT_CLM_IMPL-changelog.sql 00:35.5 1557 EXECUTED 8:424fde792016cfcb3ecb2d9fb57e8c71 sql 4.21.1
33_124 M11X ./…/com/idrc/impl/CMS_FCT_CLM_IMPL/CMS_FCT_CLM_IMPL-changelog.sql 00:36.4 1558 EXECUTED 8:8ddc3bf0aebdefe58671127a6c718488 sql 4.21.1
33_125 M11X ./…/com/idrc/impl/CMS_FCT_CLM_IMPL/CMS_FCT_CLM_IMPL-changelog.sql 00:37.6 1559 EXECUTED 8:23b9130739ed5d02247d7875b1d1fc15 sql 4.21.1
33_126 M11X ./…/com/idrc/impl/CMS_FCT_CLM_IMPL/CMS_FCT_CLM_IMPL-changelog.sql 00:38.6 1560 EXECUTED 8:1233f73bdf5fa7c3ee483cfab949cb6a sql 4.21.1
33_127 M11X ./…/com/idrc/impl/CMS_FCT_CLM_IMPL/CMS_FCT_CLM_IMPL-changelog.sql 00:40.0 1561 EXECUTED 8:38edfdeb3ebf5553fa1b634ee3147298 sql 4.21.1
33_128 M11X ./…/com/idrc/impl/CMS_FCT_CLM_IMPL/CMS_FCT_CLM_IMPL-changelog.sql 00:40.9 1562 EXECUTED 8:254fcf5eb5815dc6dc0cc1cb902ec0a1 sql 4.21.1
33_129 M11X ./…/com/idrc/impl/CMS_FCT_CLM_IMPL/CMS_FCT_CLM_IMPL-changelog.sql 00:42.2 1563 EXECUTED 8:14819e9d1c3039df029bddd500e5bf22 sql 4.21.1
33_130 M11X ./…/com/idrc/impl/CMS_FCT_CLM_IMPL/CMS_FCT_CLM_IMPL-changelog.sql 00:43.5 1564 EXECUTED 8:7ae5229343b5c1898dacf9ba058e95c8 sql 4.21.1
33_131 M11X ./…/com/idrc/impl/CMS_FCT_CLM_IMPL/CMS_FCT_CLM_IMPL-changelog.sql 00:44.4 1565 EXECUTED 8:be3d0a4ea996d171535754a93995c448 sql 4.21.1
33_132 M11X ./…/com/idrc/impl/CMS_FCT_CLM_IMPL/CMS_FCT_CLM_IMPL-changelog.sql 00:45.5 1566 EXECUTED 8:5883f780e28185fd54edad61ce7373ee sql 4.21.1
39_123 M11X ./…/com/idrc/impl/CMS_VIEW_CLM_IMPL/CMS_VIEW_CLM_IMPL-changelog.sql 00:46.7 1567 EXECUTED 8:b51b27d8ac8a05d0d7654efd6b6b9f34 sql 4.21.1
39_124 M11X ./…/com/idrc/impl/CMS_VIEW_CLM_IMPL/CMS_VIEW_CLM_IMPL-changelog.sql 00:48.1 1568 EXECUTED 8:f0a88ad3f640344b12782f6c5c8d3b8d sql 4.21.1
39_125 M11X ./…/com/idrc/impl/CMS_VIEW_CLM_IMPL/CMS_VIEW_CLM_IMPL-changelog.sql 00:49.2 1569 EXECUTED 8:6fc037ab17138b1388e7a17e95457e82 sql 4.21.1
39_126 M11X ./…/com/idrc/impl/CMS_VIEW_CLM_IMPL/CMS_VIEW_CLM_IMPL-changelog.sql 00:52.7 1570 EXECUTED 8:6535b8e1fa2181df678782a03d45e892 sql 4.21.1
39_127 M11X ./…/com/idrc/impl/CMS_VIEW_CLM_IMPL/CMS_VIEW_CLM_IMPL-changelog.sql 00:53.8 1571 EXECUTED 8:c7b2868ebabc03169990c5c71e0e86d2 sql IDRS_2513 4.21.1

I can confirm that the --rollback statements are present in the 2 changelog files as well, but they do not get produced using the rollback-sql command.

2

Hi @bwmann89,

In order to help troubleshoot this, I think we need a few more details.

What are you actually seeing? Do you get any error messages? Do you get nothing back? Do you get some but not all of the rollback statements?

-PJ

3

Hi @PJatLiquibase thanks for replying. I’m not seeing any errors, but just lock and unlock statements.

In my pipeline, I’m running the following:
liquibase --username=$LIQUIBASE_TST_USER --password=$LIQUIBASE_TST_PASS --url=“jdbc:snowflake://cms-idritlab.privatelink.snowflakecomputing.com/?&warehouse=IDRC_NP_DEVOPS_WH_BATCH&db=IDRC_TST&schema=LB_CHANGELOG_TST” --changeLogFile=db.change-master.xml rollback-sql --tag=“initial-deployment” > …/…/scripts/rollback-sql.sql

Following this I read in this file, …/…/scripts/rollback-sql.sql, and it only shows the following, instead of all of the rollback statements I specified for each changeset.

alter session set jdbc_query_result_format = ‘JSON’;

– Lock Database
UPDATE LB_CHANGELOG_IMPL.DATABASECHANGELOGLOCK SET LOCKED = TRUE, LOCKEDBY = ‘idrc-poc-liquibase-deployment-itlab-527-m9v86-m0f13-47hsj (100.85.159.190)’, LOCKGRANTED = current_timestamp::timestamp_ntz WHERE ID = 1 AND LOCKED = FALSE;

alter session set jdbc_query_result_format = ‘JSON’;

alter session set jdbc_query_result_format = ‘JSON’;

– *********************************************************************
– Rollback to ‘initial-deployment’ Script
– *********************************************************************
– Change Log: db.change-master.xml
– Ran at: 4/20/23, 6:38 PM
– Against: IDRC_IMPL_LB_SVC@jdbc:snowflake://cms-idritlab.privatelink.snowflakecomputing.com:443/
– Liquibase version: 4.21.1
– *********************************************************************

– Release Database Lock
alter session set jdbc_query_result_format = ‘JSON’;

UPDATE LB_CHANGELOG_IMPL.DATABASECHANGELOGLOCK SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

alter session set jdbc_query_result_format = ‘JSON’;

Thanks,
Braz

4

Some additional strange behavior I’m seeing: Regular rollback does not work either, and whenever I run a ‘liquibase update’, it’s trying to execute the changesets again… it’s like Liquibase doesn’t recognize that these changes are in place already, and in the designated table for DATABASECHANGELOG. Is there a reason why this could be?