MySQL ALTER 操作可能导致数据丢失

原创
01/10 15:00
阅读数 22

本文为墨天轮数据库管理服务团队原创内容,如需转载请联系小墨(VX:modb666)并注明来源。


作者:杨明翰

墨天轮数据库管理服务团队总监

专注于MySQL性能优化和功能改进,擅长MogDB(openGauss)运维与优化,所服务的行业涉及电商、金融和政企等,致力于将积累的IT技术与行业实践分享给业界的客户及同仁,帮助到更多的企业和用户。


一、问题

MySQL存在一个严重的bug,当alter table且ALGORITHM=INPLACE时,有概率会触发bug,导致非预期的唯一键冲突错误造成变更失败。更严重的情况会导致数据丢失。涉及的版本为8.0.27+以及8.4.0+。官方预计8.0.41版本修复。percona在版本8.0.39-30 (released 2024-10-08) 中修复。
该问题是由MySQL 8.0.27中引入的新的并行online alter table代码导致的,除了数据丢失外,还会导致optimize/alter table 触发非预期的唯一键冲突造成操作失败的问题。涉及的bug有:
https://bugs.mysql.com/bug.php?id=113812;
https://bugs.mysql.com/bug.php?id=115608;
https://perconadev.atlassian.net/browse/PS-9144;
https://perconadev.atlassian.net/browse/PS-9214
虽然触发的概率较低,但该问题应属于严重bug。一旦触发数据丢失,不仅alter table不会有任何异常提示,在errlog和binlog中也不会有丢失行的任何信息,可能很长时间都不会被发现,这会导致数据找回非常困难。非预期的唯一键冲突虽然不会影响数据完整性,但也造成系统资源的无谓消耗,特别是在变更窗口有限的情况下。


二、分析

对以上这两个问题的分析,参考
https://github.com/percona/percona-server/commit/e331f9f2a28af52e3e25050ad3d8c99c6b408595;https://github.com/percona/percona-server/commit/d9d50adab52b453dd7504d784060bd593d6af812
8.0.27引入的并行ALTER TABLE INPLACE新特性,在单线程的在线table重建中也会用到。在重建过程中会迭代处理表中所有的行,使用不同的线程处理Btree中的不同子树,在这一过程中会时不时的暂停,以提交innodb MTR(Mini Transaction保证数据页操作的完整性和持久性)和释放持有的page latches。为了在暂停后恢复执行,在释放锁之前需要持久化光标指向的记录。
上述逻辑的恢复迭代和初始化游标在PCursor::restore_position() 中实现,在暂停时会将当前记录在页面中的上一个记录作为保存点,然后提交 mini-transaction并释放latches。恢复时将游标指向下一个记录,恢复迭代。这里有一个例外,如果暂停期间保存点这个记录被purge,PCursor::restore_position() 会将光标移动到下一个记录。也就是说可能会触发光标移动两次,导致丢失记录的情况。
举个例子,一个page存了abcde五条记录 [inf, a, b, c, d, e, sup],在处理c时暂停(此时记录b已被删除),这时会将b作为保存点,提交MTR,释放page latches。latches释放后b被purge线程清理。迭代恢复时光标指向a,PCursor::restore_position() 检测到保存点b已被清理,会将光标移动到下一个记录c,这时由于c行在暂停前已被构建所以会立刻插入内存缓冲区。由于暂停时保存点是待处理记录的上一条记录,还会将光标下移一位进行补充,此时光标指向e并处理。记录d被跳过,变更完成后记录d丢失。
由于ALTER TABLE INPLACE执行过程中会多次暂停,所以极端情况下丢失的数据不止一行。


三、复现

测试环境版本8.0.38,创建一张测试表
h:localhost u:root :db(none) [16:59:59]>select version();
+-----------+
| version() |
+-----------+
| 8.0.38 |
+-----------+
1 row in set (0.00 sec)

