最近碰到一个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的原理。
从原理图中可以看到，
-
对于全量数据的同步，pt-online-schema-change是以chunk为单位分批来拷贝的。
-
对于增量数据的同步，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表的约束。
通过上面的分析，我们得到了两点启示：
- 类似DDL（将列的属性从null修改为not null default ‘abc’）要注意。
从原理上看，既然涉及到全量数据+增量数据的同步，都会存在这种问题，不单单是pt-online-schema-change，包括Online DDL，gh-ost同样如此。
只不过，触发器这种方案会将业务SQL和触发操作耦合在一起，相对来说，对业务有一定的侵入性。
- 既然触发器会将业务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，几乎是不可能的，除非：
-
该列不存在null值。
-
在DDL的过程中，没有类似于”insert into slowtech.t1(id) values(1)”的业务SQL出现。
结论
很显然，这两个条件很难同时满足。既然如此，这个需求还能实现吗？能！只不过比较复杂。
下面，看看具体的实施步骤。
-
首先，将列的属性调整为null default xxx，这样做的目的是为了避免增量同步过程中，类似”insert into slowtech.t1(id) values(1)”的业务SQL，产生新的null值。
-
其次，手动将null值调整为默认值。需要注意的是，如果记录数较多，这一步的操作难度也是极大的。
-
最后，将列的属性调整为not null default xxx。
对于not null default xxx的正确理解
在很多数据库规范里面，都推荐将列定义为not null default xxx，但很多童鞋，对这段定义的实际效果却相当模糊。
下面具体来说说，这段定义的实际作用。这段定义实际上由两部分组成：
- 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
- 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。
Original: https://www.cnblogs.com/ivictor/p/14347901.html
Author: iVictor
Title: null调整为not null default xxx，不得不注意的坑
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/599270/
转载文章受原作者版权保护。转载请注明原作者出处！