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)
• Status: Closed
• Category: MySQL Server: Archive storage engine
• Severity: S1 (Critical)
• Version: 5.0.64/5.1.46/6.0
• OS: Any
• CPU Architecture: Any

[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] MySQL Verification Team

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] MySQL Verification Team

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] MySQL Verification Team

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] MySQL Verification Team

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.