MYSQL Truncate 引发数据表损坏案例解析

2024-07-24 12:34:59

字体：大中小

来源：转载

供稿：网友

　　最近发布到市场的版本频繁出现数据库表损坏的情况，具体的现象是select表提示表不存在，但是查看data文件，对应表的ibd和frm文件都在。

　　通过对多个故障的统计，找到几个频繁出现损坏的表，在分析过程中，发现这些数据表都使用了truncate清除数据，所以怀疑是truncate操作的问题。

　　设计如下过程来验证这个分析结果：

　　1、创建存储过程如下，对一张表模拟频繁调用TRUNCATE

　　DROP PROCEDURE IF EXISTS prcTest5;
　　CREATE PROCEDURE prcTest5(in ic int)
　　BEGIN
　　declare i int;
　　set i=0;
　　while(i<5) DO
　　truncate table alarmtest5;
　　insert into alarmtest5 select * from port limit ic;
　　set i=i+1;
　　END WHILE;

　　END;

　　2、使用SOAP UI创建压力测试用例

　　发起的线程为5
　　测试时间间隔0.5s

　　3、使用bat脚本周期taskkill mysqld进程并重新启动

　　@echo off
　　:loop
　　echo kill
　　taskkill /f /im mysqld.exe
　　echo RegMysqlServer
　　call RegMysqlServer.bat
　　call:sleep 20000
　　::调用方法call:sleep [毫秒] (1秒=1000毫秒)

　　goto loop

　　:sleep
　　set tmp="%temp%/tmp.vbs"
　　echo wscript.sleep %1>%tmp%&%tmp%&del %tmp%
　　goto :eof

　　4、启动测试，持续5-10分钟，关闭测试，打开数据库，发现数据表损坏。

　　select * from alarmtest5；

　　提示表不存在，实际到data目录下看，frm和ibd文件都在。

　　5、怀疑是多线程导致问题，将线程数降为1，运行5-10分钟后，依然出现数据库表损坏现象。
　　6、将存储过程修改为使用DELETE语句，测试线程数5，没有出现数据库表损坏的情况。

　　DROP PROCEDURE IF EXISTS prcTest4;
　　CREATE PROCEDURE prcTest4(in ic int)
　　BEGIN
　　declare i int;
　　set i=0;
　　while(i<5) DO
　　delete from alarmtest4;
　　insert into alarmtest4 select * from port limit ic;
　　set i=i+1;
　　END WHILE;

　　END;
　　7、查看MySQL官方文档，When a table is truncated, it is dropped and re-created in a new .ibd file，结合自测的情况，怀疑是我们每张表使用一个ibd文件，TRUNCATE表是重建ibd文件过程中mysql进程中断，导致ibd文件损坏。

　　7、重新创建一个数据库，将innodb_file_per_table = 1参数去掉，所有表共享一个ibd文件。
　　重复上面的测试，运行15分钟没有出现数据库表损坏的情况。

　　分析结论

　　innodb_file_per_table = 1，使用TRUNCATE会重新创建ibd文件，如果这个过程中mysqld进程意外中断，有很大概率出现数据库表损坏的现象。

（编辑：武林网）

上一篇：mysql 主从复制原则

下一篇：mysql 多表查询示例