MySQL JSON 類型 NOT NULL 竟無法約束 NULL 值插入？
故障現(xiàn)象
業(yè)務人員執(zhí)行了 DELETE 操作，導致數(shù)據(jù)誤刪除，需要數(shù)據(jù)回滾。通過工具回滾時，發(fā)現(xiàn)回滾生成的 SQL 執(zhí)行時報錯：
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 里的反斜杠導致數(shù)據(jù)被截斷了，sed 完再導入 SQL 發(fā)現(xiàn)還是有問題。SQL 文件不小，表也很寬，很難檢查問題，但還是試著分析數(shù)據(jù)，發(fā)現(xiàn)反斜杠是對的。汗。。。
又通過報錯仔細分析對應字段 search_stats 的值，發(fā)現(xiàn) JSON 字段類型回滾生成的 SQL 竟然是 '' （空）值！
- 版本：MySQL 5.7.21
- SQL_MODE: ''
故障分析
正向解析 binlog 生成 SQL 看并無問題，binlog 里記錄的確實是 '' （空）值。
但是通過查看線上表數(shù)據(jù)發(fā)現(xiàn)，在 有 JSON NOT NULL 類型的約束下，對應的回滾 SQL 主鍵值竟然是 NULL 值！ 這和 binlog 里記錄的 '' （空）值不一致。
正常寫入 NULL 值，會觸發(fā)約束報錯，猜測可能是歷史上有過 DDL 操作。
故障復現(xiàn)
將 SQL_MODE 設置為 ''，退出重連。（PS：切記重連生效?。?/p>
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，感覺這種情況應該是 Error 才對。
Records: 1 Duplicates: 0 Warnings: 1
Warning (code 1265): Data truncated for column 'age' at row 1
# 到這里就發(fā)現(xiàn)不對勁了，不僅有 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)
# 查一下試試，也能查出來
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 解析出來 binlog：null 值 update 的時候 binlog 會記錄為 ''
### 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 */問題處理
查到是字段兼容性問題就好處理了，方法如下：
新建一個庫，建議一個同表結構的表，將 JSON 字段改寫為 TEXT 字段，然后將回滾 SQL 導入。再 UPDATE 修改 '' （空）值為 {}，然后再修改為 JSON 字段。業(yè)務判斷數(shù)據(jù)是否正確，INSERT ... SELECT ... 寫入回原表。
這樣就解決了。。。
后續(xù)測試
其他版本 MySQL 是否存在同樣的問題？親測 5.7.35 和 5.7.44 也同樣存在，這里不再概述。
但是用 MySQL 8.0 的 mysqlbinlog 去解析 binlog，記錄為 null。
測試 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 會自動轉換為空值。
SQL_MODE
# 再執(zhí)行復現(xià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, '{}');總結
- 線上修改表結構，一定要先備份數(shù)據(jù)，然后在測試環(huán)境上修改表結構，測試通過后再上線。
- 修改表結構時，一定要注意數(shù)據(jù)類型的變化，特別是 JSON、BLOB 等特殊類型的變化。
- 建議線上開啟 SQL_MODE 嚴格模式，避免出現(xiàn)一些意想不到的問題。
- 本次問題不影響線上數(shù)據(jù)以及主從復制，理論上來說只是 MySQL 5.7 版本 mysqlbinlog 的問題，以及違反了非空約束。
