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

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Comments: 20 (1 by maintainers)

Most upvoted comments

Thanks a lot @lauxjpn ! Looking forward for 5.0.2 to test it out

@imalkevich I am unable to reproduce the issue with the following code and Pomelo 5.0.1:

Program.cs
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        
        [Required]
        [StringLength(20_000)]
        public string Name { get; set; }
    }
    
    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                const string connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1493";
                var serverVersion = ServerVersion.AutoDetect(connectionString);

                optionsBuilder.UseMySql(
                        connectionString,
                        serverVersion)
                    .UseLoggerFactory(
                        LoggerFactory.Create(
                            configure => configure
                                .AddConsole()
                                .AddFilter(level => level >= LogLevel.Information)))
                    .EnableSensitiveDataLogging()
                    .EnableDetailedErrors();
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }
    }

    internal static class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
        }
    }
}

It generates the following SQL output:

CREATE TABLE `IceCreams` (
    `IceCreamId` int NOT NULL AUTO_INCREMENT,
    `Name` longtext CHARACTER SET utf8mb4 NOT NULL,
    CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
) CHARACTER SET utf8mb4;

Please either change the sample code in a way, that reproduces the issue, or post the relevant parts of the original migration files (Up() method and related x.Design.cs file parts), so we can investigate this further.