Liquibase rollback in formatted sql not working from command line

General Discussion
1

–liquibase formatted sql

–changeset your.name:1 labels:example-label context:example-context
–comment: example comment
create table person (
id int primary key auto_increment not null,
name varchar(50) not null,
address1 varchar(50),
address2 varchar(50),
city varchar(30)
)
–rollback DROP TABLE person;

–changeset your.name:2 labels:example-label context:example-context
–comment: example comment
create table company (
id int primary key auto_increment not null,
name varchar(50) not null,
address1 varchar(50),
address2 varchar(50),
city varchar(30)
)
–rollback DROP TABLE company;

–changeset other.dev:3 labels:example-label context:example-context
–comment: example comment
alter table person add column country varchar(2)
–rollback ALTER TABLE person DROP COLUMN country;

– changeset liquibaseuser:4
create table testTable ( id int primary key, name varchar(255) );
– rollback drop table testTable;

– changeset liquibaseuser:5
insert into testTable values (1,‘The First’);
insert into testTable values (2,‘The Second’);
– rollback delete from testTable where id=‘1’
– rollback delete from testTable where id=‘2’

– changeset liquibaseuser:6
insert into testTable values (3,‘The thrid’);
– rollback delete from testTable where id=‘3’

– changeset liquibaseuser:7
insert into testTable values (4,‘The fourth’);
– rollback delete from testTable where id=‘4’

– changeset liquibaseuser:8
insert into testTable values (5,‘The fifth’);
– rollback delete from testTable where id=‘5’

– changeset liquibaseuser:9
insert into testTable values (6,‘The six’);
– rollback delete from testTable where id=‘6’

I am using below command to rollback-
liquibase rollback-sql --tag=testTag3 --changelog-file=example-changelog.sql

Log shows like

  • Lock Database
    UPDATE flywaymigration.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = ‘DIC-LAP-149 (192.168.1.28)’, LOCKGRANTED = NOW() WHERE ID = 1 AND LOCKED = 0;

– Rolling Back ChangeSet: example-changelog.sql::9::liquibaseuser
delete from testTable where id=‘6’;

DELETE FROM flywaymigration.DATABASECHANGELOG WHERE ID = ‘9’ AND AUTHOR = ‘liquibaseuser’ AND FILENAME = ‘example-changelog.sql’;

– Rolling Back ChangeSet: example-changelog.sql::8::liquibaseuser
delete from testTable where id=‘5’;

DELETE FROM flywaymigration.DATABASECHANGELOG WHERE ID = ‘8’ AND AUTHOR = ‘liquibaseuser’ AND FILENAME = ‘example-changelog.sql’;

– Rolling Back ChangeSet: example-changelog.sql::7::liquibaseuser
delete from testTable where id=‘4’;

DELETE FROM flywaymigration.DATABASECHANGELOG WHERE ID = ‘7’ AND AUTHOR = ‘liquibaseuser’ AND FILENAME = ‘example-changelog.sql’;

– Release Database Lock
UPDATE flywaymigration.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

Liquibase command ‘rollback-sql’ was executed successfully.

But there is no rollback in database. COuld you please help me here.

2

The command “rollback-sql” will just provide a script of the the sql that would run if you ran a “rollback” command, and not change anything in your database.

The commands that end with “-sql” just produce a script, and do not actual change anything in your database.

3

thanks for clarification. Does this command creates rollback sql in memory or do we need to write those?

4

The rollback sql statements are provided in the Liquibase output, which you provided above.