Bug #30798 CHECK says table crashed; REPAIR says it is ok
Submitted: 4 Sep 2007 14:53 Modified: 5 Sep 2007 16:41
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.0.45-community-nt-log OS:Microsoft Windows (W2K server /Linux)
Assigned to: CPU Architecture:Any
Tags: qc

[4 Sep 2007 14:53] Dave Pullin
Description:
I have a table that CHECK (and other operation, except REPAIR) says 'is crashed and should be repaired', but REPAIR says 'Table is already up to date'

I can provide you a copy of the table, but when zipped it is 10MB which exceeds your upload limits. I cant make the table any smaller because all operations fail on it.

.. except REPAIR .. USE_FRM which results in a empty table.

[Of course what I REALLY need to do is repair this table!]

CONSOLE LOG

mysql> check table crashed;
+--------------+-------+----------+---------------------------------------------
-----------------------+
| Table        | Op    | Msg_type | Msg_text
                       |
+--------------+-------+----------+---------------------------------------------
-----------------------+
| test.crashed | check | error    | Table '.\test\crashed' is marked as crashed
and should be repaired |
+--------------+-------+----------+---------------------------------------------
-----------------------+
1 row in set (0.00 sec)

mysql> repair table crashed;
+--------------+--------+----------+-----------------------------+
| Table        | Op     | Msg_type | Msg_text                    |
+--------------+--------+----------+-----------------------------+
| test.crashed | repair | status   | Table is already up to date |
+--------------+--------+----------+-----------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> repair table crashed extended;
+--------------+--------+----------+-----------------------------+
| Table        | Op     | Msg_type | Msg_text                    |
+--------------+--------+----------+-----------------------------+
| test.crashed | repair | status   | Table is already up to date |
+--------------+--------+----------+-----------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> repair table crashed use_frm;
+--------------+--------+----------+----------+
| Table        | Op     | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| test.crashed | repair | status   | OK       |
+--------------+--------+----------+----------+
1 row in set (0.01 sec)

mysql> select count(*) from crashed;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

How to repeat:
if you let me upload the table, unzip it

CHECK TABLE CRASHED
REPAIR TABLE CRASHED

etc.
[4 Sep 2007 14:57] MySQL Verification Team
Thank you for the bug report. Could you please provide the mentioned tables:
ftp.mysql.com/pub/mysql/upload within a zip file. Comment here the file name
when done. Thanks in advance.
[4 Sep 2007 16:44] MySQL Verification Team
Thank you for the feedback.

miguel@luar:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.50-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> check table crashed\G
*************************** 1. row ***************************
   Table: test.crashed
      Op: check
Msg_type: error
Msg_text: Table upgrade required. Please do "REPAIR TABLE `crashed`" to fix it!
1 row in set (0.00 sec)

mysql> repair table crashed;
+--------------+--------+----------+--------------------------------------------------------------------+
| Table        | Op     | Msg_type | Msg_text                                                           |
+--------------+--------+----------+--------------------------------------------------------------------+
| test.crashed | repair | Error    | Table './test/crashed' is marked as crashed and should be repaired |
| test.crashed | repair | Error    | Table 'crashed' is marked as crashed and should be repaired        |
| test.crashed | repair | status   | Table is already up to date                                        |
+--------------+--------+----------+--------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> repair table crashed use_frm;
+--------------+--------+----------+----------+
| Table        | Op     | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| test.crashed | repair | status   | OK       |
+--------------+--------+----------+----------+
1 row in set (0.13 sec)

mysql> select count(*) from crashed;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql>
[4 Sep 2007 22:55] MySQL Verification Team
myisamchk -r followed by "repair table crashed" makes it usable again.  This table came from 4.x probably.
[5 Sep 2007 16:41] Dave Pullin
I discovered the myisamchk will repair the table.
I had assumed that REPAIR and myisamchk -r were equivalent functions, but not so.
[3 Apr 2008 15:30] Andreas Götz
I'm seing a similiar issue using QB- please let me know if I should fork to separate bug instead. Running CHECK, REPAIR, CHECK using QB I'm getting these messages:

------------------------------------------------------------
Status checked for table siebel.log.
Table upgrade required. Please do "REPAIR TABLE `log`" to fix it!

------------------------------------------------------------
Table siebel.log repaired.
Table is already up to date

------------------------------------------------------------
Status checked for table siebel.log.
Table upgrade required. Please do "REPAIR TABLE `log`" to fix it!

So obviously REPAIR didn't work. Any ideas?