Bug #29249 thread hang with concurrent insert/optimize of archive tables
Submitted: 20 Jun 2007 21:01 Modified: 27 Jun 2007 7:42
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S1 (Critical)
Version:5.0.44 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: hang

[20 Jun 2007 21:01] Shane Bester
Description:
20 threads inserting and optimizing an archive table leads to a indefinate hangup and 100% cpu usage

mysql> show processlist;
+----+------+-----------+------+---------+------+--------------------------+---------------
| Id | User | Host      | db   | Command | Time | State                    | Info
+----+------+-----------+------+---------+------+--------------------------+---------------
| 18 | root | localhost | test | Query   |    8 | freeing items            | SHOW /*!50010
| 19 | root | localhost | test | Execute |  495 | Waiting for table        | insert into t1
| 20 | root | localhost | test | Prepare |  490 | Opening tables           | insert into t1
| 21 | root | localhost | test | Query   |  495 | Waiting to get writelock | optimize table
| 22 | root | localhost | test | Execute |  495 | Opening tables           | insert into t1
| 23 | root | localhost | test | Query   |  495 | Opening tables           | check table t1
| 24 | root | localhost | test | Query   |  495 | Waiting for table        | check table t1
| 25 | root | localhost | test | Prepare |  495 | Waiting for table        | insert into t1
| 26 | root | localhost | test | Execute |  495 | Opening tables           | insert into t1
| 27 | root | localhost | test | Execute |  490 | Opening tables           | insert into t1
| 28 | root | localhost | test | Execute |  490 | Opening tables           | insert into t1
| 29 | root | localhost | test | Execute |  495 | Opening tables           | insert into t1
| 30 | root | localhost | test | Execute |  490 | Opening tables           | insert into t1
| 31 | root | localhost | test | Query   |  495 | Waiting to get writelock | optimize table
| 32 | root | localhost | test | Execute |  495 | Opening tables           | insert into t1
| 33 | root | localhost | test | Execute |  490 | Opening tables           | insert into t1
| 34 | root | localhost | test | Query   |  490 | Waiting for table        | checksum table
| 35 | root | localhost | test | Query   |  495 | Opening tables           | optimize table
| 36 | root | localhost | test | Execute |  490 | Opening tables           | insert into t1
| 37 | root | localhost | test | Query   |  495 | Waiting to get writelock | optimize table
| 38 | root | localhost | test | Execute |  495 | Waiting for table        | insert into t1
| 41 | root | localhost | test | Query   |    0 | NULL                     | show processli
+----+------+-----------+------+---------+------+--------------------------+---------------

How to repeat:
will make a testcase later.

Suggested fix:
.
[20 Jun 2007 21:34] MySQL Verification Team
see top of file for user, host, password, gcc compile example.

Attachment: bug29249.c (text/plain), 6.05 KiB.

[20 Jun 2007 21:34] MySQL Verification Team
5.0.44 hangs. the crash in 5.1.20 is addressed in bug #29250
[27 Jun 2007 7:42] Sergey Vojtovich
This test case discovers a lot of problems with archive. After fixing some of them 5.1 deadlocks as 5.0. So this bug is a duplicate of BUG#29250.