We are using SqlServer with sqlFiles, and were testing the failure behavior. We expected a bad insert (string into an int col) to fail the changeSet and not write the record into the DATABASECHANGELOG table, but we got different results depending on where the failing insert was placed in the file. If the failing insert was first, the changeset failed as expected. If a passing insert was placed before the failing one, the entire changeset was rolled back (the passing insert was not in the table) but the changeset was logged as successful in the DATABASECHANGELOG table.
Has anyone had this happen before? Workarounds? Bug?
Liquibase should try to start a transaction at the beginning of the changeSet and only commit it and the insert into databasechangelog at the end. Could you post an example of the files you are using?
Thanks for the test, that helped. What I found is that it looks like an issue with the JDBC driver. Because you have endDelimiter="\nGO" in your changelog but no GO in your file, Liquibase is not splitting the two statements and just passing the entire string as a single statement to the JDBC driver in statement.execute(). With the asExpected test, the driver throws an SQLException that is caught and handled correctly.
With the unexpected test, however, the JDBC driver does not throw an exception even though the statement is rolled back. I’m not seeing any known issues with sqlserver or the jdbc driver with a quick google search and I’m also not seeing a way with the JDBC interface to know that there really was an error even though no exception was thrown.
I created https://liquibase.jira.com/browse/CORE-2162 to track the issue but for now I think your best work-around is to make sure that the statements are being split correctly. Either adding a GO after each line or adding a semicolon to the end of the insert statements and removing the endDelimiter setting will have each ran separately and then everything works as expected.
You’re right Nathan, our splitting wasn’t working correctly. We originally had lowercase ‘go’ in our scripts and they were failing to run because Liquibase didn’t match the regex - that’s why we left out the 'go’s in our test. In the time between our posting this and your reply, we discovered that we needed to change the regex to be a bit more permissive (any case comibanition). I’ve tried my test again with the correct split statements, and it works as expected.
