c# Having trouble with mysql : Incorrect string value code 1366 for column at row 1

Question

ERROR C# MVC5 using ADO.NET when update columns using Chinese in mysql(version:5.7.27-0ubuntu0.18.04.1 - (Ubuntu)): {"Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'Title_1' at row 1"}

I have already tried charset for database, table and columns.

(1)'utf8' 'utf8_general_ci'
(2)'utf8' 'utf8_unicode_ci'
(3)'utf8mb4' 'utf8mb4_general_ci'
(4)'utf8mb4' 'utf8mb4_unicode_ci'

Also,I've added charset=utf8mb4 or charset=utf8 in connectionstring in Web.config

my connection string is

<add name="constr" connectionString="Data Source=ServerIP;port=3306;Initial Catalog=jn001;User Id=Userid;password=Password;charset=utf8mb4">

Google all day finding answers, Still can't find a solution, Please tell me a solution,Thanks!

here is my mysql charset

I've followed LocEngineer's advertisement and set mysql server to utf8mb4

here is my new mysql charset

And this is my collation

All change to utf8mb4 (including connectionstring) ,but still the same error occur.

Anyway ,thanks for the suggestions.

------------------------------9/5update--------------------------------

my SHOW CREATE TABLE

 public bool UpdateDetails(Class1 content)
        {
            connection();
            MySqlCommand cmdSet = new MySqlCommand("set names utf8mb4", con);
            MySqlCommand cmd = new MySqlCommand("update", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Title_1",content.Title_1);
            cmd.Parameters.AddWithValue("@Title_2", content.Title_2);
            cmd.Parameters.AddWithValue("@Title_3", content.Title_3);
            cmd.Parameters.AddWithValue("@Content_1", content.Content_1);
            cmd.Parameters.AddWithValue("@Content_2", content.Content_2);
            cmd.Parameters.AddWithValue("@Content_3", content.Content_3);

            con.Open();
            cmdSet.ExecuteNonQuery();
            int i = cmd.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
                return true;
            else
                return false;
        }

this is mysql update Procedure

UPDATE Stop_Info SET Title_1 = Title_1,Title_2=Title_2,Title_3=Title_3,Content_1=Content_1,Content_2=Content_2,Content_3=Content_3 WHERE Pkey = 3

HTML form accept-charset="utf-8"

<form id="registerForm" method="post" action="~/Home/Index" class="form-horizontal" accept-charset="utf-8">

Did you specify the useUnicode in your connection string ? --> useUnicode=true&characterEncoding=utf8 — Olivier Depriester
– Olivier Depriester, Commented Sep 4, 2019 at 8:41
here is my connection string , <add name="constr" connectionString="Data Source=ServerIP;port=3306;Initial Catalog=jn001;User Id=Userid;password=Password@;charset=utf8"/> — jimmyan
– jimmyan, Commented Sep 4, 2019 at 8:48
Olivier Depriester {"Option not supported.\r\n: useunicode"} seems like useUnicode error — jimmyan
– jimmyan, Commented Sep 4, 2019 at 8:53
Sorry, I gave you jdbc connection string parameters. They do not apply to c# — Olivier Depriester
– Olivier Depriester, Commented Sep 4, 2019 at 9:10
It seems that utf8 is a bit of a misnomer in mySql. You need to use utf8mb4, both in your database but also in your connection string. Check out mathiasbynens.be/notes/mysql-utf8mb4 and stackoverflow.com/questions/28540772/using-utf8mb4-in-mysql . — LocEngineer
– LocEngineer, Commented Sep 4, 2019 at 9:11

Rick James · Accepted Answer · 2019-09-05 02:18:25Z

1

\xE4\xB8\xAD\xE6\x96\x87 is hex for 中文 ("Chinese language").

In MySQL, this will work with either utf8 or utf8mb4. However, since there are several Chinese characters that need 4 bytes, you are correct to use utf8mb4. Meanwhile, the COLLATION (eg, utf8mb4_unicode_ci) does not matter to the Question in hand.

The individual column's charset is important, not the database's. And the 191 kludge is needed only in 5.5 and 5.6. Please provide SHOW CREATE TABLE.

Another way to establish the connection parameters is to issue SET NAMES utf8mb4 immediately after connecting. (This is not preferred, but might be worth trying. It should be a workaround for the Gorm issue mentioned.)

Are you getting the error message when connecting? Or when issuing a particular query? If so, please provide that query.

Check that skip-character-set-client-handshake is not being used.

Check the "Best practice" in https://stackoverflow.com/a/38363567/1766831

Addenda Since you mentioned a stored procedure, I suggest you do SHOW CREATE PROCEDURE to see what charset it was built with. Here's an example of what can happen:

mysql> SET NAMES latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE x () BEGIN END ;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE PROCEDURE x \G
*************************** 1. row ***************************
           Procedure: x
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `x`()
BEGIN END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8mb4_unicode_520_ci
1 row in set (0.00 sec)

mysql> DROP PROCEDURE x;
Query OK, 0 rows affected (0.01 sec)

versus:

mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE x () BEGIN END ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE PROCEDURE x \G
*************************** 1. row ***************************
           Procedure: x
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `x`()
BEGIN END
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_unicode_520_ci
1 row in set (0.00 sec)

If you don't see utf8mb4 on your PROCEDURE and FUNCTION declarations, rebuild them.

edited Sep 5, 2019 at 2:18

answered Sep 4, 2019 at 21:52

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jimmyan Over a year ago

Thanks and sorry for the late, I've update SHOW CREATE TABLE and some code , please check my content above.

jimmyan Over a year ago

I've tried the link and made some change to my code,can't still solve the problem

Rick James Over a year ago

@jimmyan - Stored Proc was the clue I needed. See my Addenda.

jimmyan Over a year ago

OMG! I'm so dumb , forget to change utf8 in my PROCEDURE. I've solved the problem!! Thanks for reminding , saving my day.

Rick James Over a year ago

@jimmyan - You are welcome. (And click the check mark.)

Dharman · Accepted Answer · 2020-10-22 01:04:04Z

1

This didnt work for me. After days of fiddling and trying practically everything, I had to add charset utf8mb4 to the parameter declaration in the stored proc. param text charset utf8mb4.

edited Oct 22, 2020 at 1:04

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Oct 22, 2020 at 0:58

shai

1112 silver badges14 bronze badges

Collectives™ on Stack Overflow

c# Having trouble with mysql : Incorrect string value code 1366 for column at row 1

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related