null调整为not null default xxx，不得不注意的坑

概述最近碰到一个case，值得分享一下。 现象 一个DDL，将列的属性从null调整为not null default xxx， alter table slowtech.t1 modify name v

最近碰到一个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的原理，我们来重现下问题场景。

@R_502_5605@> create table slowtech.t1(ID int primary key,name 10));@R_502_5605@table slowtech._t1_new(ID table slowtech._t1_new modify name ';@R_502_5605@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`);@R_502_5605@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 *** 作失败。

@R_502_5605@drop table slowtech._t1_new;@R_502_5605@into slowtech.t1 1,victor1146 (42S02): table slowtech._t1_new' doesnt exist

 

数据拷贝也有坑

在执行DDL之前，还有一段小插曲。

在执行DDL之前，开发提单将该列的null值修改为了默认值。这样就导致了，问题是在业务sql插入的过程中暴露的，而不是在数据拷贝过程中暴露。

在数据拷贝的过程中，如果拷贝的数据中，该列存在null值，pt-online-schema-change会直接报错退出。

@R_502_5605@);# pt-online-schema-change h=xxxxx,u=root,p=123456,D=slowtech,t=t1 --alter "modify name varchar(10) not null default 'slowtech'" --executeNo 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,  copy_rows,1); Font-weight: bold">0.25  create_triggers,1)">  drop_triggers,1)">  swap_tables,1)">  update_foreign_keys,1)">Altering `slowtech`.`t1`...Creating new ...Created new  slowtech._t1_new OK.Altering new ...Altered `slowtech`.`_t1_new` OK.2020-09-07T09:13:25 Creating triggers... Created triggers OK.25 copying approximately  rows... DropPing triggers... Dropped triggers OK.25 DropPing new ...25 Dropped new  OK.`slowtech`.`t1` was not altered.        (in cleanup) 25 Error copying rows from `slowtech`.`t1` to `slowtech`.`_t1_new`: 25 copying rows caused a @R_502_5605@ error 1048:    Level: Warning     Code:   Message: null    query: INSERT LOW_PRIORITY IGnorE INTO `slowtech`.`_t1_new` (`ID`,1)">SELECT `ID`,`name` FROM `slowtech`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 9234 copy table*/ Dropped triggers OK.`slowtech`.`t1` was not altered.

上述报错，pt-online-schema-change加个参数即可规避（--null-to-not-null）。

在实现上，该参数会忽略1048错误，此时，对于字符类型的列，会填充空字符，对于数字类型的列，会填充0。

@R_502_5605@);@R_502_5605@select * from slowtech.t1;+--+------+| ID | name ||  1 | NulL 1 row in set (0.00 sec)@R_502_5605@insert low_priority ignore into slowtech._t1_new (ID,name) select ID,1)">from slowtech.t1 lock in share mode;query OK,1); Font-weight: bold">1 row affected,1); Font-weight: bold">1 warning (0.01 sec)Records: 1  Duplicates: 0  Warnings: @R_502_5605@> show warnings;-------+------+------------------------------+Level   | Code | Message                      | Warning | 1048 null  slowtech._t1_new;|      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值，如，

@R_502_5605@null

2.  default 'slowtech'，如果在插入时，没有显式指定值，则以默认值填充。

@R_502_5605@--+----------+| name     | slowtech 0.00 sec)

可以看到，这两部分其实没有任何关系，对于一个列，我们同样可以定义为null default xxx。

 

总结

以上是内存溢出为你收集整理的null调整为not null default xxx，不得不注意的坑全部内容，希望文章能够帮你解决null调整为not null default xxx，不得不注意的坑所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。