MySQL 8.0/8.4執(zhí)行DDL會(huì)丟數(shù)據(jù)？是，但影響有限

作者：八怪、葉金榮 2024-12-18 10:18:09

在線上生產(chǎn)環(huán)境中，除了必要的增刪字段、增刪索引、修改字段定義外，直接執(zhí)行 ALTER TABLE ... ENGINE=InnoDB? 或 OPTIMIZE TABLE? 重建整個(gè)表空間的行為還是比較少的，尤其是操作大表時(shí)，也基本上都習(xí)慣了用類似 gt-osc 之類的第三方輔助工具來(lái)完成。

先說(shuō)解決辦法，從簡(jiǎn)單到麻煩：

執(zhí)行 ALTER TABLE 時(shí)，顯式指定ALGORITHM=INSTANT/COPY，反正不要使用 INPLACE。
適當(dāng)調(diào)大 innodb_ddl_buffer_size 參數(shù)值，其默認(rèn)值1MB，例如調(diào)大到100MB就可以應(yīng)對(duì)大部分業(yè)務(wù)表的DDL操作場(chǎng)景。
利用 pt-osc 或 gh-ost 等工具進(jìn)行 Online DDL 操作。
在業(yè)務(wù)低谷時(shí)段執(zhí)行DDL操作，有條件的話甚至可以在業(yè)務(wù)維護(hù)期間再執(zhí)行DDL操作。
升級(jí)版本到已修復(fù)的 Percona 分支版本（下文會(huì)提到）。

問(wèn)題來(lái)源

在 MySQL 8.0.27 版本中新增并行DDL功能后才“引入”了這個(gè)問(wèn)題。目前在最新的 8.1.x/8.3.x/8.3.x/8.4.x/9.0.x/9.1.x 等版本中依然存在，預(yù)計(jì)到 MySQL 8.0.41 新版本會(huì)修復(fù)。

For online DDL operations, storage is usually the bottleneck. To address this issue, CPU utilization and index building has been improved. Indexes can now be built simultaneously instead of serially. Memory management has also been tightened to respect memory configuration limits set by the user.

詳見(jiàn)：https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-27.html

觸發(fā)原因：在INPLACE模式的DDL操作中重建主鍵索引時(shí)，因錯(cuò)誤處理會(huì)略過(guò)部分記錄，導(dǎo)致數(shù)據(jù)丟失。

觸發(fā)條件：只影響INPLACE模式的DDL操作，不影響COPY和INSTANT模式的DDL操作。以下是幾種常見(jiàn)的可能觸發(fā)問(wèn)題的DDL操作場(chǎng)景：

場(chǎng)景1：ALTER TABLE ENGINE=INNODB 重整表空間操作，需要重建主鍵索引。
場(chǎng)景2：ALTER TABLE ADD NEW-COL ...，ALGORITHM=INPLACE，新增列操作，因指定了INPLACE模式，需要重建主鍵索引。

其他例如INSTANT模式加新字段，增刪索引則不會(huì)觸發(fā)該問(wèn)題。

關(guān)于該問(wèn)題的詳細(xì)解讀詳見(jiàn)幾篇文章：

八怪老師推文 8027后關(guān)于DDL 丟數(shù)據(jù)問(wèn)題https://www.jianshu.com/p/c66fe0349345?v=1734349439280
Rex老師推文 MySQL 8.4-LTS DDL會(huì)導(dǎo)致數(shù)據(jù)丟失。
丁奇老師推文丟數(shù)據(jù)風(fēng)險(xiǎn) @ MySQL官方最新版。
Percona 推文 Who Ate My MySQL Table Rows?。

涉及到2個(gè)MySQL bug：

DDL 丟數(shù)風(fēng)險(xiǎn)：https://bugs.mysql.com/bug.php?id=115608
DDL 重復(fù)行報(bào)錯(cuò)：https://bugs.mysql.com/bug.php?id=115511

