Strange string appear when I backup a Chinese "JSON" type column.

[XiDeng]

I backup a database and there is a table which has a "JSON" type column. Some filed in the json column is Chinese. After backup it becomes strange string, not Chinese.

I had met this situation. I got same solution when I use mysql command "select json_list_name from table;" through Mysql.Data.
When I use "select json_unquote(json_list_name ) from table;", it become normal.
Hope this issue helpful.

使用MySqlBackup.NET备份中文JSON字段的时候乱码

[XiDeng]

Here is my code:

using (MySqlConnection conn = new MySqlConnection(connstring))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        using (MySqlBackup mb = new MySqlBackup(cmd))
                        {
                            cmd.Connection = conn;
                            conn.Open();
                            mb.ExportCompleted += mb_ExportCompeleted;
                            mb.ExportToFile(filePath);
                        }
                    }
                }

[adriancs2]

It could be the chatset issue. Try get the info of the table:

SHOW CREATE TABLE `tablename`;

and see what is the default charset used by the table

[XiDeng]

It could be the chatset issue. Try get the info of the table:

SHOW CREATE TABLE `tablename`;

and see what is the default charset used by the table

Here is the result.

+-----------+----------------------------------------+
| Table     | Create Table                           |
+-----------+----------------------------------------+
| checklist | CREATE TABLE `checklist` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `checkcontent` json NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8 |
+-----------+----------------------------------------+

[XiDeng]

I query in Mysql Workbench and navicat. All is normal.

[adriancs2]

Try this:

using (MySqlConnection conn = new MySqlConnection(connstring))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = conn;
            conn.Open();

            cmd.CommandText = "SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES';";
            cmd.ExecuteNonQuery();

            mb.ExportCompleted += mb_ExportCompeleted;

            mb.ExportToFile(filePath);
        }
    }
}

You may execute this line after all the process completed to avoid memory leak. You may skip this if you're doing this in ASP.NET page.

mb.ExportCompleted -= mb_ExportCompeleted;

[XiDeng]

Try this:

using (MySqlConnection conn = new MySqlConnection(connstring))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = conn;
            conn.Open();

            cmd.CommandText = "SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES';";
            cmd.ExecuteNonQuery();

            mb.ExportCompleted += mb_ExportCompeleted;

            mb.ExportToFile(filePath);
        }
    }
}

You may execute this line after all the process completed to avoid memory leak. You may skip this if you're doing this in ASP.NET page.

mb.ExportCompleted -= mb_ExportCompeleted;

QAQ It doesn't work.

[XiDeng]

Could you please help me to solve this problem? Thanks.

[adriancs2]

Can you provide the SQL dump that you created by using MySqlWorkbench or MySqlDump?
So that I can use it to recreate the error.
The SQL dump file created by Mysql Workbench should contain the "CREATE TABLE" and some "INSERT"s.

[adriancs2]

You may upload the dump file as attachment in your next reply

[XiDeng]

I have dropped my test datas before and I created a sql file which contains one row data to upload. The structure of this sql file is same as the former one.
And the attachment is here:
test.zip

Thank you very much!

[adriancs2]

Hi, I have run a quick test on my computer. It runs perfectly alright in both import and export. The chinese characters are shown correctly. Therefore, I think the problem might be the settings of the MySql server.

The first thing came into my mind is still the character set.
First lets try to see the default character set of the MySQL server.
You may try to execute the following to find out:

show variables like 'character%';
show variables like 'collation%';

Are they all UTF8?

[XiDeng]

Thanks @adriancs2 a lot! It works! I checked my setting once again and you are right.
I set these filed in Command Line and the setting return to the original status after I disconnect to the server. So I try to edit the character set setting in my.ini (the Mysql configuration file in Windows) and restart the mysql server. It seems editing the configuration file is not a temporary method to edit the character set fileds. And this issue is solved.

MysqlBackup.NET备份Mysql 5.7以上版本的json字段出现乱码有可能是数据库字符集默认设置为gbk。可以通过修改mysql的配置文件中的两个配置项进行配置。

 [mysql]
 default-character-set=utf8
 [mysqld]
 character-set-server=utf8

我曾尝试使用mysql的命令行进行修改，发现可能不能达到永久修改的目的。
如有错误请谅解小白T-T

[XiDeng]

Try this:

using (MySqlConnection conn = new MySqlConnection(connstring))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = conn;
            conn.Open();

            cmd.CommandText = "SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES';";
            cmd.ExecuteNonQuery();

            mb.ExportCompleted += mb_ExportCompeleted;

            mb.ExportToFile(filePath);
        }
    }
}

You may execute this line after all the process completed to avoid memory leak. You may skip this if you're doing this in ASP.NET page.

mb.ExportCompleted -= mb_ExportCompeleted;

And now I am not using the code

cmd.CommandText = "SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES';";

cmd.ExecuteNonQuery();

It works perfectly, too.