The wrong order of generated SQL instructions in a Liquibase SQL diff make the diff unexecutable
Description
Environment
using the diff Maven goal with diffChangeLogFile ending with ".sql"
Activity
Show:
Giuliano Santandrea June 19, 2018 at 2:29 PMEdited
As a workaround we have created a script that reorders the generated Liquibase SQL instructions in the following way:
all the DROP CONSTRAINTS instructions
all the other DROP instructions
all the other SQL instructions
So in this way we also solve the problem of deleting a TABLE with foreign keys.
If you try to create a diff between two DBs with the following situation:
DB1: table T1, table T2
DB2: view T1, table T2
That is, if in the final DB (DB2 in the example) a table is deleted and a view is created with the same name.
The order of the generated SQL instructions in the diff is:
CREATE VIEW T1 ...
DROP TABLE T1 ...
that causes the final SQL file to fail with "Error Code: 955, SQL State: 42000] ORA-00955: name is already used by an existing object" (we try to CREATE VIEW before DROP TABLE )
(I tried to write in the Liquibase forum, but after the Zoho signup the home page of the forum keeps refreshing...)