Bug #37280 check and repair table report table corrupted when run concurrently with inserts
Submitted: 9 Jun 2008 12:36 Modified: 5 Mar 2012 20:13
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S1 (Critical)
Version:5.0.64/5.1.46/6.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[9 Jun 2008 12:36] Shane Bester
Description:
if you run check table at the same time some insert is inserting into an archive table, you get the following result from the check/repair table:

mysql> check table t1 extended;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | error    | Corrupt  |
+---------+-------+----------+----------+
1 row in set (4.78 sec)

mysql> repair table t1;
+---------+--------+----------+-----------------------------------------------
| Table   | Op     | Msg_type | Msg_text
+---------+--------+----------+-----------------------------------------------
| test.t1 | repair | Error    | Out of memory at line 51, '.\sql_string.cc'
| test.t1 | repair | Error    | needed 2779096488 byte (2713962k), memory in u
| test.t1 | repair | status   | OK
+---------+--------+----------+-----------------------------------------------
3 rows in set (14.14 sec)

How to repeat:
run the attached php testcase to insert rows into archive table.
in another thread run "check table t1" until it reports corrupt, then
try repair table t1 and see how it fails

Suggested fix:
fix the locking issues in 5.0?
[9 Jun 2008 12:37] Shane Bester
run this in one thread and in another thread 'check table t1' over and over on 5.0.x

Attachment: bug37280.php (application/octet-stream, text), 658 bytes.

[10 Jun 2008 13:28] Miguel Solorzano
c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3351 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.26-rc-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >use test
Database changed
mysql 5.1 >check table t1 extended;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | error    | Corrupt  |
+---------+-------+----------+----------+
1 row in set (3.48 sec)
[10 Jun 2008 14:02] Miguel Solorzano
Thank you for the bug report.

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3350 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.64-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > use test
Database changed
mysql 5.0 > check table t1 extended;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | error    | Corrupt  |
+---------+-------+----------+----------+
1 row in set (1.48 sec)

c:\dbs>c:\dbs\6.0\bin\mysql -uroot --port=3360 --prompt="mysql 6.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.6-alpha-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 6.0 > use test
Database changed
mysql 6.0 > check table t1 extended;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | error    | Corrupt  |
+---------+-------+----------+----------+
1 row in set (0.73 sec)
[1 May 2010 7:57] Shane Bester
bug #51252 covers this problem in the megatest program.
[20 Sep 2010 18:35] Konstantin Osipov
Please re-verify against 5.5.6. (Might have been fixed along with Bug#989).
[24 Sep 2010 19:57] Sveta Smirnova
Still repeatable with 5.6.99, although more tries needed to repeat the problem.
[24 Sep 2010 20:03] Sveta Smirnova
Same with mysql-trunk.
[27 Sep 2010 18:42] Konstantin Osipov
Workaround: LOCK TABLE t1 WRITE; REPAIR TABLE t1;
[27 Sep 2010 18:43] Konstantin Osipov
Sergey, please change R/E if you disagree with my assessment.
[27 Sep 2010 19:04] Sergey Vojtovich
I have a strong feeling (though no proof yet) that this bug's roots are the same as with BUG#42784. And BUG#42784 is unlikely R2/E2, rather R2 (because affects only archive engine) E4 (because there are lots of things to get fixed).
[5 Mar 2012 20:13] Paul Dubois
Noted in 5.1.62, 5.5.22, 5.6.5 changelogs.

Due to improper locking, concurrent inserts into an ARCHIVE table at
the same time as repair and check operations on the table resulted in
table corruption.