Liquibase rollback not working with "Liquibase Rollback Successful"

[AlekseyFilippov]

Hi guys! Please, can you help me with rollback action? It is some kind of magic - liquibase report me about successful rollback action, but nothing change.
We use sql formatted SQL. For example i have 3 files:

CASE-0-1.sql:

–liquibase formatted sql

–changeset CASE-0-1:1 failOnError:true
CREATE TABLE tt1(
cc1 INT(11));

–rollback DROP TABLE tt1;
–rollback CREATE TABLE tr1(
–rollback cr1 INT(20));

CASE-0-2.sql:

–liquibase formatted sql

–changeset CASE-0-2:1 failOnError:true
CREATE TABLE tt2(
cc2 INT(11));

–rollback DROP TABLE tt2;
–rollback CREATE TABLE tr2(
–rollback cr2 INT(20));

CASE-0-3.sql:

–liquibase formatted sql

–changeset CASE-0-3:1 failOnError:true
CREATE TABLE tt3(
cc3 INT(11));

–rollback DROP TABLE tt3;
–rollback CREATE TABLE tr3(
–rollback cr3 INT(20));

I TAG database before making any changes:

/root/liquibase/liquibase --logLevel=debug --classpath=/usr/share/java/mysql-connector-java.jar --driver=com.mysql.jdbc.Driver
  --url=“jdbc:mysql://localhost/liquibase_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true”
  --logFile=0state.tag.log --username=trunk --password=Trunk
  tag “0state”

After that i apply files and TAG database after each file apply:

/root/liquibase/liquibase --logLevel=debug --classpath=/usr/share/java/mysql-connector-java.jar --driver=com.mysql.jdbc.Driver
  --url=“jdbc:mysql://localhost/liquibase_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true”
  --changeLogFile=CASE-0-1.sql --logFile=CASE-0-1.sql.update.log --username=trunk --password=Trunk
  update
/root/liquibase/liquibase --logLevel=debug --classpath=/usr/share/java/mysql-connector-java.jar --driver=com.mysql.jdbc.Driver
  --url=“jdbc:mysql://localhost/liquibase_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true”
  --logFile=CASE-0-1.sql.tag.log --username=trunk --password=Trunk
  tag “CASE-0-1”

/root/liquibase/liquibase --logLevel=debug --classpath=/usr/share/java/mysql-connector-java.jar --driver=com.mysql.jdbc.Driver
  --url=“jdbc:mysql://localhost/liquibase_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true”
  --changeLogFile=CASE-0-2.sql --logFile=CASE-0-2.sql.update.log --username=trunk --password=Trunk
  update
/root/liquibase/liquibase --logLevel=debug --classpath=/usr/share/java/mysql-connector-java.jar --driver=com.mysql.jdbc.Driver
  --url=“jdbc:mysql://localhost/liquibase_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true”
  --logFile=CASE-0-2.sql.tag.log --username=trunk --password=Trunk
  tag “CASE-0-2”

/root/liquibase/liquibase --logLevel=debug --classpath=/usr/share/java/mysql-connector-java.jar --driver=com.mysql.jdbc.Driver
  --url=“jdbc:mysql://localhost/liquibase_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true”
  --changeLogFile=CASE-0-3.sql --logFile=CASE-0-3.sql.update.log --username=trunk --password=Trunk
  update
/root/liquibase/liquibase --logLevel=debug --classpath=/usr/share/java/mysql-connector-java.jar --driver=com.mysql.jdbc.Driver
  --url=“jdbc:mysql://localhost/liquibase_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true”
  --logFile=CASE-0-3.sql.tag.log --username=trunk --password=Trunk
  tag “CASE-0-3”

Result:

[root@mysql]# mysql -e "SELECT ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, EXECTYPE, DESCRIPTION, COMMENTS, TAG \

           FROM DATABASECHANGELOG;" liquibase_test
±--------------±----------±-------------------±--------------------±--------------±---------±------------±---------±---------+
| ID            | AUTHOR    | FILENAME           | DATEEXECUTED        | ORDEREXECUTED | EXECTYPE | DESCRIPTION | COMMENTS | TAG      |
±--------------±----------±-------------------±--------------------±--------------±---------±------------±---------±---------+
| 1506498093527 | liquibase | liquibase-internal | 2017-09-27 10:41:33 |             1 | EXECUTED | empty       |          | 0state   |
| 1             | CASE-0-1  | CASE-0-1.sql       | 2017-09-27 10:41:57 |             2 | EXECUTED | sql         |          | CASE-0-1 |
| 1             | CASE-0-2  | CASE-0-2.sql       | 2017-09-27 10:42:08 |             3 | EXECUTED | sql         |          | CASE-0-2 |
| 1             | CASE-0-3  | CASE-0-3.sql       | 2017-09-27 10:42:12 |             4 | EXECUTED | sql         |          | CASE-0-3 |
±--------------±----------±-------------------±--------------------±--------------±---------±------------±---------±---------+
[root@mysql]# mysql -e “SHOW TABLES;” liquibase_test
±-------------------------+
| Tables_in_liquibase_test |
±-------------------------+
| DATABASECHANGELOG        |
| DATABASECHANGELOGLOCK    |
| tt1                      |
| tt2                      |
| tt3                      |
±-------------------------+

Now, lets try rollback:

/root/liquibase/liquibase --logLevel=debug --classpath=/usr/share/java/mysql-connector-java.jar --driver=com.mysql.jdbc.Driver
  --url=“jdbc:mysql://localhost/liquibase_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true”
  --changeLogFile=CASE-0-2.sql --logFile=CASE-0-2.sql.rollback.log --username=trunk --password=Trunk
  rollback “CASE-0-2”
Liquibase Rollback Successful

Looks like all fine, but rollback was not executed:

[root@mysql]# mysql -e "SELECT ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, EXECTYPE, DESCRIPTION, COMMENTS, TAG \

           FROM DATABASECHANGELOG;" liquibase_test
±--------------±----------±-------------------±--------------------±--------------±---------±------------±---------±---------+
| ID            | AUTHOR    | FILENAME           | DATEEXECUTED        | ORDEREXECUTED | EXECTYPE | DESCRIPTION | COMMENTS | TAG      |
±--------------±----------±-------------------±--------------------±--------------±---------±------------±---------±---------+
| 1506498093527 | liquibase | liquibase-internal | 2017-09-27 10:41:33 |             1 | EXECUTED | empty       |          | 0state   |
| 1             | CASE-0-1  | CASE-0-1.sql       | 2017-09-27 10:41:57 |             2 | EXECUTED | sql         |          | CASE-0-1 |
| 1             | CASE-0-2  | CASE-0-2.sql       | 2017-09-27 10:42:08 |             3 | EXECUTED | sql         |          | CASE-0-2 |
| 1             | CASE-0-3  | CASE-0-3.sql       | 2017-09-27 10:42:12 |             4 | EXECUTED | sql         |          | CASE-0-3 |
±--------------±----------±-------------------±--------------------±--------------±---------±------------±---------±---------+
[root@mysql]# mysql -e “SHOW TABLES;” liquibase_test
±-------------------------+
| Tables_in_liquibase_test |
±-------------------------+
| DATABASECHANGELOG        |
| DATABASECHANGELOGLOCK    |
| tt1                      |
| tt2                      |
| tt3                      |
±-------------------------+

As you can see - same set of tables we have, no table drops, no additional tables was created.

What we have in rollback logfile:

[root@mysql]# cat CASE-0-2.sql.rollback.log:
DEBUG 27.09.17 10:53: liquibase: Connected to trunk2@127.0.0.1@jdbc:mysql://localhost/liquibase_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true
DEBUG 27.09.17 10:53: liquibase: Setting auto commit to false from true
DEBUG 27.09.17 10:53: liquibase: Executing QUERY database command: select count() from liquibase_test.DATABASECHANGELOGLOCK
DEBUG 27.09.17 10:53: liquibase: Executing QUERY database command: select count() from liquibase_test.DATABASECHANGELOGLOCK
DEBUG 27.09.17 10:53: liquibase: Executing QUERY database command: SELECT LOCKED FROM liquibase_test.DATABASECHANGELOGLOCK WHERE ID=1
DEBUG 27.09.17 10:53: liquibase: Lock Database
DEBUG 27.09.17 10:53: liquibase: Executing UPDATE database command: UPDATE liquibase_test.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = ‘mysql-gigantic-dummy-dvaco-trunk-28.aws.srv (172.29.13.83)’, LOCKGRANTED = ‘2017-09-27 10:53:26.625’ WHERE ID = 1 AND LOCKED = 0
INFO 27.09.17 10:53: liquibase: Successfully acquired change log lock
DEBUG 27.09.17 10:53: liquibase: Computed checksum for 1506498806750 as 17f1742877fc068db5453ca9db9d60e3
DEBUG 27.09.17 10:53: liquibase: Executing QUERY database command: SELECT MD5SUM FROM liquibase_test.DATABASECHANGELOG WHERE MD5SUM IS NOT NULL LIMIT 1
DEBUG 27.09.17 10:53: liquibase: Executing QUERY database command: select count(*) from liquibase_test.DATABASECHANGELOG
INFO 27.09.17 10:53: liquibase: Reading from liquibase_test.DATABASECHANGELOG
DEBUG 27.09.17 10:53: liquibase: Executing QUERY database command: SELECT * FROM liquibase_test.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
DEBUG 27.09.17 10:53: liquibase: CASE-0-2.sql: CASE-0-2.sql::1::CASE-0-2: Computed checksum for inputStream as 80fa2b893f3b36ef72cf796a9ce61189
DEBUG 27.09.17 10:53: liquibase: CASE-0-2.sql: CASE-0-2.sql::1::CASE-0-2: Computed checksum for 7:80fa2b893f3b36ef72cf796a9ce61189: as 6c11f0a414ae9865f42a220f71f1e3d1
DEBUG 27.09.17 10:53: liquibase: Release Database Lock
DEBUG 27.09.17 10:53: liquibase: Executing UPDATE database command: UPDATE liquibase_test.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1
INFO 27.09.17 10:53: liquibase: Successfully released change log lock

