UpdateSQL is creating objects but then dropping them at the end

Hello to all,


I’m using liquibase (v2.0.1 and Informix DB) to generate differences from a referenced DB and my development one using the “diffChangeLog” command and redirecting the output to a XML file. I then used the “updateSQL” command based on this XML file to generate a SQL script that will bring my development DB to the same state as the reference one.


Examining the generated SQL script, I see that exists statements to correct some objects (like “CREATE INDEX xpto”, p.ex.) but then at the end of the script it drops the same objects (“DROP INDEX xpto”). I believe the drop statement should come before the create. This is getting me confused. Can someone please help me understand what is happening?


Cheers!


I’ve just examined the output of the “diff” command and I realized that this objects that are created and after dropped are stated under “Missing” and then “Unexpected”. Could this be the reason for the “CREATE” (as for “Missing”) and then “DROP” (as for “Unexpected”). Although, wouldn’t be more correct if Liquibase could “understand” that this is the same object and just drop it and then create it as it exists on the reference Database?


Cheers!

Yes, that idea is for liquibase to understand if they are the same object or not (using the object .equal() methods). The problem is that things get vague with indexes sometimes (the name is different, but they are the same column, but is it a regular index or a FK or PK index?). The plan is to look at all that logic hard in the 2.1 liquibase release. For now, you will need to do manual cleanup of the generated changelog file.

Nathan

Hello Nathan,


Thank you so much for your reply and help.


In my case, the situation is an index that on the reference database as a specific column order (let’s say INDEX my_index (col A, col B) ) and on the development database an index with the exact name also exists but with a different column order (INDEX my_index (col B, col A) ). So in this case, first the index is stated as “missing” on the development DB (therefore the CREATE) and then after as “unexpected” (therefore the DROP). From what you say, liquibase compares the objects and understands correctly that they are different. But the the generated SQL statements file as two errors: 

  1. the CREATE will produce an “Index already exists” error;

  2. even if we manually drop the index before running the SQL statements, it will be created and then dropped.


All this would be solved if the “Unexpected” come before the “Missing”. The DROP would come before the CREATE, no error would occur when running the SQL and the incorrect index would be dropped and then the correct one created as defined on the reference Database.


What is your opinion on this?


Cheers!