explicit_defaults_for_timestamp参数导致复制中断

explicit_defaults_for_timestamp是从5.6.6引入的一个新参数，默认是off。

作用：对TIMESTAMP类型列的默认值和NULL值的处理，是否启用非标准特性。

默认情况下，explicit_defaults_for_timestamp被禁用，即启用非标准特性。

什么是非标准特性？

标准特性：如果没有显示声明为 NOT NULL，则默认声明为 NULL （除timestamp外的其他数据类型）

非标准特性：如果没有显示声明为 NULL，则默认声明为 NOT NULL（timestamp）

01

当explicit_defaults_for_timestamp=0时，默认状态，即启用非标准特性

1. TIMESTAMP列如果没有显示声明NULL属性，则会自动加上NOT NULL属性 1）如果往这个列中插入null值，会自动的设置该列的值为current timestamp值。 2）表中的第一个TIMESTAMP列，如果没有指定null属性或者没有指定默认值，也没有指定ON UPDATE语句。那么该列会自动被加上DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性。 3）第一个TIMESTAMP列之后的其他的TIMESTAMP类型的列，如果没有指定null属性，也没有指定默认值，那么该列会被自动加上DEFAULT '0000-00-00 00:00:00'属性。如果insert语句中没有为该列指定值，那么该列中插入'0000-00-00 00:00:00'，并且没有warning。
2. TIMESTAMP列如果没有显式声明NOT NULL属性(或显示声明NULL属性)，那么默认的该列可以为NULL 1）此时向该列中插入null值时，会直接记录null

测试1：

id=1的行，往timestamp列插入null值时，会自动为该列设置为current time

id=2的行，插入时未指定值的timestamp列中被插入了0000-00-00 00:00:00(非表中第一个timestamp列)

id=3的行，插入时未指定值的第一个timestamp列中被插入了current time值

02

当explicit_defaults_for_timestamp=1时，即启用标准特性

1. TIMESTAMP列如果没有显式声明NOT NULL属性(或显示声明NULL属性)，那么默认的该列可以为null 1）此时向该列中插入null值时，会直接记录null，而不是current timestamp值。
2. TIMESTAMP列如果显示声明NOT NULL属性 1）没有指定默认值，此时如果向表中插入记录，但是没有给该TIMESTAMP列指定值的时候，如果strict sql_mode被指定了，那么会直接报错。如果strict sql_mode没有被指定，那么会向该列中插入'0000-00-00 00:00:00'并且产生一个warning 2）指定了默认值，除DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP外，默认值会变成'0000-00-00 00:00:00'

测试2：

id=1的行，如果timestamp列指定not null属性，在非stric sql_mode模式下，如果插入的时候该列没有指定值，那么会向该列中插入0000-00-00 00:00:00，并且产生告警

★

通过对 explicit_defaults_for_timestamp的了解，排查一个问题

★

架构如下：

现象：

二级从库复制频繁中断，一级从库正常

日志：

2017-08-18 14:17:47 357305 [ERROR] Slave SQL: Error 'Column 'modified' cannot be null' on query. Default database: 'jpos'. Query: 'insert into activity_account_sum(id,activity_id,income_number,expend_number,created,modified) values(0,584126,0,0,'2017-08-18 14:17:46',null)', Error_code: 1048
2017-08-18 14:17:47 357305 [Warning] Slave: Column 'modified' cannot be null Error_code: 1048
2017-08-18 14:17:47 357305 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000233' position 879369308

初步分析：

通过日志信息可知，modified字段插入了null值，与表声明的not null冲突，导致错误引起复制中断。

具体分析：

• mysql主库为5.5.38版本，一、二级从库版本为5.6.36，我们知道5.6后引入explicit_defaults_for_timestamp参数，通过查看explicit_defaults_for_timestamp=on，也就是timestamp采用的是标准特性，modified字段指定了NOT NULL并且有DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP默认值。
• 查看表结构：

• 查看sql mode：都没有启用stric严格模式

• 通过以上信息可知，modified列插入null值会报错：ERROR 1048 (23000): Column 'modified' cannot be null。但是为什么一级从库没有报错呢？二级从库已获取到binlog，说明一级从库已执行完成。主库是5.5.38，比从库版本低，推断可能是出于版本兼容性考虑，在解析SQL时保持与5.5.38一致的策略，所以一级从库执行成功；二级从库与一级从库版本一致，直接使用5.6.36解析SQL的策略，所以二级从库执行失败。

解决：

1. 修改二级从库explicit_defaults_for_timestamp=0，往timestamp数据类型列插入null值时，会自动为该列设置为current time（需要重启mysql服务后恢复）
2. 研发修改sql，将null值修改成now()

explicit_defaults_for_timestamp跟其他参数正好相反，NULL或NOT NULL需要十分注意，最好的方式就是规范话，统一为NOT NULL 再加上默认值，即便如此，跨版本之间也容易出现问题，所以新版本上线前新引入的参数一定要有所了解，不然一不小心就会入坑。