好險(xiǎn)！一入職，就遇到 MySQL 大 Bug！差點(diǎn)背鍋?zhàn)呷?..

今年這種情況，有時(shí)候不找好下家還真不敢跳，這不，前段時(shí)間剛跳到新東家，剛辦入職那天，就遇上事了，真的是嚇出一身冷汗（老大一直盯著我，說(shuō)要快速解決這個(gè)問(wèn)題），差點(diǎn)被（背）開(kāi)（鍋）了....

情況如何？且聽(tīng)我下面慢慢道來(lái)！??！希望對(duì)大家有所幫助與借鑒。

問(wèn)題描述

線上有個(gè)重要Mysql客戶(hù)的表在從5.6升級(jí)到5.7后，master上插入過(guò)程中出現(xiàn)"Duplicate key"的錯(cuò)誤，而且是在主備及RO實(shí)例上都出現(xiàn)。

以其中一個(gè)表為例，遷移前通過(guò)“show create table” 命令查看的auto increment id為1758609， 遷移后變成了1758598,實(shí)際對(duì)遷移生成的新表的自增列用max求最大值為1758609。

用戶(hù)采用的是Innodb引擎，而且據(jù)運(yùn)維同學(xué)介紹，之前碰到過(guò)類(lèi)似問(wèn)題，重啟即可恢復(fù)正常。

內(nèi)核問(wèn)題排查

由于用戶(hù)反饋在5.6上訪問(wèn)正常，切換到5.7后就報(bào)錯(cuò)。因此，首先得懷疑是5.7內(nèi)核出了問(wèn)題，因此第一反應(yīng)是從官方bug list中搜索一下是否有類(lèi)似問(wèn)題存在，避免重復(fù)造車(chē)。經(jīng)過(guò)搜索，發(fā)現(xiàn)官方有1個(gè)類(lèi)似的bug，這里簡(jiǎn)單介紹一下該bug。

背景知識(shí)1

Innodb引擎中的auto increment 相關(guān)參數(shù)及數(shù)據(jù)結(jié)構(gòu)。

主要參數(shù)包括：innodb_autoinc_lock_mode用于控制獲取自增值的加鎖方式，auto_increment_increment， auto_increment_offset用于控制自增列的遞增的間隔和起始偏移。

主要涉及的結(jié)構(gòu)體包括：數(shù)據(jù)字典結(jié)構(gòu)體，保存整個(gè)表的當(dāng)前auto increment值以及保護(hù)鎖；事務(wù)結(jié)構(gòu)體，保存事務(wù)內(nèi)部處理的行數(shù)；handler結(jié)構(gòu)體，保存事務(wù)內(nèi)部多行的循環(huán)迭代信息。

背景知識(shí)2

mysql及Innodb引擎中對(duì)autoincrement訪問(wèn)及修改的流程

• (1) 數(shù)據(jù)字典結(jié)構(gòu)體(dict_table_t)換入換出時(shí)對(duì)autoincrement值的保存和恢復(fù)。換出時(shí)將autoincrement保存在全局的的映射表中，然后淘汰內(nèi)存中的dict_table_t。換入時(shí)通過(guò)查找全局映射表恢復(fù)到dict_table_t結(jié)構(gòu)體中。相關(guān)的函數(shù)為dict_table_add_to_cache及dict_table_remove_from_cache_low。
• (2) row_import, table truncate過(guò)程更新autoincrement。
• (3) handler首次open的時(shí)候，會(huì)查詢(xún)當(dāng)前表中最大自增列的值，并用最大列的值加1來(lái)初始化表的data_dict_t結(jié)構(gòu)體中的autoinc的值。
• (4) insert流程。相關(guān)對(duì)autoinc修改的堆棧如下：

