Pomelo.entityframeworkcore.mysql: error unicode when get from database

[marknguyen85]

in startup file

services.AddDbContextPool<BackendContext>(options =>
                options.UseMySql(
                    sqlConnectionString,
                    b => {
                        b.MigrationsAssembly("xxx");
                        b.UnicodeCharSet(CharSet.Utf8mb4);
                    }

                )
            );

in connectstring

server=x.x.x.x;userid=x;password=x;database=xxxx;CharSet=utf8mb4;

but result get from database
from Người dùng
to Ng??i d�ng

in database i was setting charset utf8 and when i try set utf8mb4 then get error
ERROR 1115: Unknown character set: 'utf8mb4'

why in enum Charset not contain utf8

[mguinness]

A couple of points:

• Regarding connection string, the CharacterSet option is hardcoded to utf8mb4.
• Charset utf8 (alias utf8mb3) only stores 3 bytes per code point instead of 4 bytes with utf8mb4 which is recommended.

Is your MySQL version greater than 5.5.3? This question is asked in the new issue template for a reason.

[marknguyen85]

thanks @mguinness
version of mysql i'm using is 5.1 and not valid utf8mb4 so only use utf8

[mguinness]

In that case you'll have to use CharSet.Utf8mb3 instead of CharSet.Utf8mb4 as the latter isn't supported by your version of MySQL.

[bgrainger]

MySqlConnector now automatically falls back to utf8 if utf8mb4 isn't supported: https://github.com/mysql-net/MySqlConnector/commit/6d3b2445d97de0471543edb954ce6d8ebd949788

[brunobertechini]

I'm trying to upgrade to Dotnet core 3.0 and Pomelo.EntityFrameworkCore.MySql Version="3.0.0". My mysql database version is 5.1.73. I can't upgrade the database version. And I'm trying to overcome this error.

I already tried to update the connection string and the AddDbContext line with no luck.

I have tried CharSet.Utf8mb4 and CharSet.Utf8.

The difference I see is that I don't have the extension method UnicodeCharSet, only CharSet.

Any clues?

[mguinness]

EF Core 3.0 is EOL, I suggest you use 3.1 and the Configuration Options should be available.

[brunobertechini]

I have plans to move to 3.1, but I can't at this point.

Are you saying only 3.1 will fix the issue? I have configured everything as suggested here, no luck at all.
mysq 5.1.73.

I gave up and will try to upgrade to mysql-5.5 which has utf8mb4

It's complicated when the solution is always "upgrade" and you have to maintain legacy systems. Not everybody can upgrade that easily.

[lauxjpn]

Regarding MySQL:

My mysql database version is 5.1.73.

The 5.1 version is over 12 years old and reached its end-of-life on December 31, 2013.

I gave up and will try to upgrade to mysql-5.5 which has utf8mb4

The 5.5 version is over 9 years old and reached its end-of-life on December 3, 2018.

You should upgrade to either 8.0.19, which will reach its end-of-life in April, 2026 or to 5.7, which will reach its end-of-life on October 21, 2023.

Pomelo officially supports the last two major versions (though other versions will generally work as well for the most part).

Regarding .NET Core

I'm trying to upgrade to Dotnet core 3.0

EF Core 3.0 is EOL, I suggest you use 3.1

As @mguinness already said, .NET Core 3.0 reached already its end-of-life on March 3, 2020. The LTS version is .NET Core 3.1, which will reach its end-of-life on December 3, 2022.

So you should upgrade to the latest version of .NET Core 3.1 (currently 3.1.5).

If you have to upgrade to .NET Core 3.0 and cannot upgrade to .NET Core 3.1, we would be interested to know, what is blocking you from doing so.

Regarding EF Core and Pomelo

The latest version of Pomelo is 3.1.1, that is compatible with EF Core 3.1 (use the latest patch release). Both support .NET Standard 2.0, so they can be used together with .NET Core 2.0+. That means, that even if you have to use .NET Core 3.0 for some reason, you can still use EF Core 3.1 and Pomelo 3.1.1.

Regarding char set problems

Pomelo 3.1.1 has extended char set (and collation) support. There are multiple mechanisms to specify what char set to use (e.g. you can specify a charset for a column).