MySQL Bugs: #44639: mysqlcheck wants REPAIR TABLE for InnoDB tables

Bug #44639	mysqlcheck wants REPAIR TABLE for InnoDB tables
Submitted:	4 May 2009 11:31	Modified:	6 May 2009 9:47
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Upgrade from 5.0 to 5.1.

Use mysql_upgrade.

mysql_upgrade runs mysqlcheck.

Output from mysqlcheck:

error : Table upgrade required. Please do "REPAIR TABLE `YourTable`" to fix it!

Unfortunately YourTable is an InnoDB table and REPAIR TABLE won't work here.

How to repeat:
Very hard to repeat. Customer run into this issue.

But there is none hint in source code that storage engines will be checked.

Verification happened by reading source code.

Suggested fix:
Check storage engines and let the user know what to do with InnoDB tables instead of recommending to use REPAIR TABLE which is only for MyISAM

Also worth commenting. We are using MySQL rpms where this issue occurred.

So we had a working 5.0 instance. We stop it and upgrade to 5.1.
We run mysql_upgrade which tells us that we need to REPAIR the table, but this doesn't work (for innobase).

Further investigation shows that it's probably necessary to actually dump the table contents (from 5.0), but we're now running the 5.1 binaries so we need to stop mysql again and reinstall the 5.0 binaries in order to dump the data.

Suggestion (1): create a pre-upgrade script which will warn users of situations like this so that they can prepare BEFORE doing the upgrade.

Suggestion (2): in the mysql_upgrade script do NOT recommend using REPAIR table if it is not going to work. Innodb tables are "normal" so the software should really be able to explain the necessary steps.

1. downgrade to 5.0.
2. dump innodb tables
3. upgrade back to 5.1.
4. reload dumps of previous tables.

Failing this point to documentation which explains the issue and how to resolve it.

Looks like duplicate of bug #42563

You are right. This is a duplicate of bug #42563