Shulou(Shulou.com)06/01 Report--

Recently released to the market version of the frequent database table corruption, the specific phenomenon is that the select table does not exist, but look at the data file, the corresponding table ibd and frm files are there.

Through the statistics of multiple faults, several tables with frequent damage are found. In the process of analysis, it is found that these tables use truncate to clear the data, so it is suspected to be the problem of truncate operation.

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

1. The stored procedure is created as follows. TRUNCATE is called frequently to simulate a table.

DROP PROCEDURE IF EXISTS prcTest5

CREATE PROCEDURE prcTest5 (in ic int)

BEGIN

Declare i int

Set iTunes 0

While (i%tmp%&%tmp%&del tmp%

Goto: eof

4. Start the test for 5-10 minutes, close the test, open the database, and find that the data table is damaged.

Select * from alarmtest5

Prompt table does not exist, actually look in the data directory, frm and ibd files are there.

5. It is suspected that multithreading is the cause of the problem. Reduce the number of threads to 1. After running for 5-10 minutes, the database table is still damaged.

6. Modify the stored procedure to use delete statement, the number of test threads is 5, and there is no damage to the database table.

DROP PROCEDURE IF EXISTS prcTest4

CREATE PROCEDURE prcTest4 (in ic int)

BEGIN

Declare i int

Set iTunes 0

While (I)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.