Pomelo.EntityFrameworkCore.MySql: Migrations that used to work, now fail with 2.1.0-rc1-final

Steps to reproduce

Ideally include a complete code listing that we can run to reproduce the issue. Alternatively, you can provide a project/solution that we can run.

The issue

Describe what is not working as expected. Migrations that used to work in 2.0.1 now fails. Exceptions are thrown for dropping indexes, even though now explicit index drops are performed. Complete tables with explicit indexes are being dropped though. Exceptions are being thrown due to long identifier names, FK_ names.

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message:
Cannot drop index 'IX_Attachments_FileId': needed in a foreign key constraint

Stack trace:
-		$exception	{MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot drop index 'IX_Attachments_FileId': needed in a foreign key constraint ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot drop index 'IX_Attachments_FileId': needed in a foreign key constraint
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 43
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 92
   at MySql.Data.MySqlClient.MySqlDataReader.ReadFirstResultSetAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 324
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(MySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 309
   at MySqlConnector.Core.TextCommandExecutor.ExecuteReaderAsync(String commandText, MySqlParameterCollection parameterCollection, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 73
   at MySqlConnector.Core.TextCommandExecutor.ExecuteNonQueryAsync(String commandText, MySqlParameterCollection parameterCollection, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 26
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 60
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Backend.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory, IServiceScopeFactory scopeFactory, IHubContext`1 hubContext, IApplicationLifetime applicationLifetime, IHttpContextAccessor httpContextAccessor, IOptions`1 applicationOptionsStore) in D:\Projects\Git\Angular\web-iot-starter\net\Backend\Backend\Startup.cs:line 384}	MySql.Data.MySqlClient.MySqlException

Further technical details

MySQL version: ‘5.7.17’ Operating system: Windows 10 Spring Creators Update Pomelo.EntityFrameworkCore.MySql version: 2.1.0-rc1-final

Other details about my project setup:

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 27 (13 by maintainers)

Most upvoted comments

This seems to be an issue with how the migrations have been generated previously. I don’t think there’s a reasonable way of accounting for it in the provider without changing the migrations manually.

@WillooWisp The repro you provided seems to have manually edited migrations, so I am not sure how well it represents the real app, but here are two ways of fixing it:

Identify the migration that’s causing the error. Remove it and all migrations after it then create a new migration.
If you have to keep the existing migrations then identify the foreign key using the column that’s being altered and add corresponding DropForeignKey and AddForeignKey calls around it in the migration. For the repro project it’s FK_Attachments_FileConfigs_FileId, so the AddedUnknown migration would look like this:

migrationBuilder.DropForeignKey(
    name: "FK_Attachments_FileConfigs_FileId",
    table: "Attachments");

migrationBuilder.AlterColumn<Guid>(
    name: "FileId",
    table: "Attachments",
    nullable: false,
    oldClrType: typeof(Guid),
    oldNullable: true);

migrationBuilder.AddForeignKey(
    name: "FK_Attachments_FileConfigs_FileId",
    table: "Attachments",
    column: "FileId",
    principalTable: "FileConfigs",
    principalColumn: "Id",
    onDelete: ReferentialAction.Cascade);

AndriySvyryd on Jun 29, 2018

I will try to as I get free time but it would be great if some people in the community who have a good understanding of C# and a strong reliance on EF and MySQL could become core contributors. As #522 states I no longer use MySQL at my day job. It is always easier to debug this provider when you actively work on a big project that uses it, otherwise you don’t hit all of the edge cases. For the issues in 2.1-rc like this that affect small a handful of projects these bugs are much more easily fixed by those who are actively working in those projects.

2.1-rc actually passes many more of the EF core tests than ever before, so I’d consider it pretty stable in its current state. The Microsoft team did a lot of work to get it that way.

Fixing every single upgrade issue in migrations might be tough, as stated before you can easily add SQL to a migration by hand to fix columns and get your migrations to work. Lots of ORMs don’t have code first and I feel like with EF people forget that is an option sometimes. Sometimes hand patching the migration and moving on is the best course of action. Hopefully having the new EF functional tests in place would prevent this from happening in the future.

caleblloyd on Jun 28, 2018

Fixed in 2.1.0-rc2-final

caleblloyd on Jul 3, 2018

Ok, then I’ll send a PR to go back to the old behavior.

AndriySvyryd on Jul 2, 2018

Give a break to @caleblloyd he will work on this when he had some free time.

We are all waiting for the 2.1 final release.

YandyZaldivar on Jun 27, 2018

this no longer seems that supported I mean

This provider is community supported. It gets better when more people contribute and collaborate. See #522

The process for working on an issue like this would be to clone the repository, make the cloned repository a dependency of your project, and step-through debug the issue.

To work around a migration issue, you can easily add SQL to a migration by hand to fix columns. This issue should not be blocking.

caleblloyd on Jun 27, 2018