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
• Status: Closed
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 8.0.13
• OS: Windows (generic)
• 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