Incorrect string value: '\xD8\xA7\xD9\x84\xD8\xB9...' for column 'DisplayName' at row 1

Question

I'm getting the below exception when running server-side code.

FATAL 2018-04-25 14:41:59,757 [1    ] Abp.AbpBootstrapper             
- Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. --->
MySql.Data.MySqlClient.MySqlException: Incorrect string value:
'\xD8\xA7\xD9\x84\xD8\xB9...' for column 'DisplayName' at row 1 --->
MySql.Data.MySqlClient.MySqlException: Incorrect string value:
'\xD8\xA7\xD9\x84\xD8\xB9...' for column 'DisplayName' at row 1    at
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
task)    at
System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
at
MySqlConMyCompanytor.Core.ResultSet.<ReadResultSetHeaderAsync>d__1.MoveNext()
in
C:\projects\mysqlconMyCompanytor\src\MySqlConMyCompanytor\Core\ResultSet.cs:line
43    --- End of inner exception stack trace ---    at
MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet
resultSet) in
C:\projects\mysqlconMyCompanytor\src\MySqlConMyCompanytor\MySql.Data.MySqlClient\MySqlDataReader.cs:line
92    at
MySql.Data.MySqlClient.MySqlDataReader.<ReadFirstResultSetAsync>d__65.MoveNext()
in
C:\projects\mysqlconMyCompanytor\src\MySqlConMyCompanytor\MySql.Data.MySqlClient\MySqlDataReader.cs:line
297
--- End of stack trace from previous location where exception was thrown ---    at
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
task)    at
MySql.Data.MySqlClient.MySqlDataReader.<CreateAsync>d__64.MoveNext()
in
C:\projects\mysqlconMyCompanytor\src\MySqlConMyCompanytor\MySql.Data.MySqlClient\MySqlDataReader.cs:line
287
--- End of stack trace from previous location where exception was thrown ---    at
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
task)    at
MySqlConMyCompanytor.Core.TextCommandExecutor.<ExecuteReaderAsync>d__3.MoveNext()
in
C:\projects\mysqlconMyCompanytor\src\MySqlConMyCompanytor\Core\TextCommandExecutor.cs:line
70
--- End of stack trace from previous location where exception was thrown ---    at
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
task)    at
MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior
behavior) in
C:\projects\mysqlconMyCompanytor\src\MySqlConMyCompanytor\MySql.Data.MySqlClient\MySqlCommand.cs:line
172    at
Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.<ExecuteAsync>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---    at
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
task)    at
Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.Execute(IRelationalConMyCompanytion
conMyCompanytion, DbCommandMethod executeMethod, IReadOnlyDictionary`2
parameterValues)    at
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConMyCompanytion
conMyCompanytion, IReadOnlyDictionary`2 parameterValues)    at
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConMyCompanytion
conMyCompanytion)    --- End of inner exception stack trace ---    at
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConMyCompanytion
conMyCompanytion)    at
Microsoft.EntityFrameworkCore.Update.Internal.MySqlBatchExecutor.Execute(IEnumerable`1
commandBatches, IRelationalConMyCompanytion conMyCompanytion)    at
Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1
entries)    at
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1
entriesToSave)    at
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean
acceptAllChangesOnSuccess)    at
Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean
acceptAllChangesOnSuccess)    at
Abp.EntityFrameworkCore.AbpDbContext.SaveChanges() in
D:\Github\aspnetboilerplate\src\Abp.EntityFrameworkCore\EntityFrameworkCore\AbpDbContext.cs:line
198    at
Abp.Zero.EntityFrameworkCore.AbpZeroCommonDbContext`3.SaveChanges() in
D:\Github\aspnetboilerplate\src\Abp.ZeroCore.EntityFrameworkCore\Zero\EntityFrameworkCore\AbpZeroCommonDbContext.cs:line
154    at
MyCompany.MyProject.EntityFrameworkCore.Seed.Host.DefaultLanguagesCreator.AddLanguageIfNotExists(ApplicationLanguage
language) in
C:\Users\MyName\Source\Workspaces\Workspace\FoodKioskSolution\MyCompany.MyProject\src\MyCompany.MyProject.EntityFrameworkCore\EntityFrameworkCore\Seed\Host\DefaultLanguagesCreator.cs:line
59    at
MyCompany.MyProject.EntityFrameworkCore.Seed.Host.DefaultLanguagesCreator.CreateLanguages()
in
C:\Users\MyName\Source\Workspaces\Workspace\FoodKioskSolution\MyCompany.MyProject\src\MyCompany.MyProject.EntityFrameworkCore\EntityFrameworkCore\Seed\Host\DefaultLanguagesCreator.cs:line
47    at
MyCompany.MyProject.EntityFrameworkCore.Seed.Host.DefaultLanguagesCreator.Create()
in
C:\Users\MyName\Source\Workspaces\Workspace\FoodKioskSolution\MyCompany.MyProject\src\MyCompany.MyProject.EntityFrameworkCore\EntityFrameworkCore\Seed\Host\DefaultLanguagesCreator.cs:line
40    at
MyCompany.MyProject.EntityFrameworkCore.Seed.Host.InitialHostDbBuilder.Create()
in
C:\Users\MyName\Source\Workspaces\Workspace\FoodKioskSolution\MyCompany.MyProject\src\MyCompany.MyProject.EntityFrameworkCore\EntityFrameworkCore\Seed\Host\InitialHostDbBuilder.cs:line
15    at
MyCompany.MyProject.EntityFrameworkCore.Seed.SeedHelper.SeedHostDb(MyProjectDbContext
context) in
C:\Users\MyName\Source\Workspaces\Workspace\FoodKioskSolution\MyCompany.MyProject\src\MyCompany.MyProject.EntityFrameworkCore\EntityFrameworkCore\Seed\SeedHelper.cs:line
25    at
MyCompany.MyProject.EntityFrameworkCore.Seed.SeedHelper.WithDbContext[TDbContext](IIocResolver
iocResolver, Action`1 contextAction) in
C:\Users\MyName\Source\Workspaces\Workspace\FoodKioskSolution\MyCompany.MyProject\src\MyCompany.MyProject.EntityFrameworkCore\EntityFrameworkCore\Seed\SeedHelper.cs:line
41    at
MyCompany.MyProject.EntityFrameworkCore.Seed.SeedHelper.SeedHostDb(IIocResolver
iocResolver) in
C:\Users\MyName\Source\Workspaces\Workspace\FoodKioskSolution\MyCompany.MyProject\src\MyCompany.MyProject.EntityFrameworkCore\EntityFrameworkCore\Seed\SeedHelper.cs:line
17    at
MyCompany.MyProject.EntityFrameworkCore.MyProjectEntityFrameworkModule.PostInitialize()
in
C:\Users\MyName\Source\Workspaces\Workspace\FoodKioskSolution\MyCompany.MyProject\src\MyCompany.MyProject.EntityFrameworkCore\EntityFrameworkCore\MyProjectEntityFrameworkModule.cs:line
46    at System.Collections.Generic.List`1.ForEach(Action`1 action)   
at Abp.AbpBootstrapper.Initialize() in
D:\Github\aspnetboilerplate\src\Abp\AbpBootstrapper.cs:line 155

Exception is in file DefaultLanguagesCreator.cs:

using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Abp.Localization;

namespace MyCompany.MyProject.EntityFrameworkCore.Seed.Host
{
    public class DefaultLanguagesCreator
    {
        public static List<ApplicationLanguage> InitialLanguages => GetInitialLanguages();

        private readonly SparDbContext _context;

        private static List<ApplicationLanguage> GetInitialLanguages()
        {
            return new List<ApplicationLanguage>
            {
                new ApplicationLanguage(null, "en", "English", "famfamfam-flags gb"),
                new ApplicationLanguage(null, "ar", "???????", "famfamfam-flags sa"),
                new ApplicationLanguage(null, "de", "German", "famfamfam-flags de"),
                new ApplicationLanguage(null, "it", "Italiano", "famfamfam-flags it"),
                new ApplicationLanguage(null, "fr", "Français", "famfamfam-flags fr"),
                new ApplicationLanguage(null, "pt-BR", "Portuguese", "famfamfam-flags br"),
                new ApplicationLanguage(null, "tr", "Türkçe", "famfamfam-flags tr"),
                new ApplicationLanguage(null, "ru", "???????", "famfamfam-flags ru"),
                new ApplicationLanguage(null, "zh-CN", "????", "famfamfam-flags cn"),
                new ApplicationLanguage(null, "es-MX", "Español México", "famfamfam-flags mx"),
                new ApplicationLanguage(null, "nl", "Nederlands", "famfamfam-flags nl"),
                new ApplicationLanguage(null, "ja", "???", "famfamfam-flags jp")
            };
        }

        public DefaultLanguagesCreator(SparDbContext context)
        {
            _context = context;
        }

        public void Create()
        {
            CreateLanguages();
        }

        private void CreateLanguages()
        {
            foreach (var language in InitialLanguages)
            {
                AddLanguageIfNotExists(language);
            }
        }

        private void AddLanguageIfNotExists(ApplicationLanguage language)
        {
            if (_context.Languages.IgnoreQueryFilters().Any(l => l.TenantId == language.TenantId && l.Name == language.Name))
            {
                return;
            }

            _context.Languages.Add(language);
            _context.SaveChanges();
        }
    }
}

It is giving exception for Arabic language.

score 6 · Accepted Answer · answered May 24 '18 at 12:03

Thanks, @Dalton, and @Lajos for your suggestions, It has really helped in solving the issue.

I have found the solution with the help of your suggestions. This is not EF Core issue, It is the default collation configured in my MySql. So you have to change your default collation on your database. I had latin1 - default collation" in the database, which is not useful for inserting Unicode and non-English word(s), Please run the below query to change the collation of all the tables of your database.

SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` convert to character set utf8 collate utf8_unicode_ci;") AS    mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="<YouDatabaseName>"
AND TABLE_TYPE="BASE TABLE"

If you don't want to run this query in MySQL, then you can add the above script in the code migration.

SOAL ABDELDJALLIL · Answer 2 · 2018-09-30T10:48:26.580

2

Make your database and all tables interclassment as 'utf8_unicode_ci' You can do it manually or with executing this query :

Database :

ALTER DATABASE <database-name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Tables :

 ALTER TABLE <table-name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Here is link with more details and step by step guide

edited Sep 30 '18 at 10:48

answered Sep 29 '18 at 14:57

SOAL ABDELDJALLIL

416
5
14

You can't do it from c# code, if you're using phpmyadmin or any MySQL administrator you can do it manually or from a quey I'll share the query with you. – SOAL ABDELDJALLIL Sep 30 '18 at 10:38

score 1 · Answer 3 · answered Apr 25 '18 at 09:53

You have an error in your SQL, particularly with a varchar you try to assign. Possbile causes:

you might be using different encoding in your application than the encoding your field expects
you might be trying to insert a varchar which is longer than the maximum length allowed by your field

score 1 · Answer 4 · answered May 21 '18 at 13:17

1

Your are using aspnetboilerplate with mysql, to avoid those errors: before migrate, try to change MySQL database table encoding. It should be UTF-8 (utf8_unicode_ci) for all tables.

Note: if you got "Specified key was too long; max key length is 1000 bytes" error running migrations try to change The default storage engine to InnoDB in my.ini

answered May 21 '18 at 13:17

Dalton

435
6
12

Cant I change table encoding to UTF-8 from C# code? Or it has to be done manually from MySQL? – Vivek Nuna May 21 '18 at 13:24
Thanks for your help – Vivek Nuna May 24 '18 at 12:04

score -1 · Answer 5 · edited Sep 29 '22 at 12:47

-1

open my.ini file at C:\Program Files (x86)\MySQL\MySQL Server 5.0 then change default-character-set=latin1 to default-character-set=utf8 search again and change.

edited Sep 29 '22 at 12:47

Amfasis

3,932
2
18
27

answered Sep 25 '22 at 10:19

Gujja

1
1

Incorrect string value: '\xD8\xA7\xD9\x84\xD8\xB9...' for column 'DisplayName' at row 1

5 Answers5