null調整為not null default xxx，不得不注意的坑

iVictor發表於2021-01-30
Null

最近碰到一個case，值得分享一下。

 

現象

一個DDL，將列的屬性從null調整為not null default xxx，

alter table slowtech.t1 modify name varchar(10) not null default 'slowtech';

通過平臺執行（平臺呼叫的是pt-online-schema-change）。

但在執行的過程中，業務SQL報錯，提示“ERROR 1048 (23000): Column 'name' cannot be null”。

 

在剖析具體的問題之前，首先，我們看看pt-online-schema-change的原理。

 

PT-OSC的實現原理

 

從原理圖中可以看到，

1.  對於全量資料的同步，pt-online-schema-change是以chunk為單位分批來拷貝的。

2.  對於增量資料的同步，pt-online-schema-change是通過觸發器來實現的。

 

結合pt-online-schema-change的原理，我們來重現下問題場景。

mysql> create table slowtech.t1(id int primary key,name varchar(10));

mysql> create table slowtech._t1_new(id int primary key,name varchar(10));

mysql> alter table slowtech._t1_new modify name varchar(10) not null default 'slowtech';

mysql> create trigger slowtech.`pt_osc_slowtech_t1_ins` after insert on `slowtech`.`t1` for each row replace into `slowtech`.`_t1_new` (`id`, `name`) values (new.`id`, new.`name`);

mysql> insert into slowtech.t1(id) values(1);
ERROR 1048 (23000): Column 'name' cannot be null

問題完美呈現，有的童鞋可能會有疑問，t1的name列預設不是null麼？為什麼不允許null值的插入？

 

問題原因

問題出在觸發器上面。

觸發器會將業務SQL（“insert into slowtech.t1(id) values(1)”）和觸發操作（“replace into slowtech._t1_new (id, name) values(1, null)”）放到一個事務內執行。

“insert into slowtech.t1(id) values(1)”並不違反t1表的約束，但違反了_t1_new表的約束。

 

通過上面的分析，我們得到了兩點啟示：

1.  類似DDL（將列的屬性從null修改為not null default 'abc'）要注意。

從原理上看，既然涉及到全量資料+增量資料的同步，都會存在這種問題，不單單是pt-online-schema-change，包括Online DDL，gh-ost同樣如此。

只不過，觸發器這種方案會將業務SQL和觸發操作耦合在一起，相對來說，對業務有一定的侵入性。

 

2. 既然觸發器會將業務SQL和觸發操作放到一個事務內執行，如果pt-online-schema-change異常退出，留下了觸發器和中間表（_t1_new），在清理現場時，應首先刪除觸發器，再刪除中間表。

如果首先刪除中間表，會導致針對原表的所有DML操作失敗。

mysql> drop table slowtech._t1_new;

mysql> insert into slowtech.t1 values(1,'victor');
ERROR 1146 (42S02): Table 'slowtech._t1_new' doesn't exist

 

資料拷貝也有坑

在執行DDL之前，還有一段小插曲。

在執行DDL之前，開發提單將該列的null值修改為了預設值。這樣就導致了，問題是在業務SQL插入的過程中暴露的，而不是在資料拷貝過程中暴露。

在資料拷貝的過程中，如果拷貝的資料中，該列存在null值，pt-online-schema-change會直接報錯退出。

mysql> create table slowtech.t1(id int primary key,name varchar(10));

mysql> insert into slowtech.t1(id) values(1);