h:localhost u:root :db(none) [17:00:04]>show create table test.his_schedule_job_run;
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| his_schedule_job_run | CREATE TABLE `his_schedule_job_run` (
`id` varchar(36) NOT NULL,
`job_id` varchar(36) DEFAULT NULL,
`job_type` varchar(2) DEFAULT NULL,
`job_group` varchar(45) DEFAULT NULL,
`job_obiect` varchar(256) DEFAULT NULL,
`shell_path` varchar(256) DEFAULT NULL,
`cron_expression` varchar(128) DEFAULT NULL,
`run_start_time` datetime DEFAULT NULL,
`run_end_time` datetime DEFAULT NULL,
`run_result` varchar(2) DEFAULT NULL,
`run_message` varchar(4000) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `fk_reference_02` (`job_id`) USING BTREE,
KEY `fk_reference_03` (`run_start_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='logtable' |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
使用以下脚本,使用mysql_random_data_load工具向test.his_schedule_job_run表插入2000条数据,记录当前行数。delete部分数据后再记录剩余行数,最后执行alter table test.his_schedule_job_run engine = innodb(默认会使用inpalce方法)并统计表行数。循环50次,并且将结果输出到日志。理论上后两次的count(*)结果应相等,如果触发bug则第三次的count(*)会小于第二次
#!/bin/bash
>/tmp/his_schedule_job_run.log
for i in {1..50}
do
echo "call test.his_schedule_job_run $i time" >> /tmp/his_schedule_job_run.log
/root/mysql_random_data_load -uroot -pXXXXXXXX -P3306 --max-threads=2 test his_schedule_job_run 2000
/usr/local/mysql/bin/mysql -uroot -pXXXXXXXX -S /tmp/mysql.sock -e "select count(*) from test.his_schedule_job_run;" >> /tmp/his_schedule_job_run.log
/usr/local/mysql/bin/mysql -uroot -pXXXXXXXX -S /tmp/mysql.sock -e "delete from test.his_schedule_job_run where run_start_time < curdate() - interval 7 day;"
echo "call $i time alter before" >> /tmp/his_schedule_job_run.log
/usr/local/mysql/bin/mysql -uroot -pXXXXXXXX -S /tmp/mysql.sock -e "select count(*) from test.his_schedule_job_run;" >> /tmp/his_schedule_job_run.log
/usr/local/mysql/bin/mysql -uroot -pXXXXXXXX -S /tmp/mysql.sock -e "alter table test.his_schedule_job_run engine = innodb;"
echo "call $i time alter after" >> /tmp/his_schedule_job_run.log
/usr/local/mysql/bin/mysql -uroot -pXXXXXXXX -S /tmp/mysql.sock -e "select count(*) from test.his_schedule_job_run;" >> /tmp/his_schedule_job_run.log
echo "call test.his_schedule_job_run $i time" >> /tmp/his_schedule_job_run.log
done

查看日志/tmp/his_schedule_job_run.log,发现存在触发bug的现象

.........

call test.his_schedule_job_run 42 time
count(*)
3823
call 42 time alter before
count(*)
1869
call 42 time alter after
count(*)
1868
call test.his_schedule_job_run 42 time

call test.his_schedule_job_run 43 time
count(*)
3868
call 43 time alter before
count(*)
1913
call 43 time alter after
count(*)
1911
call test.his_schedule_job_run 43 time


...............

call test.his_schedule_job_run 45 time
count(*)
3955
call 45 time alter before
count(*)
1999
call 45 time alter after
count(*)
1997
call test.his_schedule_job_run 45 time

..............

call test.his_schedule_job_run 48 time
count(*)
4090
call 48 time alter before
count(*)
2115
call 48 time alter after
count(*)
2114
call test.his_schedule_job_run 48 time

call test.his_schedule_job_run 49 time
count(*)
4114
call 49 time alter before
count(*)
2149
call 49 time alter after
count(*)
2148
call test.his_schedule_job_run 49 time

...........


四、规避

1、官方还未发布修复该bug的版本(计划中的8.0.41会修复),percona版本建议升级到MySQL 8.0.39-30 or 8.4.2-2
2、alter table时指定ALGORITHM=INSTANT/COPY ,避免使用INPALCE方法。但INSTANT支持的类型有限且有64次的限制,TOTAL_ROW_VERSIONS可以通过 >SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'dbname/tablename' ; 查看。COPY执行期间会阻塞对表的修改。
3、使用gh-ost 或者 pt-osc等第三方在线ddl工具执行alter table操作。


参考文档:

https://www.percona.com/blog/who-ate-my-mysql-table-rows/


THE END

墨天轮从乐知乐享的数据库技术社区蓄势出发,全面升级,提供多类型数据库管理服务。墨天轮数据库管理服务旨在为用户构建信赖可托付的数据库环境,并为数据库厂商提供中立的生态支持。

服务官网:https://www.modb.pro/service

点击进入作者个人主页

本文分享自微信公众号 - 墨天轮(enmocs)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
0 评论
0 收藏
0
分享
返回顶部
顶部