Efcore: Idempotent scripts aren't really idempotent - they fail the second time

[tomasaschan]

We're using the script --idempotent feature to generate database migration scripts that our CI system can run without having detailed knowledge about what migrations to apply, and/or installing more tooling than Invoke-SqlCmd (which comes with the database installation anyway). However, we've discovered that in certain cases, they aren't idempotent - specifically, if one migration does multiple things, and any but the first ones fail, you're left in a "in the middle of" state that can be tricky to recover from without very manual intervention.

For example, consider a migration with two actions like this:

• Add a new column
• Do something that fails

dotnet ef migrations script --idempotent will generate a script along the following lines:

IF EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180117085558_MyMigration')
BEGIN
    ALTER TABLE [FooEntities] ADD [NewColumn] nvarchar(max) NULL;
END;

GO

IF EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180117085558_MyMigration')
BEGIN
    -- do something that fails here      
END;

GO

GO

IF EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180117085558_MyMigration')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES  (N'20180117085558_MyMigration');
END;

GO

When executing this script (e.g. with Invoke-SqlCmd, it will add the column, and then fail before adding a row to the migrations history. Thus, when running the script a second time, it will fail even earlier, when trying to add a column that already exists, and it will thus not be possible to recover by just fixing the broken migration step and trying again.

I believe (but I might be incorrect here) that EF 6.x wraps each migration in a transaction, that ensure that you either apply the entire thing, or none of it. It seems (although I've just tested by running dotnet ef database update a couple of times) that the CLI tooling for EF Core does this as well. Would it be reasonable to implement that also in the generated scripts?

---

My setup:

dotnet --version: 2.1.4
EF Core packages and tooling: 2.0.1

[bricelam]

Discussed previously in #7681

[tomasaschan]

@bricelam I get that adding transactions here might provide more complexity than value, so I'm not going to question your call there. However, the way the script is currently written and output, it's also difficult for me to, in some automated way, add transactions around each migration.

Could an option be to support, via some command line flag, to output each migration to a separate file? Then I could figure out a way to run Invoke-SqlCmd on each file with a transaction per call, and my immediate problem would be solved. Not sure what a good API design for that would be, so that it doesn't break the current --output behavior, though, but I'm imagining something like dotnet ef migrations script --idempotent --file-per-migration --output-dir ./migration-scripts, resulting in a bunch of files, named after the migrations so they sort well, inside the indicated folder. Would that be feasible? Is there any chance the team would implement it?

[bricelam]

You can generate one script per file by using the TO and FROM arguments.

dotnet ef migrations script 0 Migration1 --output Migration1.sql
dotnet ef migrations script Migration1 Migration2 --output Migration2.sql
dotnet ef migrations script Migration2 Migration3 --output Migration3.sql

You can get a list of all the migrations (in order) using:

dotnet ef migrations list

[tomasaschan]

Sure; I might go that way as a workaround. Still, I think it would be useful (for performance and for simplicity) to have it built in :)

[bricelam]

Here's a bit of PowerShell magic:

$migrations = dotnet ef migrations list
$previous = '0'

foreach ($migration in $migrations)
{
    dotnet ef migrations script $previous $migration --idempotent `
        --output ".\migration-scripts\$migration.sql"
    $previous = $migration
}

[bricelam]

If logging is interfering with dotnet ef migrations list, you can use this to get the right data into $migrations.

$migrations = dotnet ef migrations list --prefix-output |
        where { $_.StartsWith("data:") } |
        foreach { $_.Substring(5).Trim() }

[Tonvengo]

@bricelam Hi. I was running your script for producing migrations and I got produced all migrations except initial one. Also I get this warning:

The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes.

Can this error be because of version mismatch?

[ajcvickers]

@Tonvengo That warning can be ignored--see https://github.com/aspnet/Home/releases/tag/2.1.3#known-issues

[Tonvengo]

@ajcvickers @bricelam This is what I get when I am running script above in VSTS:

List of migrations: The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes. 20180918133748_Initial 20180921103430_FjernetGamleTabeller 20180921104046_MigreringsDataOppdatert 20180921104649_OppdaterteAuditTabel
2018-09-25T08:24:29.6381862Z The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes.
2018-09-25T08:24:30.1907454Z System.InvalidOperationException: The migration 'The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes.' was not found.
2018-09-25T08:24:30.1910305Z at Microsoft.EntityFrameworkCore.Migrations.MigrationsAssemblyExtensions.GetMigrationId(IMigrationsAssembly assembly, String nameOrId)
2018-09-25T08:24:30.1919407Z at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.PopulateMigrations(IEnumerable1 appliedMigrationEntries, String targetMigration, IReadOnlyList1& migrationsToApply, IReadOnlyList1& migrationsToRevert) 2018-09-25T08:24:30.1922097Z at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateScript(String fromMigration, String toMigration, Boolean idempotent) 2018-09-25T08:24:30.1922530Z at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.ScriptMigration(String fromMigration, String toMigration, Boolean idempotent, String contextType) 2018-09-25T08:24:30.1922893Z at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScriptMigration.<>c__DisplayClass0_1.<.ctor>b__0() 2018-09-25T08:24:30.1923208Z at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_01.b__0()
2018-09-25T08:24:30.1923503Z at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
2018-09-25T08:24:30.1924003Z The migration 'The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes.' was not found.
2018-09-25T08:24:30.2561346Z Prodused new migration: The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes.
2018-09-25T08:24:33.6387099Z The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes.
2018-09-25T08:24:34.1229565Z System.InvalidOperationException: The migration 'The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes.' was not found.
2018-09-25T08:24:34.1230179Z at Microsoft.EntityFrameworkCore.Migrations.MigrationsAssemblyExtensions.GetMigrationId(IMigrationsAssembly assembly, String nameOrId)
2018-09-25T08:24:34.1231507Z at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateScript(String fromMigration, String toMigration, Boolean idempotent)
2018-09-25T08:24:34.1233823Z at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.ScriptMigration(String fromMigration, String toMigration, Boolean idempotent, String contextType)
2018-09-25T08:24:34.1234740Z at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScriptMigration.<>c__DisplayClass0_1.<.ctor>b__0()
2018-09-25T08:24:34.1235099Z at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.b__0()
2018-09-25T08:24:34.1235583Z at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
2018-09-25T08:24:34.1236020Z The migration 'The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes.' was not found.
2018-09-25T08:24:34.1484030Z Prodused new migration: 20180918133748_Initial
2018-09-25T08:24:37.3862489Z The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes.
2018-09-25T08:24:37.9729892Z Prodused new migration: 20180921103430_FjernetGamleTabeller
2018-09-25T08:24:41.1123266Z The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes.
2018-09-25T08:24:41.7061105Z Prodused new migration: 20180921104046_MigreringsDataOppdatert
2018-09-25T08:24:44.8478354Z The EF Core tools version '2.1.2-rtm-30932' is older than that of the runtime '2.1.3-rtm-32065'. Update the tools for the latest features and bug fixes.
2018-09-25T08:24:45.4311757Z Prodused new migration: 20180921104649_OppdaterteAuditTabel
2018-09-25T08:24:45.4737064Z 20180921103430_FjernetGamleTabeller.sql
2018-09-25T08:24:45.4744425Z 20180921104046_MigreringsDataOppdatert.sql
2018-09-25T08:24:45.4752015Z 20180921104649_OppdaterteAuditTabel.sql

The warning becoming a part of migration list.. The initial migration was not produced.
I followed instructions from post above , but still get this error.

[Tonvengo]

Ok, I didn't add part of code for removing logging that @bricelam was proposing. Now I get warning but all migrations are prodused 👍

[bricelam]

Note, based on feedback, we're re-considering #7681