MSSQL stored procedure GO issue with includeAll configuration

General Discussion
1

Hello,

We are trying to configure Liquibase to run stored procedure changes in Microsoft SQLServer.
Those scripts contain batch terminators (GO statements) that cause liquibase changesets execution to fail (logs below) when used with includeAll changeset configuration

configuration yaml:

databaseChangeLog:
  - includeAll:
      path: changes

I know there is a configuration available using endDelimiter parameter but it is available for sqlfile type of change and we want to keep the includeAll option.

Something like this works but forces us to update configuration yaml everytime we want to add a sql file for execution:

databaseChangeLog:
  -  changeSet:
        id:  someChangeset
        author:  someUser
        changes:
        -  sqlFile:
            endDelimiter:  \nGO
            path:  changes\01_CreateSomeProcedure.sql
  -  changeSet:
        id:  someChangeset2
        author:  someUser
        changes:
        -  sqlFile:
            endDelimiter:  \nGO
            path:  changes\02_CreateSomeProcedure.sql

Liquibase Logs:

Starting Liquibase at 11:39:44 (version 4.16.1 #4594 built at 2022-09-14 15:27+0000)
Liquibase Version: 4.16.1
Liquibase Community 4.16.1 by Liquibase
Running Changeset: changes/04_CreateSomeProcedure.sql::raw::includeAll
Unexpected error running Liquibase: Migration failed for changeset changes/04_CreateSomeProcedure.sql::raw::includeAll:
     Reason: liquibase.exception.DatabaseException: Incorrect syntax near 'GO'. [Failed SQL: (102) USE [LiquibaseTest1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [dbo].[AddRecordToTable2](
	@Id int,
	@Description varchar(50)
)
AS
	
BEGIN
	Insert into dbo.table2 values (@Id, @Description)
END
GO]
2

Anyone? Do You know how to run any sql (like stored Procedures with GO statements) in a specified path without naming the files itself using Liquibase?

3

I think you would need to add the changeset details, with the endDelimiter parameter set properly, in each changelog.

4

Could You please provide some example?

As I wrote, I need to keep an an approach where particular changesets are defined as path to folder not as a list of particular sql files. I do not really understand why liquibase’s tolerance to t-SQL syntax differs in both cases (includeAll vs SqlFile)

5

I’m not familiar with t-SQL, but Oracle PL/SQL has the same need for endDelimiter.

So in the Changes folder you would several files, in each file you would define the endDelimiter:

File= first.sql

–liquibase formatted sql
–changeset X:1 endDelimiter:/
BEGIN
NULL;
END;
/

File = second.sql

–liquibase formatted sql
–changeset X:2 endDelimiter:/
BEGIN
NULL;
END;
/

6

You can also add the endDelimiter to your liquibase.properties file.

The endDelimiter can also be set in the Liquibase properties file by setting delimiter=<string> .

1 Like
7

Hi,

Thank You all for the answers given so far.

I prefer setting delimiter globally and already tried setting it in liquibase.properties file but the parameter seems to be ignored.

liquibase.properties
delimiter=GO

update output:

Unexpected error running Liquibase: Migration failed for changeset changes/08_CreateSomeTable.sql::raw::includeAll:
     Reason: liquibase.exception.DatabaseException: Incorrect syntax near 'GO'. [Failed SQL: (102) USE [LiquibaseTest1]
GO
CREATE TABLE [dbo].[table3](
	[Id] [int] NOT NULL,
	[Description] [varchar](50) NULL
 CONSTRAINT [PK_table3] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]]
Logs saved to C:\data\LiquibaseProjects\LocalTester\liquibase.log

liquibase.log:

[2022-10-10 11:29:04] WARNING [liquibase.configuration] Potentially ignored key(s) in file C:\data\LiquibaseProjects\LocalTester\liquibase.properties
 - 'delimiter'

I found this post from 2020 stating that liquibase online documentation is wrong about the delimiter param and endDelimiter should be used instead:

However, the result is the same:

liquibase.properties
endDelimiter=GO

liquibase.log:

[2022-10-10 11:37:27] WARNING [liquibase.configuration] Potentially ignored key(s) in file C:\data\LiquibaseProjects\LocalTester\liquibase.properties
 - 'endDelimiter'

Can anyone check this please? Is there anyone to set endDelimiter in liquibase.properties file?

8

There isn’t a global end delimiter setting, since the times you should have to specify the end delimiter should be very small and what it actually takes to specify a delimiter can be complex and error prone.

You should not need to specify a delimiter for common things like ; and GO and /, especially with newer verisons like 4.16.1 that you are using.

If you remove the endDelimiter setting completely, does it work?

Nathan

9

The thing is it does not work without delimiter. As You noticed I am using liquibase version that is up to date. I was pretty surprised to see that pretty common t-SQL syntax does not work out of the box for this configuration:

databaseChangeLog:
  - includeAll:
      path: changes

I really like Liquibase for its execution log and control over what has been done and what not. It’s really grat in this area, exactly what I’ve been looking for. It is just flexibility that it is lacking or am I missing something? :slight_smile:

10

Glad to hear Liquibase is working well otherwise for you :slight_smile:

I see where the trouble is: we have the contents of an include/includeAll raw sql file not being attempted to be split on delimiters. We did that back in the day when we didn’t have as smart of an SQL parser, since it would just split on ; even if it was in a stored procedure or something.

In effect, since we couldn’t trust our ability to split the statements, we rely on you to split them yourself if using includeAll.

The parser has improved, but it’s not always failsafe. But perhaps to the point where the number people with invalidly split SQL is lower that the number of people expecting SQL to be split?

I created Split statements going through SqlChangeLogParser by nvoxland · Pull Request #3406 · liquibase/liquibase · GitHub to track that feature change.

In the meantime, you can control the splitting of the sql by converting your included sql to formatted sql by adding --liquibase formatted sql and a --changeset raw:includeAll splitStatements:true line at the beginning of the files.

Nathan
Nathan