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
Is this EF Core issue? Or is there any workaround to generate the database with the proper collation or encoding?
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');
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