Broken SQL rollback - now what?

General Discussion
1

Hi Liquibasers!

We just changed over to LiquiBase for our development, and predictably my first change failed in an interesting manner. I know now to review the SQL before running it, even on a test DB, but I have a couple questions.

Here is my changelog entry. I am essentially moving a column from one table to another, then dropping the old table.

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns=“http://www.liquibase.org/xml/ns/dbchangelog/1.9”
  xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”
  xsi:schemaLocation=“http://www.liquibase.org/xml/ns/dbchangelog/1.9 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd”>
 
   
      <column name=“PhysicalQuantityId” type=“number(19) null”
              remarks=“Which physical quantity this IOType measures”/>
   
   
      UPDATE IOType io
        SET PhysicalQuantityId = (SELECT PhysicalQuantityID FROM OC_IOType oc_io WHERE oc_io.Id = io.IoType);
   
   
   
     
       
       
        <column name=“PhysicalQuantityId” type=“number(19) not null”
     
     
        UPDATE OC_IOType oc_io
          SET PhysicalQuantityId = (SELECT PhysicalQuantityID FROM IOType io WHERE oc_io.Id = io.IoType);
     
     
   
 

The update ran fine, but when I tried to rollback, I got

liquibase.exception.RollbackFailedException: liquibase.exception.RollbackImpossibleException: No inverse to liquibase.change.SQLFileChange created

I don’t see anything in the manual about special rollback handling for SQL changes, not any examples of how to do it. I expected the SQL in the rollback would have done it. How should such a rollback really be done? Even dividing it into several changeSets does not appear to help.

Now I have a database with an un-rollbackable changeset applied. Obviously, it’d be bad to give that to everybody else, so I need to either recreate my database (which would be a lot of work, as it is also a copy of a production database for scaling tests) or manually rollback the change. The change itself would be easy enough to rollback, but how do I convince LiquiBase that I have rolled it back?

Am I right to think that if a change has been (successfully) rolled back, I can remove the change file in order to not have it applied again?

On another note, I was confused for a while trying to find the actual refactorings in the manual until I found out that they only show up at the bottom of the front page of the manual, not in the menu on the right. Is that a limitation of the Wiki or something else?

Thanks in advance,
-Lars Clausen

2

Since you specified a block, it shouldn’t be complaining about the lack of auto-rollback support.  Normally, there are changes that can be rolled back (add table) that you don’t have to specify a command for and it can figure it out, and there are ones you cannot roll back automatically (drop table) because liquibase cannot figure them out.  cannot be rolled back because we have no idea what it is doing.

I wonder if it is a bug with the rollback check with multiple changes in the same changeSet.  Try splitting it up into multiple changesets with a tag per changeset (you won’t need one for the addColumn change). 

Once a change has been rolled back, you can remove it from the changelog, or modify it and re-run it.  The only time you cannot do that is if you have committed your changeset to your code repository and other people have ran it. 

The refactorings are not on the right navigation mainly because I thought it would make the right navigation too large.

Nathan

3

Oy vey, I thought I had email notifications on. Thank you for your reply. I have found, firstly, the real culprit, a previous change that used SqlFile (it’s hard to see which change gives an error sometimes). The situation is pretty much the same, but simpler, and I’ve tried some variations.  Here’s the original:

    <?xml version="1.0" encoding="UTF-8"?> http://www.liquibase.org/xml/ns/dbchangelog/1.9"   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">                          

I’ve tried with two separate changesets, with one rollback file for each SQL file, and finally with just one file:

    <?xml version="1.0" encoding="UTF-8"?> http://www.liquibase.org/xml/ns/dbchangelog/1.9"   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">                      
Still get the same issue. Since we haven't started doing a lot of changes yet, it hasn't been much of a problem so far, but it will be.

Yours,
-Lars Clausen

4

I created an issue for it: http://liquibase.jira.com/browse/CORE-465 and will investigate it more for 2.0.

Nathan

5

So until that is fixed, we cannot do rollbacks at all. Or is there a way to mark that rollbacks of further changes are fine even though this one change cannot be rolled back?

Thanks,
-Lars