MySQL 8.0 DDL 导致数据丢失问题分析

腾讯云数据库TencentDB

2025-01-03 15:39发布于北京腾讯云数据库官方账号

在数据库领域，数据安全永远是用户最为关注的焦点之一。MySQL 8.0在执行DDL操作时存在导致数据丢失的情况让广大用户担忧。无论是对于开发者还是企业用户而言，数据就如同企业的生命线，任何数据丢失风险都可能引发严重的后果。腾讯云数据库企业级内核TXSQL修复了MySQL 8.0 DDL导致数据丢失的问题，并且云数据库MySQL 8.0最新内核小版本(20230704)现已全网发布。那么，这个问题到底是如何触发的呢？其背后的原因究竟是什么？我们又该如何有效地规避呢？今天，就让我们一同深入探究其中的奥秘。

问题简述

在 MySQL 8.0.27 到 8.0.40 版本中，执行表重建操作可能导致记录丢失，包括以下操作：

● ALTER TABLE table_name ENGINE=InnoDB

● OPTIMIZE TABLE table_name

● 前置触发条件：在表重建之前执行了 DELETE 操作，并立即重建表。

实际上，所有使用 INPLACE 方式的表结构变更都有可能触该bug，如ADD/DROP 列等。

触发流程

1. 对表执行删除操作

2. 立即进行表重建

3. 重建过程中由于 DDL 缓冲区不足，需要暂存游标状态

4. 暂存游标指向的行恰好是已删除记录

5. 该游标指向的该行记录在恢复前被 purge 线程清理

6. 恢复后游标位置错位，导致跳过一条记录

触发逻辑：先对表删除一条或一批记录，然后立即重建表，重建表过程中，由于ddl buffer不够，需要暂存当前游标状态，清空ddl buffer后再继续，如果游标指向的前一行恰好是被标记为deleted的记录，且这条记录在游标恢复前被purge线程清理，将导致恢复后的游标指错位置，跳过一条记录，最终丢失该条被跳过记录。

问题引入

MySQL 8.0.27 引入了 InnoDB 中并行 ALTER TABLE INPLACE 的新实现。即使在单线程情况下，其代码也用于在线表重建。

这里引入了innodb_ddl_buffer_size作Online DDL的缓冲区，用于提高插入性能。在重建TABLE操作时，使用Online DDL的能力来完成该行为，过程中扫描原始表，将记录添加到innodb_ddl_buffer_size缓冲区，并使用一个游标（PCursor）来存储待拷贝数据的位置。在代码中使用PCursor::savepoint()方式保存游标位置，使用PCursor::resume() 方式来恢复游标，这两个函数详细情况如下：

PCursor::savepoint() 逻辑：

voidPCursor::savepoint()noexcept{
/* Store the cursor position on the previous user record on the page. */
//假设有记录顺序为：a b c d e，当前处理c
  m_pcur->move_to_prev_on_page();//游标指向前一行b

  m_pcur->store_position(m_mtr);//保存

  m_mtr->commit();
}

关键点：游标指向了前一行。

PCursor::resume() 逻辑：

voidPCursor::resume()noexcept{
  m_mtr->start();

  m_mtr->set_log_mode(MTR_LOG_NO_REDO);

/* Restore position on the record, or its predecessor if the record was purged meanwhile. */
//假设有记录顺序为：a b c d e，当前处理c
restore_position();//恢复出的游标指向b

if(!m_pcur->is_after_last_on_page()){
/* Move to the successor of the saved record. */
    m_pcur->move_to_next_on_page();//游标向后一步指向c，补偿save时的向前一步
}
}

关键点：补偿了游标––savepoint时指向前一行，在resume中向后指一行，最终游标仍然指向当前处理行c。

当resume 执行完，游标指向当前正在处理的行c，在外层函数Parallel_reader::Ctx::traverse_recs 中将再执行一次 page_cur_move_to_next 指向c的下一行d，即当前正在处理行c，下一个将处理行d。

问题发生关键点

核心点在 PCursor::resume() 中调用的 restore_position()函数，如果savepoint中游标指向的行(记录 b)在调用的 restore_position()函数前被purge掉，在restore_position()函数中就会找到该行的下一行，也就是指向了当前行(记录 c)，然后在后面补偿savepoint函数中的向前一步操作，又向后指一行指向(记录 d)，最终函数执行结束时，游标指向了(记录 d)，并且认为(记录 d)是当前处理行，下一个将处理行为(记录 e)，导致(记录 d)被跳过。

