Migration for changing Key Column (along with foreign keys attached to it) fails on removing/adding fk constraints

ahmadalli commented 3 years ago

I'm sorry that I'm reporting two problems in a single issue. I was trying to reproduce a problem but I faced another one which was stopping me to reproduce the main problem. So I'm going to explain both of them

Steps to reproduce

here's my example code

The issue

If you run the sample project I provided (it's runnable on docker-compose), you'll face this exception when StartupHostedService tries to run await dbContext.Database.MigrateAsync(cancellationToken);.

Exception message:

this is the exception you get when you run the project

MySqlConnector.MySqlException: 'Can't DROP 'FK_Children_Parents_ParentId'; check that column/key exists

Stack trace:

   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 131
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 436
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 60
   at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 264
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at FKTest.Services.HostedServices.StartupHostedService.StartAsync(CancellationToken cancellationToken) in E:\tmp\FKTest\FKTest\Services\HostedServices\StartupHostedService.cs:line 35
   at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host)
   at FKTest.Program.Main(String[] args) in E:\tmp\FKTest\FKTest\Program.cs:line 15

I guess it's happening when ef is trying to run this part of the migration

https://github.com/ahmadalli/PomeloMysqlFKTest/blob/e432af74d8ad3ef0c964dc376a6a3698ea0993e4/FKTest/Migrations/20201227210928_ChangeIdColumn.cs#L17-L19

and it makes sense since FK_Children_Parents_ParentId has not been created yet, The migration should delete FK_Children_Parents_ParentCode instead.

The Main Problem

I was facing another issue on my project and I wanted to report that (with similar section on migration code) but I faced this issue first. The issue I'm facing on another project is that the migration fails because it tries to run this query which obviously fails because there's no ParentId column in Parents table:

ALTER TABLE `Children` ADD CONSTRAINT `FK_Children_Parents_ParentId` FOREIGN KEY (`ParentId`) REFERENCES `Parents` (`ParentId`) ON DELETE CASCADE;

I was looking for the code that generates this query but I couldn't find it but I found the code that's supposed to test it:

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/c8647fc801397185faa429878d361c949826cf26/test/EFCore.MySql.FunctionalTests/MigrationSqlGeneratorMySqlTest.MySql.cs#L49-L54

But I think these lines doesn't cover all the corner cases since it the LicensePlateNumber is the name of foreign key column and also the name of primary column on the reference table.

Further technical details

MySQL version: 5.7 Operating system: Windows 10 Pomelo.EntityFrameworkCore.MySql version: 5.0.0-alpha.2 Microsoft.AspNetCore.App version: 5.0.1

lauxjpn commented 3 years ago

@ahmadalli Thanks for reporting! The issue is connected to the fix of #678. For more information, take a look at the latest comment over there or at the PR with the fix for your issue here.

Once we have merged #1282, feel free to use the latest nightly build containing the fix.

ahmadalli commented 3 years ago

Thanks :) I'm looking forward to it

PomeloFoundation / Pomelo.EntityFrameworkCore.MySql