vreplication: cannot replicate null json values

[derekperkins]

Overview of the Issue

In https://github.com/vitessio/vitess/pull/7640, json began to be cast as utf8mb4. That appears to fail on null json columns, as 'null' is cast as a string rather than a true json null value. This is the error after using Materialize to copy a table with null json values.

Cannot create a JSON value from a string with CHARACTER SET 'binary'. (errno 3144) (sqlstate 22032) during query: 
insert into tenant_copy(tenant_id,json_field) values (12345,'null'),

CREATE TABLE `tenant_copy` (
  `tenant_id` bigint NOT NULL,
  `json_field` json DEFAULT NULL,
  PRIMARY KEY (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED

cc @rohit-nayak-ps

[shlomi-noach]

That appears to fail on null json columns, as 'null' is cast as a string rather than a true json null value.

@derekperkins trying to pinpoint the problem, are suggesting this is a MySQL issue? From local testing,

mysql [localhost:8020] {root} (test) > create table tjson(id int primary key, j json);

mysql [localhost:8020] {root} (test) > insert into tjson values (2,null);

mysql [localhost:8020] {root} (test) > select * from tjson;
+----+------+
| id | j    |
+----+------+
|  2 | NULL |
+----+------+

mysql [localhost:8020] {root} (test) > select *, convert(j using utf8mb4) as c from tjson;
+----+------+------+
| id | j    | c    |
+----+------+------+
|  2 | NULL | NULL |
+----+------+------+

MySQL seems to convert NULL json into NULL text correctly. Is it an issue with specific versions? Or am I looking at the wrong thing?

[shlomi-noach]

Meanwhile, I validated that json NULL values are translated correctly in OnlineDDL/VReplication; which suggests the issue is probably not generic to VReplication, but maybe to Materialize? @derekperkins could you provide reproduction steps?

[rohit-nayak-ps]

Shlomi has already mentioned this but null values are working properly in the standard sql distros we test on.

The vreplication e2e tests already test null values for the json column, with MoveTables and Reshard. I just did a debug print of the sql DMLs in one of the tests and they look like this

insert into customer(cid,name,meta,typ,sport,ts,bits) values (1004,'tempCustomer7',convert(null using utf8mb4),null,null,'2021-11-26 17:12:35',b'00000011'). meta is a json column.

mysql> select * from customer;
+-----+--------+------+------------+-------------------+---------------------+------+
| cid | name   | meta | typ        | sport             | ts                  | bits |
+-----+--------+------+------------+-------------------+---------------------+------+
|   2 | paul   | {}   | soho       | cricket           | 2021-11-26 18:11:43 |     |
|   1 | john   | {}   | individual | football,baseball | 2021-11-26 18:11:43 |     |
|   3 | ringo  | NULL | enterprise |                   | 2021-11-26 18:11:43 |     |
|   5 | george | NULL | NULL       | NULL              | 2021-11-26 18:12:37 |     |
+-----+--------+------+------------+-------------------+---------------------+------+
4 rows in set (0,02 sec)

mysql> select cid, length(meta) from customer;
+-----+--------------+
| cid | length(meta) |
+-----+--------------+
|   2 |            2 |
|   1 |            2 |
|   3 |         NULL |
|   5 |         NULL |
+-----+--------------+
4 rows in set (0,00 sec)

Maybe it has to do with the Materialize spec, as Shlomi suspects. We will need more info to repro/fix this issue.

[derekperkins]

Seems very possible that this was caused by the same root issue as #9207

[mattlord]

@derekperkins are you still able to repeat this on latest main? I ask because I cannot repeat https://github.com/vitessio/vitess/issues/9207 there now...

[mattlord]

I wonder if this was some collation related oddness that's been fixed by the new MySQL compatible collations that have landed in main in recent weeks.

[mattlord]

I believe that this issue has been resolved by subsequent collation and JSON related work. I'm not aware of any specific bug or how to repeat it anymore so I'm going to close it for now.

If anyone has additional info please let me know and we'll re-open it. Thanks!