Idempotent scripts aren't really idempotent - they fail the second time #10717
Comments
|
Discussed previously in #7681 |
|
@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 |
|
You can generate one script per file by using the 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.sqlYou can get a list of all the migrations (in order) using: dotnet ef migrations list |
|
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 :) |
|
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
} |
|
If logging is interfering with $migrations = dotnet ef migrations list --prefix-output |
where { $_.StartsWith("data:") } |
foreach { $_.Substring(5).Trim() } |
|
@bricelam Hi. I was running your script for producing migrations and I got produced all migrations except initial one. Also I get this warning:
Can this error be because of version mismatch? |
|
@Tonvengo That warning can be ignored--see https://github.com/aspnet/Home/releases/tag/2.1.3#known-issues |
|
@ajcvickers @bricelam This is what I get when I am running script above in VSTS:
The warning becoming a part of migration list.. The initial migration was not produced. |
|
Ok, I didn't add part of code for removing logging that @bricelam was proposing. Now I get warning but all migrations are prodused 👍 |
|
Note, based on feedback, we're re-considering #7681 |
We're using the
script --idempotentfeature 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 thanInvoke-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:
dotnet ef migrations script --idempotentwill generate a script along the following lines: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 updatea 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
The text was updated successfully, but these errors were encountered: