关于mysql:一入职就遇到-MySQL-这么大-Bug差点背锅走人

6次阅读

共计 4970 个字符，预计需要花费 13 分钟才能阅读完成。

往年这种状况，有时候不找好下家还真不敢跳，这不，前段时间刚跳到新东家，刚办入职那天，就遇上事了，真的是吓出一身冷汗（老大始终盯着我，说要疾速解决这个问题），差点被（背）开（锅）了 ….

状况如何？且听我上面缓缓道来！！！心愿对大家有所帮忙与借鉴。

线上有个重要 Mysql 客户的表在从 5.6 降级到 5.7 后，master 上插入过程中呈现 ”Duplicate key” 的谬误，而且是在主备及 RO 实例上都呈现。

以其中一个表为例，迁徙前通过“show create table”命令查看的 auto increment id 为 1758609，迁徙后变成了 1758598, 理论对迁徙生成的新表的自增列用 max 求最大值为 1758609。

用户采纳的是 Innodb 引擎，而且据运维同学介绍，之前碰到过相似问题，重启即可恢复正常。

因为用户反馈在 5.6 上拜访失常，切换到 5.7 后就报错。因而，首先得狐疑是 5.7 内核出了问题，因而第一反馈是从官网 bug list 中搜寻一下是否有相似问题存在，防止反复造车。通过搜寻，发现官网有 1 个相似的 bug，这里简略介绍一下该 bug。

Innodb 引擎中的 auto increment 相干参数及数据结构。

主要参数包含：innodb_autoinc_lock_mode 用于管制获取自增值的加锁形式，auto_increment_increment，auto_increment_offset 用于管制自增列的递增的距离和起始偏移。

次要波及的构造体包含：数据字典构造体，保留整个表的以后 auto increment 值以及爱护锁；事务构造体，保留事务外部解决的行数；handler 构造体，保留事务外部多行的循环迭代信息。

mysql 及 Innodb 引擎中对 autoincrement 拜访及批改的流程

(1) 数据字典构造体 (dict_table_t) 换入换出时对 autoincrement 值的保留和复原。换出时将 autoincrement 保留在全局的的映射表中，而后淘汰内存中的 dict_table_t。换入时通过查找全局映射表复原到 dict_table_t 构造体中。相干的函数为 dict_table_add_to_cache 及 dict_table_remove_from_cache_low。
(2) row_import, table truncate 过程更新 autoincrement。
(3) handler 首次 open 的时候，会查问以后表中最大自增列的值，并用最大列的值加 1 来初始化表的 data_dict_t 构造体中的 autoinc 的值。
(4) insert 流程。相干对 autoinc 批改的堆栈如下：

ha_innobase::write_row：write_row 的第三步中调用 handler 句柄中的 update_auto_increment 函数更新 auto increment 的值。handler::update_auto_increment: 调用 Innodb 接口获取一个自增值，并依据以后的 auto_increment 相干变量的值调整获取的自增值；同时设置以后 handler 要解决的下一个自增列的值。ha_innobase::get_auto_increment: 获取 dict_tabel 中的以后 auto increment 值，并依据全局参数更新下一个 auto increment 的值到数据字典中
ha_innobase::dict_table_autoinc_initialize: 更新 auto increment 的值，如果指定的值比以后的值大，则更新。handler::set_next_insert_id: 设置以后事务中下一个要解决的行的自增列的值。

(5) update_row。对于”INSERT INTO t (c1,c2) VALUES(x,y) ON DUPLICATE KEY UPDATE”语句，无论惟一索引列所指向的行是否存在，都须要推动 auto increment 的值。相干代码如下：

if (error == DB_SUCCESS
    && table->next_number_field
    && new_row == table->record[0]
    && thd_sql_command(m_user_thd) == SQLCOM_INSERT
    && trx->duplicates)  {
    ulonglong    auto_inc;
       ……
    auto_inc = table->next_number_field->val_int();
    auto_inc = innobase_next_autoinc(auto_inc, 1, increment, offset, col_max_value);
    error = innobase_set_max_autoinc(auto_inc);
       ……
}

从咱们的理论业务流程来看，咱们的谬误只可能波及 insert 及 update 流程。

BUG 76872 / 88321: "InnoDB AUTO_INCREMENT produces same value twice"

(1) bug 概述: 当 autoinc_lock_mode 大于 0，且 auto_increment_increment 大于 1 时，零碎刚重启后多线程同时对表进行 insert 操作会产生“duplicate key”的谬误。
(2) 起因剖析：重启后 innodb 会把 autoincrement 的值设置为 max(id) + 1。

此时，首次插入时，write_row 流程会调用 handler::update_auto_increment 来设置 autoinc 相干的信息。首先通过 ha_innobase::get_auto_increment 获取以后的 autoincrement 的值(即 max(id) + 1)，并依据 autoincrement 相干参数批改下一个 autoincrement 的值为 next_id。

