MYSQL Truncate trigger data table corruption case analysis

Recently released to the market version of the frequent occurrence of database table corruption, the specific phenomenon is that the Select Table hint table does not exist, but to view the data file, the corresponding table of IBD and frm files are in.

Through the statistics of multiple failures, found several frequently damaged tables, in the analysis process, found that these data tables used truncate to clear the data, so suspect is the truncate operation problem.

The following procedure is designed to verify the results of this analysis:

1. Create stored procedure as follows, simulate frequent calls to a table 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. Creating stress test Cases using the SOAP UI

The thread that originated is 5
Test time interval 0.5s

3. Use the bat script cycle taskkill mysqld process and restart

@echo off
: Loop
Echo Kill
taskkill/f/im Mysqld.exe
Echo Regmysqlserver
Call Regmysqlserver.bat
Call:sleep 20000
:: Call method Call:sleep [Ms] (1 seconds =1000 milliseconds)

Goto Loop

: Sleep
Set tmp= "%temp%\tmp.vbs"
echo Wscript.Sleep%1>%tmp%&%tmp%&del%tmp%
Goto:eof

4, start the test, for 5-10 minutes, close the test, open the database, found the data table corruption.

SELECT * from ALARMTEST5;

The hint table does not exist, actually to the data directory to see, frm and IBD files are in.

5, the suspicion is multi-threaded causes the problem, the number of threads down to 1, after running 5-10 minutes, the database table corruption still occurs.
6, modify the stored procedure to use the DELETE statement, test the number of threads 5, there is no database table corruption situation.

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. To view the official MySQL document, when a table is truncated, it's dropped and re-created in a new IBD file, combined with a self-test case, the suspect is that each of us uses an IBD document for each table, Truncat The e table is the process of rebuilding the IBD file during the MySQL process interruption, resulting in IBD file corruption.

7, re-create a database, the innodb_file_per_table = 1 parameters are removed, all tables share an IBD file.
Repeat the test above and run for 15 minutes without a database table corruption.

Analysis conclusion

innodb_file_per_table = 1, using truncate will recreate the IBD file, if the process mysqld process unexpectedly interrupted, there is a large probability that the database table corruption phenomenon.

MYSQL Truncate trigger data table corruption case analysis