MySQL Bugs: #5699: OPTIMIZE TABLE corrupt the table

Bug #5699	OPTIMIZE TABLE corrupt the table
Submitted:	22 Sep 2004 13:59	Modified:	15 Feb 2005 8:35
Reporter:	François Jeanmougin	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.18 and 4.0.21	OS:	Linux (linux RHEL 2.1)
Assigned to:		CPU Architecture:	Any

Description:
Using a MyISAM table like this :
+--------------------+--------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
| Name               | Type   | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Create_options | Comment |
+--------------------+--------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+| phpads_adstats     | MyISAM | Dynamic    | 1930896 |             21 |    40617148 |      4294967295 |     60020736 |         0 |           NULL | 2004-09-20 12:11:09 | 2004-09-22 14:28:09 | 2004-09-22 12:28:21 |                |         |

OPTIMIZE TABLE failed with error code 28 and corrupt the table (probably don't close it properly).

After that, the transactions to that DB where not logged in the binary log file. So we lose some hours of transactions.

How to repeat:
Reproductible using an heavily loaded phpadnews server, using about 15 php servers and a MySQL database. Apparently appeared when number of rows reached about 2,000,000 (not sure). Tested on 4.0.18 and 4.0.21. THe product is using the root user for all the transactions (a bad idea, I think, I'm not responsible for that).

Suggested fix:
OPTIMIZE TABLE should close the table properly even if it fails. OPTIMIZE TABLE should be able to work on production environment OR update the documentation to disadvise the use of OPTIMIZE TABLE under heavily loaded servers.

Even failed (or inifinitely delayed) transactions should be logged somewhere so that we can replay it on the database when the table is repaired.

Thank you.

Well, I didn't see any other bug like this till the one corrected in 2002 on MySQL 3. I think it is not low priority anyway.

Might it be that the system runs out of disk space during OPTIMIZE?

No. The same database ran out of disk space months ago without such problem. Currently, the disk partition is only 25% full. The problem is reproductible among the master and the replicant. Disks are RAID 1 hard. I tried to find a disk error but they were none.

I could reproduce the bug, if you need any trace, but it is really a problem because it is a production system.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Sorry, I missed the "Need feedback" comment. There is no disk problem on the system. We have a course in France at Mysql.com in march, we will discuss this problem with the expert and give you a complete technical feedback, because the course will cover the migration of this database on a NBD cluster.