Description:
In binary character set,select statement will be failed when the table contains json virtual column.
We must flush tables with other character set,and then open table.
After that,we can select data with binary character set.
This problem no occur in json column and json general column!
Because of this,we can't use mysqldump in binary character set.
How to repeat:
SET NAMES utf8;
CREATE DATABASE IF NOT EXISTS test_json;
USE test_json;
CREATE TABLE t1 (c JSON);
INSERT INTO t1 (c) VALUES ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'), ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
CREATE TABLE t2 (c JSON, g INT GENERATED ALWAYS AS (c->"$.id") VIRTUAL, INDEX i (g));
INSERT INTO t2 (c) VALUES ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'), ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
CREATE TABLE t3 (c JSON, g INT GENERATED ALWAYS AS (c->"$.id") STORED, INDEX i (g)) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
INSERT INTO t3 (c) VALUES ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'), ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
SET NAMES binary;
FLUSH TABLES;
mysql> select * from t1;
+-------------------------------+
| c |
+-------------------------------+
| {"id": "1", "name": "Fred"} |
| {"id": "2", "name": "Wilma"} |
| {"id": "3", "name": "Barney"} |
| {"id": "4", "name": "Betty"} |
+-------------------------------+
4 rows in set (0.00 sec)
mysql> select * from t2;
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'.
mysql> select * from t3;
+-------------------------------+------+
| c | g |
+-------------------------------+------+
| {"id": "1", "name": "Fred"} | 1 |
| {"id": "2", "name": "Wilma"} | 2 |
| {"id": "3", "name": "Barney"} | 3 |
| {"id": "4", "name": "Betty"} | 4 |
+-------------------------------+------+
4 rows in set (0.00 sec)
Suggested fix:
If possible,we can use column's character in virtual column,instead of use create/open table character set.
Description: In binary character set,select statement will be failed when the table contains json virtual column. We must flush tables with other character set,and then open table. After that,we can select data with binary character set. This problem no occur in json column and json general column! Because of this,we can't use mysqldump in binary character set. How to repeat: SET NAMES utf8; CREATE DATABASE IF NOT EXISTS test_json; USE test_json; CREATE TABLE t1 (c JSON); INSERT INTO t1 (c) VALUES ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'), ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}'); CREATE TABLE t2 (c JSON, g INT GENERATED ALWAYS AS (c->"$.id") VIRTUAL, INDEX i (g)); INSERT INTO t2 (c) VALUES ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'), ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}'); CREATE TABLE t3 (c JSON, g INT GENERATED ALWAYS AS (c->"$.id") STORED, INDEX i (g)) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; INSERT INTO t3 (c) VALUES ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'), ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}'); SET NAMES binary; FLUSH TABLES; mysql> select * from t1; +-------------------------------+ | c | +-------------------------------+ | {"id": "1", "name": "Fred"} | | {"id": "2", "name": "Wilma"} | | {"id": "3", "name": "Barney"} | | {"id": "4", "name": "Betty"} | +-------------------------------+ 4 rows in set (0.00 sec) mysql> select * from t2; ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'. mysql> select * from t3; +-------------------------------+------+ | c | g | +-------------------------------+------+ | {"id": "1", "name": "Fred"} | 1 | | {"id": "2", "name": "Wilma"} | 2 | | {"id": "3", "name": "Barney"} | 3 | | {"id": "4", "name": "Betty"} | 4 | +-------------------------------+------+ 4 rows in set (0.00 sec) Suggested fix: If possible,we can use column's character in virtual column,instead of use create/open table character set.