Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Idempotent migration scripts fails #12911

Open
christianholsen opened this issue Aug 7, 2018 · 20 comments

Comments

@christianholsen
Copy link

commented Aug 7, 2018

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:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180519095519_AddIndexToMyTable')
BEGIN
        CREATE UNIQUE INDEX [NameIndex] ON [MyTable] ([Name]) WHERE [Name] IS NOT NULL;
END;

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180720070812_RemoveNameFromMyTable')
BEGIN
    DROP INDEX [NameIndex] ON [MyTable];
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180720070812_RemoveNameFromMyTable')
BEGIN
    DECLARE @var19 sysname;
    SELECT @var19 = [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'[MyTable]') AND [c].[name] = N'Name');
    IF @var19 IS NOT NULL EXEC(N'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @var19 + '];');
    ALTER TABLE [MyTable] DROP COLUMN [Name];
END;
GO

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

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180519095519_AddIndexToMyTable')
BEGIN
        CREATE UNIQUE INDEX [NameIndex] ON [MyTable] ([Name]) WHERE [Name] IS NOT NULL;
END;

The SQL error message is

Invalid column name 'Name'.

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
protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<MyTable>().HasIndex(c => c.Name).IsUnique();
        }
  1. Make a new migration
  2. Remove the field Name from MyTable and remove the unique index in OnModelCreating
  3. Make a new migration
  4. Create an idempotent migration script with
dotnet ef migrations script -o migrationscript.sql --startup-project MyProject.csproj --configuration release --idempotent
  1. 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:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180519095519_AddIndexToMyTable')
BEGIN
        EXEC('CREATE UNIQUE INDEX [NameIndex] ON [MyTable] ([Name]) WHERE [Name] IS NOT NULL;')
END;

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

@ajcvickers

This comment has been minimized.

Copy link
Member

commented Aug 10, 2018

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 167

ADO.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();
    }
}
@ajcvickers ajcvickers added this to the 3.0.0 milestone Aug 28, 2018
@ajcvickers

This comment has been minimized.

Copy link
Member

commented Aug 28, 2018

Triage: for 3.0, we will investigate the minimal set of places where we have to wrap in an exec call to avoid the SQL Server parsing issue. Worst case, we will have to do it everywhere. Note that this should only be done when generating idempotent scripts.

@ajcvickers ajcvickers added the type-bug label Aug 28, 2018
@Tonvengo

This comment has been minimized.

Copy link

commented Sep 21, 2018

@christianholsen can you paste a gist with a powershell script that you use to prepare migration file?
Thanks in advance.

@christianholsen

This comment has been minimized.

Copy link
Author

commented Oct 4, 2018

Yep. As a wrote above the only problem I found was the specific scenario specific where I have created an index for a nullable column and later remove it. The problem arises when the CREATE UNIQUE INDEX statement is evaluated even though it is not executed. So I have chosen to wrap all CREATE UNIQUE INDEX statements in the SQL in EXEC blocks. This has the effect that the script is dynamic and will only be evaluated when it actually is executed - fixing the immediate problem.

I use Team City as my build tools. You should be able to use this approach with any build tool.

I have a build step where I create the idempotent SQL file as mentioned in my first post. This step is executed as a command line step using the dotnet cli : dotnet ef migration script...etc (see my first post) This creates the idempotent script 'migrationscript.sql'

Next I have a powershell script step where I replace CREATE UNIQUE INDEX using regular expressions.
The code is here:

<#

Replace all 
CREATE UNIQUE INDEX [...]
with 
EXEC('CREATE UNIQUE INDEX [...]')
in migrationscript.sql

#>
$regexA = '\s*(CREATE UNIQUE INDEX.+)'
$encoding = New-Object System.Text.UTF8Encoding
$invocation = (Get-Variable MyInvocation).Value
Get-ChildItem  "migrationscript.sql" | % {
  $c = (Get-Content $_.FullName) -replace $regexA,'EXEC(''$0'')' -join "`r`n"
  [IO.File]::WriteAllText("$((Get-Item -Path ".\").FullName)\\migrationscript.sql", $c, $encoding)
}
Write-Host ("migrationscript.sql has been fixed")

As I understand it this problem will be fixed at some point in EF CORE, so this is working fine for me for now. :-)

Hope this helps,
/Christian

@Tonvengo

This comment has been minimized.

Copy link

commented Oct 4, 2018

@christianholsen Thanks for response. I found different solution. I am producing all migrations separately and in Octopus Deploy run only those migrations, that were not applied. So I am waiting as well when this will be fixed, so I can switch back to migrations.sql. :)

@Kukkimonsuta

This comment has been minimized.

Copy link
Contributor

commented Oct 10, 2018

We are experiencing the same issue with hand written updates after column was removed/renamed: migrationBuilder.Sql(@"UPDATE [Foo] SET [Bar] = 15 WHERE [Bar] = 10"); - it would be great if this was also wrapped when generating a script.

