共計 4856 個字符，預計需要花費 13 分鐘才能閱讀完成。

這篇文章主要介紹“MySQL 中的 BUG 分析”，在日常操作中，相信很多人在 MySQL 中的 BUG 分析問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL 中的 BUG 分析”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

▌問題描述

近期，線上有個重要 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。

背景知識 1

Innodb 引擎中的 auto increment 相關參數及數據結構

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

主要涉及的結構體包括：數據字典結構體，保存整個表的當前 auto increment 值以及保護鎖；事務結構體，保存事務內部處理的行數；handler 結構體，保存事務內部多行的循環迭代信息。

這部分網上有篇文章介紹的比較好，具體參見：(https://www.cnblogs.com/zengkefu/p/5683258.html)。

背景知識 2

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;  hellip; hellip; 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);  hellip; hellip; }

從我們的實際業務流程來看，我們的錯誤只可能涉及 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。

到此，關于“MySQL 中的 BUG 分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！