故障分析 | MySQL 遷移後 timestamp 列 cannot be null

愛可生雲資料庫發表於2021-11-01
MySqlNull

作者：秦福朗

愛可生 DBA 團隊成員，負責專案日常問題處理及公司平臺問題排查。熱愛網際網路，會攝影、懂廚藝，不會廚藝的 DBA 不是好司機，didi~

本文來源：原創投稿

*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來>源。

背景

一個業務系統剛遷移完，筆者剛回到家，開發那邊就遇到了業務報錯”Column ‘create_time’ cannot be null”，從字面意思可以理解為表欄位’create_time’想插入null值，但報錯該欄位不能為null。由此引發了對explicit_defaults_for_timestamp這個有關時間引數的思考。

概念概述

1. TIMESTAMP和DATETIME

提 explicit_defaults_for_timestamp 引數，首先就要簡單解釋下時間資料型別 TIMESTAMP 和 DATETIME ：

  • TIMESTAMP 是一個時間戳，範圍是'1970-01-01 00:00:01.000000'UTC 到'2038-01-19 03:14:07.999999'UTC。
  • DATETIME是日期和時間的組合，範圍是'1000-01-01 00:00:00.000000'到 '9999-12-31 23:59:59.999999'。

TIMESTAMP 和 DATETIME 列都可以自動初始化並且可以更新為當前的日期和時間，列還可以將當前的時間戳指定為預設值、自動更新的值或者兩個同時使用都可以。

2. explicit_defaults_for_timestamp

這個系統變數決定了 MySQL 是否為 TIMESTAMP 列的預設值和 NULL 值的處理啟用某些非標準的行為。在 MySQL5.7 的預設情況下，explicit_defaults_for_timestamp 是禁用的，這將啟用非標準的行為。在 MySQL8.0 的預設值是開啟的。本文預設在 MySQL5.7 場景下。

看場景

業務報錯”Column ‘create_time’ cannot be null”，該列不能插入 null 值，檢視一下表結構：

#只展示部分時間相關列
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
`update_time` timestamp NULL DEFAULT NULL COMMENT '更新時間',

可以看到 create_time 列的屬性是 not null ，按照慣性思維想，此列不應該插入 null ，為何之前的環境是沒有問題的呢？經檢查引數發現問題出在 explicit_defaults_for_timestamp 引數上，在遷移前系統沒有單獨設定該引數值，從 MySQL5.7 的官方文件可知，此時使用預設值為 OFF ，在遷移後的新系統使用的愛可生的 DMP 資料庫運維平臺的預設 MySQL5.7 配置檔案，此時配置檔案是配置了該引數值為 ON 。

現場進行引數關閉，改為 OFF ，測試插入正常。那麼引數值具體為何能操縱 TIMESTAMP 列的預設值和 null 值呢？繼續測試分析。

測試分析

1.首先是看一下官網對 explicit_defaults_for_timestamp 詳細解釋：

（1）如果 explicit_defaults_for_timestamp=OFF ，伺服器會啟用非標準行為，並按以下方式處理 TIMESTAMP 列：

  • 沒有明確使用NULL屬性宣告的TIMESTAMP列會自動使用NOT NULL屬性宣告。給這樣的列分配一個NULL的值是允許的，並將該列設定為current timestamp。
  • 表中的第一個TIMESTAMP列，如果沒有明確地用NULL屬性，DEFAULT屬性或ON UPDATE屬性宣告，將自動用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP屬性宣告。
  • 在第一個列之後的TIMESTAMP列，如果沒有明確地用NULL屬性或明確的DEFAULT屬性來宣告，就會自動宣告為DEFAULT '0000-00-00 00:00:00' 。對於插入的行，如果沒有為該列指定明確的值，那麼該列將被分配為'0000-00-00 00:00:00'，並且不會發生警告。根據是否啟用了嚴格的SQL mode或包含NO_ZERO_DATE的SQL mode，預設值'0000-00-00 00:00:00'可能是不被允許的。

另外需要知道的是這種非標準行為已被廢棄；預計將在MySQL的未來版本中被刪除。

