MySQL自增列导致主键重复案例分析

点击上方蓝色字体，关注我们

一、现象描述

        2020-05-08 10:00左右有开发反馈线上有个表报主键冲突，报错如下

其实就是往一个表insert一条数据报错

二、原因分析

       1、对于这个报错，有经验的DBA第一反应是不是整形溢出了，可是笔者查看主键类型为int，max(id)才几百万，肯定不是这个原因，排除

       2、于是笔者查看表结构信息，如图所示

看出问题了吗？表自增初始值居然比max(id)小，了解自增ID的都知道，当有记录进行插入时，自增列产生的值就有可能与已有的记录主键冲突，导致出错，为了先恢复业务，于是笔者临时调整了自增初始值，只要确保比max(id)大即可，可是原因总要分析下不是吗？刚临时解决完这个表的问题，另外一个业务开发反馈另外一个实例也发生类似问题，也是报主键冲突，查看现象都是类似的，对于DBA来说，第一原则永远是先恢复业务后排查问题，于是笔者马上也调大报错表的自增值，先让表恢复使用。

       3、对于DBA来说怎么快速troubleshooting，根据现场的记录和现象快速定位原因呢？对于使用TDSQL我们只能从我们使用层面上去排查问题，其实笔者之前也怀疑有可能是TDSQL的bug导致的，比如计数器没有及时更新，当然后面我们把这个原因排查了。也曾想过是不是因为自增ID没有持久化的原因，比如实例重启了，但是一想也不可能，因为首先我们没收到实例挂掉的告警，而且我们公司业务不依赖自增id，其次也不会导致数据插入失败的，于是这个也pass掉。

提示：MySQL8.0以前自增id是不持久化的，所以实例重启之后，自增初始值是max(id)+1开始算的，其实对于依赖自增id的业务或者归档数据可能会有一些问题，MySQL8.0之后将自增主键的计数器持久化到redo log中。每次计数器发生改变，都会将其写入到redo log中。如果数据库发生重启，InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响，计数器写入到redo log中，并不会马上刷新。当然MySQL8.0之前的bug只针对InnoDB，Myiasm是没有这个问题的。

         4、经过前面排除一系列的原因之后，笔者就想是不是开发去更新主键id呢？于是去找开发看了一部分代码，部分代码如下：

<update id="updateByExampleSelective" parameterType="map">
    update xxxx_env_version_history
    <set>
      <if test="record.id != null">
        id = #{record.id,jdbcType=INTEGER},
      </if>
      <if test="record.apiId != null">
        api_id = #{record.apiId,jdbcType=VARCHAR},
      </if>
      <if test="record.version != null">
        version = #{record.version,jdbcType=VARCHAR},
      </if>

这个明显是去更新主键id，可是开发说传的id和更新的id是一样的，好吧，笔者去拉取binlog分析一番，后面发现更新id和where条件id是不一致的，由于binlog过多，只展示部分binlog信息，如下图所示：

看到了吗，更新的id比原来的id大，这就会出现max(id)比自增初始值大，从而发生导致主键冲突的情况，既然知道原因，那么解决办法就简单多了。

三、解决方案

        1、将主键id设置为Null，部分代码如下：

 for (OpenApiVersionHistory info : openApiInfo.getOpenApiVersionHistoryList()) {
            info.setId(null);
            if (info.getVersion().equals(openApiInfo.getSubmitVersion())) {
                info.setCurrentUse(1);
            } else {
                info.setCurrentUse(0);
            }

        2、更新字段把自增id去掉即可。     

四、数据库规范

      【强制】业务逻辑不能包含更新自增主键的代码

扫描二维码

获取更多知识

DBA入坑指南