MySQL生产事故一例

背景

线上日志报错：

18:57:54.985 [http-nio-8082-exec-9] ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'inserttime' cannot be null
### The error may exist in class path resource [mapping/VarMonitorMapper.xml]
### The error may involve com.aaa.dao.VarMonitorMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into var_monitor (var_id, inserttime, insertby, updatetime, updateby) values (?, ?, ?, ?, ?)
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'inserttime' cannot be null
; Column 'inserttime' cannot be null; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'inserttime' cannot be null] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'inserttime' cannot be null

对应的建表语句：

inserttime timestamp default CURRENT_TIMESTAMP not null comment '插入时间',

分析

问题总结：
测试环境和生产环境 inserttime 字段的定义相同：​​​inserttime timestamp default CURRENT_TIMESTAMP not null comment '插入时间'​​​，差别之处在于版本号。
测试环境：

select version();
5.7.16

生产环境：

select version();
5.7.21-20-log

带着关键字​​timestamp default CURRENT_TIMESTAMP​​​和​​Column 'inserttime' cannot be null​​​搜索Google。找到​​MySQL案例一则​​。

测试环境：

show variables like '%explicit_defaults_for_timestamp%';
explicit_defaults_for_timestamp=OFF

生产环境：

show variables like '%explicit_defaults_for_timestamp%';
explicit_defaults_for_timestamp=ON

建表语句的意思是insert数据时，如果 inserttime 数据为空，则用系统default时间，这也是测试环境没有发现这个问题的原因。
生产环境里，​​​explicit_defaults_for_timestamp=ON​​​，即如果 inserttime 数据为空，则报错​​Column 'inserttime' cannot be null​​。注意：可以直接通过客户端如 dataGrip insert 一条 inserttime is null 的数据。

解决方法

1. 修改生产参数配置，一来不建议，二来没有修改权限，需要联系DBA执行（还说服他们）：
   ​​​set explicit_defaults_for_timestamp=OFF​​
2. mybatis使用insertSelective而不用insert，建议：

<insert id="insert" parameterType="com.aaa.model.VarMonitor">
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
        SELECT LAST_INSERT_ID()
    </selectKey>
    insert into var_monitor (var_id, inserttime, insertby, updatetime, updateby)
    values (#{varId,jdbcType=INTEGER}, #{inserttime,jdbcType=TIMESTAMP}, #{insertby,jdbcType=VARCHAR}, #{updatetime,jdbcType=TIMESTAMP}, #{updateby,jdbcType=VARCHAR})
</insert>

<insert id="insertSelective" parameterType="com.aaa.model.VarMonitor">
   <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
       SELECT LAST_INSERT_ID()
   </selectKey>
   insert into var_monitor
   <trim prefix="(" suffix=")" suffixOverrides=",">
       <if test="varId != null">
           var_id,
       </if>
       <if test="inserttime != null">
           inserttime,
       </if>
       <if test="insertby != null">
           insertby,
       </if>
       <if test="updatetime != null">
           updatetime,
       </if>
       <if test="updateby != null">
           updateby,
       </if>
   </trim>
   <trim prefix="values (" suffix=")" suffixOverrides=",">
       <if test="varId != null">
           #{varId,jdbcType=INTEGER},
       </if>
       <if test="inserttime != null">
           #{inserttime,jdbcType=TIMESTAMP},
       </if>
       <if test="insertby != null">
           #{insertby,jdbcType=VARCHAR},
       </if>
       <if test="updatetime != null">
           #{updatetime,jdbcType=TIMESTAMP},
       </if>
       <if test="updateby != null">
           #{updateby,jdbcType=VARCHAR},
       </if>
   </trim>
</insert>

3. 修改DB设置，不建议：
   ​​​alter table var_monitor modify column inserttime timestamp default CURRENT_TIMESTAMP null comment '插入时间';​​

参考

​​MySQL案例一则​​