MySQL問題兩則

1 | 前言

近期在給開發(fā)小伙伴解決問題的時候，收集了兩個個人覺得比較有意思的問題給大家分享一下。 一個是在執(zhí)行ALTER TABLE ADD COLUMN語句時，報了Duplicate entry的錯誤； 另一個是關于在MySQL中正確存取emoji表情的問題。

2 | ALTER TABLE ADD COLUMN報Duplicate entry錯誤

2.1 問題描述

某日系統(tǒng)上線，接到開發(fā)小伙伴電話說在上線時，執(zhí)行一個增加字段的DDL語句腳本時，報錯了，錯誤如下:
ERROR 1062 (23000) at line 1: Duplicate entry 'UR000021426347' for key 'T_CAP_CUST_MIDDLE_INFO_UNIQ_INDEX' 
根據(jù)錯誤提示的條件去數(shù)據(jù)庫中查詢卻只能查到一條記錄，并沒有重復記錄。 DDL腳本無法執(zhí)行，影響后續(xù)上線步驟了。 當時由于不在現(xiàn)場，了解到的信息只有：

1. DDL語句腳本中只有兩條DDL語句，且都是添加字段的語句。

2. 腳本正常運行只需要40-70秒。

3. 當時并不是停業(yè)窗口。

考慮到數(shù)據(jù)庫版本為5.6.34，添加字段并不會阻塞DML操作，讓開發(fā)小伙伴再運行一次試試，結(jié)果這次執(zhí)行成功了，并沒有報沖突的錯誤。 線上問題順利解決，具體原因得線下分析了。 雖說解決問題是主要矛盾，但是搞清楚問題原因有著更深層次的意義。

2.2 原因定位

下面就是到了尋找問題原因的時候了，為什么同樣的DDL語句腳本第一次執(zhí)行的時候報了Duplicate entry錯誤，第二次卻順利運行了。 其實問題原因很好找，打開Google，輸入關鍵字 mysql alter table add column duplicate entry ，搜索結(jié)果中很多關鍵字完全匹配的鏈接，說明很多人遇到過相同問題。 搜索結(jié)果中一眼就看到一個鏈接 MySQL Bugs:#76895:Adding new column OR Drop column causes duplicate PK error ，看到MySQL Bug就莫名興奮。 通過該Bug鏈接了解到該問題是Online DDL的一個限制問題，官方認為該問題是一種限制，并不是Bug，所以目前為止還沒有得到解決。

When running an online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.
 解釋一下就是當執(zhí)行Oline DDL操作時，MySQL實際上是將DML緩存（該緩存大小由變量 innodb_online_alter_log_max_size控制，默認128M）起來，等DDL執(zhí)行完成后再將緩存中的DML重新應用到表上。 如果有別的線程執(zhí)行了DML操作，在DDL完成后，應用DML時，可能會出現(xiàn)duplicate entry錯誤。

2.3 實驗驗證

上面通過Google找到了理論上可能能解釋問題的原因描述，但是還沒有實際驗證，所以接下來就是線下復現(xiàn)環(huán)節(jié)。先去找開發(fā)同事問了下線上報錯的表只有一種操作 insert into ... on duplicate key... ，且報Duplicate entry的字段上有唯一索引。如果沒有沖突的記錄則插入，否則就更新。那么驗證測試步驟也比較簡單了，找一張測試表，執(zhí)行ALTER TABLE ADD COLUMN操作，并同時執(zhí)行insert into...on duplicate key...操作，觀察DDL語句是否會有報錯。

2.3.1 實驗環(huán)境

• redhat-6.7/redhat-7.4

• MySQL-5.6.34/MySQL-5.7.22

• 準備測試環(huán)境。

col1字段的值與id字段的值是一致的，test表共有1600W+條記錄。

• 執(zhí)行DDL同時執(zhí)行insert into ... on duplicate key...操作。

順利的復現(xiàn)了線上的問題現(xiàn)象，那說明當時線上就是因為DML更新了相同的唯一屬性字段鍵值導致DDL執(zhí)行失敗，報錯。 測試過程中想到insert into... on duplicate key...不行，那么replace into 會不會也一樣導致問題呢，于是就同樣對replace into語句進行了測試。

• 執(zhí)行DDL同時執(zhí)行replace into操作。

• 后續(xù)以同樣方式測試了UPDATE、INSERT操作，實驗證明都會影響DDL操作的正確執(zhí)行，在5.7.22版本上也是一樣的現(xiàn)象。 這邊出于篇幅考慮就不將測試過程給出了，感興趣的讀者可以自己實驗一把，并且可以驗證下在8.0版本中是否仍然存在該問題。

2.4 小結(jié)

在這對這個問題做幾點總結(jié)：

• 問題原因： Oline DDL的原理簡單一點理解就是將DML操作緩存起來，等到DDL執(zhí)行完成后重新應用緩存中的DML語句，如果在Oline DDL執(zhí)行過程中，DML操作產(chǎn)生了Duplicate entry錯誤，并不會直接影響DDL操作，而是在DDL執(zhí)行完成最終應用DML時報錯，導致DDL執(zhí)行失敗。 關于Oline DDL執(zhí)行步驟可以參考： https://yq.aliyun.com/articles/282290 。

