SQL Scripts with multiple GO statement no longer working

[xvc]

Hi,

We were successfully able to reference TSQL scripts with multiple go statements in v4.4.0

We just upgraded Liquibase to 4.16.1 and nothing is working anymore. We cannot deploy the vast majority of our SQL scripts due to the following error:

Unexpected error running Liquibase: Migration failed for changeset Dependencies.xml::1::
     Reason: liquibase.exception.DatabaseException: Incorrect syntax near 'GO'.

Our scripts are structured as follows:

• SQL Scripts with multiple TSQL statements separated by GO statements (creating stored procedures, tables,etc for SQL Server RDBMS)
• Scripts are referenced as follows:

        <changeSet author="xav" id="1">
            <sqlFile path="Dependencies/1. IMWeb_PartitionDependencies.sql" encoding="UTF-8" endDelimiter="GO" splitStatements="true"/>
        </changeSet>

As mentioned, everything works smoothly in v4.4.0. This only happens in 4.16.1.

Could someone please provide assistance?

Thanks!
-Xavier

[raul.balestra]

Hello Xavier,

I’ve just tested the endDelimiter=“GO” using the latest product release 4.16.1 and I didn’t face any issues.

  <changeSet author="your.name" id="sql-example">
    <sql dbms="mysql" splitStatements="true" endDelimiter="GO" stripComments="true">
      DROP FUNCTION IF EXISTS calcProfit;
      /
      CREATE FUNCTION calcProfit(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2)
      DETERMINISTIC
      BEGIN
        DECLARE profit DECIMAL(9,2);
        SET profit = price-cost;
        RETURN profit;
      END
      /
    </sql>
  </changeSet>
</databaseChangeLog>

I also checked the release notes and there was a bug fix related to endDelimiter( * Improved handling of endDelimiter=“/” by [@nvoxland] in [#3118]) that might be connected to your issue, I highly suggest you upgrade to the latest product version and test your script once again.

[raul.balestra]

Here is my output:

[tabbyfoo]

SQL has some specific quirks. I edited your post to say 4.16.0 instead of 5.16.0 because there is no version 5 at this time We have released an update to 4.16 though and as @raul.balestra said there was a bug fix in the most recent release that will probably resolve this for you.

I also found an accepted answer on StackOverflow that addresses this issue:

This issue was addressed in two ways.

1. appending ; at the end of the trigger logic in the SQL file
2. adding splitStatements="false" endDelimiter=";" to the sqlFile parameters

[xvc]

Thank you for your answers and I apologize for mis-stating the version.

Some of our scripts with multiple go statements are working while others aren’t. I suspect there is a bug somewhere. I don’t believe this particular error should ever happen since SQL Server should never see those “GO” statements which are for the client only.

I am trying to find a specific example to send to you guys. The one that fails create is a large 10 MB script and I cannot share it unfortunately.

Thanks again for the assistance
-Xavier

[xvc]

I found what the root cause is: It is due to the following change: Do not split SQL on delimiters within BEGIN/END blocks by nvoxland · Pull Request #1589 · liquibase/liquibase · GitHub

Several of our stored procedures have explicit database transactions. Transactions in TSQL are written using BEGIN TRAN and either Commit or Rollback. There is no “END”. e.g

BEGIN TRANSACTION;  
DELETE FROM HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
COMMIT;  

I believe that your code does not work with database transactions in SQL Server.

We downgraded to 4.14.0 and everything works.

Thanks
-Xavier

[xvc]

Just to confirm please…

is this an issue that will be prioritized and addressed in future releases?

Thanks
-Xavier

[tabbyfoo]

Hi @xvc - the nvoxland who opened that PR is one of our founders and works on our community team to address these issues, so I am sure it will be reviewed by the team and prioritized/assigned as soon as we can. Thank you for asking, and thanks for posting!

Edited to add: This was Merged and released in version 4.15.0.

[nvoxland]

@xvc That make sense, thanks for picking up the BEGIN TRANSACTION thing. I’ll open an issue for that so it can get looked at.

[nvoxland]

I created End delimiter logic broken by BEGIN TRANSACTION · Issue #3356 · liquibase/liquibase · GitHub to track the issue

Nathan

[xvc]

Hi,

When will this issue be addressed? We purchased the professional version of Liquibase and are happy so far. However this is a major issue when SQL Server is used. Essentially we are stuck to version 4.14 and cannot upgrade.

Thanks
-Xavier