发生场景模拟

初始环境

● 表记录数：100万行

● 主键范围：id 1 ~ 1,000,000

● 被删除记录：id = 12345

● DDL缓冲区设置：innodb_ddl_buffer_size = 1M（假设可容纳

12344条记录）

问题触发时序

1. DDL缓冲区满时状态

- 已处理记录：id 1 ~ 12344

- 当前正处理：id 12346

- DDL缓冲区已满，触发批量处理

2. Savepoint 保存时刻

- 触发原因：DDL缓冲区已满，需要清空

- 游标位置：指向 id 12345（被标记删除但物理记录仍存在）

- 当前状态：id 12345 仅标记为已删除

3. Resume 恢复前

- purge线程执行：物理清除 id 12345 的记录

- 页面状态：不再包含 id 12345 的记录

4. Resume 过程

restore_position() 执行：

├─ 查找 id 12345 记录 → 未找到

├─ 自动定位到下一条 → id 12346

└─ move_to_next_on_page → 指向 id 12347

5. 最终结果

- 处理序列：... → 12344 → 12346 → 12348 → ...

- 丢失记录：id 12347

- 影响范围：一条有效记录未被复制到新表

执行流程图如下：

构造数据复现问题

问题的发生逻辑已经明确，现在构造一个最小化的复现用例。

触发问题的几个关键点：

1. delete表中特定记录。

2. ddl buffer不够，触发savepoint和resume机制。

3. purge 线程需要在特定时间窗口内执行（savepoint 创建之后，resume 执行之前purge被标记deleted记录）。

为了尽量少的数据就可以写满ddl buffer触发关键动作，将innodb_ddl_buffer_size设到最小值64k，然后建表插入数据，然后找到ddl buffer满要触发savepoint的关键行。

建一个主键为id BIGINT 的表，并插入了100行数据，将ddl buffer设到最小的64kb，建表及插入数据语句如下：

droptable my_table;
dropPROCEDURE CreateMyTable;

DELIMITER//
CREATEPROCEDURE CreateMyTable()
BEGIN
SET@sql='CREATE TABLE my_table (id BIGINT AUTO_INCREMENT PRIMARY KEY, ';

SET@n=1;
WHILE@n<=100DO
SET@sql= CONCAT(@sql,'col',@n,' BIGINT');
IF@n<100THEN
SET@sql= CONCAT(@sql,', ');
ENDIF;
SET@n=@n+1;
ENDWHILE;

SET@sql= CONCAT(@sql,');');

PREPARE stmt FROM@sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;
END//
DELIMITER;

CALL CreateMyTable();

dropPROCEDURE insert_data;
DELIMITER//
CREATEPROCEDURE insert_data(IN num_rows INT)
BEGIN
DECLARE i INTDEFAULT0;

WHILE i < num_rows DO
INSERTINTO my_table (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10,
                              col11, col12, col13, col14, col15, col16, col17, col18, col19, col20,
                              col21, col22, col23, col24, col25, col26, col27, col28, col29, col30,
                              col31, col32, col33, col34, col35, col36, col37, col38, col39, col40,
                              col41, col42, col43, col44, col45, col46, col47, col48, col49, col50,
                              col51, col52, col53, col54, col55, col56, col57, col58, col59, col60,
                              col61, col62, col63, col64, col65, col66, col67, col68, col69, col70,
                              col71, col72, col73, col74, col75, col76, col77, col78, col79, col80,
                              col81, col82, col83, col84, col85, col86, col87, col88, col89, col90,
                              col91, col92, col93, col94, col95, col96, col97, col98, col99, col100)
VALUES(1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1);
SET i = i +1;
ENDWHILE;
END//
DELIMITER;

setsession innodb_ddl_buffer_size=65536;

truncateTABLE my_table;
CALL insert_data(100);

接下来需要找到触发问题的的关键行记录，然后针对该特定记录做删除，快速复现问题。根据问题发生逻辑，下面提供一个直接找到关键的行的方法：

1. gdb挂载mysqld进程，在 PCursor::savepoint() 打断点，执行重建表：alter table my_table engine=innodb;

