Bug #88288 Reading and writing a table with a JSON-generated column fails with ERROR 3144
Submitted: 29 Oct 2017 23:39 Modified: 30 Oct 2017 9:51
Reporter: vigo harrach Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.7.19 OS:Ubuntu (16.04.3)
Assigned to: CPU Architecture:Any
Tags: 3144, BINARY, flush tables, generated column, json

[29 Oct 2017 23:39] vigo harrach
What I did:

Follow the "setup" and "break things" steps from the "How to repeat" section, first creating a table with a JSON_EXTRACT-generated column, then changing the client character set to binary, and flushing the created table.

Issue a SELECT against that table; then INSERT values using a charset introducer. Start a new session, and try the same.

What I wanted to happen:

SELECTs and INSERTs as described above should work.

What actually happened:

All SELECTs and INSERTs above failed with "ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'."

The "break things" steps are always used by "mydumper", and potentially by "mysqldump" as well (. In effect, running a backup might leave any other session unable to work with affected tables.

How to repeat:
/* setup */
CREATE TABLE test.test (data JSON, x INT AS (data->"$.x")) CHARSET utf8;
INSERT INTO test.test (data) VALUES ('{"x":1}');

/* break things */
SET NAMES binary;
FLUSH TABLES test.test;

/* failure - selecting should always work */
SELECT * FROM test.test; /* error 3144, this also affects other sessions */

SELECT * FROM test.test; /* fails as above */

/* failure - inserting with a non-binary charset should work */
INSERT INTO test.test (data) VALUES ('{"x":2}'); /* fails as above */
INSERT INTO test.test (data) VALUES (_utf8'{"x":3}'); /* fails as above */

/* unbreak things */
FLUSH TABLES test.test;

SELECT * FROM test.test; /* works */

INSERT INTO test.test (data) VALUES ('{"x":2}'); /* works */

SET NAMES binary;
INSERT INTO test.test (data) VALUES (_utf8'{"x":3}'); /* works */

Suggested fix:
It looks like some table state (charset or error state related) is persisted by flushing the table.
[30 Oct 2017 6:36] MySQL Verification Team
Hello Vigo Harrach,

Thank you for the report.
This reminds me of Bug #86709, which is fixed in 8.0.1 under the heading of Bug#22991924 	GCOLS: UTF16 STRING OPERATIONS GIVE DOUBLED \0\0 STRINGS ON EACH TABLE REBUILD

[30 Oct 2017 9:51] vigo harrach
Any plans to fix this in the 5.7 range as well? It's a pretty awkward situation to have unrelated sessions wrongly fail with ERROR 3144 during and after a backup.
[1 Jun 2022 12:24] Jean-François Gagné
Probably related: Bug#107438.
[7 Jun 2022 9:34] huahua xu
The slave sql worker does not catch the error when the function `Item::save_in_field` returns TYPE_ERR_BAD_VALUE cause by ER_INVALID_JSON_CHARSET in `ensure_utf8mb4`.

if (cs == &my_charset_bin)
    if (require_string)
      my_error(ER_INVALID_JSON_CHARSET, MYF(0), my_charset_bin.csname);
    return true;