• 在MySQL Bug網(wǎng)站上，官方人員回復該現(xiàn)象并不是Bug，而是一種限制。 但是個人認為是可以做一些改善的，因為在測試insert into ... on duplicate key...以及update和insert語句時，對于執(zhí)行DML操作的客戶端已經(jīng)直接返回報錯了，但是從現(xiàn)象上看MySQL仍然將報錯的DML語句放到了Oline DDL的緩存中，如果直接將報錯語句從緩存中去除則不會影響DDL的正常執(zhí)行。 這只是個人簡單認為，深入的話需要可以通過代碼去確認。

• Google是位好老師。

3 | MySQL存取emoji的正確姿勢

3.1 問題描述

開發(fā)小伙伴在測試環(huán)境測試過程中，需要往MySQL數(shù)據(jù)庫中插入emoji表情，但是發(fā)現(xiàn)一套測試環(huán)境可以插入成功，另一套測試環(huán)境不行，且插入成功的那套環(huán)境數(shù)據(jù)庫將數(shù)據(jù)查詢出來是 ???? 亂碼。經(jīng)過與開發(fā)確認后了解到以下幾點信息：

• 表中只有一個字段需要存放emoji表情，開發(fā)對該字段單獨設置了utf8mb4字符集。

• 可以成功插入成功的測試環(huán)境數(shù)據(jù)庫版本為5.6.34，無法插入的測試環(huán)境數(shù)據(jù)庫版本為5.7.22。

• 應用連接代碼中使用的是utf8字符集。

那其實需要解決的問題其實是兩個問題： 1、5.6版本下emoji存取亂碼問題 2、5.7版本emoji無法插入的問題。

3.2 實驗驗證

3.2.1 MySQL- 5.6 emoji存取亂碼問題。

我們先來看5.6版本下emoji存儲亂碼的問題，理論上從數(shù)據(jù)庫角度考慮，字段字符集已經(jīng)設置為utf8mb4，應用使用的是utf8字符集連接，插入emoji需要utf8mb4字符集，多半是連接字符集設置的問題。 大膽假設已經(jīng)完成，接下來就是小心求證的過程。

實驗環(huán)境

• 數(shù)據(jù)庫版本： 5.6.44

• sql_mode='NO_ENGINE_SUBSTITUTION'

• default-character-set=utf8

• 使用utf8字符集連接MySQL，并檢查參數(shù)設置。

• 創(chuàng)建測試。

• 插入emoji表情。

表情的確能插入數(shù)據(jù)庫，但是insert時有兩條warning記錄提示是invalid的字符串，并且select查詢出來的數(shù)據(jù)也是一樣 ???? 亂碼。

• 使用utf8mb4字符集插入emoji表情并查詢。

3.2.2 MySQL-5.7 emoji無法插入的問題

定位了亂碼的問題，再來看看emoji無法插入的問題。

實驗環(huán)境

• 數(shù)據(jù)庫版本： 5.7.22

• sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

• default-character-set=utf8

• 使用utf8字符集連接MySQL，并檢查參數(shù)設置。

• 創(chuàng)建測試。

• 插入emoji表情。

• 查看sql_mode參數(shù)默認值。

mysql> show variables like '%sql_mode%'\G
*************************** 1. row ***************************
Variable_name: sql_mode
    Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

查看官方文檔關于各個sql_mode選項的值，找到了懷疑對象STRICT_TRANS_TABLES，表示開啟嚴格模式，嚴格模式下如果插入的數(shù)據(jù)不在范圍之類會報錯中斷語句。

• 修改sql_mode參數(shù)值，再次插入emoji表情測試。

mysql> set session sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec

可以看到修改sql_mode值后，現(xiàn)象與5.6版本下一致。

使用utf8mb4字 符集插入emoji表情并查詢。

3.3 小結(jié)

在此對emoji問題做一下小結(jié)：

• MySQL中存儲emoji不僅需要將表結(jié)構(gòu)中字段字符集設置為utf8mb4，還需要考慮連接字符集的問題。

• 建議數(shù)據(jù)庫使用5.7.22及以上版本，并且sql_mode開啟嚴格模式，這樣當數(shù)據(jù)出現(xiàn)異?？梢约皶r發(fā)現(xiàn)。

• 在sql_mode開啟嚴格模式的情況下，應用端連接數(shù)據(jù)庫也需要調(diào)整為utf8mb4字符集才可以正常插入emoji表情。

| 作者簡介

沈 剛·沃趣科技數(shù)據(jù)庫技術(shù)專家

熟悉MySQL數(shù)據(jù)庫運行機制，豐富的數(shù)據(jù)庫及復制架構(gòu)故障診斷、性能調(diào)優(yōu)、數(shù)據(jù)庫備份恢復及遷移經(jīng)驗。