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

SQL Server Migrations: Idempotent scripts fails with 'invalid column name' (needs EXEC) #12911

Closed
christianholsen opened this issue Aug 7, 2018 · 62 comments · Fixed by #21933
Assignees
Labels
area-migrations closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported punted-for-3.0 type-enhancement
Milestone

Comments

@christianholsen
Copy link

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
Copy link
Member

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
Copy link
Member

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.

@tonven
Copy link

tonven 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
Copy link
Author

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

@tonven
Copy link

tonven 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
Copy link
Contributor

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
Copy link

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
Copy link

acarrau 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
Copy link

Shtong 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
Copy link

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
Copy link
Contributor

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

@jacqueskang
Copy link

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

@mitchelsellers
Copy link

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

@ghost
Copy link

ghost 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>();

@oising
Copy link

oising 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
Copy link

oising 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
Copy link

koshkarov 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
Copy link

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.

@ChristopherHaws
Copy link
Contributor

We also wrap all of our migrationBuilder.Sql("...") calls with EXEC to prevent future failures if schema's change. :)

@bricelam
Copy link
Contributor

bricelam commented Aug 6, 2020

@ChristopherHaws Good strategy. We should mention this in the docs. Filed dotnet/EntityFramework.Docs#2561

@derigel23
Copy link

@bricelam Any plans to backport fix to 3.1.x branch?

@bricelam
Copy link
Contributor

bricelam commented Aug 9, 2020

No, sorry. This fix required too significant of changes for a patch release—especially on an LTS release.

@ajcvickers ajcvickers modified the milestones: 5.0.0, 5.0.0-rc1 Aug 14, 2020
@binaryio
Copy link

I've found another workaround to this issue by using a powershell script to edit the Idempotent script prior to executing it. It fetches the list of MigrationIds and comments out the relevant script blocks.

param ($server, $database, $username, $password, $script)

#
# Create the temporary file to contain the modified script
#
$newscript = Join-Path -Path (Split-Path -Path $script) -ChildPath ("fixed_" + (Split-Path -Path $script -Leaf));
Set-Content -Path $newscript -Value "" -Encoding 'utf8'

#
# Fetch the currently applied migrations
#
$migrationIds = ""
$qry = Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $password -Query "SELECT DISTINCT [MigrationId] FROM [__EFMigrationsHistory]" -ErrorAction SilentlyContinue
if ($qry -ne $null)
{
    $migrationIds = ($qry | Select-Object -ExpandProperty MigrationId) -Join "|"
}

#
# Match the chunks in the script with the list of applied migrations, and comment them out
#
if ($migrationIds -ne "")
{
    $regex = "(?ms)^IF NOT EXISTS\(SELECT \* FROM \[__EFMigrationsHistory\] WHERE \[MigrationId\] = N'(" + $migrationIds + ")'\).*?END;\s+GO"
    $c = (Get-Content $script -Raw) -replace $regex,"/*`r`n`$0`r`n*/";
    Set-Content -Path $newscript -Value $c -Encoding 'utf8'
} else {
    Copy-Item $script $newscript
}

#
# Execute the fixed Migrations script
#
Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $password -InputFile $newscript -Verbose

@ajcvickers ajcvickers modified the milestones: 5.0.0-rc1, 5.0.0 Nov 7, 2020
@DmytroHeorhin
Copy link

DmytroHeorhin commented Dec 18, 2020

Here are the cases I found that require EXEC for the SQL EF generates:

  • AddColumn with ComputedColumnSql
  • AddCheckConstraint
  • CreateIndex with Filter
  • DeleteData
  • InsertData
  • UpdateData

It'd also be nice to have DROP INDEX and probably DROP any object in this list. I face an error like "Cannot drop the index 'my index name', because it does not exist or you do not have permission."

Thanks for the fix already made! It'll avoid the error in case of CREATE INDEX for me.

@VidSankar
Copy link

VidSankar commented Feb 2, 2021

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;

I am having the same issue. Was there any fix or work around for this?

