MySQL JSON 類型 NOT NULL 竟無(wú)法約束 NULL 值插入？
故障現(xiàn)象
業(yè)務(wù)人員執(zhí)行了 DELETE 操作，導(dǎo)致數(shù)據(jù)誤刪除，需要數(shù)據(jù)回滾。通過(guò)工具回滾時(shí)，發(fā)現(xiàn)回滾生成的 SQL 執(zhí)行時(shí)報(bào)錯(cuò)：
ERROR 3140 (22032) at line 38454 in file: 'rollback.9591.sql': Invalid JSON text: "The document is empty." at position 0 in value for column 'life_band_dig_query.search_stats'.
起初，以為是 SQL 里的反斜杠導(dǎo)致數(shù)據(jù)被截?cái)嗔耍瑂ed 完再導(dǎo)入 SQL 發(fā)現(xiàn)還是有問(wèn)題。SQL 文件不小，表也很寬，很難檢查問(wèn)題，但還是試著分析數(shù)據(jù)，發(fā)現(xiàn)反斜杠是對(duì)的。汗。。。
又通過(guò)報(bào)錯(cuò)仔細(xì)分析對(duì)應(yīng)字段 search_stats 的值，發(fā)現(xiàn) JSON 字段類型回滾生成的 SQL 竟然是 '' （空）值！
- 版本：MySQL 5.7.21
- SQL_MODE: ''
故障分析
正向解析 binlog 生成 SQL 看并無(wú)問(wèn)題，binlog 里記錄的確實(shí)是 '' （空）值。
但是通過(guò)查看線上表數(shù)據(jù)發(fā)現(xiàn)，在 有 JSON NOT NULL 類型的約束下，對(duì)應(yīng)的回滾 SQL 主鍵值竟然是 NULL 值！ 這和 binlog 里記錄的 '' （空）值不一致。
正常寫入 NULL 值，會(huì)觸發(fā)約束報(bào)錯(cuò)，猜測(cè)可能是歷史上有過(guò) DDL 操作。
故障復(fù)現(xiàn)
將 SQL_MODE 設(shè)置為 ''，退出重連。（PS：切記重連生效！）
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)
# 注意到這里有 Warning，感覺(jué)這種情況應(yīng)該是 Error 才對(duì)。
Records: 1 Duplicates: 0 Warnings: 1
Warning (code 1265): Data truncated for column 'age' at row 1
# 到這里就發(fā)現(xiàn)不對(duì)勁了，不僅有 null 值，而且還由 NULL 大寫變成了小寫。
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
1rowinset (0.00 sec)
# 查一下試試，也能查出來(lái)
MySQL [xuzong]> select * from q WHERE JSON_EXTRACT(age, '$.age') IS NULL;
+----+------+
| id | age |
+----+------+
| 1 | null |
+----+------+
1 row in set (0.0019 sec)
# update 一下試試 binlog 是怎么記錄的
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
# 用 5.7.21 的 mysqlbinlog 解析出來(lái) binlog：null 值 update 的時(shí)候 binlog 會(huì)記錄為 ''
### UPDATE `xuzong`.`q`
### 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 */問(wèn)題處理
查到是字段兼容性問(wèn)題就好處理了，方法如下：
新建一個(gè)庫(kù)，建議一個(gè)同表結(jié)構(gòu)的表，將 JSON 字段改寫為 TEXT 字段，然后將回滾 SQL 導(dǎo)入。再 UPDATE 修改 '' （空）值為 {}，然后再修改為 JSON 字段。業(yè)務(wù)判斷數(shù)據(jù)是否正確，INSERT ... SELECT ... 寫入回原表。
這樣就解決了。。。
后續(xù)測(cè)試
其他版本 MySQL 是否存在同樣的問(wèn)題？親測(cè) 5.7.35 和 5.7.44 也同樣存在，這里不再概述。
但是用 MySQL 8.0 的 mysqlbinlog 去解析 binlog，記錄為 null。
測(cè)試 VARCHAT 類型
MySQL [xuzong]> create table j (id int , age varchar(20));
Query OK, 0 rows affected (0.01 sec)
MySQL [xuzong]> insert into j values (1,NULL);
Query OK, 1 row affected (0.01 sec)
MySQL [xuzong]> select * from j;
+------+------+
| id | age |
+------+------+
| 1 | NULL |
+------+------+
1 row in set (0.01 sec)
MySQL [xuzong]> alter table j modify age varchar(20) not null;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 1
MySQL [xuzong]> show Warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'age' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
MySQL [xuzong]> select * from j;
+------+-----+
| id | age |
+------+-----+
| 1 | |
+------+-----+
1 row in set (0.00 sec)這樣看的 VARCHAR 會(huì)自動(dòng)轉(zhuǎn)換為空值。
SQL_MODE
# 再執(zhí)行復(fù)現(xiàn)操作就報(bào)錯(cuò)了，所以還是建議線上開(kāi)啟嚴(yán)格模式的。
set global sql_mode=deafult;用 ibd2sql 解析一下 ibd 文件，發(fā)現(xiàn)數(shù)據(jù)是正確的:
[root@nvm-22vdnhahrwnq37 ibd2sql-main]# python3 main.py ../mysql4223/xuzong/j.ibd --sql --ddl
CREATE TABLE IF NOT EXISTS `xuzong`.`j`(
`id` int NULL,
`age` json NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `xuzong`.`j` VALUES (2, '{}');總結(jié)
- 線上修改表結(jié)構(gòu)，一定要先備份數(shù)據(jù)，然后在測(cè)試環(huán)境上修改表結(jié)構(gòu)，測(cè)試通過(guò)后再上線。
- 修改表結(jié)構(gòu)時(shí)，一定要注意數(shù)據(jù)類型的變化，特別是 JSON、BLOB 等特殊類型的變化。
- 建議線上開(kāi)啟 SQL_MODE 嚴(yán)格模式，避免出現(xiàn)一些意想不到的問(wèn)題。
- 本次問(wèn)題不影響線上數(shù)據(jù)以及主從復(fù)制，理論上來(lái)說(shuō)只是 MySQL 5.7 版本 mysqlbinlog 的問(wèn)題，以及違反了非空約束。
