MySQL collation issue with Entity Framework Core

asp.net-core c# entity-framework-core mysql

Question

My asp.net core web application is hitting is MySQL database. I generate migration and update database, then I see the collation of table which is different on different machines. On my machine its latin1 - default collation and on a different machine, it is utf8 - default collation.

So due to this behavior, I'm getting an exception while inserting Japanese or some other language work like 日本語, Español México or 简体中文 into a table which has a varchar(64) column. It works fine on another machine which has utf8 - default collation on this table.

EF Core Version: 2.0.1

MySql: 5.7.22**

Is this EF Core issue? Or is there any workaround to generate the database with the proper collation or encoding?

1
0
5/24/2018 11:08:04 AM

Accepted Answer

It is not EF Core issue, if you have default collation configured in your MySql then that will be picked by MySql. So collation is database system configuration thing. Probably you have to change your default collation on your machine.

Other option is to add after creating table statement in migration:

Sql('alter table <some_table> convert to character set utf8 collate utf8_unicode_ci');
1
5/24/2018 11:22:59 AM

Popular Answer

In MySQL "latin1 - default collation" is not useful for inserting Unicode and non-English word(s), You need to use Utf8Mb4 collation (better version of utf8).

On the table you can set the table charset like this

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Here is a post that give details why utf8mb4 is better



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow