ajcvickers
commented
6 years ago Note for triage: I was able to reproduce this both in SQL Management Studio and when running the commands directly from ADO.NET:
Migrating once:
Migrating twice:
Unhandled Exception: System.Data.SqlClient.SqlException: Invalid column name 'Name'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Program.ApplyMigrations(DbConnection connection) in C:\Stuff\TwoOneCore\TwoOneCore\Program.cs:line 210
at Program.Main() in C:\Stuff\TwoOneCore\TwoOneCore\Program.cs:line 167ADO.NET repro code:
public class BloggingContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
}
public class Program
{
private static readonly string[] migrations =
{
@"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;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192355_Initial')
BEGIN
CREATE TABLE [Blog] (
[Id] int NOT NULL IDENTITY,
CONSTRAINT [PK_Blog] PRIMARY KEY ([Id])
);
END;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192355_Initial')
BEGIN
CREATE TABLE [Post] (
[Id] int NOT NULL IDENTITY,
[BlogId] int NULL,
CONSTRAINT [PK_Post] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Post_Blog_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blog] ([Id]) ON DELETE NO ACTION
);
END;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192355_Initial')
BEGIN
CREATE INDEX [IX_Post_BlogId] ON [Post] ([BlogId]);
END;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192355_Initial')
BEGIN
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20180810192355_Initial', N'2.1.1-rtm-30846');
END;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192452_AddName')
BEGIN
ALTER TABLE [Blog] ADD [Name] nvarchar(max) NULL;
END;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192452_AddName')
BEGIN
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20180810192452_AddName', N'2.1.1-rtm-30846');
END;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192552_AddIndex')
BEGIN
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Blog]') AND [c].[name] = N'Name');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Blog] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Blog] ALTER COLUMN [Name] nvarchar(450) NULL;
END;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192552_AddIndex')
BEGIN
CREATE UNIQUE INDEX [IX_Blog_Name] ON [Blog] ([Name]) WHERE [Name] IS NOT NULL;
END;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192552_AddIndex')
BEGIN
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20180810192552_AddIndex', N'2.1.1-rtm-30846');
END;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192708_RemoveName')
BEGIN
DROP INDEX [IX_Blog_Name] ON [Blog];
END;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192708_RemoveName')
BEGIN
DECLARE @var1 sysname;
SELECT @var1 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Blog]') AND [c].[name] = N'Name');
IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [Blog] DROP CONSTRAINT [' + @var1 + '];');
ALTER TABLE [Blog] DROP COLUMN [Name];
END;
",
@"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192708_RemoveName')
BEGIN
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20180810192708_RemoveName', N'2.1.1-rtm-30846');
END;
"
};
public static void Main()
{
using (var context = new BloggingContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var connection = context.Database.GetDbConnection();
Console.WriteLine("Migrating once:");
ApplyMigrations(connection);
Console.WriteLine("Migrating twice:");
ApplyMigrations(connection);
}
}
private static void ApplyMigrations(DbConnection connection)
{
connection.Open();
foreach (var migration in migrations)
{
var command = connection.CreateCommand();
command.CommandText = migration;
command.ExecuteNonQuery();
}
connection.Close();
}
}
tonven
christianholsen
Kukkimonsuta
mitchelsellers
acarrau
Shtong
adria3a3
bricelam
jacqueskang
ghost
oising
koshkarov
kovyfive
patrickklaeren
stephensowerby-ncl
Adrian10988
joelmeaders
natcoxall
Bluhmanman
markmonster
onur-ozguzel
matthias-schuchardt
ChristopherHaws
coldacid
gabrielionita
derigel23
binaryio
DmytroHeorhin
VidSankar
I have encountered a problem with idempotent migration scripts that make them fail in our continous integration system. The reason is that some migration scripts are parsed even though they are not going to be executed.
This issue is much like issue #10717. This issue can be reproduced by manipulating EF types i a sequence of migrations.
In example I have these two migrations, the first is adding a unique index for a nullable column, the second is removing the column:
This will work fine in the first execution, just as #10717
The second time this in run in our CI system, MyTable will no longer have the column "Name" and will fail in the execution of
The SQL error message is
This happens even though the migration "AddIndexToMyTable" has been installed and the "If not exists.." statement should avoid execution of the script, but as i happens it is parsed anyways, making it fail!
Steps to reproduce
1) Create a entity type with a nullable field with a unique index using fluent API
2) Make a new migration 3) Remove the field Name from MyTable and remove the unique index in OnModelCreating 4) Make a new migration 5) Create an idempotent migration script with
6) execute migrationscript.sql twice on the database making it fail
Proposal for a solution
This problem only occurs because the script section is parsed in the sql server even though it is not going to be executed. If this could be avoided the problem would not occur. It could be solved by using dynamic a sql script, as:
As it is i'm adding the exec to the migration script with powershell in CI, but I believe that everyone would be happier if we could rely on the script produced by EFCore :-)
Further technical details
EF Core version: 2.1.1