Bug #93052 Cannot create a JSON value from a string with CHARACTER SET 'binary'
Submitted: 2 Nov 2018 3:34 Modified: 27 Nov 2018 12:13
Reporter: Huishan Piao Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:8.0.13 OS:Windows (generic)
Assigned to: CPU Architecture:x86
Tags: json

[2 Nov 2018 3:34] Huishan Piao 
Description:
I tried to create an FEDERATED table and included an JSON Column.
But When I Select from this table shows:
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'.

Table1:
CREATE TABLE employees (
 	data JSON NOT NULL
);
Table2:
CREATE TABLE employees2 ( data JSON NOT NULL)engine=federated default charset=utf8mb4 connection='mysql://root:234@127.0.0.1:3306/testa/employees';

When I select from Table2
 MySQL  localhost:33060+ ssl  testa  SQL > select * from employees2;
ERROR: 3144: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

How to repeat:
Create a federate table include a JSON column, and select that one
[2 Nov 2018 12:36] MySQL Verification Team 
Hello Huishan,

Thank you for the report!

regards,
Umesh
[2 Nov 2018 12:36] MySQL Verification Team 
-- 8.0.13 started with --federated

mysql> create user 'ushastry'@'%';
Query OK, 0 rows affected (0.09 sec)

mysql> grant all on *.* to 'ushastry'@'%';
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE SERVER fedlink
    -> FOREIGN DATA WRAPPER mysql
    -> OPTIONS (USER 'ushastry', HOST 'hod03.no.oracle.com', PORT 9999, DATABASE 'federated');
Query OK, 1 row affected (0.09 sec)

mysql> create database federated;
Query OK, 1 row affected (0.07 sec)

mysql> use federated
Database changed
mysql> CREATE TABLE employees (
    ->  data JSON NOT NULL
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> create database test;
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE employees2 ( data JSON NOT NULL)ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='fedlink/employees';
Query OK, 0 rows affected (0.08 sec)

mysql>

mysql> select * from federated.employees;
+------+
| data |
+------+
| {}   |
+------+
1 row in set (0.00 sec)

mysql> select * from test.employees2;
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'.
mysql>
[27 Nov 2018 12:13] Jon Stephens 
Documented fix as follows in the MySQL 8.0.14 changelog:

        When trying to select from a JSON column of a FEDERATED table,
        the server returned ER_INVALID_JSON_PATH_CHARSET -Cannot create
        a JSON value from a string with CHARACTER SET 'binary'-.

        In addition, neither DELETE or UPDATE had any effect on a
        FEDERATED table containing a JSON column.

Closed.
[12 Dec 2018 7:46] AMU lin 
How to solve it?
[12 Dec 2018 8:02] MySQL Verification Team 
Bug #93568 marked as duplicate of this one