SqlServer Migrations: Scripts do not fail on failure #22613
Description
One of the new features added in 5.0-rc1 was that transactions are now used in migrations, however if there is a failure in your migration, the transaction does not work properly because of the use of GO batch terminators. The transaction will not rollback changes prior to the error and will continue executing after the error'ed batch and will proceed to commit the successful batches of the failed migration.
In short, the current behaviors of suppressTransaction = true and suppressTransaction = false are pretty much the same. While a transaction is added to the script, it doesn't add any protection and the results of executing the script are the same.
Steps to reproduce
- Create a new project with the following DbContext setup:
// Startup.cs
services.AddDbContext<ApplicationContext>(x => {
x.UseSqlServer(@"Server=(localdb)\mssqllocaldb; Database=ef; Trusted_Connection=True; MultipleActiveResultSets=true");
});
public class ApplicationContext : DbContext {
public ApplicationContext(DbContextOptions<ApplicationContext> options) : base(options) {}
public DbSet<Blog> Blogs { get; set; }
protected override void OnModelCreating(ModelBuilder model) {
model.Entity<Blog>(x => {
x.Property(x => x.Name).HasMaxLength(10);
x.HasData(new Blog { BlogId = 1, Name = "Blog 1" });
x.HasData(new Blog { BlogId = 2, Name = "Blog 2 - Name is too long" });
});
}
}
public class Blog {
public int BlogId { get; set; }
public string Name { get; set; }
}- Create a migration:
dotnet migrations add Initial
public partial class Initial : Migration {
protected override void Up(MigrationBuilder migrationBuilder) {
migrationBuilder.CreateTable(
name: "Blogs",
columns: table => new {
BlogId = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column<string>(type: "nvarchar(10)", maxLength: 10, nullable: true)
},
constraints: table => {
table.PrimaryKey("PK_Blogs", x => x.BlogId);
});
migrationBuilder.InsertData(
table: "Blogs",
columns: new[] { "BlogId", "Name" },
values: new object[] { 1, "Blog 1" });
migrationBuilder.InsertData(
table: "Blogs",
columns: new[] { "BlogId", "Name" },
values: new object[] { 2, "Blog 2 - Name is too long" });
}
}- Create a migration script:
dotnet ef migrations script -o "migrate.sql"
IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
CREATE TABLE [__EFMigrationsHistory] (
[MigrationId] nvarchar(150) NOT NULL,
[ProductVersion] nvarchar(32) NOT NULL,
CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);
END;
GO
BEGIN TRANSACTION;
GO
CREATE TABLE [Blogs] (
[BlogId] int NOT NULL IDENTITY,
[Name] nvarchar(10) NULL,
CONSTRAINT [PK_Blogs] PRIMARY KEY ([BlogId])
);
GO
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
SET IDENTITY_INSERT [Blogs] ON;
INSERT INTO [Blogs] ([BlogId], [Name])
VALUES (1, N'Blog 1');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
SET IDENTITY_INSERT [Blogs] OFF;
GO
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
SET IDENTITY_INSERT [Blogs] ON;
INSERT INTO [Blogs] ([BlogId], [Name])
VALUES (2, N'Blog 2 - Name is too long');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
SET IDENTITY_INSERT [Blogs] OFF;
GO
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20200919002238_Initial', N'5.0.0-rc.1.20451.13');
GO
COMMIT;
GO- Run the
migration.sqlscript in an empty SQL database and notice that you get the following error:
(1 row affected)
Msg 8152, Level 16, State 13, Line 31
String or binary data would be truncated.
The statement has been terminated.
(1 row affected)
Completion time: 2020-09-18T17:29:20.1685190-07:00
The script continued to run even though there was an error and inserted the first blog entry and the migration entry but not the second blog entry.
Possible Solutions
You can see a current working solution for EF Core 3.1 here: #7681 (comment)
The basic details of that solution are:
- Execute
SET XACT_ABORT ON;at the beginning of the script - Only execute the batch
IF XACT_STATE() = 1 - Only commit a transaction
IF XACT_STATE() = 1
Something to be aware of, using XACT_STATE() within an IF statement with more than 1 condition causes it to not work properly so you will need to make a variable to capture the state. See: #7681 (comment)
Following these steps, the migration would look something like this:
SET XACT_ABORT ON;
IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
CREATE TABLE [__EFMigrationsHistory] (
[MigrationId] nvarchar(150) NOT NULL,
[ProductVersion] nvarchar(32) NOT NULL,
CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);
END;
GO
BEGIN TRANSACTION;
GO
IF XACT_STATE() = 1
BEGIN
CREATE TABLE [Blogs] (
[BlogId] int NOT NULL IDENTITY,
[Name] nvarchar(10) NULL,
CONSTRAINT [PK_Blogs] PRIMARY KEY ([BlogId])
);
END
GO
IF XACT_STATE() = 1
BEGIN
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
SET IDENTITY_INSERT [Blogs] ON;
INSERT INTO [Blogs] ([BlogId], [Name])
VALUES (1, N'Blog 1');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
SET IDENTITY_INSERT [Blogs] OFF;
END
GO
IF XACT_STATE() = 1
BEGIN
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
SET IDENTITY_INSERT [Blogs] ON;
INSERT INTO [Blogs] ([BlogId], [Name])
VALUES (2, N'Blog 2 - Name is too long');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
SET IDENTITY_INSERT [Blogs] OFF;
END
GO
IF XACT_STATE() = 1
BEGIN
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20200919002238_Initial', N'5.0.0-rc.1.20451.13');
END
GO
IF XACT_STATE() = 1
BEGIN
COMMIT;
END
GOWhich results in the __EFMigrationsHistory table being created, but it stays empty and all the rest of the script is rolled back.
Further technical details
EF Core version: 5.0.0-rc.1.20451.13
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.8.0 Preview 3.0