MySQL Bugs: #107438: JSON STORED generated column values are inserted as NULL on replica

Bug #107438	JSON STORED generated column values are inserted as NULL on replica
Submitted:	31 May 2022 19:57	Modified:	1 Jun 2022 8:56
Reporter:	Erik Halperin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.7.38	OS:	CentOS (8)
Assigned to:		CPU Architecture:	Any
Tags:	BINARY, generated column, json, null, replication, stored column

Description:
In a replicated set up, running "set names binary" on a primary and then "start slave; stop slave;" will cause all values for a JSON-generated column to be inserted as NULL on the replica. Also, running "flush tables" or restarting the mysql instance--instead of "start slave; stop slave;"--on the replica will induce the same behavior.

Probably related to Bug#88288

How to repeat:
Set up a primary and replica MySQL instance

/* on the primary */
create database test_eh;
create table test_eh.t (json_col JSON, gen_col INT GENERATED ALWAYS AS (json_extract(json_col, '$.x')) STORED);
insert into test_eh.t (json_col) values ('{"x": 1}');
set names binary;
insert into test_eh.t (json_col) values (_utf8mb4'{"x": 2}');

/* on the replica */
stop slave; start slave;

/* on the primary - done in the same session as 'set names binar' */
insert into test_eh.t (json_col) values (_utf8mb4'{"x": 3}');
select * from test_eh.t;

Result:
+----+----------+---------+
| id | json_col | gen_col |
+----+----------+---------+
|  1 | {"x": 1} |       1 |
|  2 | {"x": 2} |       2 |
|  3 | {"x": 3} |       3 |
+----+----------+---------+

/* on the replica */
select * from test_eh.t;

Result:
+----------+---------+
| json_col | gen_col |
+----------+---------+
| {"x": 1} |       1 |
| {"x": 2} |       2 |
| {"x": 3} |    NULL |
+----------+---------+

A similar/weirder problem happens on non-STORED generated columns with an index.

dbdeployer deploy replication mysql_5.7.38

./m <<< "
  CREATE DATABASE test_jfg;
  CREATE TABLE test_jfg.t (
    id int AUTO_INCREMENT PRIMARY KEY,
    j json NOT NULL,
    v int unsigned GENERATED ALWAYS AS (json_unquote(json_extract(j,'$.v'))),
    KEY(v))"

-- In a session on the primary (sorry, I have trouble with Linux Shell, dbdeployer and SET NAMES binary).

INSERT INTO test_jfg.t(j) VALUES('{"v":1}');
SET NAMES binary;
INSERT INTO test_jfg.t(j) VALUES(_utf8'{"v":2}');

./s2 <<< "STOP SLAVE; START SLAVE"

-- Back in the session on the primary (which had SET NAMES binary).

INSERT INTO test_jfg.t(j) VALUES(_utf8'{"v":3}');

# No null, but...

./m test_jfg -e "SELECT * FROM t"
+----+----------+------+
| id | j        | v    |
+----+----------+------+
|  1 | {"v": 1} |    1 |
|  2 | {"v": 2} |    2 |
|  3 | {"v": 3} |    3 |
+----+----------+------+
./s1 test_jfg -e "SELECT * FROM t"
+----+----------+------+
| id | j        | v    |
+----+----------+------+
|  1 | {"v": 1} |    1 |
|  2 | {"v": 2} |    2 |
|  3 | {"v": 3} |    3 |
+----+----------+------+
./s2 test_jfg -e "SELECT * FROM t"
+----+----------+------+
| id | j        | v    |
+----+----------+------+
|  1 | {"v": 1} |    1 |
|  2 | {"v": 2} |    2 |
|  3 | {"v": 3} |    3 |
+----+----------+------+

# If querying just the v column (and for v = 3), we have our null (and no rows with v = 3 because the index contains null):

./m test_jfg -e "SELECT v FROM t; SELECT * FROM t WHERE v = 3"
+------+
| v    |
+------+
|    1 |
|    2 |
|    3 |
+------+
+----+----------+------+
| id | j        | v    |
+----+----------+------+
|  3 | {"v": 3} |    3 |
+----+----------+------+
./s1 test_jfg -e "SELECT v FROM t; SELECT * FROM t WHERE v = 3"
+------+
| v    |
+------+
|    1 |
|    2 |
|    3 |
+------+
+----+----------+------+
| id | j        | v    |
+----+----------+------+
|  3 | {"v": 3} |    3 |
+----+----------+------+
./s2 test_jfg -e "SELECT v FROM t; SELECT * FROM t WHERE v = 3"
+------+
| v    |
+------+
| NULL |
|    1 |
|    2 |
+------+

Hello Erik Halperin,

Thank you for the report and feedback.
Verified as described with 5.7.38 build.

regards,
Umesh

5.7.38 test results

Attachment: 107438_5.7.38.results (application/octet-stream, text), 6.47 KiB.

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

It is not super explicit from what is written in this report, putting this explicitly in this comment...

MySQL 8.0 (at least 8.0.33) is not affected: both eh and jfg tests are returning expected results.