Bug #44640 mysql_upgrade/mysqlcheck fails on InnoDB tables
Submitted: 4 May 2009 11:38 Modified: 22 May 2009 20:12
Reporter: Susanne Ebrecht Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[4 May 2009 11:38] Susanne Ebrecht
Customer want to upgrade from 5.0 to 5.1.

He run mysql_upgrade and that runs mysqlcheck.

He got an error message and the recommendation that he should use REPAIR TABLE.

But the table, which mysqlcheck thought is defect is an InnoDB table.

There is none hint in the documentation that mysql_upgrade don't take care of InnoDB tables.

A note here at mysql_upgrade and mysqlcheck that these tools only work fine with MyISAM would be nice.

How to repeat:

Suggested fix:
Just a note at the mysql_upgrade and/or mysqlcheck section that these tools could fail with InnoDB or that these tools are mainly for MyISAM.
[8 May 2009 16:15] Paul DuBois
I'm not sure what you want.

http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html does point out that the program runs mysqlcheck:

"mysql_upgrade executes the following commands to check and repair tables and to upgrade the system tables. fix_priv_tables represents a script generated interally by mysql_upgrade that contains SQL statements to upgrade the tables in the mysql database:

mysqlcheck --check-upgrade --all-databases --auto-repair
mysql < fix_priv_tables
mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names"

And also says:

"For details about what is checked, see the description of the FOR UPGRADE option of the CHECK TABLE statement (see Section, “CHECK TABLE Syntax”)."

And the CHECK TABLE section
lists the applicable storage engines.

You're right that CHECK TABLE shouldn't suggest REPAIR TABLE for InnoDB, because REPAIR TABLE doesn't support InnoDB tables. But that's a CHECK TABLE problem, which has been reported as Bug#42563.
[8 May 2009 17:10] Simon Mudd
What I want is to NOT have to upgrade to 5.1 to run mysqlcheck, in order to find there are tables that I must first dump by downgrading again to 5.0, so I can upgrade back to 5.1 to reload them. Follow me? Playing start/stop instances with different versions is just silly.

So what I would really like is that 5.1 CAN read and REPAIR Innodb tables, even those generated by 5.0 and that this is part of the "standard 5.1 upgrade procedure". (even mysqldump / load but using 5.1 binaries would be "ok" though probably slower.

I just would like to avoid switching binaries several times just to work out WHAT needs upgrading and and what doesn't.

That doesn't seem possible now and is frustrating.
[8 May 2009 17:18] Simon Mudd
I'd suggest more than a note in the documentation. Make life easy for the DBA. 

Provide tools which tell him what needs to be done (this avoids mistakes), and ensure that there's a clear step by step procedure for checking what needs to be done and then performing the upgrade.

So if mysql_upgrade is supposed to "upgrade the server" and mysqlcheck is supposed to "check what actions need to be done to upgrade the server", then make them work like that and do the full job.

If not please MENTION explicitly how to check what needs to be done for non-MyISAM tables and at what moment.
[8 May 2009 17:25] Simon Mudd
A further point that is not clear to me, and might be useful to know.

mysqlcheck is a CLIENT program. Can I successfully run a 5.1 mysqlcheck on a running 5.0 mysqld instance? That is can I check what needs to be done without actually upgrading the server to 5.1?

If so that would make life MUCH simpler. It's a pain with rpm installations as you don't support multiple version binaries to be installed at once, but as a temporary solution binary tarballs can be downloaded and used for this testing (if indeed it works).

So perhaps you can clarify this point. I've assumed it doesn't work but perhaps mysqlcheck (5.1) could determine it's talking to a 5.0 server and tell you:

* these tables need to be mysqldumped on the 5.0 version and then reloaded after the upgrade
* these tables need to be upgraded with repair table after upgrading
* etc..

That would be much kinder to the dba, especially if it also detected issues with stored procedures, triggers and grants which are mentioned in several different places in the mysql upgrade documentation.

Then if run on a 5.1 server it could say: you forgot to dump these tables and thus must downgrade to mysqldump them....
[8 May 2009 17:30] Paul DuBois
This is beyond a documentation request. It is at least a feature request. (e.g., make mysql_upgrade do the full job.)
[11 May 2009 10:45] Susanne Ebrecht
Ok, some confusion here.

Let me clarify my original thoughts here and why just documentation and not a server/client bug/feature request:

What Simon mentioned here is not related to this bug report here. But I am on that topic too in another thread.

What I wanted to get documented:

output of mysql_upgrade/mysqlcheck:

error: ... use REPAIR TABLE ...

Development is already on the way to fix this error message into use REPAIR TABLE or ALTER TABLE.

For MyISAM tables repair table should work and also REPAIR TABLE already is documented.

But even when there is the error message to use ALTER TABLE it is not clear for a user what exactly he needs to do with tables from other storage engine.

It would be nice to have in the documentation something like:

When you will get this error message:
error ... use REPAIR TABLE ...

and your table is using another storage engine then MyISAM then use the following for repairing:

ALTER TABLE <tab> ENGINE=<engine>;

ALTER TABLE ENGINE will copy the table and this will repair your table by automatism.
[15 May 2009 19:41] Paul DuBois

I think some of the information you want is probably already at:

But I will see about expanding this and tying it to the mysqlcheck/mysql_upgrade sections.


Yes, you can connect to a running 5.0 mysqld with a 5.1 mysqlcheck. But that won't tell you anything about upgrading because all that mysqlcheck does is send SQL statements to the server to be executed.
[17 May 2009 8:18] Simon Mudd
Hi Paul,

Even though only SQL statements are sent to the server the 5.1 mysqlcheck could be recognise that it is running against a 5.0 server and (differently to the current behaviour) check for upgrade issues for migrating from 5.0 to 5.1. I believe that currently it looks at migratation issues for 5.0.

Having that option would allow me to run this command on a 5.0 server and get a report of what steps need to be done. That of course assumes that mysqlcheck does more than at the moment and looks for problems with MyISAM tables.
[20 May 2009 16:31] Paul DuBois

Alas, the 5.0 server knows only about issue for older servers. It does not know what happens with newer servers.
[22 May 2009 20:12] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
[18 May 2010 21:28] James Day
Improving the upgrade process is covered in bug #47205. Please see and watch that bug if your interest is the upgrade process itself, rather than the documentation.

The use of ALTER TABLE after an upgrade from 5.0 to 5.1 is described at http://dev.mysql.com/doc/refman/5.1/en/rebuilding-tables.html . Please note that it's possible that it might not be sufficient in a future upgrade and we'll update the documentation as needed for future upgrades.