当 auto_increment_increment 大于 1 时，max(id) + 1 会不大于 next_id。handler::update_auto_increment 获取到引擎层返回的值后为了避免有可能某些引擎计算自增值时没有思考到以后 auto increment 参数，会从新依据参数计算一遍以后行的自增值，因为 Innodb 外部是思考了全局参数的，因而 handle 层对 Innodb 返回的自增 id 算出的自增值也为 next_id，行将会插入一条自增 id 为 next_id 的行。

handler 层会在 write_row 完结的时候依据以后行的值 next_id 设置下一个 autoincrement 值。如果在 write_row 尚未设置表的下一个 autoincrement 期间，有另外一个线程也在进行插入流程，那么它获取到的自增值将也是 next_id。这样就产生了反复。

(3) 解决办法：引擎外部获取自增列时思考全局 autoincrement 参数，这样重启后第一个插入线程获取的自增值就不是 max(id) + 1，而是 next_id，而后依据 next_id 设置下一个 autoincrement 的值。因为这个过程是加锁爱护的，其余线程再获取 autoincrement 的时候就不会获取到反复的值。

通过上述剖析，这个 bug 仅在 autoinc_lock_mode > 0 并且 auto_increment_increment > 1 的状况下会产生。理论线上业务对这两个参数都设置为 1，因而，能够排除这个 bug 造成线上问题的可能性。

既然官网 bug 未能解决咱们的问题，那就得自食其力，从谬误景象开始剖析了。

(1) 剖析 max id 及 autoincrement 的法则因为用户的表设置了 ON UPDATE CURRENT_TIMESTAMP 列，因而能够把所有的出错的表的 max id、autoincrement 及最近更新的几条记录抓取进去，看看是否有什么法则。抓取的信息如下：

乍看起来，这个谬误还是很有法则的，update time 这一列是最初插入或者批改的工夫，联合 auto increment 及 max id 的值，景象很像是最初一批事务只更新了行的自增 id，没有更新 auto increment 的值。

联想到【官网文档】中对 auto increment 用法的介绍，update 操作是能够只更新自增 id 但不触发 auto increment 推动的。依照这个思路，我尝试复现了用户的现场。复现办法如下：

同时在 binlog 中，咱们也看到有 update 自增列的操作。如图：

不过，因为 binlog 是 ROW 格局，咱们也无奈判断这是内核出问题导致了自增列的变动还是用户本人更新所致。因而咱们分割了客户进行确认，后果用户很确定没有进行更新自增列的操作。

那么这些自增列到底是怎么来的呢？

(2) 剖析用户的表及 sql 语句持续剖析，发现用户总共有三种类型的表

hz_notice_stat_sharding
hz_notice_group_stat_sharding
hz_freeze_balance_sharding

这三种表都有自增主键。

然而后面两种都呈现了 autoinc 谬误，唯独 hz_freeze_balance_sharding 表没有出错。难道是用户对这两种表的拜访形式不一样？抓取用户的 sql 语句，果然，前两种表用的都是 replace into 操作，最初一种表用的是 update 操作。难道是 replace into 语句导致的问题？搜寻官网 bug, 又发现了一个疑似 bug。

bug #87861:“Replace into causes master/slave have different auto_increment offset values”

起因：

(1) Mysql 对于 replace into 理论是通过 delete + insert 语句实现，然而在 ROW binlog 格局下，会向 binlog 记录 update 类型日志。Insert 语句会同步更新 autoincrement，update 则不会。
(2) replace into 在 Master 上依照 delete+insert 形式操作，autoincrement 就是失常的。基于 ROW 格局复制到 slave 后，slave 机上依照 update 操作回放，只更新行中自增键的值，不会更新 autoincrement。

因而在 slave 机上就会呈现 max(id)大于 autoincrement 的状况。此时在 ROW 模式下对于 insert 操作 binlog 记录了所有的列的值，在 slave 上回放时并不会重新分配自增 id，因而不会报错。然而如果 slave 切 master，遇到 Insert 操作就会呈现”Duplicate key”的谬误。

(3) 因为用户是从 5.6 迁徙到 5.7，而后间接在 5.7 上进行插入操作，相当于是 slave 切主，因而会报错。

业务侧的可能解决方案：

(1) binlog 改为 mixed 或者 statement 格局。
(2) 用 Insert on duplicate key update 代替 replace into。

内核侧可能解决方案：

(1) 在 ROW 格局下如果遇到 replace into 语句，则记录 statement 格局的 logevent，将原始语句记录到 binlog。
(2) 在 ROW 格局下将 replace into 语句的 logevent 记录为一个 delete event 和一个 insert event。

(1) autoincrement 的 autoinc_lock_mode 及 auto_increment_increment 这两个参数变动容易导致呈现反复的 key，应用过程中要尽量避免动静的去批改。
(2) 在碰到线上的问题时，首先应该做好现场剖析，明确故障产生的场景、用户的 SQL 语句、故障产生的范畴等信息，同时要对波及实例的配置信息、binlog 甚至实例数据等做好备份以防过期失落。

只有这样能力在找官网 bug 时精准的匹配场景，如果官网没有相干 bug，也能通过已有线索独立剖析。