MySQL Migrations error

[Alik2015]

Steps to reproduce using mysql 8+.

Clone master branch and set values in appsettings.json storageconfiguration to MYSQL and connection string.

dotnet ef migrations add 210224 --project ..\SimpleIdServer.IdServer.MySQLMigrations\SimpleIdServer.IdServer.MySQLMigrations.csproj -- works fine and creates migrations

dotnet ef database update seems create most of the db but then fails with the following.

Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE `AuthenticationContextClassReferenceRealm` DROP FOREIGN KEY `FK_AuthenticationContextClassReferenceRealm_Acrs_Authenticatio~`;

Can't DROP 'FK_AuthenticationContextClassReferenceRealm_Acrs_Authenticatio~'; check that column/key exists

This is as far as it gets.

[Alik2015]

Found a possible solution is to replace Mysql.EntityFramework in this file project file https://github.com/simpleidserver/SimpleIdServer/blob/master/src/IdServer/SimpleIdServer.IdServer.MySQLMigrations/SimpleIdServer.IdServer.MySQLMigrations.csproj with Pomelo.EntityFrameworkCore.MySql version 7.0.0.

If you could make this change in master branch it would fix the db creation as it seems Pomelo handles the migration creation process better than Mysql.EF.

[simpleidserver]

Hello,

I have updated all the names of the foreign keys in the '20231117143633_InitMySQL' file, and the issue is now resolved.

Before generating the EF migration script, could you please rename all the foreign key names in the database?

Old	New
FK_AuthenticationSchemeProviderMapper_AuthenticationSchemeProvi~	FK_AuthenticationSchemeProviderMapper_AuthenticationSchemeProv~
FK_AuthenticationSchemeProviderRealm_AuthenticationSchemeProvid~	FK_AuthenticationSchemeProviderRealm_AuthenticationSchemeProvi~
FK_AuthenticationSchemeProviders_AuthenticationSchemeProviderDe~	FK_AuthenticationSchemeProviders_AuthenticationSchemeProviderD~
FK_CertificateAuthorityRealm_CertificateAuthorities_Certificate~	FK_CertificateAuthorityRealm_CertificateAuthorities_Certificat~
FK_ClientCertificate_CertificateAuthorities_CertificateAuthorit~	FK_ClientCertificate_CertificateAuthorities_CertificateAuthori~
FK_ConfigurationDefinitionRecord_Definitions_ConfigurationDefin~	FK_ConfigurationDefinitionRecord_Definitions_ConfigurationDefi~
FK_ConfigurationDefinitionRecordTranslation_ConfigurationDefini~	FK_ConfigurationDefinitionRecordTranslation_ConfigurationDefin~
FK_ConfigurationDefinitionRecordTranslation_Translations_Transl~	FK_ConfigurationDefinitionRecordTranslation_Translations_Trans~
FK_ConfigurationDefinitionRecordValue_ConfigurationDefinitionRe~	FK_ConfigurationDefinitionRecordValue_ConfigurationDefinitionR~
FK_ConfigurationDefinitionRecordValueTranslation_ConfigurationD~	FK_ConfigurationDefinitionRecordValueTranslation_Configuration~
FK_ConfigurationDefinitionRecordValueTranslation_Translations_T~	FK_ConfigurationDefinitionRecordValueTranslationTranslations~
FK_IdentityProvisioningHistory_IdentityProvisioningLst_Identity~	FK_IdentityProvisioningHistory_IdentityProvisioningLst_Identit~
FK_IdentityProvisioningLst_IdentityProvisioningDefinitions_Defi~	FK_IdentityProvisioningLst_IdentityProvisioningDefinitions_Def~
FK_IdentityProvisioningMappingRule_IdentityProvisioningDefiniti~	FK_IdentityProvisioningMappingRule_IdentityProvisioningDefinit~
FK_IdentityProvisioningRealm_IdentityProvisioningLst_IdentityPr~	FK_IdentityProvisioningRealm_IdentityProvisioningLst_IdentityP~
FK_RealmSerializedFileKey_SerializedFileKeys_SerializedFileKeys~	FK_RealmSerializedFileKey_SerializedFileKeys_SerializedFileKey~
FK_UMAPermissionTicketRecord_UMAPermissionTicket_UMAPermissionT~	FK_UMAPermissionTicketRecord_UMAPermissionTicket_UMAPermission~
FK_UMAResourcePermissionClaim_UMAResourcePermission_UMAResource~	FK_UMAResourcePermissionClaim_UMAResourcePermission_UMAResourc~
FK_ConfigurationDefinitionRecordValueTranslation_ConfigurationD~	FK_ConfigurationDefinitionRecordValueTranslation_Configuration~