# pt-online-schema-change h=xxxxx,u=root,p=123456,D=slowtech,t=t1 --alter "modify name varchar(10) not null default 'slowtech'" --execute
No slaves found.  See --recursion-method if host xxxx has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `slowtech`.`t1`...
Creating new table...
Created new table slowtech._t1_new OK.
Altering new table...
Altered `slowtech`.`_t1_new` OK.
2020-09-07T09:13:25 Creating triggers...
2020-09-07T09:13:25 Created triggers OK.
2020-09-07T09:13:25 Copying approximately 1 rows...
2020-09-07T09:13:25 Dropping triggers...
2020-09-07T09:13:25 Dropped triggers OK.
2020-09-07T09:13:25 Dropping new table...
2020-09-07T09:13:25 Dropped new table OK.
`slowtech`.`t1` was not altered.
        (in cleanup) 2020-09-07T09:13:25 Error copying rows from `slowtech`.`t1` to `slowtech`.`_t1_new`: 2020-09-07T09:13:25 Copying rows caused a MySQL error 1048:
    Level: Warning
     Code: 1048
  Message: Column 'name' cannot be null
    Query: INSERT LOW_PRIORITY IGNORE INTO `slowtech`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `slowtech`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 9234 copy table*/
2020-09-07T09:13:25 Dropping triggers...
2020-09-07T09:13:25 Dropped triggers OK.
`slowtech`.`t1` was not altered.

 

上述報錯，pt-online-schema-change加個引數即可規避（--null-to-not-null）。

在實現上，該引數會忽略1048錯誤，此時，對於字元型別的列，會填充空字元，對於數字型別的列，會填充0。

mysql> create table slowtech.t1(id int primary key,name varchar(10));

mysql> create table slowtech._t1_new(id int primary key,name varchar(10));

mysql> alter table slowtech._t1_new modify name varchar(10) not null default 'slowtech';

mysql> insert into slowtech.t1(id) values(1);

mysql> select * from slowtech.t1;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
+----+------+
1 row in set (0.00 sec)

mysql> insert low_priority ignore into slowtech._t1_new (id, name) select id, name from slowtech.t1 lock in share mode;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+------------------------------+
| Level   | Code | Message                      |
+---------+------+------------------------------+
| Warning | 1048 | Column 'name' cannot be null |
+---------+------+------------------------------+
1 row in set (0.00 sec)

mysql> select * from slowtech._t1_new;
+----+------+
| id | name |
+----+------+
|  1 |      |
+----+------+
1 row in set (0.00 sec)

所以，線上使用該引數要注意，要確認被填充的值是否符合自己的預期行為。

 

從目前的分析來看，要將一個列的屬性從null直接修改為not null default xxx，幾乎是不可能的，除非：

1.  該列不存在null值。

2.  在DDL的過程中，沒有類似於“insert into slowtech.t1(id) values(1)”的業務SQL出現。

 

結論

很顯然，這兩個條件很難同時滿足。既然如此，這個需求還能實現嗎？能！只不過比較複雜。

下面，看看具體的實施步驟。

1. 首先，將列的屬性調整為null default xxx，這樣做的目的是為了避免增量同步過程中，類似“insert into slowtech.t1(id) values(1)”的業務SQL，產生新的null值。

2. 其次，手動將null值調整為預設值。需要注意的是，如果記錄數較多，這一步的操作難度也是極大的。

3. 最後，將列的屬性調整為not null default xxx。

 

對於not null default xxx的正確理解

在很多資料庫規範裡面，都推薦將列定義為not null default xxx，但很多童鞋，對這段定義的實際效果卻相當模糊。

下面具體來說說，這段定義的實際作用。這段定義實際上由兩部分組成：

1.  not null，約束，指的是不可顯式插入null值，如，

mysql> create table slowtech.t1(id int primary key,name varchar(10) not null default 'slowtech');

mysql> insert into slowtech.t1 values(1,null);
ERROR 1048 (23000): Column 'name' cannot be null

2.  default 'slowtech'，如果在插入時，沒有顯式指定值，則以預設值填充。

mysql> insert into slowtech.t1(id) values(1);

mysql> select * from slowtech.t1;
+----+----------+
| id | name     |
+----+----------+
|  1 | slowtech |
+----+----------+
1 row in set (0.00 sec)

可以看到，這兩部分其實沒有任何關係，對於一個列，我們同樣可以定義為null default xxx。

 

相關文章