Though Idempotent script, sql seems to evaluate the query, though it will not get executed. And I am getting an error "Invalid column name". Any idea?

@derigel23
Copy link

@bricelam what about net5.0? Still doesn't work.

@VidSankar
Copy link

VidSankar commented Mar 14, 2021 via email

@bricelam
Copy link
Contributor

@derigel23 Can you create a new issue with more details about the issue you're encountering?

@derigel23
Copy link

@bricelam I've found out that errors are rising from my custom SQL statements in migration builder.
Should I escape them manually in EXEC or is there some better way?

@bricelam
Copy link
Contributor

bricelam commented May 3, 2021

Escape them manually. Users are responsible for ensuring their custom SQL statements work with the idempotent script. (Since we generally avoid trying to parse SQL in EF)

@jasonbert
Copy link

jasonbert commented May 31, 2022

I've found another workaround to this issue by using a powershell script to edit the Idempotent script prior to executing it. It fetches the list of MigrationIds and comments out the relevant script blocks.

param ($server, $database, $username, $password, $script)

#
# Create the temporary file to contain the modified script
#
$newscript = Join-Path -Path (Split-Path -Path $script) -ChildPath ("fixed_" + (Split-Path -Path $script -Leaf));
Set-Content -Path $newscript -Value "" -Encoding 'utf8'

#
# Fetch the currently applied migrations
#
$migrationIds = ""
$qry = Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $password -Query "SELECT DISTINCT [MigrationId] FROM [__EFMigrationsHistory]" -ErrorAction SilentlyContinue
if ($qry -ne $null)
{
    $migrationIds = ($qry | Select-Object -ExpandProperty MigrationId) -Join "|"
}

#
# Match the chunks in the script with the list of applied migrations, and comment them out
#
if ($migrationIds -ne "")
{
    $regex = "(?ms)^IF NOT EXISTS\(SELECT \* FROM \[__EFMigrationsHistory\] WHERE \[MigrationId\] = N'(" + $migrationIds + ")'\).*?END;\s+GO"
    $c = (Get-Content $script -Raw) -replace $regex,"/*`r`n`$0`r`n*/";
    Set-Content -Path $newscript -Value $c -Encoding 'utf8'
} else {
    Copy-Item $script $newscript
}

#
# Execute the fixed Migrations script
#
Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $password -InputFile $newscript -Verbose

Super useful, great idea, we're building the idempotent script and publishing as an artifact. At the time of build we can't determine which migrations have been applied, just when we're running the release. Should resolve the problem described in this issue about the invalid column name, which we're experiencing 👍.

Worth noting that this only happens when we're running the release through Azure DevOps pipelines. It works fine If I take the script and run it through MSSMS.

@ChristopherHaws
Copy link
Contributor

We have been using this extension method in place of migrationBuilder.Sql("...") for years now and it has worked great for us.

