最近碰到一个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。