updateTestingRollback not rolling back

I am following Activity 8: Testing a Rollback Liquibase University

I followed the steps for updateTestingRollback, but Liquibase is not rolling back as expected. Why would it not rollback? Any idea?

/usr/local/Cellar/liquibase/4.3.5/liquibase --changeLogFile=dbchangeloga8.sql --logLevel=info updateTestingRollback
[2021-07-09 14:59:57] INFO [liquibase.integration] No Liquibase Pro license key supplied. Please set liquibaseProLicenseKey on command line or in liquibase.properties to use Liquibase Pro features.
Liquibase Community 4.3.5 by Datical
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 14:59:57 (version 4.3.5 #62 built at 2021-04-29 18:31+0000)
[2021-07-09 15:00:04] INFO [liquibase.lockservice] Successfully acquired change log lock
[2021-07-09 15:00:12] INFO [liquibase.changelog] Reading from predict.DATABASECHANGELOG
[2021-07-09 15:00:13] INFO [liquibase.changelog] Custom SQL executed
[2021-07-09 15:00:13] INFO [liquibase.changelog] ChangeSet dbchangeloga8.sql::a8b.1::activity.8 ran successfully in 541ms
[2021-07-09 15:00:14] INFO [liquibase.changelog] Custom SQL executed
[2021-07-09 15:00:14] INFO [liquibase.changelog] ChangeSet dbchangeloga8.sql::a8b.2::activity.8 ran successfully in 530ms
[2021-07-09 15:00:16] INFO [liquibase.lockservice] Successfully released change log lock
[2021-07-09 15:00:17] INFO [liquibase.lockservice] Successfully acquired change log lock
[2021-07-09 15:00:25] INFO [liquibase.changelog] Reading from predict.DATABASECHANGELOG
[2021-07-09 15:00:26] INFO [liquibase.lockservice] Successfully released change log lock
[2021-07-09 15:00:28] INFO [liquibase.lockservice] Successfully acquired change log lock
[2021-07-09 15:00:35] INFO [liquibase.changelog] Reading from predict.DATABASECHANGELOG
[2021-07-09 15:00:36] INFO [liquibase.lockservice] Successfully released change log lock
Liquibase command 'updateTestingRollback' was executed successfully.
1 Like

Can you show us the content of dbchangeloga8.sql?

Yup. No problem. I copy and pasted straight from the tutorial.

cat dbchangeloga8.sql
-- liquibase formatted sql

-- changeset activity.8:a8b.1
create table person_a8b (
    id int primary key,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
)
-- rollback drop table person_a8b

-- changeset activity.8:a8b.2
create table company_a8b (
    id int primary key,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
)
-- rollback drop table company_a8b

I am using a different version of Liquibase (3.10.2), but I was able to use your exact files and run updateTestingRollback successfully.

Why do you think it’s not rolling-back?

The tables still exist and there is no mention of rollback in the output. If it were rolling back, it should contain output like this:

Rolling Back Changeset:dbchangeloga8.sql::a8b.2::activity.8
Rolling Back Changeset:dbchangeloga8.sql::a8b.1::activity.8

The tables will exist after updateTestingRollback has completed. It does three steps:

  1. run update for all pending changesets
  2. run rollback for the pending changesets
  3. run update again for the pending changesets

This ensures that the rollback works and the changesets can be applied again.

Can you confirm that the changesets were not already applied before you ran updateTestingRollback? I got the same results as you when they were already applied.

More details found here:
https://docs.liquibase.com/commands/community/updatetestingrollback.html

Hi everyone!
I’m facing the same problem and I still havent found a solution. Any help is appreciated.

I have written wrong rollback commands on purpose, however liquibase keep on saying that the command execution was successfull.
The changesets were not already applied before running updateTestingRollback.

PS C:\00000000-OTP\Liquibase\___PRUEBAS\liquibase-4.6.2> ./liquibase --defaultsFile=liquibase.properties --logLevel=info updateTestingRollback                                                                                               ####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 16:38:35 (version 4.6.2 #886 built at 2021-11-30 16:20+0000)
Liquibase Version: 4.6.2
Liquibase Community 4.6.2 by Liquibase
[2021-12-28 16:38:36] INFORMACIËN [liquibase.lockservice] Successfully acquired change log lock
[2021-12-28 16:38:36] INFORMACIËN [liquibase.changelog] Reading resource: 3.0.0.0/0001_ddl.oracle.sql
[2021-12-28 16:38:37] INFORMACIËN [liquibase.changelog] Reading from LOCAL_EPM.DATABASECHANGELOG
[2021-12-28 16:38:37] INFORMACIËN [liquibase.changelog] Custom SQL executed
[2021-12-28 16:38:37] INFORMACIËN [liquibase.changelog] ChangeSet 3.0.0.0/0001_ddl.oracle.sql::3000-0001-0001::liquibase ran successfully in 24ms
[2021-12-28 16:38:37] INFORMACIËN [liquibase.changelog] Custom SQL executed
[2021-12-28 16:38:37] INFORMACIËN [liquibase.changelog] ChangeSet 3.0.0.0/0001_ddl.oracle.sql::3000-0001-0002::liquibase ran successfully in 12ms
[2021-12-28 16:38:37] INFORMACIËN [liquibase.lockservice] Successfully released change log lock
[2021-12-28 16:38:37] INFORMACIËN [liquibase.lockservice] Successfully acquired change log lock
[2021-12-28 16:38:37] INFORMACIËN [liquibase.changelog] Reading from LOCAL_EPM.DATABASECHANGELOG
[2021-12-28 16:38:38] INFORMACIËN [liquibase.lockservice] Successfully released change log lock
[2021-12-28 16:38:38] INFORMACIËN [liquibase.lockservice] Successfully acquired change log lock
[2021-12-28 16:38:38] INFORMACIËN [liquibase.changelog] Reading from LOCAL_EPM.DATABASECHANGELOG
[2021-12-28 16:38:38] INFORMACIËN [liquibase.lockservice] Successfully released change log lock
Liquibase command 'updateTestingRollback' was executed successfully.

liquibase.properties

driver: oracle.jdbc.OracleDriver
classpath: ojdbc7.jar
url: jdbc:oracle:thin:@localhost:1521/OTPCI
username: LOCAL_EPM
password: xxxxxxx
changeLogFile: liquibase_master_changelog.xml
liquibase.hub.mode=off
parameter.tablespace_index=EPM_INDICES
parameter.tablespace_datos=EPM_DATOS

changesets

-- liquibase formatted sql

-- changeset liquibase:3000-0001-0001
-- comment: Modificar tabla C_RAIZ para añadir PK
ALTER TABLE C_RAIZ ADD PRIMARY KEY (CODIGO) USING INDEX TABLESPACE ${tablespace_index};
-- rollback ALTERRRRR TABLE C_RAIZ DROP PRIMARY KEY;

-- changeset liquibase:3000-0001-0002
-- comment: Creación Sequencia SEQ_TENAPO 
CREATE SEQUENCE SEQ_TENAPO MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
-- rollback DROOOOP SEQUENCE SEQ_TENAPO ;

If I execute the rollback command I get the error I would expected to have with updateTestingRollback command

PS C:\00000000-OTP\Liquibase\___PRUEBAS\liquibase-4.6.2> ./liquibase --defaultsFile=liquibase.properties --logLevel=info rollback baseline                                                                                        ####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 17:09:58 (version 4.6.2 #886 built at 2021-11-30 16:20+0000)
Liquibase Version: 4.6.2
Liquibase Community 4.6.2 by Liquibase
[2021-12-28 17:10:00] INFORMACIËN [liquibase.lockservice] Successfully acquired change log lock
[2021-12-28 17:10:00] INFORMACIËN [liquibase.changelog] Reading resource: 3.0.0.0/0001_ddl.oracle.sql
[2021-12-28 17:10:00] INFORMACIËN [liquibase.changelog] Reading from LOCAL_EPM.DATABASECHANGELOG
Rolling Back Changeset:3.0.0.0/0001_ddl.oracle.sql::3000-0001-0002::liquibase
[2021-12-28 17:10:00] INFORMACIËN [liquibase.lockservice] Successfully released change log lock
[2021-12-28 17:10:00] GRAVE [liquibase.integration] ORA-00900: sentencia SQL no vßlida

liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: Unexpected error running Liquibase: liquibase.exception.DatabaseException: ORA-00900: sentencia SQL no vßlida
 [Failed SQL: (900) DROOOOP SEQUENCE SEQ_TENAPO]

What I’m doing wrong? any idea?
Thanks in advance

1 Like

Did anyone figure out the problem at last? I faced the same problem as you guys where
updateTestingRollback did not roll back. I am 100% sure that I have cleaned the database before running updateTestingRollback.

I realized this problem only happened when using an Oracle database running in a container, otherwise worked as expected.

If you are not using Liquibase PRO, I would not recommend using this command.

There is an open issue that you should read first: https://github.com/liquibase/liquibase/issues/1535

So, until this issue is solved, I found two choices:
1- changelogs with just one changeset ( don´t even think about it)
2- Implement your updateTestingRollback functionality using status, update, rollback commands, and shell scripting.

2 Likes

Thanks for you reply! FYI, I am using Microsoft SQL Server 2012 which is not supported by Liquibase according to Using Liquibase with MSSQL. I suspect that causes updateTestingRollback not working as expected. If I switched back to the bundled H2 database, it worked as expected.

1 Like

I spoke with Adrian on our Documentation team. He said the reason this works for the bundled H2 database is that it’s up to date and that since MSSQL Server 2012 is EOL according to Microsoft, it would be a good idea to update to a supported OS.

1 Like

Thanks for you information @tabbyfoo!