MySQL Bugs: #88288: Reading and writing a table with a JSON-generated column fails with ERROR 3144

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:	None
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

Description:
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 */
SET NAMES utf8;
CREATE DATABASE test CHARSET utf8;
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 */

SET NAMES utf8;
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.

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

Thanks,
Umesh

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.

Probably related: Bug#107438.

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;
  }
```