MYSQL Truncate 引發(fā)數(shù)據(jù)表損壞案例分析

最近發(fā)布到市場的版本頻繁出現(xiàn)數(shù)據(jù)庫表損壞的情況，具體的現(xiàn)象是select表提示表不存在，但是查看data文件，對應(yīng)表的ibd和frm文件都在。

通過對多個故障的統(tǒng)計，找到幾個頻繁出現(xiàn)損壞的表，在分析過程中，發(fā)現(xiàn)這些數(shù)據(jù)表都使用了truncate清除數(shù)據(jù)，所以懷疑是truncate操作的問題。

設(shè)計如下過程來驗證這個分析結(jié)果：

1、 創(chuàng)建存儲過程如下，對一張表模擬頻繁調(diào)用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創(chuàng)建壓力測試用例

發(fā)起的線程為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
::調(diào)用方法call:sleep [毫秒] (1秒=1000毫秒)

goto loop

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

4、 啟動測試，持續(xù)5-10分鐘，關(guān)閉測試，打開數(shù)據(jù)庫，發(fā)現(xiàn)數(shù)據(jù)表損壞。

select * from alarmtest5；

提示表不存在，實際到data目錄下看，frm和ibd文件都在。

5、 懷疑是多線程導(dǎo)致問題，將線程數(shù)降為1，運行5-10分鐘后，依然出現(xiàn)數(shù)據(jù)庫表損壞現(xiàn)象。
6、 將存儲過程修改為使用DELETE語句，測試線程數(shù)5，沒有出現(xiàn)數(shù)據(jù)庫表損壞的情況。

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，結(jié)合自測的情況，懷疑是我們每張表使用一個ibd文件，TRUNCATE表是重建ibd文件過程中mysql進程中斷，導(dǎo)致ibd文件損壞。

7、 重新創(chuàng)建一個數(shù)據(jù)庫，將innodb_file_per_table = 1參數(shù)去掉，所有表共享一個ibd文件。
重復(fù)上面的測試，運行15分鐘沒有出現(xiàn)數(shù)據(jù)庫表損壞的情況。

分析結(jié)論

innodb_file_per_table = 1，使用TRUNCATE會重新創(chuàng)建ibd文件，如果這個過程中mysqld進程意外中斷，有很大概率出現(xiàn)數(shù)據(jù)庫表損壞的現(xiàn)象。