Cannot start Duende server in visual Studio with MySql

[penCsharpener]

Describe the bug

I always get the error Table 'skoruba-auth-server-duende.DataProtectionKeys' doesn't exist when trying to start the solution.

To Reproduce

• create project secrets.json of projects Admin. Admin.Api and STS.Identity
• configure to use MySql and set connection string (with empty db in MySql)
• set multiple startup projects in solution (Admin, Admin.Api, STS.Identity)
• run solution with Kestrel, not IIS

the same happens when attempting to first run STS.Identity, Admin.Api and Admin (in that order) via dotnet run

Relevant parts of the log file

2022-02-20 11:26:19.454 +01:00 [ERR] Failed executing DbCommand (36ms) [Parameters=[], CommandType='"Text"', CommandTimeout='30']
SELECT `d`.`Id`, `d`.`FriendlyName`, `d`.`Xml`
FROM `DataProtectionKeys` AS `d`
2022-02-20 11:26:19.528 +01:00 [ERR] An exception occurred while iterating over the results of a query for context type 'Skoruba.Duende.IdentityServer.Admin.EntityFramework.Shared.DbContexts.IdentityServerDataProtectionDbContext'.
MySqlConnector.MySqlException (0x80004005): Table 'skoruba-auth-server-duende.DataProtectionKeys' doesn't exist
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) 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(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 456
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 330
   at MySqlConnector.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in /_/src/MySqlConnector/MySqlCommand.cs:line 272
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
MySqlConnector.MySqlException (0x80004005): Table 'skoruba-auth-server-duende.DataProtectionKeys' doesn't exist
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) 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(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 456
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 330
   at MySqlConnector.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in /_/src/MySqlConnector/MySqlCommand.cs:line 272
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
2022-02-20 11:26:19.535 +01:00 [ERR] An error occurred while reading the key ring.
MySqlConnector.MySqlException (0x80004005): Table 'skoruba-auth-server-duende.DataProtectionKeys' doesn't exist
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) 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(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 456
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 330
   at MySqlConnector.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in /_/src/MySqlConnector/MySqlCommand.cs:line 272
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.AspNetCore.DataProtection.EntityFrameworkCore.EntityFrameworkCoreXmlRepository`1.<GetAllElements>g__GetAllElementsCore|3_0()+MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Microsoft.AspNetCore.DataProtection.EntityFrameworkCore.EntityFrameworkCoreXmlRepository`1.GetAllElements()
   at Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager.GetAllKeys()
   at Microsoft.AspNetCore.DataProtection.KeyManagement.KeyRingProvider.CreateCacheableKeyRingCore(DateTimeOffset now, IKey keyJustAdded)
   at Microsoft.AspNetCore.DataProtection.KeyManagement.KeyRingProvider.Microsoft.AspNetCore.DataProtection.KeyManagement.Internal.ICacheableKeyRingProvider.GetCacheableKeyRing(DateTimeOffset now)
   at Microsoft.AspNetCore.DataProtection.KeyManagement.KeyRingProvider.GetCurrentKeyRingCore(DateTime utcNow, Boolean forceRefresh)

[penCsharpener]

as a relevant side note the same works with your IS4 solution

[skoruba]

Hello, can you please check that tables for dataproctection is created in the db? :) Thanks

[penCsharpener]

no, the table is not there. When I start with an empty db and do dotnet run in the .Admin project I get this error.

2022-02-22 21:50:19.841 +01:00 [ERR] Failed executing DbCommand (0ms) [Parameters=[], CommandType='"Text"', CommandTimeout='30']
ALTER TABLE `IdentityResourceProperties` DROP INDEX `IX_IdentityResourceProperties_IdentityResourceId`;
2022-02-22 21:50:19.872 +01:00 [FTL] Host terminated unexpectedly
MySqlConnector.MySqlException (0x80004005): Cannot drop index 'IX_IdentityResourceProperties_IdentityResourceId': needed in a foreign key constraint
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 956
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 44
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 108
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 456
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 282
   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 Skoruba.Duende.IdentityServer.Admin.EntityFramework.Shared.Helpers.DbMigrationHelpers.EnsureDatabasesMigratedAsync[TIdentityDbContext,TConfigurationDbContext,TPersistedGrantDbContext,TLogDbContext,TAuditLogDbContext,TDataProtectionDbContext](IServiceProvider services) in D:\projects\github.com\skoruba\Duende.IdentityServer.Admin\src\Skoruba.Duende.IdentityServer.Admin.EntityFramework.Shared\Helpers\DbMigrationHelpers.cs:line 95
   at Skoruba.Duende.IdentityServer.Admin.EntityFramework.Shared.Helpers.DbMigrationHelpers.ApplyDbMigrationsWithDataSeedAsync[TIdentityServerDbContext,TIdentityDbContext,TPersistedGrantDbContext,TLogDbContext,TAuditLogDbContext,TDataProtectionDbContext,TUser,TRole](IHost host, Boolean applyDbMigrationWithDataSeedFromProgramArguments, SeedConfiguration seedConfiguration, DatabaseMigrationsConfiguration databaseMigrationsConfiguration) in D:\projects\github.com\skoruba\Duende.IdentityServer.Admin\src\Skoruba.Duende.IdentityServer.Admin.EntityFramework.Shared\Helpers\DbMigrationHelpers.cs:line 54
   at Skoruba.Duende.IdentityServer.Admin.Program.ApplyDbMigrationsWithDataSeedAsync(String[] args, IConfiguration configuration, IHost host) in D:\projects\github.com\skoruba\Duende.IdentityServer.Admin\src\Skoruba.Duende.IdentityServer.Admin\Program.cs:line 80
   at Skoruba.Duende.IdentityServer.Admin.Program.Main(String[] args) in D:\projects\github.com\skoruba\Duende.IdentityServer.Admin\src\Skoruba.Duende.IdentityServer.Admin\Program.cs:line 39

