MySQL 8.0/8.4执行DDL丢数据有什么影响

问题是有，但好在规避办法也比较简单，影响也有限。

先说解决办法，从简单到麻烦：

执行 ALTER TABLE 时，显式指定ALGORITHM=INSTANT/COPY，反正不要使用 INPLACE。

适当调大 innodb_ddl_buffer_size 参数值，其默认值1MB，例如调大到100MB就可以应对大部分业务表的DDL操作场景。

利用 pt-osc 或 gh-ost 等工具进行 Online DDL 操作。

在业务低谷时段执行DDL操作，有条件的话甚至可以在业务维护期间再执行DDL操作。

升级版本到已修复的 Percona 分支版本（下文会提到）。

问题来源

在 MySQL 8.0.27 版本中新增并行DDL功能后才“引入”了这个问题。目前在最新的 8.1.x/8.3.x/8.3.x/8.4.x/9.0.x/9.1.x 等版本中依然存在，预计到 MySQL 8.0.41 新版本会修复。

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.

详见：https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-27.html

触发原因：在INPLACE模式的DDL操作中重建主键索引时，因错误处理会略过部分记录，导致数据丢失。

触发条件：只影响INPLACE模式的DDL操作，不影响COPY和INSTANT模式的DDL操作。以下是几种常见的可能触发问题的DDL操作场景：

场景1：ALTER TABLE ENGINE=INNODB 重整表空间操作，需要重建主键索引。

场景2：ALTER TABLE ADD NEW-COL ...，ALGORITHM=INPLACE，新增列操作，因指定了INPLACE模式，需要重建主键索引。

其他例如INSTANT模式加新字段，增删索引则不会触发该问题。

关于该问题的详细解读详见几篇文章：

八怪老师推文 https://www.jianshu.com/p/c66fe0349345?v=1734349439280 。

Rex老师推文 MySQL 8.4-LTS DDL会导致数据丢失。

丁奇老师推文 丢数据风险 @ MySQL官方最新版。

Percona 推文 Who Ate My MySQL Table Rows?。

涉及到2个MySQL bug：

DDL 丢数风险：https://bugs.mysql.com/bug.php?id=115608

DDL 重复行报错：https://bugs.mysql.com/bug.php?id=115511

该问题核心就存在于如果涉及到需要用INPLACE算法重建主键索引的DDL操作，就需要在 innodb_ddl_buffer_size 用满后直接插入到 #sql-ibXXX 数据文件中，这个时候可能正在page的中间的某个位置，插入的时候会暂时放弃page上的mutex，并且保存游标到持久游标，然后插入数据，插入完成后再从持久游标恢复游标。这样做的目的可能是为了提高page修改的并发，但是这里保存和恢复持久游标却出了问题，主要是page中的数据可能出现修改，这种修改对应了前面的2个BUG：

Purge线程，清理del flag。

其他线程INSERT了数据。

具体游标的保存和恢复出现的问题，可以参考Rex老师的文章 MySQL 8.4-LTS DDL会导致数据丢失。

问题影响

目前该问题已知影响的版本列表如下：

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 版本。

未受影响或已修复的版本列表如下：

所有早于 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 版本均未修复，已安排在MySQL 8.0.41版本修复该问题。GreatSQL也会在下一个新版本中修复该问题。

问题复现/interwetten与威廉的赔率体系

模拟测例1

经过测试，该问题触发概率和 update/delete 并发负载有关，结合 MySQL bug #113812 提供的案例，我进行了简化和改造，测试用例如下：

#/bin/sh
#bugtest.sh，测例1
#需要先安装mysql_random_data_load测试工具
#通过socket方式连接MySQL时用root密码并且是空密码
MYSQL="mysql-N-s-uroot-S/data/MySQL/mysql.sock"
HOST=127.0.0.1
PORT=3306
USER="yejr"
PWD="yejr"

echo"1.Preparework"

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/null2>&1
c_before_del=`${MYSQL}-e"selectcount(*)fromtest.t1;"`
c_delete=`${MYSQL}-e"selectcount(*)fromtest.t1wherec3< 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

执行该测试用例脚本，当发现有问题时，结果显式如下：

$sh./bugtest.sh
1.Preparework
2.Startingruntest
run10times
run20times
run30times
...
run175times,delete:979,beforealter:3436,afteralter:3435

这就表示执行到第175次后触发问题，发现丢了一条记录。在这个测例中，如果加大 innodb_ddl_buffer_size 参数值到10MB，则不再触发问题。

模拟测例2

对上面的测试用例再进行调整后，改成下面这个测例，在执行完1000次后仍未触发问题（可见并不总是会触发问题，只有个别情况下会踩雷）：

#!/bin/sh
#bugtest.sh，测例2
#需要先安装mysql_random_data_load测试工具
#通过socket方式连接MySQL时用root密码并且是空密码
MYSQL="mysql-N-s-uroot-S/nvme/GreatSQL/mysql.sock"
HOST=127.0.0.1
PORT=3306
USER="yejr"
PWD="yejr"

echo"1.Preparework"

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/null2>&1
c_before_del=`${MYSQL}-e"selectcount(*)fromtest.t1;"`
${MYSQL}-e"deletefromtest.t1LIMIT980;"
c_before_alter=`${MYSQL}-e"selectcount(*)fromtest.t1;"`
${MYSQL}-e"altertabletest.t1engine=innodb;"
c_after_alter=`${MYSQL}-e"selectcount(*)fromtest.t1;"`
if[${c_before_alter}-ne${c_after_alter}];then
echo"run${i}times,beforealter:${c_before_alter},afteralter:${c_after_alter}"
exit
fi
if[`expr${i}%10`-eq0];then
echo"run${i}times"
fi
done

从多次反复测试的结果来看，大致的规律是当执行 ALTER TABLE 操作特别频繁时，就可能会在表重建时遇到被 Purge 的记录还没来得及被抹掉，这就比较容易触发问题。试着把上面的测例1做些微调，把 ALTER TABLE 这部分的处理逻辑修改成下面这样：

...
47if[`expr${i}%20`-eq0];then
48sleep2
49${MYSQL}-e"altertabletest.t1engine=innodb;"
50fi
...

即每完成20轮测试后再执行 ALTER TABLE 操作，并且在此之前还要先休眠等待2秒。改用新逻辑后，就没再触发问题。

模拟测例3

提示：该测例需要改成MySQL debug版本运行（平时使用的是release二进制包，是无法复现的）。

准备测试数据

CREATETABLEt1(pkCHAR(5)PRIMARYKEY);
INSERTINTOt1VALUES('aaaaa'),('bbbbb'),('bbbcc'),('ccccc'),('ddddd'),('eeeee');

测试方法

测试结果

写在后面

在线上生产环境中，除了必要的增删字段、增删索引、修改字段定义外，直接执行 ALTER TABLE ... ENGINE=InnoDB 或 OPTIMIZE TABLE 重建整个表空间的行为还是比较少的，尤其是操作大表时，也基本上都习惯了用类似 gt-osc 之类的第三方辅助工具来完成。

此外，调大 innodb_ddl_buffer_size 参数值也可以应对大部分业务表的DDL操作需求，在我的测试中，调大到10MB就可以保证上述测试表有几十万行数据时不出问题，调大到100MB则可以保证上述测试表有千万行数据时不出问题。如果是更大、更宽的表就需要进一步测试验证了。

总的来看，这个问题在线上生产环境中并不是百分百会触发，只是存在一定较低的几率，在文章一开始也提到了几个可以规避的方法，所以说其影响其实也是有限的，不必过于紧张。先采用紧急办法规避问题，后面再择机升级版本就好。