2. 命中断点后，将游标指向的记录打印出来，由于表有主键，游标指向的是页面上行的主键的物理位置，主键是8字节bigint，把该位置8个字节打印出来，然后再根据innodb行记录格式写个小程序，把这个8个字节转换成原来的id值得到63，也就是说当前行是id=63。

接下来反复执行下面脚本，删除当前行的前一行，构造触发bug关键点，快的话几次，慢的话10多次一般就能复现bug：

truncateTABLE my_table;
CALL insert_data(100);

deletefrom  my_table  where id=62;
altertable my_table engine=innodb;

deletefrom  my_table  where id=63;
altertable my_table engine=innodb;

deletefrom  my_table  where id=64;
altertable my_table engine=innodb;

deletefrom  my_table  where id=65;
altertable my_table engine=innodb;

deletefrom  my_table  where id=66;
altertable my_table engine=innodb;

deletefrom  my_table  where id=67;
altertable my_table engine=innodb;

deletefrom  my_table  where id=68;
altertable my_table engine=innodb;
selectcount(*)from my_table;

由于每次删除和重建表，都是在触发bug位置行做savepoint和resume操作，只要purge线程在中间插入一次purge动作就能触发bug，所以该场景下命中机率非常高。

脚本中只删了7行，当未触发问题时最后是93行，触发问题时结果是92行。

出现概率与规避手段

这应该是广大用户最关心的问题的，尽管此问题长期存在未修复，但并不意味着它难以触发。相反，在特定业务场景下，触发概率可能异常高。并且，特别需要注意的是，此问题更容易在规范的运维操作中被触发：

普通操作：大量删除表记录，无后续处理，purge 线程自动清理，无风险。
规范的运维操作：大量删除记录，对表空间重建优化，purge 线程自动清理，此时便处于风险中！

概率估算

该问题的出现看似随机，但通过特定方法，我们能够对其发生概率做出估算。下面以几个典型场景为例，分别估算其发生概率。

场景1：

假设表有100万记录，一次删除一行，这一行在随机位置，删完后立即重建表，那可以粗略的认为是百万分之一乘以purge线程purge掉这行概率，大概几百万分之一概率。（如果在删除后立马重建表，purge插入savepoint和resume中间执行的机率并不低，比方上面测试中平均几次能出现一次）

假设删除1行的id为135，则仅136是危险位置，savepoint在此行发生时可能触发bug。

场景2：

一次按主键顺序删除30万行，实际上只有最后那行有概率命中关键行，所以可以粗略认为是七十万分之一乘以purge线程purge掉这行概率，出现概率仍然在百万分之一以下。

删除一批连续记录，仅140是危险位置，savepoint在此行发生时可能触发bug。

场景3：

同样删除30万行，但按不主键顺序删除，按其它列条件删除，这个条件与主键无关联，使删除的数据离散的分布在全表，那么这个问题命中机率就接近了30万/70万 * purge掉这行概率，该场景下问题出现机率将可能上升到百分之几！所以有使用重建表场景不能掉以轻心，需要分析具体业务场景，估算问题发生概率，防范于未然。

删除一批物理位置上不连续的行，则所有的删除行后面都是危险位置，如110、128、146等，savepoint在这些行发生时都可能触发bug。

⚠️ 需重点防范的高危场景

如果业务有批量删除表记录后立即重建表的逻辑，并且删除的行随机分散在全表范围，那么命中该问题的风险会变的极高，需要重点防范！

规避手段

1. 避免在大量delete记录后立即执行表重建ddl。可以detete一批记录后延迟10秒或更长时间后再执行重建表，等待purge线程完成清理后再操作，slave若无延迟也会延迟执行对应ddl，可以绕开该问题。

2. 调大innodb_ddl_buffer_size 可以减少命中机率，调大多少倍就会将savepoint出现在危险位置机率降低多少倍。

3. 不使用inplace方式DDL，或用其它工具替换DDL，如果pt-osc，gh-ost等。

4. 升级到修复该问题版本，官方暂定8.0.41（未发布）版本修复，perona版本8.0.39已经修复，我们云上修复该问题版本已经发布。

官方及percona该问题记录

MySQL Bugs: #115608: Inplace ALTER TABLE might cause lost rows if concurrent purge

﹀