ha_innobase::write_row：write_row的第三步中調(diào)用handler句柄中的update_auto_increment函數(shù)更新auto increment的值。
handler::update_auto_increment:?調(diào)用Innodb接口獲取一個(gè)自增值，并根據(jù)當(dāng)前的auto_increment相關(guān)變量的值調(diào)整獲取的自增值；同時(shí)設(shè)置當(dāng)前handler要處理的下一個(gè)自增列的值。
ha_innobase::get_auto_increment:獲取dict_tabel中的當(dāng)前auto?increment值，并根據(jù)全局參數(shù)更新下一個(gè)auto?increment的值到數(shù)據(jù)字典中
ha_innobase::dict_table_autoinc_initialize:更新auto increment的值，如果指定的值比當(dāng)前的值大，則更新。
handler::set_next_insert_id:設(shè)置當(dāng)前事務(wù)中下一個(gè)要處理的行的自增列的值。

• (5) update_row。對(duì)于”INSERT INTO t (c1,c2) VALUES(x,y) ON DUPLICATE KEY UPDATE”語(yǔ)句，無(wú)論唯一索引列所指向的行是否存在，都需要推進(jìn)auto increment的值。相關(guān)代碼如下：

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);
???????……
}

從我們的實(shí)際業(yè)務(wù)流程來(lái)看，我們的錯(cuò)誤只可能涉及insert及update流程。

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

• (1) bug概述:當(dāng)autoinc_lock_mode大于0，且auto_increment_increment大于1時(shí)，系統(tǒng)剛重啟后多線程同時(shí)對(duì)表進(jìn)行insert操作會(huì)產(chǎn)生“duplicate key”的錯(cuò)誤。
• (2) 原因分析：重啟后innodb會(huì)把a(bǔ)utoincrement的值設(shè)置為max(id) + 1。

此時(shí)，首次插入時(shí)，write_row流程會(huì)調(diào)用handler::update_auto_increment來(lái)設(shè)置autoinc相關(guān)的信息。首先通過(guò)ha_innobase::get_auto_increment獲取當(dāng)前的autoincrement的值(即max(id) + 1)，并根據(jù)autoincrement相關(guān)參數(shù)修改下一個(gè)autoincrement的值為next_id。

當(dāng)auto_increment_increment大于1時(shí)，max(id) + 1 會(huì)不大于next_id。handler::update_auto_increment獲取到引擎層返回的值后為了防止有可能某些引擎計(jì)算自增值時(shí)沒(méi)有考慮到當(dāng)前auto increment參數(shù)，會(huì)重新根據(jù)參數(shù)計(jì)算一遍當(dāng)前行的自增值，由于Innodb內(nèi)部是考慮了全局參數(shù)的，因此handle層對(duì)Innodb返回的自增id算出的自增值也為next_id，即將會(huì)插入一條自增id為next_id的行。

handler層會(huì)在write_row結(jié)束的時(shí)候根據(jù)當(dāng)前行的值next_id設(shè)置下一個(gè)autoincrement值。如果在write_row尚未設(shè)置表的下一個(gè)autoincrement期間，有另外一個(gè)線程也在進(jìn)行插入流程，那么它獲取到的自增值將也是next_id。這樣就產(chǎn)生了重復(fù)。

• (3) 解決辦法：引擎內(nèi)部獲取自增列時(shí)考慮全局autoincrement參數(shù)，這樣重啟后第一個(gè)插入線程獲取的自增值就不是max(id) + 1，而是next_id，然后根據(jù)next_id設(shè)置下一個(gè)autoincrement的值。由于這個(gè)過(guò)程是加鎖保護(hù)的，其他線程再獲取autoincrement的時(shí)候就不會(huì)獲取到重復(fù)的值。

通過(guò)上述分析，這個(gè)bug僅在autoinc_lock_mode > 0 并且auto_increment_increment > 1的情況下會(huì)發(fā)生。實(shí)際線上業(yè)務(wù)對(duì)這兩個(gè)參數(shù)都設(shè)置為1，因此，可以排除這個(gè)bug造成線上問(wèn)題的可能性。

