Liquibase Version: 2.0.3 (Liquibase with Raw SQL and SQL Server Not Handling endDelimiter Value)

theoriginalbob · February 14, 2013, 6:51am

There appears to be a bug with Liquibase and I want to confirm with you folks.

In my setup I want to use raw SQL. I have a really basic sql file People.sql that I want to execute using Liquibase. So to do this I went the formatted SQL route. Below is my file and the error I get. It appears Liquibase isn’t honoring the value of endDelimiter:"" as it’s still adding extra go statements, in addition to chocking on executing the SQL. Just to note I want to use ; and GO in a single change set as part of the raw sql script. Also note when this People.sql file is executed via SQL Server Management Studio or sqlcmd(command line for sql server) it runs without error.

So far the only way I can get around this issue is to have liquibase spit out the sql output to a file and execute that which runs fine because it just the raw sql, though it still has double GO statements added to the very end, see the end of post for example.

–START People.sql

–liquibase formatted sql

–changeset henrythethird:C1 stripComments:false splitStatements:false endDelimiter:"" runOnChange:false

–comment:Conversion to LiquiBase.

CREATE TABLE dbo.People

(

People_IDX int NOT NULL IDENTITY(1, 1),

FirstName varchar(30) NOT NULL

);

GO

ALTER TABLE dbo.People ADD CONSTRAINT PK_People_IDX PRIMARY KEY CLUSTERED (People_IDX);

GO

–END People.sql

When I run liquibase via from command prompt on windows like this:

liquibase --changeLogFile 0_LiquiBaseBuildConfig.xml update

I get this message:

C:\Users\me\LiquibaseBI\SQLServer\Instances\DataWareHouse\Databases\Dev\DW>liquibase --changeLogFile 0_LiquiBaseBuildConfig.xml update

INFO 2/13/13 4:38 PM:liquibase: Successfully acquired change log lock

INFO 2/13/13 4:38 PM:liquibase: Reading from [dbo].[DATABASECHANGELOG]

SEVERE 2/13/13 4:38 PM:liquibase: Change Set …/VueDW/Tables/Address.sql::C1::henrythethird failed. Error: Error executing SQL --comment:Conversion to LiquiBase.

CREATE TABLE dbo.People

(

People_IDX int NOT NULL IDENTITY(1, 1),

FirstName varchar(30) NOT NULL

);

GO

ALTER TABLE dbo.People ADD CONSTRAINT PK_People_IDX PRIMARY KEY CLUSTERED (People_IDX);

GO: Incorrect syntax near ‘GO’.

liquibase.exception.DatabaseException: Error executing SQL --comment:Conversion to LiquiBase.

CREATE TABLE dbo.People

(

People_IDX int NOT NULL IDENTITY(1, 1),

FirstName varchar(30) NOT NULL

);

GO

ALTER TABLE dbo.People ADD CONSTRAINT PK_People_IDX PRIMARY KEY CLUSTERED (People_IDX);

GO: Incorrect syntax near ‘GO’.

at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)

at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)

at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1075)

at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:1059)

at liquibase.changelog.ChangeSet.execute(ChangeSet.java:317)

at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)

at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)

at liquibase.Liquibase.update(Liquibase.java:113)

at liquibase.integration.commandline.Main.doMigration(Main.java:779)

at liquibase.integration.commandline.Main.main(Main.java:133)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘GO’.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:775)

at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:676)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:649)

at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92)

at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)

… 9 more

INFO 2/13/13 4:38 PM:liquibase: Successfully released change log lock

Liquibase Update Failed: Error executing SQL --comment:Conversion to LiquiBase.

CREATE TABLE dbo.People

(

People_IDX int NOT NULL IDENTITY(1, 1),

FirstName varchar(30) NOT NULL

);

GO

ALTER TABLE dbo.People ADD CONSTRAINT PK_People_IDX PRIMARY KEY CLUSTERED (People_IDX);

GO: Incorrect syntax near ‘GO’.

SEVERE 2/13/13 4:38 PM:liquibase: Error executing SQL --comment:Conversion to LiquiBase.

CREATE TABLE dbo.People

(

People_IDX int NOT NULL IDENTITY(1, 1),

FirstName varchar(30) NOT NULL

);

GO

ALTER TABLE dbo.People ADD CONSTRAINT PK_People_IDX PRIMARY KEY CLUSTERED (People_IDX);

GO: Incorrect syntax near ‘GO’.

liquibase.exception.MigrationFailedException: Migration failed for change set …/VueDW/Tables/Address.sql::C1::henrythethird:

Reason: liquibase.exception.DatabaseException: Error executing SQL --comment:Conversion to LiquiBase.

CREATE TABLE dbo.People

(

People_IDX int NOT NULL IDENTITY(1, 1),

FirstName varchar(30) NOT NULL

);

GO

ALTER TABLE dbo.People ADD CONSTRAINT PK_People_IDX PRIMARY KEY CLUSTERED (People_IDX);

GO: Incorrect syntax near ‘GO’.:

Caused By: Error executing SQL --comment:Conversion to LiquiBase.

CREATE TABLE dbo.People

(

People_IDX int NOT NULL IDENTITY(1, 1),

FirstName varchar(30) NOT NULL

);

GO

ALTER TABLE dbo.People ADD CONSTRAINT PK_People_IDX PRIMARY KEY CLUSTERED (People_IDX);

GO: Incorrect syntax near ‘GO’.:

Caused By: Incorrect syntax near ‘GO’.

at liquibase.changelog.ChangeSet.execute(ChangeSet.java:347)

at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)

at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)

at liquibase.Liquibase.update(Liquibase.java:113)

at liquibase.integration.commandline.Main.doMigration(Main.java:779)

at liquibase.integration.commandline.Main.main(Main.java:133)

Caused by: liquibase.exception.DatabaseException: Error executing SQL --comment:Conversion to LiquiBase.

CREATE TABLE dbo.People

(

People_IDX int NOT NULL IDENTITY(1, 1),

FirstName varchar(30) NOT NULL

);

GO

ALTER TABLE dbo.People ADD CONSTRAINT PK_People_IDX PRIMARY KEY CLUSTERED (People_IDX);

GO: Incorrect syntax near ‘GO’.

at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)

at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)

at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1075)

at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:1059)

at liquibase.changelog.ChangeSet.execute(ChangeSet.java:317)

… 5 more