MySQL Bugs: #30741: REPAIR TABLE doesn't work anymore

Bug #30741	REPAIR TABLE doesn't work anymore
Submitted:	31 Aug 2007 6:58	Modified:	4 Sep 2007 21:00
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.20-beta, 6.0-falcon	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Hi,

Since 5.1.20-beta (and with 6.0 as well), REPAIR TABLE seems to have no effect, it just reports the corruption (like a CHECK TABLE does).
To really repair the table, I have to use REPAIR TABLE ... USE_FRM.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` (
  `a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (1),(2);

Then, remove some bytes at the end of the MYI file, and do a FLUSH TABLES;

SELECT * FROM t1;
ERROR 1034 (HY000): Incorrect key file for table 't1'; try to repair it

REPAIR TABLE t1;
+---------+--------+----------+-----------------------------------------------------+
| Table   | Op     | Msg_type | Msg_text                                            |
+---------+--------+----------+-----------------------------------------------------+
| test.t1 | repair | Error    | Incorrect key file for table 't1'; try to repair it |
| test.t1 | repair | error    | Corrupt                                             |
+---------+--------+----------+-----------------------------------------------------+
2 rows in set (0.00 sec)

REPAIR TABLE t1;
+---------+--------+----------+-----------------------------------------------------+
| Table   | Op     | Msg_type | Msg_text                                            |
+---------+--------+----------+-----------------------------------------------------+
| test.t1 | repair | Error    | Incorrect key file for table 't1'; try to repair it |
| test.t1 | repair | error    | Corrupt                                             |
+---------+--------+----------+-----------------------------------------------------+
2 rows in set (0.00 sec)

REPAIR TABLE t1 EXTENDED;
+---------+--------+----------+-----------------------------------------------------+
| Table   | Op     | Msg_type | Msg_text                                            |
+---------+--------+----------+-----------------------------------------------------+
| test.t1 | repair | Error    | Incorrect key file for table 't1'; try to repair it |
| test.t1 | repair | error    | Corrupt                                             |
+---------+--------+----------+-----------------------------------------------------+
2 rows in set (0.00 sec)

REPAIR TABLE t1 USE_FRM;
+---------+--------+----------+------------------------------------+
| Table   | Op     | Msg_type | Msg_text                           |
+---------+--------+----------+------------------------------------+
| test.t1 | repair | warning  | Number of rows changed from 0 to 2 |
| test.t1 | repair | status   | OK                                 |
+---------+--------+----------+------------------------------------+
2 rows in set (0.01 sec)

Regards,
  Jocelyn

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You corrupted index file. This is USE_FRM supposed to help. See also http://dev.mysql.com/doc/refman/5.1/en/repair-table.html

Additionally I can not repeat described behaviour if remove bytes from the end of file in my environment.

Hi,

In my case I didn't corrupt the header in the MYI file (I actually removed one byte at the end of the file with vi), so the REPAIR TABLE should works correctly.
Moreover 5.1.19-beta is working properly and properly repair the table.
I can upload the corrupted table files if you need them.

Regards,
  Jocelyn

Thank you for the feedback.

As I indicated before I can not repeat escribed behaviour if I remove last byte[s] using vim. So, please, upload corrupted table.

Hi,

Actually you are right, I messed up the initial file, I didn't notice I wasn't deleting the very latest bytes of the index index.
This bug can be closed, sorry.

Regards,
  Jocelyn

Thank you for the feedback.

The bug is closed again.