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, Sep 04 '19 at 08:41
Olivier Depriester {"Option not supported.\r\n: useunicode"} seems like useUnicode error — jimmyan, Sep 04 '19 at 08:53
Sorry, I gave you jdbc connection string parameters. They do not apply to c# — Olivier Depriester, Sep 04 '19 at 09: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 https://mathiasbynens.be/notes/mysql-utf8mb4 and https://stackoverflow.com/questions/28540772/using-utf8mb4-in-mysql . — LocEngineer, Sep 04 '19 at 09:11
LocEngineer thanks, i've tried and update my situation, still problem can't be solved — jimmyan, Sep 04 '19 at 09:42
Please show your current connection string. I have found an almost identical issue (solved) here: https://github.com/jinzhu/gorm/issues/1811 . Look at the connection string used in the very last entry of that thread. — LocEngineer, Sep 04 '19 at 10:01
Have you also performed for each database: `ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;` For each table: `ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;` For each column: `ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;` ? — LocEngineer, Sep 04 '19 at 14:23
yes,but still same problem, i've update some code above,please check it — jimmyan, Sep 05 '19 at 01:23
Problem been solved ,it's my PROSEDURE problem , thanks for you guys! — jimmyan, Sep 05 '19 at 02:54

Rick James · Accepted Answer · 2019-09-05T02:18:25.500

\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.

Thanks and sorry for the late, I've update SHOW CREATE TABLE and some code , please check my content above. — jimmyan, Sep 05 '19 at 01:17
I've tried the link and made some change to my code,can't still solve the problem — jimmyan, Sep 05 '19 at 01:18
@jimmyan - Stored Proc was the clue I needed. See my Addenda. — Rick James, Sep 05 '19 at 02:19
OMG! I'm so dumb , forget to change utf8 in my PROCEDURE. I've solved the problem!! Thanks for reminding , saving my day. — jimmyan, Sep 05 '19 at 02:43

score 0 · Answer 2 · edited Oct 22 '20 at 01:04

0

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 '20 at 01:04

Dharman

30,962
25
85
135

answered Oct 22 '20 at 00:58

shai

101
1
14

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

2 Answers2

Linked