I checked different variants, different liquibase file formatting, different command line options - useless. Liquibase always say “Liquibase Rollback Successful”, but do nothing.

Please, help. May be someone use rollback actions with sql, or other formats? Where the mistake? What i am doing wrong? Is it working liquibase feature at all?

[FreskimAliu]

I’m having the same issue, did you figure it out what was wrong?

[daryldoak]

Liquibase rollback to tag definitely works, I used it all the time, with all formats, xml, json, yaml ,and sql.

@FreskimAliu Please provide these details:

1. Liquibase version
2. sample changelog
3. commands executed
4. databasechangelog row results

[FreskimAliu]

@daryldoak

1. Liquibase version is 4.17.2

2. Here is a sample changelog:

-- changeset freskim:testing-data
CREATE TABLE [dbo].[testingTable](
	[AlertProfileId] [int] NOT NULL,
	[ExternalUserId] [nvarchar](450) NOT NULL
) ON [PRIMARY]
GO
-- rollback drop table testingTable

3. I run update and tag commands inside a Powershell script
   UPDATING:

    $ChangeLogFile = "mssql/$Database/db.change-log.xml"
    $URL= "jdbc:sqlserver://${DatabaseHost}:${DatabasePort};databaseName=$Database;trustServerCertificate=true"

    #Run Schema Update
    Write-Host "Updating Schema for $Database"
    docker run --rm -v $PWD/liquibase/changelog:/liquibase/changelog liquibase/liquibase --url=$URL `
        --changeLogFile=$ChangeLogFile --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver `
        --username=$Username --password=$Password update

TAGGING:

    docker run --rm -v $PWD/liquibase/changelog:/liquibase/changelog $ImagePath --url=$URL `
        --changeLogFile=$ChangeLogFile --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver `
        --username=$Username --password=$Password tag tag2

ROLLBACK:

    docker run --rm -v $PWD/liquibase/changelog:/liquibase/changelog $ImagePath --url=$URL `
        --changeLogFile=$ChangeLogFile --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver `
        --username=$Username --password=$Password rollback tag2

4. Here are DATABASECHANGELOG row results;
   
   

[daryldoak]

I see that tag2 was applied to changeset id testing2-data, but I don’t see that changeset in your examples., but even with that I’m thinking this might be related to your rollback not having a delimiter. Normally the sql statement in the rollback section needs to use the same delimiter you have defined for your changeset. In this case either GO or a semicolon. Please try to add the delimiter to the rollback statement and try again.

[FreskimAliu]

@daryldoak Still not working I added the delimiter to the rollback statement but still no luck. Rollback by count is working fine.

--changeset freskim:testing-data endDelimiter:GO
CREATE TABLE [dbo].[testingTable](
	[AlertProfileId] [int] NOT NULL,
	[ExternalUserId] [nvarchar](450) NOT NULL
) ON [PRIMARY]
GO
--rollback DROP TABLE testingTable GO

It is showing only that command was executed successfully but nothing happens inside the database.

[daryldoak]

Strange. I’ll have to let someone from Liquibase chime in on this one. I’ve never used GO, I use semicolons, and it has always worked fine.

--liquibase formatted sql

--changeset X:grant_test_employee_privs
GRANT SELECT ON test_employee TO USER1;

--rollback REVOKE SELECT ON test_employee FROM USER1;

[FreskimAliu]

Tried with semicolons too, but still doesn’t work. Suspecting that the problem could be in my master changelog file.

--liquibase formatted sql

--changeset freskim:testing2-data
CREATE TABLE [dbo].[testing2Table](
	[AlertProfileId] [int] NOT NULL,
	[ExternalUserId] [nvarchar](450) NOT NULL
) ON [PRIMARY];
--rollback DROP TABLE testing2Table;

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
	xmlns:pro="http://www.liquibase.org/xml/ns/pro"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
		http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
		http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
		http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">
  	<includeAll path="schema-changelog" relativeToChangelogFile="true"></includeAll>
</databaseChangeLog>