When using Flyway in combination with a Microsoft SQL Server, we are observing the issue described on this question.
Basically, a migration script like this one does not rollback the successful GO-delimited batches when another part failed:
BEGIN TRANSACTION
-- Create a table with two nullable columns
CREATE TABLE [dbo].[t1](
[id] [nvarchar](36) NULL,
[name] [nvarchar](36) NULL
)
-- add one row having one NULL column
INSERT INTO [dbo].[t1] VALUES(NEWID(), NULL)
-- set one column as NOT NULLABLE
-- this fails because of the previous insert
ALTER TABLE [dbo].[t1] ALTER COLUMN [name] [nvarchar](36) NOT NULL
GO
-- create a table as next action, so that we can test whether the rollback happened properly
CREATE TABLE [dbo].[t2](
[id] [nvarchar](36) NOT NULL
)
GO
COMMIT TRANSACTION
In the above example, the table t2 is being created even though the preceding ALTER TABLE statement fails.
On the linked question, the following approaches (outside of the flyway context) were suggested:
-
A multi-batch script should have a single error handler scope that rolls back the transaction on error, and commits at the end. In TSQL you can do this with dynamic sql
- Dynamic SQL makes for hard-to-read script and would be very inconvenient
-
With SQLCMD you can use the
-boption to abort the script on error- Is this available in flyway?
-
Or roll your own script runner
- Is this maybe the case in flyway? Is there a flyway-specific configuration to enable proper failing on errors?
EDIT: alternative example
Given: simple database
BEGIN TRANSACTION
CREATE TABLE [a] (
[a_id] [nvarchar](36) NOT NULL,
[a_name] [nvarchar](100) NOT NULL
);
CREATE TABLE [b] (
[b_id] [nvarchar](36) NOT NULL,
[a_name] [nvarchar](100) NOT NULL
);
INSERT INTO [a] VALUES (NEWID(), 'name-1');
INSERT INTO [b] VALUES (NEWID(), 'name-1'), (NEWID(), 'name-2');
COMMIT TRANSACTION
Migration Script 1 (failing, without GO)
BEGIN TRANSACTION
ALTER TABLE [b] ADD [a_id] [nvarchar](36) NULL;
UPDATE [b] SET [a_id] = [a].[a_id] FROM [a] WHERE [a].[a_name] = [b].[a_name];
ALTER TABLE [b] ALTER COLUMN [a_id] [nvarchar](36) NOT NULL;
ALTER TABLE [b] DROP COLUMN [a_name];
COMMIT TRANSACTION
This results in the error message Invalid column name 'a_id'. for the UPDATE statement.
Possible solution: introduce GO between statements
Migration Script 2 (with GO: working for "happy case" but only partial rollback when there's an error)
BEGIN TRANSACTION
SET XACT_ABORT ON
GO
ALTER TABLE [b] ADD [a_id] [nvarchar](36) NULL;
GO
UPDATE [b] SET [a_id] = [a].[a_id] FROM [a] WHERE [a].[a_name] = [b].[a_name];
GO
ALTER TABLE [b] ALTER COLUMN [a_id] [nvarchar](36) NOT NULL;
GO
ALTER TABLE [b] DROP COLUMN [a_name];
GO
COMMIT TRANSACTION
- This performs the desired migration as long as all values in table
[b]have a matching entry in table[a]. - In the given example, that's not the case. I.e. we get two errors:
- expected:
Cannot insert the value NULL into column 'a_id', table 'test.dbo.b'; column does not allow nulls. UPDATE fails. - unexpected:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. - Horrifyingly: the last
ALTER TABLE [b] DROP COLUMN [a_name]statement was actually executed, committed and not rolled back. I.e. one cannot fix this up afterwards as the linking column is lost.
- expected:
This behaviour is actually independent of flyway and can be reproduced directly via SSMS.