Rollback failed migration: EF Core MySQL

Question

Using:

• Pomelo.EntityFrameworkCore.MySQL 3.1.1
• Microsoft.EntityFrameworkCore 3.1.3
• MySQL 5.7

It appears that if EF Core's update-database command fails, no rollback is performed of the partial migration. Is that how it's supposed to work? If so, I'm curious why? More importantly, is there a way to make a rollback happen automatically, or at least something that can be run afterwards manually, when update-database runs into an error? I've tried something like this:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql("Start Transaction;");
...

    migrationBuilder.Sql("Commit;");
}

but running Rollback; afterwards doesn't do anything. Thanks!

Ecto

Answer 1

It appears that if EF Core's update-database command fails, no rollback is performed of the partial migration. Is that how it's supposed to work? If so, I'm curious why?

While EF Core and Pomelo both support applying migrations as part of a transaction (and are in fact doing so by default), MySQL itself will implicitly commit each structure altering statement individually.

See the following articles for further information:

• Can you rollback a query in state 'committing alter table to storage engine'?
• 13.3.3 Statements That Cause an Implicit Commit

More importantly, is there a way to make a rollback happen automatically, or at least something that can be run afterwards manually, when update-database runs into an error?

The only reliable way to safely apply migrations for MySQL is to manually rollback any partially applied migrations by using backup/restore:

1. Backup database
2. Apply scripted migrations
3. Restore previous database backup, if any migration failed

There are other database systems out there, like SQL Server or PostgreSQL, that support transaction-safe structure altering statements, but MySQL does not.

Become