@mitchelsellers

This comment has been minimized.

Copy link

commented Nov 6, 2018

We are also experiencing this issue. The workaround that @christianholsen used seems to have gotten us past this, as we are using Azure DevOps for deployments we were able to patch the script before we release.

@acarrau

This comment has been minimized.

Copy link

commented Nov 9, 2018

The same is occurring to us with Idempotent Script Generation. In our case it is not happening with an INDEX creation, but with an IDENTITY INSERT.

@Shtong

This comment has been minimized.

Copy link

commented Feb 27, 2019

Another project where this problem occurs (without involving indexes) is the current version of the Contoso University sample app (available here https://github.com/aspnet/Docs/tree/0ee6b101d9d4b4022add3583ed25f0d89674b87b/aspnetcore/data/ef-mvc/intro/samples/cu-final). The Inheritance migration creates a temporary column (OldID) that is created and immediately removed in the same migration ; and after the first run, the database rejects the itempotent migration script because the column does not exist.

Unfortunately in this case, wrapping the instructions in an EXEC call with a simple regex replace won't be enough, as we would need to escape the quotes inside the EXEC argument, like in that case:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20170816195930_Inheritance')
BEGIN
    UPDATE dbo.Enrollment SET StudentId = (SELECT ID FROM dbo.Person WHERE OldId = Enrollment.StudentId AND Discriminator = 'Student')
END;
@adria3a3

This comment has been minimized.

Copy link

commented May 13, 2019

I'm having the same problem with the migrations. When the idempotent flag is set not all statements are wrapped with a EXEC statement and the migration fails.

It there an ETA know for this issue to be resolved?

@bricelam

This comment has been minimized.

Copy link
Member

commented May 13, 2019

We still hope to address this issue before the final 3.0.0 release (this September).

@jacqueskang

This comment has been minimized.

Copy link

commented May 17, 2019

We are having this issue too and it would be nice to have it fixed for 2.x too

@mitchelsellers

This comment has been minimized.

Copy link

commented May 17, 2019

For those using Azure DevOps, we did release a set of .NET Core Pipeline tasks. A temporary workaround for this issue is included as an option in our tasks that script the migrations (https://marketplace.visualstudio.com/items?itemName=iowacomputergurus.dotnetcore-pipeline-tasks)

Just a workaround until this can be truly fixed

@CezaryKMakingWaves

This comment has been minimized.

Copy link

commented Jun 5, 2019

Hi,

Another workaround is to globally wrap SQL scripts with EXECUTE() statement. In case of someone looking for such an approach here is very dumb (roughly tested) implementation:

    public class DynamicSqlRelationalCommandBuilder : RelationalCommandBuilder
    {
        public DynamicSqlRelationalCommandBuilder(IDiagnosticsLogger<DbLoggerCategory.Database.Command> logger, IRelationalTypeMappingSource typeMappingSource) : base(logger, typeMappingSource)
        {
        }

        protected override IRelationalCommand BuildCore(IDiagnosticsLogger<DbLoggerCategory.Database.Command> logger, string commandText, IReadOnlyList<IRelationalParameter> parameters)
        {
            commandText = "EXECUTE ('" + commandText.Replace("'", "''") + "')";
            return base.BuildCore(logger, commandText, parameters);
        }
    }
    public class DynamicSqlRelationalCommandBuilderFactory : RelationalCommandBuilderFactory
    {
        public DynamicSqlRelationalCommandBuilderFactory(IDiagnosticsLogger<DbLoggerCategory.Database.Command> logger, IRelationalTypeMappingSource typeMappingSource) : base(logger, typeMappingSource)
        {
        }

        protected override IRelationalCommandBuilder CreateCore(IDiagnosticsLogger<DbLoggerCategory.Database.Command> logger,
            IRelationalTypeMappingSource relationalTypeMappingSource)
        {
            return new DynamicSqlRelationalCommandBuilder(logger, relationalTypeMappingSource);
        }
    }

Then somewhere in your IDesignTimeDbContextFactory implementation:
options.ReplaceService<IRelationalCommandBuilderFactory, DynamicSqlRelationalCommandBuilderFactory>();

@divega divega modified the milestones: 3.0.0, Backlog Jun 20, 2019
@divega divega added punted-for-3.0 and removed propose-punt labels Jun 24, 2019
@oising

This comment has been minimized.

Copy link

commented Jun 25, 2019

I came here looking for issues around creating VIEWs and TRIGGERs with an --idempotent flavoured migration script, and I get a different error, albeit should probably be thrown into the same bucket as this one. The script generated by dotnet ef migrations script ... won't parse at all because of the way it groups CREATE VIEW and CREATE TRIGGER statements.

According to the SQL laws laid down by Microsoft, CREATE VIEW needs to be the first statement in the query batch, but it isn't, because there's an IF NOT EXISTS statement there first. Here's an example from my idempotent script that was generated:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20190617133214_Add_Crossing_Coordinate_View')
BEGIN
    CREATE VIEW [dbo].[crossing_coordinate_view] 
    									AS
    									SELECT Crossing.Id,

This incurs Incorrect syntax near the keyword 'VIEW'. when attempting to parse it.

More info here:

https://dba.stackexchange.com/questions/215699/if-not-exists-using-object-id-doesnt-work-for-views-and-triggers-why

This ultimately makes dotnet ef migrations script unusable for release automation, arguably it's principal purpose. Please fix (one solution is to emit a DROP VIEW guarded by the IF, and always recreate the VIEW [or trigger]).

@oising

This comment has been minimized.

Copy link

commented Jun 25, 2019

Ok, I've worked around this by using a powershell task in my release pipeline to munge the migration script in the artifact drop folder before executing it. I'm using regex to wrap all CREATE VIEW and CREATE TRIGGER blocks with EXEC('...'). Here's my inline script:

$sql = get-content .\migrate.sql -raw
[regex]::replace($sql, "BEGIN\s+(CREATE (?:VIEW|TRIGGER).+?)END", "BEGIN`nEXEC('`$1');`nEND", "ignorecase,singleline") > migrate.sql

Make sure you tick the box to use PowerShell Core (for the -raw parameter support)

@koshkarov

This comment has been minimized.

Copy link

commented Jul 24, 2019

For those who uses idempotent script to apply the migrations. This approach splits the script on separate queries by "GO" statements an checks if this query is a part of the migration that was already applied.
_Note: the other way of fixing this issue is to generate 'per migration' idempotent scripts (which EF Core CLI allows to do), but this method is very slow.
I use PowerShell to apply them.

  1. Read migrations idempotent script file to a list of strings - $queries:
$sqlData = Get-Content $SQL_FILE_PATH -Raw
$queries = $sqlData -split "GO\r\n"
  1. Then select migrations that were already applied to the database.
$existingMigrations = Invoke-Some-Magic-And-Return-List-Of-Migrations-As-Strings

NOTE: When you make this query, make sure to check if migration table exists first.

  1. Then iterate trough $queries and extract migration name from it using RegEx:
foreach($query in $queries)
{
    $migrationFromQuery = $null
    if ($query -match "(?<=\')(\d{14}.+)(?=\')") {
        $migrationFromQuery =  $matches[0]
    }
    ....
  1. And check if it is in the list of applied migrations and apply if it is not there:
    ...
    if ($existingMigrations -notcontains $migrationFromQuery) {
        Invoke-Some-Magic-And-Apply-Query-As-Transaction -QUERY $query
    }
}

Good things about this solution:

  • it will not modify migrations code;
  • It will only filter migrations, so you do not need to specifically 'fix some of them';
  • it will not brake your migrations deployment when the fix will come from MS;
  • you do not need to modify your code or build pipeline, only deployment script.

Note: it will not filter the very first SQL query:

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;

But this query is completely safe and will not cause any problems like discussed in this issue.

@kovyfive

This comment has been minimized.

Copy link

commented Sep 26, 2019

Also including
:on error ignore
in the beginning of the script will ignore all errors.
This should help to run it, but will not show any error during deployment, potentially not showing possible deployment issues.

@Inzanit

This comment has been minimized.

Copy link

commented Oct 2, 2019

Is there an update as to when this is likely to be fixed? Run into this today, for the first time and halted deployment to our prod environment.

@Inzanit

This comment has been minimized.

Copy link

commented Oct 2, 2019

Follow up to @CezaryKMakingWaves great workaround, I've updated the code to work with EF Core 3.0

public class DynamicSqlRelationalCommandBuilder : RelationalCommandBuilder
    {
        public DynamicSqlRelationalCommandBuilder(RelationalCommandBuilderDependencies dependencies) : base(dependencies)
        {
        }

        public override IRelationalCommand Build()
        {
            var newCommandText = "EXECUTE ('" + base.ToString().Replace("'", "''") + "')";

            return new RelationalCommand(base.Dependencies, newCommandText, base.Parameters);
        }
    }

    public class DynamicSqlRelationalCommandBuilderFactory : RelationalCommandBuilderFactory
    {
        public DynamicSqlRelationalCommandBuilderFactory(RelationalCommandBuilderDependencies dependencies) : base(dependencies)
        {
        }

        public override IRelationalCommandBuilder Create()
        {
            return new DynamicSqlRelationalCommandBuilder(base.Dependencies);
        }
    }

Along with the design time options replacement

builder.ReplaceService<IRelationalCommandBuilderFactory, DynamicSqlRelationalCommandBuilderFactory>();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.