最近碰到一個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-OSC的實作原理
在剖析具體的問題之前，首先，我們看看pt-online-schema-change的原理，
從原理圖中可以看到，
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，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/254751.html
標籤：其他