該問(wèn)題核心就存在于如果涉及到需要用INPLACE算法重建主鍵索引的DDL操作，就需要在 innodb_ddl_buffer_size 用滿后直接插入到 #sql-ibXXX 數(shù)據(jù)文件中，這個(gè)時(shí)候可能正在page的中間的某個(gè)位置，插入的時(shí)候會(huì)暫時(shí)放棄page上的mutex，并且保存游標(biāo)到持久游標(biāo)，然后插入數(shù)據(jù)，插入完成后再?gòu)某志糜螛?biāo)恢復(fù)游標(biāo)。這樣做的目的可能是為了提高page修改的并發(fā)，但是這里保存和恢復(fù)持久游標(biāo)卻出了問(wèn)題，主要是page中的數(shù)據(jù)可能出現(xiàn)修改，這種修改對(duì)應(yīng)了前面的2個(gè)BUG：

Purge線程，清理del flag。
其他線程INSERT了數(shù)據(jù)。

具體游標(biāo)的保存和恢復(fù)出現(xiàn)的問(wèn)題，可以參考Rex老師的文章 MySQL 8.4-LTS DDL會(huì)導(dǎo)致數(shù)據(jù)丟失。

問(wèn)題影響

目前該問(wèn)題已知影響的版本列表如下：

MySQL 8.0.x 系列版本中，所有 >= 8.0.27 的 MySQL 8.0.x 版本；
所有 8.4.x 系列 LTS 版本；
Percona Server for MySQL 中從 8.0.27-18 至 8.0.37-29，以及 8.4.0-1 版本。
Percona XtraDB Cluster 中從 8.0.27-18.1 至 8.0.37-29，以及 8.4.0-1 版本。

未受影響或已修復(fù)的版本列表如下：

所有早于 MySQL 8.0 的版本，及 MySQL 5.6、5.7 等版本，以及 Percona 5.6、5.7 版本；
Percona 8.0 系列中 8.0.39-30 及更高版本；
Percona 8.4 系列中 8.4.2-2 及更高版本；
Percona XtraDB Cluster 8.0 系列中 8.0.39-30 及更高版本。

目前所有活躍的 MySQL 版本均未修復(fù)，已安排在MySQL 8.0.41版本修復(fù)該問(wèn)題。GreatSQL也會(huì)在下一個(gè)新版本中修復(fù)該問(wèn)題。

問(wèn)題復(fù)現(xiàn)/模擬

模擬測(cè)例1

經(jīng)過(guò)測(cè)試，該問(wèn)題觸發(fā)概率和 update/delete 并發(fā)負(fù)載有關(guān)，結(jié)合 MySQL bug #113812 提供的案例，我進(jìn)行了簡(jiǎn)化和改造，測(cè)試用例如下：

#/bin/sh
# bugtest.sh，測(cè)例1
# 需要先安裝 mysql_random_data_load 測(cè)試工具
# 通過(guò)socket方式連接MySQL時(shí)用root密碼并且是空密碼
MYSQL="mysql -N -s -uroot -S/data/MySQL/mysql.sock"
HOST=127.0.0.1
PORT=3306
USER="yejr"
PWD="yejr"

echo"1. Prepare work"

read -r -d '' bugSQL <<-EOSQL || true
CREATE DATABASE IF NOT EXISTS test;
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE IF NOT EXISTS t1(
 id int not null,
 c1 varchar(20) not null,
 c2 varchar(30) not null,
 c3 datetime not null,
 c4 varchar(30) not null,
 PRIMARY KEY (id),
 KEY idx_c3 (c3)
) ENGINE=InnoDB;

CREATE USER IF NOT EXISTS '${USER}'@'%';
ALTER USER '${USER}'@'%' IDENTIFIED BY '${PWD}';
GRANT ALL PRIVILEGES ON test.t1 TO '${USER}'@'%';
EOSQL

${MYSQL} -f -e "${bugSQL}"

echo"2. Starting run test"