（2）如果 explicit_defaults_for_timestamp=ON ，伺服器將禁用非標準行為並按如下方式處理 TIMESTAMP 列：

  • 不能實現給 TIMESTAMP 列插入一個 NULL 的值，然後自動設定為當前的時間戳。想要插入當前的時間戳，需要將該列設定為 CURRENT_TIMESTAMP 或一個同義詞，比如 NOW() 。
  • 沒有明確地用 NOT NULL 屬性宣告的 TIMESTAMP 列會自動用 NULL 屬性宣告，並允許 NULL 值。給這樣的列插入一個 NULL 值，會把它設定為 NULL 值，而不是當前的時間戳。
  • 用 NOT NULL 屬性宣告的 TIMESTAMP 列不允許NULL值。對於列指定插入 NULL ，如果啟用嚴格的 SQL mode ，其結果是單行插入報錯，或者在禁用嚴格的 SQL 模式下，多行插入的結果是'0000-00-00 00:00:00'。在任何情況下，給該列賦值為 NULL 都不會將其設定為當前的時間戳。
  • 用 NOT NULL 屬性明確宣告的 TIMESTAMP 列，如果沒有明確的 DEFAULT 屬性，將被視為沒有預設值。對於插入的行，如果沒有為這樣的列指定明確的值，其結果取決於 SQL mode 。如果啟用了嚴格的 SQL mode ，會報錯。如果沒有啟用嚴格的 SQL mode ，該列則被宣告為隱含的預設值 "0000-00-00 00:00:00"，併發出 warning 。這與 MySQL 處理其他時間型別（如 DATETIME ）的方式相似。

2.做個測試就可以看出來：

（1）explicit_defaults_for_timestamp=OFF ：

mysql> show variables like "%explicit_defaults_for_timestamp%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

建立一個帶有timestamp列的表：

mysql> create table time_off(id int,time timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> show create table time_off;
+----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------+
| Table    | Create Table                                                                                                                                                                                           |
+----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------+
| time_off | CREATE TABLE `time_off` (
  `id` int(11) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------+

1 row in set (0.00 sec)

可以看到此時 timestamp 列會有預設屬性‘NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP’。

向該表插入NULL值試試看：

mysql> insert into time_off values (1,null);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from time_off;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2021-10-12 01:05:28 |
+------+---------------------+
1 row in set (0.00 sec)
 
 
mysql> update time_off set id=2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from time_off;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    2 | 2021-10-12 01:06:30 |
+------+---------------------+
1 row in set (0.00 sec)

發現當 timestamp 列插入 null 值時會正常插入，並自動轉換為當前時間戳。更新其他列時也會依據‘ON UPDATE CURRENT_TIMESTAMP’來更新為當前的時間戳。

（2）explicit_defaults_for_timestamp=ON ：

mysql> show variables like "%explicit_defaults_for_timestamp%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+
1 row in set (0.01 sec)

建立一個帶有 timestamp 列的表：

mysql> create table time_on(id int,time timestamp);
Query OK, 0 rows affected (0.01 sec)
 
mysql>  show create table time_on;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                           |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| time_on | CREATE TABLE `time_on` (
  `id` int(11) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到此時 timestamp 列會有預設屬性‘NULL DEFAULT NULL’。
向該表插入 NULL 值試試看：

mysql> insert into time_on values (1,null);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from time_on;
+------+------+
| id   | time |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

會發現能夠成功插入，插入的為 NULL 值，而非當前的時間戳。

那麼在該引數下，向引數值為 OFF 時建立的表 time_off 裡插入 null 值會有什麼情況呢：

mysql> insert into time_off values (3,null);
ERROR 1048 (23000): Column 'time' cannot be null

會發現此時插入報錯’Column 'time' cannot be null’，符合官方文件對該引數的說明，也證明了業務測試報錯的原因是 explicit_defaults_for_timestamp 的引數值設定為 ON ，導致業務插入資料失敗。

結語

關於該引數，實際上是規範了 MySQL 時間相關的操作，使之更加嚴格，是有助於MySQL的規範化使用的，所以 MySQL 後續也廢棄掉該引數。

細節決定成敗，很多同學對遷移工作覺得是輕車熟路，但是沒有合理的遷移規劃，沒有經過嚴謹的業務測試，確實不太好說遷移的整個過程會是順順利利的，有時候坑就在小的細節點上。

相關文章