MySQL Bugs: #81677: Allows to force JSON columns encoding into pure UTF-8

Bug #81677	Allows to force JSON columns encoding into pure UTF-8
Submitted:	1 Jun 2016 13:00	Modified:	22 Apr 2018 4:40
Reporter:	n n	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	datatype, encoding, json, utf8, utf8mb4

Description:
Hi,

The new MySql 5.7 Json datatype brings advanced functions for storing and querying json.
More info here : https://dev.mysql.com/doc/refman/5.7/en/json.html

On MySql 5.6 I had some columns of type longtext containing utf8 json.
My db has been upgraded to 5.7 and I tried to use the new JSON datatype, and it works. 

Json are correctly displayed in workbench, but all the Java apps reading this db display broken accentuated char, while all was working fine with the longtext type.

It seems that the Json is stored in utf8mb4, which is unknown in Java. I tried to set the columns charset into pure utf8 but it seems we cannot do that.

Could we have the choice of the encoding used to store JSON data ?

Thanks.

How to repeat:
You can reproduce the problem by creating a json field and inserting utf8 data into it :

CREATE TABLE t1 (jdoc JSON) CHARACTER SET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO t1 VALUES('{"key1": "my value éééàààà"}');

Then try to read this table from a java app.

Suggested fix:
Allow users to define the charset to use for storing JSON.

I'd like to mention that I'm not sure if the JSON is actually encoded in utf8mb4, the documentation states that it's encoded in a 'utf8 character type' (utf8mb4, utf8, ascii). 

Running the same query casting the JSON field to utf8, utf8mb4 and ascii will render different outputs in Java. 

In my case a utf8 cast was enough to solve my problem.

Minor note, there's a small discussion on the subject here: http://stackoverflow.com/questions/43330184/how-can-i-fix-encoding-issues-with-mysqls-json...

Hello!

Thank you for the report and feedback.
Could you please confirm exact version of Connector/J that you are using in your java apps? This is most likely duplicate of Bug #80631 as I quickly tried to reproduce but not seeing any issues. Please confirm!

####
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> \s
--------------
bin/mysql  Ver 14.14 Distrib 5.7.22, for el7 (x86_64) using  EditLine wrapper

Connection id:          2
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.22 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql_ushastry.sock
Uptime:                 20 sec

Threads: 1  Questions: 10  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.500
--------------

mysql> CREATE TABLE t1 (jdoc JSON) CHARACTER SET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "my value éééàààà"}');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+-------------------------------------+
| jdoc                                |
+-------------------------------------+
| {"key1": "my value éééàààà"}        |
+-------------------------------------+
1 row in set (0.00 sec)

## with 5.1.44 (mysql-connector-java-5.1.44)

[umshastr@hod03]~/bugs: cat TestBug.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestBug {

        public static void main(String[] args) {
                try {
                        Class.forName("com.mysql.jdbc.Driver");
                } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                }
                try {
                        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3333/test",
                                        "root", "");
                        Statement sta = con.createStatement();
                        ResultSet rs = sta.executeQuery(" SELECT * FROM t1; ");
                        while (rs.next()) {
                                System.out.println(rs.getString("jdoc"));
                        }
                } catch (SQLException e) {
                        e.printStackTrace();
                }
        }

}

[umshastr@hod03]~/bugs: javac TestBug.java
[umshastr@hod03]~/bugs: java -cp '/home/umshastr/bugs/mysql-connector-java-5.1.44/mysql-connector-java-5.1.44-bin.jar:.' TestBug
{"key1": "my value éééàààà"}

## with 5.1.39 (mysql-connector-java-5.1.39)

[umshastr@hod03]~/bugs: java -cp '/home/umshastr/bugs/mysql-connector-java-5.1.39/mysql-connector-java-5.1.39-bin.jar:.' TestBug
{"key1": "my value Ã©Ã©Ã©Ã Ã Ã Ã "}

Thanks,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".