${MYSQL} -e "truncate table test.t1;"

for i in {1..1000}
do
 mysql_random_data_load -u${USER} -p${PWD} -h${HOST} -P${PORT} --max-threads=2 test t1 1000 > /dev/null 2>&1
 c_before_del=`${MYSQL} -e "select count(*) from test.t1;"`
 c_delete=`${MYSQL} -e "select count(*) from test.t1 where c3 < curdate() - interval 7 day;"`
${MYSQL} -e "delete from test.t1 where c3 < curdate() - interval 7 day;"
 c_before_alter=`${MYSQL} -e "select count(*) from test.t1;"`
${MYSQL} -e "alter table test.t1 engine=innodb;"
 c_after_alter=`${MYSQL} -e "select count(*) from test.t1;"`
if [ ${c_before_alter} -ne ${c_after_alter} ] ; then
echo"run ${i} times, delete: ${c_delete}, before alter: ${c_before_alter}, after alter: ${c_after_alter}"
exit
fi
if [ `expr ${i} % 10` -eq 0 ] ; then
echo"run ${i} times"
fi
done

執(zhí)行該測(cè)試用例腳本，當(dāng)發(fā)現(xiàn)有問(wèn)題時(shí)，結(jié)果顯式如下：

$ sh ./bugtest.sh
1. Prepare work
2. Starting run test
run 10 times
run 20 times
run 30 times
...
run 175 times, delete: 979, before alter: 3436, after alter: 3435

這就表示執(zhí)行到第175次后觸發(fā)問(wèn)題，發(fā)現(xiàn)丟了一條記錄。在這個(gè)測(cè)例中，如果加大 innodb_ddl_buffer_size 參數(shù)值到10MB，則不再觸發(fā)問(wèn)題。

模擬測(cè)例2

對(duì)上面的測(cè)試用例再進(jìn)行調(diào)整后，改成下面這個(gè)測(cè)例，在執(zhí)行完1000次后仍未觸發(fā)問(wèn)題（可見(jiàn)并不總是會(huì)觸發(fā)問(wèn)題，只有個(gè)別情況下會(huì)踩雷）：

#!/bin/sh
# bugtest.sh，測(cè)例2
# 需要先安裝 mysql_random_data_load 測(cè)試工具
# 通過(guò)socket方式連接MySQL時(shí)用root密碼并且是空密碼
MYSQL="mysql -N -s -uroot -S/nvme/GreatSQL/mysql.sock"
HOST=127.0.0.1
PORT=3306
USER="yejr"
PWD="yejr"

echo"1. Prepare work"

read -r -d '' bugSQL <<-EOSQL || true
CREATE DATABASE IF NOT EXISTS test;
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE IF NOT EXISTS t1(
 id int not null,
 c1 varchar(20) not null,
 c2 varchar(30) not null,
 c3 int not null,
 c4 varchar(30) not null,
 PRIMARY KEY (id),
 KEY idx_c3 (c3)
) ENGINE=InnoDB;

CREATE USER IF NOT EXISTS '${USER}'@'%';
ALTER USER '${USER}'@'%' IDENTIFIED BY '${PWD}';
GRANT ALL PRIVILEGES ON test.t1 TO '${USER}'@'%';
EOSQL

${MYSQL} -f -e "${bugSQL}"

echo"2. Starting run test"

${MYSQL} -e "truncate table test.t1;"

for i in {1..300}
do
 mysql_random_data_load -u${USER} -p${PWD} -h${HOST} -P${PORT} --max-threads=2 test t1 1000 > /dev/null 2>&1
 c_before_del=`${MYSQL} -e "select count(*) from test.t1;"`
${MYSQL} -e "delete from test.t1 LIMIT 980;"
 c_before_alter=`${MYSQL} -e "select count(*) from test.t1;"`
${MYSQL} -e "alter table test.t1 engine=innodb;"
 c_after_alter=`${MYSQL} -e "select count(*) from test.t1;"`
