MySQL Bugs: #40284: Upgrading from mysql 4.x to 5.x and running repair table causes odd behavior

Bug #40284	Upgrading from mysql 4.x to 5.x and running repair table causes odd behavior
Submitted:	23 Oct 2008 18:13	Modified:	13 Apr 2009 9:35
Reporter:	Nathan March	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.67	OS:	Linux (Gentoo)
Assigned to:		CPU Architecture:	Any

Description:
After upgrading a server from mysql 4 to mysql 5, a table was coming back as crashed. Ran a repair table and it didn't fix the table, even with the extended flag. Ran it with use_frm and it marked the table as ok despite returning an error, however running a check table marks it as crashed again.

I ended up having to use myisamchk to repair the table.

How to repeat:
I've got a backup of the corrupted myisam files, feel free to email me for a copy of them:

mysql> select count(*) from ADMINERRORS;
ERROR 145 (HY000): Table './nathan/ADMINERRORS' is marked as crashed and should be repaired

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

mysql> select count(*) from ADMINERRORS;
ERROR 145 (HY000): Table './nathan/ADMINERRORS' is marked as crashed and should be repaired

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

mysql> select count(*) from ADMINERRORS;
ERROR 145 (HY000): Table './nathan/ADMINERRORS' is marked as crashed and should be repaired

mysql> repair table ADMINERRORS use_frm;
+-------------+--------+----------+----------------------------------------+
| Table       | Op     | Msg_type | Msg_text                               |
+-------------+--------+----------+----------------------------------------+
| ADMINERRORS | repair | error    | Failed reparing incompatible .FRM file | 
+-------------+--------+----------+----------------------------------------+
1 row in set (0.00 sec)

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

mysql> check table ADMINERRORS;
+--------------------+-------+----------+---------------------------------------------------------------------------+
| Table              | Op    | Msg_type | Msg_text                                                                  |
+--------------------+-------+----------+---------------------------------------------------------------------------+
| nathan.ADMINERRORS | check | error    | Table upgrade required. Please do "REPAIR TABLE `ADMINERRORS`" to fix it! | 
+--------------------+-------+----------+---------------------------------------------------------------------------+
1 row in set (0.01 sec)

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

mysql> select count(*) from ADMINERRORS;
ERROR 145 (HY000): Table './nathan/ADMINERRORS' is marked as crashed and should be repaired

Thank you for the report.

Using REPAIR ... USE_FRM when upgrading major version is strictly not recommended and can lead to unexpected results. Regarding to check problem please read http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html for list of incompatible changes and if you are sure original table does not contain such fields please provide output of SHOW CREATE TABLE for the problem table.

The use_frm repair is performing as expected, with the exception that it seems to be marking the table as OK afterwards.

From: http://dev.mysql.com/doc/refman/5.0/en/repair-table.html
Caution
As of MySQL 5.0.62, if you use USE_FRM and your table was created by a different version of the MySQL server than the one you are currently running, REPAIR TABLE will not attempt to repair the table. In this case, the result set returned by REPAIR TABLE contains a line with a Msg_type value of error and a Msg_text value of Failed repairing incompatible .FRM file. 

So the error that it's returning is correct and valid, but it shouldn't be marking the table as clean after its failed to do the repair.

Thank you for the feedback.

> So the error that it's returning is correct and valid, but it shouldn't be marking the
> table as clean after its failed to do the repair.

But when it marks table as clean? Do you mean last row in the output following or what?

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

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