search

PomeloFoundation / Pomelo.EntityFrameworkCore.MySql

Entity Framework Core provider for MySQL and MariaDB built on top of MySqlConnector
MIT License
2.72k stars 386 forks source link

principalSchema dropped in creation of ForeignKey #1716

Open Trevortni opened 2 years ago

Trevortni commented 2 years ago

Steps to reproduce

Create a migration that calls CreateTable with constraints containing a ForeignKey, such that principalKey is set to a schema that exists in the database and contains the table and column specified in principalTable and principalColumn.

The issue

Foreign key cannot be added to the database, with the error message below.

On inspection, it appears that the problem is in DelimitIdentifier(string name, string schema) in MySqlSqlGeneratorHelper.cs, which calls base.DelimitIdentifier with the results of GetObjectName(name, schema) and GetSchemaName(name, schema); the first of which calls a SchemaNameTranslator if one is found, otherwise it drops the schema and just returns the name; and the latter of which just returns null. Either way, schema is simply ignored.

Exception message:
MySqlConnector.MySqlException
  HResult=0x80004005
  Message=Cannot add foreign key constraint
  Source=MySqlConnector
  StackTrace:
   at MySqlConnector.Core.ResultSet.<ReadResultSetHeaderAsync>d__2.MoveNext() in /_/src/MySqlConnector/Core/ResultSet.cs:line 44
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 127
   at MySqlConnector.MySqlDataReader.<CreateAsync>d__106.MoveNext() in /_/src/MySqlConnector/MySqlDataReader.cs:line 457
   at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext() in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.<ExecuteNonQueryAsync>d__78.MoveNext() in /_/src/MySqlConnector/MySqlCommand.cs:line 282
   at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 101
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) in /_/src/EFCore.Relational/Storage/RelationalCommand.cs:line 112
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues) in /_/src/EFCore.Relational/Migrations/MigrationCommand.cs:line 71
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection) in /_/src/EFCore.Relational/Migrations/Internal/MigrationCommandExecutor.cs:line 61
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) in /_/src/EFCore.Relational/Migrations/Internal/Migrator.cs:line 129
   at DSD_Web_API.Startup.<>c__DisplayClass7_1.<UpdateDatabase>b__2(Organization org) in C:\Users\lewisw\source\repos\dsd-api\DSD-Web-API\Startup.cs:line 233
   at System.Collections.Generic.List`1.ForEach(Action`1 action) in /_/src/libraries/System.Private.CoreLib/src/System/Collections/Generic/List.cs:line 579
   at DSD_Web_API.Startup.UpdateDatabase(IApplicationBuilder app) in C:\Users\lewisw\source\repos\dsd-api\DSD-Web-API\Startup.cs:line 204
   at DSD_Web_API.Startup.Configure(IApplicationBuilder app, IWebHostEnvironment env) in C:\Users\lewisw\source\repos\dsd-api\DSD-Web-API\Startup.cs:line 153
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Span`1& arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) in /_/src/coreclr/System.Private.CoreLib/src/System/Reflection/RuntimeMethodInfo.cs:line 435
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder) in /_/src/Hosting/Hosting/src/Internal/ConfigureBuilder.cs:line 32
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder) in /_/src/Hosting/Hosting/src/Internal/ConfigureBuilder.cs:line 21
   at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass15_0.<UseStartup>b__1(IApplicationBuilder app) in /_/src/Hosting/Hosting/src/GenericHost/GenericWebHostBuilder.cs:line 333
   at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder) in /_/src/Mvc/Mvc.Core/src/Filters/MiddlewareFilterBuilderStartupFilter.cs:line 23
   at Microsoft.AspNetCore.Server.IIS.Core.IISServerSetupFilter.<>c__DisplayClass2_0.<Configure>b__0(IApplicationBuilder app) in /_/src/Servers/IIS/IIS/src/Core/IISServerSetupFilter.cs:line 35
   at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app) in /_/src/DefaultBuilder/src/HostFilteringStartupFilter.cs:line 18
   at Microsoft.AspNetCore.Hosting.GenericWebHostService.<StartAsync>d__37.MoveNext() in /_/src/Hosting/Hosting/src/GenericHost/GenericWebHostedService.cs:line 105
   at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>d__12.MoveNext() in /_/src/libraries/Microsoft.Extensions.Hosting/src/Internal/Host.cs:line 68
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.<RunAsync>d__4.MoveNext() in /_/src/libraries/Microsoft.Extensions.Hosting.Abstractions/src/HostingAbstractionsHostExtensions.cs:line 64
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.<RunAsync>d__4.MoveNext() in /_/src/libraries/Microsoft.Extensions.Hosting.Abstractions/src/HostingAbstractionsHostExtensions.cs:line 76
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host) in /_/src/libraries/Microsoft.Extensions.Hosting.Abstractions/src/HostingAbstractionsHostExtensions.cs:line 51
   at DSD_Web_API.Program.Main(String[] args) in C:\Users\lewisw\source\repos\dsd-api\DSD-Web-API\Program.cs:line 14

Further technical details

MySQL version: 5.7.38 Operating system: Windows 10 Pomelo.EntityFrameworkCore.MySql version: 6.0.2 Microsoft.AspNetCore.App version: .NET 6.0

mguinness commented 2 years ago

See https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/pull/982 regarding schema options. This is also somewhat related to https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1192.

Trevortni commented 2 years ago

I've decided to explore the possibility of using the SchemaNameTranslator to output the concatenated schema and name: `schemaName`.`name`, but I run into the same issue I get if I try to include the schema in the name in the first place: the result of the SchemaNameTranslator gets passed to base.DelimitIdentifier, which replaces the internal backticks with doubled backticks.

mguinness commented 2 years ago

PR's are welcome and appreciated. A check could be made for identifier that is already delimited.

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/01c6941ea790796b4455bbb565ceb9dca835d5a1/src/EFCore.MySql/Storage/Internal/MySqlSqlGeneratorHelper.cs#L55-L61

Trevortni commented 2 years ago

Are you thinking that if it's already delimited, then we could safely assume that it's also already escaped?

Trevortni commented 2 years ago

I've found a solution here, but I'm having trouble getting my custom SqlGenerationHelper constructed - it can't resolve the RelationalSqlGenerationHelperDependencies service, and I'm having trouble figuring out what I'm supposed to do about that.