if [ ${c_before_alter} -ne ${c_after_alter} ] ; then
echo"run ${i} times, before alter: ${c_before_alter}, after alter: ${c_after_alter}"
exit
fi
if [ `expr ${i} % 10` -eq 0 ] ; then
echo"run ${i} times"
fi
done

從多次反復(fù)測(cè)試的結(jié)果來(lái)看，大致的規(guī)律是當(dāng)執(zhí)行 ALTER TABLE 操作特別頻繁時(shí)，就可能會(huì)在表重建時(shí)遇到被 Purge 的記錄還沒(méi)來(lái)得及被抹掉，這就比較容易觸發(fā)問(wèn)題。試著把上面的測(cè)例1做些微調(diào)，把 ALTER TABLE 這部分的處理邏輯修改成下面這樣：

...
 47  if [ `expr ${i} % 20` -eq 0 ] ; then
 48   sleep 2
 49   ${MYSQL} -e "alter table test.t1 engine=innodb;"
 50  fi
...

即每完成20輪測(cè)試后再執(zhí)行 ALTER TABLE 操作，并且在此之前還要先休眠等待2秒。改用新邏輯后，就沒(méi)再觸發(fā)問(wèn)題。

模擬測(cè)例3

提示：該測(cè)例需要改成MySQL debug版本運(yùn)行（平時(shí)使用的是release二進(jìn)制包，是無(wú)法復(fù)現(xiàn)的）。

準(zhǔn)備測(cè)試數(shù)據(jù)

CREATE TABLE t1 (pk CHAR(5) PRIMARY KEY);
INSERT INTO t1 VALUES ('aaaaa'), ('bbbbb'), ('bbbcc'), ('ccccc'), ('ddddd'), ('eeeee');

測(cè)試方法

S1	S2
這一步的目的是2行數(shù)據(jù)key buffer就滿
SET DEBUG='+d,ddl_buf_add_two';
	set global innodb_purge_stop_now=ON;
	DELETE FROM t1 WHERE pk = 'bbbcc';
進(jìn)行DDL，并且來(lái)到ddl0par-scan.cc:238 行
ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE
	SET GLOBAL innodb_purge_run_now=ON;
DDL繼續(xù)進(jìn)程(丟數(shù)據(jù))

測(cè)試結(jié)果

圖片

寫(xiě)在后面

在線上生產(chǎn)環(huán)境中，除了必要的增刪字段、增刪索引、修改字段定義外，直接執(zhí)行 ALTER TABLE ... ENGINE=InnoDB 或 OPTIMIZE TABLE 重建整個(gè)表空間的行為還是比較少的，尤其是操作大表時(shí)，也基本上都習(xí)慣了用類似 gt-osc 之類的第三方輔助工具來(lái)完成。

此外，調(diào)大 innodb_ddl_buffer_size 參數(shù)值也可以應(yīng)對(duì)大部分業(yè)務(wù)表的DDL操作需求，在我的測(cè)試中，調(diào)大到10MB就可以保證上述測(cè)試表有幾十萬(wàn)行數(shù)據(jù)時(shí)不出問(wèn)題，調(diào)大到100MB則可以保證上述測(cè)試表有千萬(wàn)行數(shù)據(jù)時(shí)不出問(wèn)題。如果是更大、更寬的表就需要進(jìn)一步測(cè)試驗(yàn)證了。

總的來(lái)看，這個(gè)問(wèn)題在線上生產(chǎn)環(huán)境中并不是百分百會(huì)觸發(fā)，只是存在一定較低的幾率，在文章一開(kāi)始也提到了幾個(gè)可以規(guī)避的方法，所以說(shuō)其影響其實(shí)也是有限的，不必過(guò)于緊張。先采用緊急辦法規(guī)避問(wèn)題，后面再擇機(jī)升級(jí)版本就好。

責(zé)任編輯：武曉燕來(lái)源： MySQL學(xué)習(xí)