Liquibase - fix wrong rollback statement

Question

My master changelog is like below,

    databaseChangeLog:
  - include:
      relativeToChangelogFile: true
      file: tables/001_table1.sql
  - include:
      relativeToChangelogFile: true
      file: tables/002_table2.sql      
  - include:
      relativeToChangelogFile: true
      file: tables/003_table3.sql        
  ...
  ...  
  - include:
      relativeToChangelogFile: true
      file: tables/020_table20.sql,

and table12's script is as below,

--liquibase formatted sql

--changeset user:table12.create stripComments:false splitStatements:true
create table table12
(
    id             integer not null,
    mod_timestamp  timestamp without time zone not null default current_timestamp
);
--rollback drop table table12 cascade;

--changeset user:table12.table12_ix1 stripComments:false splitStatements:true
create index table12_ix1 on table12(mod_timestamp);
--rollback drop index table12A_ix1 ;

After all changesets get deployed and executed(table1 to table20), I picked up the wrong rollback statement, which should be

--rollback drop index table12_ix1 ;

My question is, what is the best way to fix this? I could check in another changeset that drops the index and recreate it along with the correct rollback statement as below,

--changeset user:table12.table12_ix1_recreate stripComments:false splitStatements:true
drop index table12_ix1;
create index table12_ix1 on table12(mod_timestamp);
--rollback drop index table12_ix1 ;

But, if I kick off the "liquibase rollback-to-date ..." for example, it still hits an error when it reaches to this line "--rollback drop index table12A_ix1".

Answer 1

It depends.

If you have already deployed this changeset to some environment(s), I guess the only thing left is to edit the existing changeset and fix the incorrect roll ack statement. This is the last resort solution which comes with consequences - you will have to take the checksums generated for this changeset from DATABASECHANGELOG table from all those environments and add them all to validCheckSum changeset tag. You can also set validCheckSum to ANY but it's not recommended as it might bring issues with false positives in future. See Changeset Checksums for details.

If the only environment you deployed this is your local environment that could be dropped and recreated, then simply do it.

To avoid this issue in future, I highly recommend using update-testing-rollback command instead of update.