MySQL /seed trouble (#837)

[aldinei-sampaio]

Describe the bug

I had the same problem described at issue #837, but in MySql running on Windows.

I just want to share that I worked around the problem changing the MySQL migrations for the IdentityServerConfiguration DbContext. Just looked for "4000" inside the migration files and replaced it for "2147483647".

[skoruba]

Thanks for reporting this, I will take a look at this. 😊

[eleionomai]

Same issue here with length declaration of the utf8mb4 character set using MariaDB 10.3.28.

# 20201108173253_UpdateIdentityServerToVersion4.cs 159
Value = table.Column<string>(maxLength: 4000, nullable: false),

[09:59:08 ERR] Failed executing DbCommand (511ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `ApiResourceSecrets` (
    `Id` int NOT NULL AUTO_INCREMENT,
    `Description` varchar(1000) NULL,
    `Value` longtext CHARACTER SET utf8mb4(4000) NOT NULL,
    `Expiration` datetime(6) NULL,
    `Type` varchar(250) NOT NULL,
    `Created` datetime(6) NOT NULL,
    `ApiResourceId` int NOT NULL,
    CONSTRAINT `PK_ApiResourceSecrets` PRIMARY KEY (`Id`),
    CONSTRAINT `FK_ApiResourceSecrets_ApiResources_ApiResourceId` FOREIGN KEY (`ApiResourceId`) REFERENCES `ApiResources` (`Id`) ON DELETE CASCADE
);
[09:59:09 FTL] Host terminated unexpectedly
MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(4000) NOT NULL,
    `Expiration` datetime(6) NULL,
    `Type` varchar(250)...' at line 4
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 877
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 50
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 119
   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 444
   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 266
   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.IdentityServer4.Admin.EntityFramework.Shared.Helpers.DbMigrationHelpers.EnsureDatabasesMigratedAsync[TIdentityDbContext,TConfigurationDbContext,TPersistedGrantDbContext,TLogDbContext,TAuditLogDbContext,TDataProtectionDbContext](IServiceProvider services) in C:\Identity-Admin.API\src\Skoruba.IdentityServer4.Admin.EntityFramework.Shared\Helpers\DbMigrationHelpers.cs:line 82
   at Skoruba.IdentityServer4.Admin.EntityFramework.Shared.Helpers.DbMigrationHelpers.ApplyDbMigrationsWithDataSeedAsync[TIdentityServerDbContext,TIdentityDbContext,TPersistedGrantDbContext,TLogDbContext,TAuditLogDbContext,TDataProtectionDbContext,TUser,TRole](IHost host, Boolean applyDbMigrationWithDataSeedFromProgramArguments, SeedConfiguration seedConfiguration, DatabaseMigrationsConfiguration databaseMigrationsConfiguration) in C:\Identity-Admin.API\src\Skoruba.IdentityServer4.Admin.EntityFramework.Shared\Helpers\DbMigrationHelpers.cs:line 49
   at Skoruba.IdentityServer4.Admin.Program.ApplyDbMigrationsWithDataSeedAsync(String[] args, IConfiguration configuration, IHost host) in C:\Identity-Admin.API\src\Skoruba.IdentityServer4.Admin\Program.cs:line 59
   at Skoruba.IdentityServer4.Admin.Program.Main(String[] args) in C:\Identity-Admin.API\src\Skoruba.IdentityServer4.Admin\Program.cs:line 36

[bravecobra]

As I was testing my helm charts for kubernetes adding support for other database, I noticed the same issue. The above trace is exactly the same. The used version is 2.0.1

[bravecobra]

Any progress on this?

[tbto]

It's may be Pomelo.EntityFrameworkCore.MySql bug, @bravecobra @skoruba See: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1461

[skoruba]

Hey guys, thanks for reporting this issue I will take a look at this. thanks @tbto for your suggestion.

[bitai-cs]

The problem is caused by the following line in the migration scripts: "longtext CHARACTER SET utf8mb4(4000)" The data type 'longtext' should not be assigned a specific length.

I overcame the problem by removing the HasMaxLength (4000) from the migration scripts.