MySQL Bugs: #30495: optimize table t1,t2,t3 extended errors

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

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:
.

caused the error after 20 minutes of running this script

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

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 ..

testcase.

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

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

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.54

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.