/// <summary>
/// Executes the SQL statement via sp_executesql which does not get validated until runtime.
/// </summary>
public static OperationBuilder<SqlOperation> ExecuteSql(this MigrationBuilder migrationBuilder, string sql) =>
    migrationBuilder.Sql($"EXEC sp_executesql N'{sql.Replace("'", "''")}'");

@sgabler-solytic
Copy link

@ChristopherHaws thanks for that, it solved our problem 👍

We had issues at first, because the generated SQL script didn't properly close the ' (single quotes) correctly. After some digging, we found out that our raw SQL scripts sometimes contained GO commands.

Example

UPDATE xyz SET foo = 'bar';
GO

UPDATE xyz SET baz = 'banana';
GO

Apparently EF Core splits the scripts based on GO commands. So the above example would create two steps in the generated SQL script, but the first one would have a missing ' (single quote).

The workaround for us was to extend your method a bit:

    /// <summary>
    /// Executes the SQL statement via sp_executesql which does not get validated until runtime. This avoids problems
    /// when generating an idempotent SQL script with the command `dotnet ef migrations script` (see
    /// https://github.com/dotnet/efcore/issues/12911#issuecomment-1142441149)
    ///
    /// NOTE: When this method is called on an SQL statement that contains `GO`, EF Core splits the statements at those
    /// and creates separate steps in the migrations. Unfortunately this will lead to a broken SQL script, as there are
    /// no closing ' (single quote) characters. So we comment out the GO statements dynamically.
    /// </summary>
    public static OperationBuilder<SqlOperation> ExecuteSqlSafely(this MigrationBuilder migrationBuilder, string sql)
    {
        const string pattern = "^\\s*GO;?\\s*$";
        const string replacement = $"-- GO (Commented out by {nameof(ExecuteSqlSafely)} method)";

        // First comment out all lines that contain only a `GO` statement (case insensitive, with optional semicolon and
        // whitespace). This requires the regex to be run in Multiline mode
        var modifiedSql = Regex.Replace(sql, pattern, replacement, RegexOptions.Multiline | RegexOptions.IgnoreCase);

        // Then escape all single quotes ('), which is necessary because of EXEC sp_executesql N''
        modifiedSql = modifiedSql.Replace("'", "''");

        return migrationBuilder.Sql($"EXEC sp_executesql N'{modifiedSql}'");
    }

@alexandis
Copy link

alexandis commented Feb 19, 2023

Isn't it possible to use DbCommandInterceptor to eliminate this issue? We use it for the site, but I am not sure if it's possible to use it for migrations, inside IDesignTimeDbContextFactory implementation

@a-witkowski
Copy link

Here are the cases I found that require EXEC for the SQL EF generates:

  • AddColumn with ComputedColumnSql
  • AddCheckConstraint
  • CreateIndex with Filter
  • DeleteData
  • InsertData
  • UpdateData

And CREATE SCHEMA for the temporal tables.

Thanks for the PR!

@bricelam, dotnet ef migrations script --idempotent version 7.0.7 still produces

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20230523085552_init')
BEGIN
/****** Object: Schema [reporting] Script Date: 2023-05-17 17:27:00 ******/
CREATE SCHEMA [reporting]
END;
GO

@bricelam
Copy link
Contributor

@a-witkowski Can you file a new issue?

@brentlyjdavid
Copy link

brentlyjdavid commented Aug 17, 2023

We have been using this extension method in place of migrationBuilder.Sql("...") for years now and it has worked great for us.

/// <summary>
/// Executes the SQL statement via sp_executesql which does not get validated until runtime.
/// </summary>
public static OperationBuilder<SqlOperation> ExecuteSql(this MigrationBuilder migrationBuilder, string sql) =>
    migrationBuilder.Sql($"EXEC sp_executesql N'{sql.Replace("'", "''")}'");

I wonder if this could just be added to the framework. We are adding this to our projects and going to start exclusively using it too because of dropped columns in a similar situation. Getting frustrated it keeps randomly popping up in different circumstances.

Is there a security risk or something to not have the .Sql(...) command just automatically run it/create it like that by default? rather than have an extension method?

@patrickklaeren
Copy link

We have been using this extension method in place of migrationBuilder.Sql("...") for years now and it has worked great for us.

/// <summary>
/// Executes the SQL statement via sp_executesql which does not get validated until runtime.
/// </summary>
public static OperationBuilder<SqlOperation> ExecuteSql(this MigrationBuilder migrationBuilder, string sql) =>
    migrationBuilder.Sql($"EXEC sp_executesql N'{sql.Replace("'", "''")}'");

I wonder if this could just be added to the framework. We are adding this to our projects and going to start exclusively using it too because of dropped columns in a similar situation. Getting frustrated it keeps randomly popping up in different circumstances.

Is there a security risk or something to not have the .Sql(...) command just automatically run it/create it like that by default? rather than have an extension method?

The payload of that is specific to SQL Server. I don't think there's particularly high overhead to putting that into a project yourself, personally.

It's less of a security concern and more of a behavioural quirk, I'd say mainly because this is largely dependent on the engine you're targeting.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area-migrations closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported punted-for-3.0 type-enhancement
Projects
None yet
Development

Successfully merging a pull request may close this issue.