現(xiàn)場(chǎng)分析及復(fù)現(xiàn)驗(yàn)證

既然官方bug未能解決我們的問(wèn)題，那就得自食其力，從錯(cuò)誤現(xiàn)象開(kāi)始分析了。

(1) 分析max id及autoincrement的規(guī)律 由于用戶(hù)的表設(shè)置了ON UPDATE CURRENT_TIMESTAMP列，因此可以把所有的出錯(cuò)的表的max id、autoincrement及最近更新的幾條記錄抓取出來(lái)，看看是否有什么規(guī)律。抓取的信息如下：

乍看起來(lái)，這個(gè)錯(cuò)誤還是很有規(guī)律的，update time這一列是最后插入或者修改的時(shí)間，結(jié)合auto increment及max id的值，現(xiàn)象很像是最后一批事務(wù)只更新了行的自增id，沒(méi)有更新auto increment的值。

聯(lián)想到【官方文檔】中對(duì)auto increment用法的介紹，update操作是可以只更新自增id但不觸發(fā)auto increment推進(jìn)的。按照這個(gè)思路，我嘗試復(fù)現(xiàn)了用戶(hù)的現(xiàn)場(chǎng)。復(fù)現(xiàn)方法如下：

同時(shí)在binlog中，我們也看到有update自增列的操作。如圖：

不過(guò)，由于binlog是ROW格式，我們也無(wú)法判斷這是內(nèi)核出問(wèn)題導(dǎo)致了自增列的變化還是用戶(hù)自己更新所致。因此我們聯(lián)系了客戶(hù)進(jìn)行確認(rèn)，結(jié)果用戶(hù)很確定沒(méi)有進(jìn)行更新自增列的操作。

那么這些自增列到底是怎么來(lái)的呢？

(2) 分析用戶(hù)的表及sql語(yǔ)句 繼續(xù)分析，發(fā)現(xiàn)用戶(hù)總共有三種類(lèi)型的表

hz_notice_stat_sharding
hz_notice_group_stat_sharding
hz_freeze_balance_sharding

這三種表都有自增主鍵。

但是前面兩種都出現(xiàn)了autoinc錯(cuò)誤，唯獨(dú)hz_freeze_balance_sharding表沒(méi)有出錯(cuò)。難道是用戶(hù)對(duì)這兩種表的訪問(wèn)方式不一樣？抓取用戶(hù)的sql語(yǔ)句，果然，前兩種表用的都是replace into操作，最后一種表用的是update操作。難道是replace into語(yǔ)句導(dǎo)致的問(wèn)題？搜索官方bug, 又發(fā)現(xiàn)了一個(gè)疑似bug。

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

原因：

• (1) Mysql對(duì)于replace into實(shí)際是通過(guò)delete + insert語(yǔ)句實(shí)現(xiàn)，但是在ROW binlog格式下，會(huì)向binlog記錄update類(lèi)型日志。Insert語(yǔ)句會(huì)同步更新autoincrement，update則不會(huì)。
• (2) replace into在Master上按照delete+insert方式操作， autoincrement就是正常的?；赗OW格式復(fù)制到slave后，slave機(jī)上按照update操作回放，只更新行中自增鍵的值，不會(huì)更新autoincrement。

因此在slave機(jī)上就會(huì)出現(xiàn)max(id)大于autoincrement的情況。此時(shí)在ROW模式下對(duì)于insert操作binlog記錄了所有的列的值，在slave上回放時(shí)并不會(huì)重新分配自增id，因此不會(huì)報(bào)錯(cuò)。但是如果slave切master，遇到Insert操作就會(huì)出現(xiàn)”Duplicate key”的錯(cuò)誤。

• (3) 由于用戶(hù)是從5.6遷移到5.7，然后直接在5.7上進(jìn)行插入操作，相當(dāng)于是slave切主，因此會(huì)報(bào)錯(cuò)。

解決方案

業(yè)務(wù)側(cè)的可能解決方案：

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

