Bug #30495 optimize table t1,t2,t3 extended errors
Submitted: 19 Aug 2007 7:59 Modified: 3 Dec 2007 7:45
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.0.48,5.1.23 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: optimize table

[19 Aug 2007 7:59] Shane Bester
Description:
After running a batch of stress tests for 12 hours in multiple threads, I ran the following:

mysql> check table t1,t2,t3 extended;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       |
| test.t2 | check | status   | OK       |
| test.t3 | check | status   | OK       |
+---------+-------+----------+----------+
3 rows in set (41.56 sec)

mysql> optimize table t1,t2,t3 extended;
+---------+----------+----------+--------------------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                                       |
+---------+----------+----------+--------------------------------------------------------------------------------+
| test.t1 | optimize | info     | Found link that points at 5768065872638561180 (outside data file) at 288192    |
| test.t1 | optimize | info     | Found link that points at -4347163776345133056 (outside data file) at 13632056 |
| test.t1 | optimize | status   | OK                                                                             |
| test.t2 | optimize | info     | Found block with too small length at 2496096; Skipped                          |
| test.t2 | optimize | info     | Found block that points outside data file at 6258264                           |
| test.t2 | optimize | status   | OK                                                                             |
| test.t3 | optimize | info     | Found block with too small length at 16115876; Skipped                         |
| test.t3 | optimize | status   | OK                                                                             |
+---------+----------+----------+--------------------------------------------------------------------------------+
8 rows in set (4.14 sec)

How to repeat:
gypsy --queryfile=coverage.sql --threads=5 --duration=12000 --shuffle-queries=1

Suggested fix:
.
[19 Aug 2007 9:03] Shane Bester
caused the error after 20 minutes of running this script

Attachment: coverage.sql (application/octet-stream, text), 4.14 KiB.

[9 Oct 2007 2:09] Shane Bester
I finally have a repeatable testcase for this, and can say for sure it's a real bug.  In my new testcase, the optimize table is causing rows to be lost sometimes.
<cut>
| test.t2 | optimize | info     | Found block with too small length at 476188; Skipped
| test.t2 | optimize | warning  | Duplicate key for record at 171532 against record at 2264
| test.t2 | optimize | warning  | Number of rows changed from 13121 to 13120
| test.t2 | optimize | status   | OK
</cut>

the only unique keys are the primary autoinc, and a unique key on a prefix of a varchar column.  will upload testcase soon ..
[9 Oct 2007 3:37] Shane Bester
testcase.

Attachment: optimizer.php (application/octet-stream, text), 1.98 KiB.

[21 Nov 2007 7:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38188

ChangeSet@1.2578, 2007-11-21 11:11:22+04:00, ramil@mysql.com +3 -0
  Fix for bug #30495: optimize table t1,t2,t3 extended errors
  
  Problem: we have CHECK TABLE options allowed (by accident?) for
  ANALYZE/OPTIMIZE TABLE.
  
  Fix: disable them.
  
  Note: it might require additional fixes in 5.1/6.0
[28 Nov 2007 10:25] Bugs System
Pushed into 6.0.4-alpha
[28 Nov 2007 10:26] Bugs System
Pushed into 5.1.23-rc
[28 Nov 2007 10:27] Bugs System
Pushed into 5.0.54
[3 Dec 2007 7:45] MC Brown
A note has been added to the 6.0.4, 5.1.23 and 5.0.54 changelogs: 

The options available to the CHECK TABLE statement were also allowed in OPTIMIZE TABLE and ANALYZE TABLE statements, but caused corruption during their execution. These options were never supported for the these statements, and an error is now raised if you try to apply these options to these statements.