Description:
type varchar insert NULL value, and then alter json not NULL.
This data change to null , and if you update this data, you can get a '' value from binlog.
But , slave can replica this data successful.
SQL_MODE is default.
How to repeat:
MySQL [xuzong]> create table q(id int ,age varchar(200) default NULL);
Query OK, 0 rows affected (0.0107 sec)
MySQL [xuzong]> insert into q values(1,NULL);
Query OK, 1 row affected (0.0040 sec)
MySQL [xuzong]> select * from q;
+----+------+
| id | age |
+----+------+
| 1 | NULL |
+----+------+
1 row in set (0.0020 sec)
MySQL [xuzong]> alter table q modify age json not null ;
Query OK, 1 row affected, 1 warning (0.0164 sec)
/*This is a warning, not error.*/
MySQL [xuzong]> select * from q;
+----+------+
| id | age |
+----+------+
| 1 | null |
+----+------+
1 row in set (0.0020 sec)
MySQL [xuzong]> show create table q \G
*************************** 1. row ***************************
Table: q
Create Table: CREATE TABLE `q` (
`id` int(11) DEFAULT NULL,
`age` json NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
MySQL [xuzong]> select * from q WHERE JSON_EXTRACT(age, '$.age') IS NULL;
+----+------+
| id | age |
+----+------+
| 1 | null |
+----+------+
1 row in set (0.0019 sec)
/* If you update this data ，binlog can record '' */
MySQL [xuzong]> update q set id=2 where id=1;
Query OK, 1 row affected (0.0023 sec)
Rows matched: 1 Changed: 1 Warnings: 0
binlog:
### UPDATE `xuzong`.`t`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='' /* JSON meta=4 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='' /* JSON meta=4 nullable=0 is_null=0 */
Suggested fix:
I suggest this warngin change to error , if sql_mode is strict mode.
Description: type varchar insert NULL value, and then alter json not NULL. This data change to null , and if you update this data, you can get a '' value from binlog. But , slave can replica this data successful. SQL_MODE is default. How to repeat: MySQL [xuzong]> create table q(id int ,age varchar(200) default NULL); Query OK, 0 rows affected (0.0107 sec) MySQL [xuzong]> insert into q values(1,NULL); Query OK, 1 row affected (0.0040 sec) MySQL [xuzong]> select * from q; +----+------+ | id | age | +----+------+ | 1 | NULL | +----+------+ 1 row in set (0.0020 sec) MySQL [xuzong]> alter table q modify age json not null ; Query OK, 1 row affected, 1 warning (0.0164 sec) /*This is a warning, not error.*/ MySQL [xuzong]> select * from q; +----+------+ | id | age | +----+------+ | 1 | null | +----+------+ 1 row in set (0.0020 sec) MySQL [xuzong]> show create table q \G *************************** 1. row *************************** Table: q Create Table: CREATE TABLE `q` ( `id` int(11) DEFAULT NULL, `age` json NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) MySQL [xuzong]> select * from q WHERE JSON_EXTRACT(age, '$.age') IS NULL; +----+------+ | id | age | +----+------+ | 1 | null | +----+------+ 1 row in set (0.0019 sec) /* If you update this data ，binlog can record '' */ MySQL [xuzong]> update q set id=2 where id=1; Query OK, 1 row affected (0.0023 sec) Rows matched: 1 Changed: 1 Warnings: 0 binlog: ### UPDATE `xuzong`.`t` ### WHERE ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2='' /* JSON meta=4 nullable=0 is_null=0 */ ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2='' /* JSON meta=4 nullable=0 is_null=0 */ Suggested fix: I suggest this warngin change to error , if sql_mode is strict mode.