內(nèi)核側(cè)可能解決方案：

• (1) 在ROW格式下如果遇到replace into語(yǔ)句，則記錄statement格式的logevent，將原始語(yǔ)句記錄到binlog。
• (2) 在ROW格式下將replace into語(yǔ)句的logevent記錄為一個(gè)delete event和一個(gè)insert event。

心得

• (1) autoincrement的autoinc_lock_mode及auto_increment_increment這兩個(gè)參數(shù)變化容易導(dǎo)致出現(xiàn)重復(fù)的key，使用過(guò)程中要盡量避免動(dòng)態(tài)的去修改。
• (2) 在碰到線上的問(wèn)題時(shí)，首先應(yīng)該做好現(xiàn)場(chǎng)分析，明確故障發(fā)生的場(chǎng)景、用戶(hù)的SQL語(yǔ)句、故障發(fā)生的范圍等信息，同時(shí)要對(duì)涉及實(shí)例的配置信息、binlog甚至實(shí)例數(shù)據(jù)等做好備份以防過(guò)期丟失。

只有這樣才能在找官方bug時(shí)精準(zhǔn)的匹配場(chǎng)景，如果官方?jīng)]有相關(guān)bug，也能通過(guò)已有線索獨(dú)立分析。

薦書(shū)：《R語(yǔ)言數(shù)據(jù)分析與可視化--從入門(mén)到精通》
編輯推薦：?
1. 理論為輔、實(shí)踐為主。本書(shū)涉及一些必要的理論知識(shí)，特別是在數(shù)據(jù)分析部分，但總體以實(shí)踐為主，因此幾乎每節(jié)都有大量的代碼，方便讀者實(shí)踐。
2. 知識(shí)全面、系統(tǒng)。本書(shū)在介紹了R語(yǔ)言的基礎(chǔ)知識(shí)后，從數(shù)據(jù)獲取和導(dǎo)出、數(shù)據(jù)清理和操作、數(shù)據(jù)分析和可視化方面分別進(jìn)行了探討，內(nèi)容由淺入深、循序漸進(jìn)。
3. 案例廣泛。本書(shū)中的案例涉及心理學(xué)、社會(huì)學(xué)、醫(yī)學(xué)、商業(yè)和經(jīng)濟(jì)等領(lǐng)域，但并不需要讀者具備這些領(lǐng)域的專(zhuān)業(yè)知識(shí)。
4. “新手問(wèn)答”和“小試牛刀”知識(shí)模塊?！靶率謫?wèn)答”主要對(duì)讀者學(xué)習(xí)過(guò)程中易出現(xiàn)的疑問(wèn)或容易犯的錯(cuò)誤進(jìn)行針對(duì)性的解答；“小試牛刀”結(jié)合每章知識(shí)及相關(guān)技能，列舉綜合上機(jī)案例，讓讀者在學(xué)完一章內(nèi)容后能及時(shí)回顧和練習(xí)，旨在讓讀者鞏固知識(shí)、學(xué)以致用。

如何購(gòu)買(mǎi)：閱讀原文購(gòu)買(mǎi)，也可堅(jiān)持留言打卡獲得！
如何贈(zèng)送：留言集贊數(shù)大于30贊且排名前三的同學(xué)各贈(zèng)送一本，定價(jià)119RMB。

覺(jué)得本文對(duì)你有幫助？請(qǐng)分享給更多人
關(guān)注「全棧開(kāi)發(fā)者社區(qū)」加星標(biāo)，提升全棧技能

本公眾號(hào)會(huì)不定期給大家發(fā)福利，包括送書(shū)、學(xué)習(xí)資源等，敬請(qǐng)期待吧！
如果感覺(jué)推送內(nèi)容不錯(cuò)，不妨右下角點(diǎn)個(gè)在看轉(zhuǎn)發(fā)朋友圈或收藏，感謝支持。

好文章，留言、點(diǎn)贊、在看和分享一條龍吧??