Pomelo.EntityFrameworkCore.MySql: Create table script adds `CHARACTER SET utf8mb4` and this causes error

The issue

I have a project with I started with the previous version of Pomelo. Recently I migrated to version 5.0.1.

When I’m trying to make a fresh installation of the application, I’m getting incorrect syntax message for the following line:

Message longtext CHARACTER SET utf8mb4(2048) NOT NULL

If I manually remove CHARACTER SET and execute the command in Workbench, everything works smoothly.

I tried to following the following issue and apply the suggested fix of not specifying character set explicitly - https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1435 - but with no success…

Here is my Startup.cs configuration:

services.AddDbContext<ApplicationDbContext>(options =>
    options.UseMySql(
        Configuration.GetConnectionString("DefaultConnection"),
        new MariaDbServerVersion(new Version(10, 3))
    ),
    ServiceLifetime.Scoped);

Here is what I added in ApplicationDbContext.cs following the discussions:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    // this should have fixed issue and act similar to CharSetBehavior(CharSetBehavior.NeverAppend) #1435
    builder.HasCharSet(null, DelegationModes.ApplyToDatabases);
}

But I’m not able to fix the error - here is a stack trace that I’m getting:

2021-08-22 15:03:09.8549|20102|ERROR|Microsoft.EntityFrameworkCore.Database.Command|Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `Logs` (
    `Id` varchar(255) NOT NULL,
    `UserName` varchar(512) NULL,
    `UserIp` varchar(512) NULL,
    `Action` varchar(512) NOT NULL,
    `Status` varchar(512) NOT NULL,
    `AffectedUserName` varchar(512) NULL,
    `EntityId` varchar(512) NULL,
    `EntityIcon` varchar(512) NULL,
    `CardName` varchar(512) NULL,
    `RoomName` varchar(512) NULL,
    `AutomationName` varchar(512) NULL,
    `ObjectName` varchar(512) NULL,
    `Location` varchar(512) NULL,
    `Message` longtext CHARACTER SET utf8mb4(2048) NOT NULL,
    `CreatedDate` datetime(6) NOT NULL,
    `IsArchived` tinyint(1) NOT NULL,
    CONSTRAINT `PK_Logs` PRIMARY KEY (`Id`)
); |url: |action: 
2021-08-22 15:03:09.8821||ERROR|Iotics.Api.Program|Stopped program because of exception 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 '(2048) NOT NULL,
    `CreatedDate` datetime(6) NOT NULL,
    `IsArchived` tin...' at line 15
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 888
   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 135
   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 Iotics.Api.Program.Main(String[] args) in C:\MyProjects\iotics\Iotics.Admin\Iotics.Api\Program.cs:line 35|url: |action: 
Unhandled exception. 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 '(2048) NOT NULL,
    `CreatedDate` datetime(6) NOT NULL,
    `IsArchived` tin...' at line 15
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 888
   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 135
   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 Iotics.Api.Program.Main(String[] args) in C:\MyProjects\iotics\Iotics.Admin\Iotics.Api\Program.cs:line 35
   at Iotics.Api.Program.<Main>(String[] args)
/run.sh: line 28:   308 Aborted                 (core dumped) dotnet Iotics.Api.dll

Further technical details

MySQL version: Maria DB 10.3 Operating system: Ubuntu 20.04.3 Pomelo.EntityFrameworkCore.MySql version: 5.0.1