Once they are renamed, you can generate the migration script and apply it :)

[Alik2015]

I would still suggest using the pomelo package. As I found it does not cause the issue. On 21 Feb 2024 15:41, SimpleIdServer @.***> wrote: Hello, I have updated all the names of the foreign keys in the '20231117143633_InitMySQL' file, and the issue is now resolved. Before generating the EF migration script, could you please rename all the foreign key names in the database? OldNewFK_AuthenticationSchemeProviderMapper_AuthenticationSchemeProvi~FK_AuthenticationSchemeProviderMapper_AuthenticationSchemeProv~FK_AuthenticationSchemeProviderRealm_AuthenticationSchemeProvid~FK_AuthenticationSchemeProviderRealm_AuthenticationSchemeProvi~FK_AuthenticationSchemeProviders_AuthenticationSchemeProviderDe~FK_AuthenticationSchemeProviders_AuthenticationSchemeProviderD~FK_CertificateAuthorityRealm_CertificateAuthorities_Certificate~FK_CertificateAuthorityRealm_CertificateAuthorities_Certificat~FK_ClientCertificate_CertificateAuthorities_CertificateAuthorit~FK_ClientCertificate_CertificateAuthorities_CertificateAuthori~FK_ConfigurationDefinitionRecord_Definitions_ConfigurationDefin~FK_ConfigurationDefinitionRecord_Definitions_ConfigurationDefi~FK_ConfigurationDefinitionRecordTranslation_ConfigurationDefini~FK_ConfigurationDefinitionRecordTranslation_ConfigurationDefin~FK_ConfigurationDefinitionRecordTranslation_Translations_Transl~FK_ConfigurationDefinitionRecordTranslation_Translations_Trans~FK_ConfigurationDefinitionRecordValue_ConfigurationDefinitionRe~FK_ConfigurationDefinitionRecordValue_ConfigurationDefinitionR~FK_ConfigurationDefinitionRecordValueTranslation_ConfigurationD~FK_ConfigurationDefinitionRecordValueTranslation_Configuration~FK_ConfigurationDefinitionRecordValueTranslation_Translations_T~FK_ConfigurationDefinitionRecordValueTranslationTranslations~FK_IdentityProvisioningHistory_IdentityProvisioningLst_Identity~FK_IdentityProvisioningHistory_IdentityProvisioningLst_Identit~FK_IdentityProvisioningLst_IdentityProvisioningDefinitions_Defi~FK_IdentityProvisioningLst_IdentityProvisioningDefinitions_Def~FK_IdentityProvisioningMappingRule_IdentityProvisioningDefiniti~FK_IdentityProvisioningMappingRule_IdentityProvisioningDefinit~FK_IdentityProvisioningRealm_IdentityProvisioningLst_IdentityPr~FK_IdentityProvisioningRealm_IdentityProvisioningLst_IdentityP~FK_RealmSerializedFileKey_SerializedFileKeys_SerializedFileKeys~FK_RealmSerializedFileKey_SerializedFileKeys_SerializedFileKey~FK_UMAPermissionTicketRecord_UMAPermissionTicket_UMAPermissionT~FK_UMAPermissionTicketRecord_UMAPermissionTicket_UMAPermission~FK_UMAResourcePermissionClaim_UMAResourcePermission_UMAResource~FK_UMAResourcePermissionClaim_UMAResourcePermission_UMAResourc~FK_ConfigurationDefinitionRecordValueTranslation_ConfigurationD~FK_ConfigurationDefinitionRecordValueTranslation_Configuration~ Once they are renamed, you can generate the migration script and apply it :)

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: @.***>

[simpleidserver]

I made the change and used the NuGet package Pomelo.EntityFrameworkCore.MySql.