Bug #89290 Connector returns JSON-Column value in wrong encoding

• Submitted: 17 Jan 2018 16:39
• Modified: 23 Jan 2018 6:40
• Reporter: Andreas Baier
• Status: Verified
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 6.10.5.0
• OS: Windows
• CPU Architecture: Any
• Tags: json, System.String, utf8

[17 Jan 2018 16:39] Andreas Baier

Description:
The .Net connector returns the value of a JSON-Column as a System.String in wrong encoding.

By using of the alternate MySqlConnector (https://mysql-net.github.io/MySqlConnector/) the output is the right encoding

How to repeat:
Create a table and insert a JSON value like this:

CREATE TABLE `test`.`tbljsontest` (
  `Id` INT NOT NULL AUTO_INCREMENT,
  `Json` JSON NOT NULL,
  PRIMARY KEY (`Id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;

INSERT INTO `test`.`tbljsontest`
(`Json`) VALUES ('{Key": "©"}');

Create a C# console app with MySql .Net connector:

using MySql.Data.MySqlClient;
using System;

namespace MySqlJsonTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var _conn = new MySqlConnection("server=192.168.1.44;database=test;user id=jsonuser;password=json");
            _conn.Open();
            var _sql = @"SELECT Json FROM test.tbljsontest;";
            var _c = new MySqlCommand(_sql, _conn);
            var _r = _c.ExecuteReader();
            _r.Read();
            Console.WriteLine(_r.GetString(0));
            _conn.Close();
            Console.ReadKey();
        }
    }
}

Output must be '{"Key": "©"}', but instead the connector encodes to '{"Key": "©"}'.

[18 Jan 2018 10:02] Chiranjeevi Battula

Hello Andreas Baier,

Thank you for the bug report testcase.
I could not repeat the issue at our end using with Visual Studio 2015, MySQL 5.7.20 and Connector/NET 6.10.5 version.

Thanks,
Chiranjeevi.

[18 Jan 2018 10:02] Chiranjeevi Battula

Screenshot

Attachment: Bug_89290.png (image/png, text), 95.39 KiB.

[18 Jan 2018 16:17] Andreas Baier

Thanks for watching the bug.

Maybe I chose an unsuitable character.
Can u repeat the test with the follwing INSERT:

INSERT INTO `test`.`tbljsontest`
(`Json`) VALUES ('{"Key": "äÄöÖüÜ"}');

The hex value output at MySql-Workbench is atteched in the screenshot.

These german characters are output in this way:
{"Key": "äÄöÖüÜ"}

I use Visual Studio 2017, .Net-Framework 4.6.2, Connector 6.10.5.0 on Windows 10 x64

[18 Jan 2018 16:18] Andreas Baier

Hex Value in MySql Workbench

Attachment: 02.PNG (image/png, text), 4.11 KiB.

[18 Jan 2018 16:19] Andreas Baier

Output in Visual Studio 2017

Attachment: 01.PNG (image/png, text), 41.71 KiB.

[19 Jan 2018 7:02] Bradley Grainger

I can reproduce this if mysqld is started with --character-set-server=latin1

When that server option is set, it doesn't matter if the client sets MySqlConnectionStringBuilder.CharacterSet = "utf8mb4" (or "utf8"); it doesn't matter if the client executes "SET NAMES utf8mb4;". No matter what I tried, the UTF-8 bytes being sent on the wire were being misinterpreted as Latin1 by Connector/NET.

This definitely looks like a connector bug to me (because MySqlConnector is handling it correctly with the same --character-set-server setting).

[20 Jan 2018 12:03] Andreas Baier

I can reproduce what Bradley Grainger commented:

Only with the option character_set_server = utf8mb4 the connector returns the correct string.

For me, it's a bug in Connector/NET to handle JSON values.

[23 Jan 2018 6:40] Chiranjeevi Battula

Hello Andreas Baier,

Thank you for the feedback.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.