[skoruba]

Honestly, I do not know where is the issue 😁 what version of mysql do you have? I tried it with MySql database and it works. Maybe you can try to delete default mysql db migrations and generate them from scratch. Give me please feedback if you find the solution. Thanks!

[longletian]

me too

[gepa21]

had the same issue too, deleting and regenerating migrations "fixed" it. (using MySQL 8.0.22)

[skoruba]

OK, thanks for reporting this issue, do you know where is the issue?

[longletian]

delete code

[nighttiger1990]

I got same when using MySql DB. Version 8.0.27 When using each database connection for each context I got issue at TST Project which don't know DataProtectionDB

Solved as same as @gepa21 deleting migrations then regenerating them

1./ Delete Migrations folder in ${projectName}.Admin.EntityFramework.MySql 2./ Run these command - change ${projectName} to your_real_project_name

cd ${projectName}/src/${projectName}.Admin
dotnet ef migrations add DBInit -c AdminIdentityDbContext -o Migrations\Identity -p ..\${projectName}.Admin.EntityFramework.MySql\${projectName}.Admin.EntityFramework.MySql.csproj
dotnet ef migrations add DBInit -c AdminLogDbContext -o Migrations\Logging -p ..\${projectName}.Admin.EntityFramework.MySql\${projectName}.Admin.EntityFramework.MySql.csproj
dotnet ef migrations add DBInit -c IdentityServerConfigurationDbContext -o Migrations\IdentityServerConfiguration -p ..\${projectName}.Admin.EntityFramework.MySql\${projectName}.Admin.EntityFramework.MySql.csproj
dotnet ef migrations add DBInit -c IdentityServerPersistedGrantDbContext -o Migrations\IdentityServerGrants -p ..\${projectName}.Admin.EntityFramework.MySql\${projectName}.Admin.EntityFramework.MySql.csproj
dotnet ef migrations add DBInit -c AdminAuditLogDbContext -o Migrations\AuditLogging -p ..\${projectName}.Admin.EntityFramework.MySql\${projectName}.Admin.EntityFramework.MySql.csproj
dotnet ef migrations add DBInit -c IdentityServerDataProtectionDbContext -o Migrations\DataProtection -p ..\${projectName}.Admin.EntityFramework.MySql\${projectName}.Admin.EntityFramework.MySql.csproj

[pegan23]

I too found issues with the 20220123140746_UpdateToIS61.cs migration MySql 8.0.29 .. Duende Admin v6.1.0

After some digging I was able to resolve most of the issues.

1. The CREATE INDEX migrations must be moved ahead of the associated DROP INDEX migrations

            migrationBuilder.CreateIndex(
                name: "IX_IdentityResourceProperties_IdentityResourceId_Key",
                table: "IdentityResourceProperties",
                columns: new[] { "IdentityResourceId", "Key" },
                unique: true);

            migrationBuilder.DropIndex(
                name: "IX_IdentityResourceProperties_IdentityResourceId",
                table: "IdentityResourceProperties");

MySql does not allow deletion of the indexes as they are associated with the Foreign Key Adding the new index before removing the old resolves this issue.

2. there are two indexes that are not valid for MySql a) IX_ClientRedirectUris_ClientId_RedirectUri b) IX_ClientPostLogoutRedirectUris_ClientId_PostLogoutRedirectUri

Both of these items index on ClientId, then a URL field that is 2000 chars These fail due to an index limit of 3072 .. I have to assume that the URL field is unicode, meaning a total of 4000 bytes for the URL.

Commenting out both the CREATE and DELETE for these the indexes related to these allowed the migration to complete

            // migrationBuilder.CreateIndex(
            //    name: "IX_ClientRedirectUris_ClientId_RedirectUri",
            //    table: "ClientRedirectUris",
            //    columns: new[] { "ClientId", "RedirectUri" },
            //    unique: true);

            // migrationBuilder.DropIndex(
            //    name: "IX_ClientRedirectUris_ClientId",
            //    table: "ClientRedirectUris");

[bruceharrison1984]

@nighttiger1990 Regenerating the MySQL migrations worked for me. Thanks!

[skoruba]

I fixed the migration for MySql in the 1.2.0 release, thanks for your feedback guys .👍🏼