Bug #45121 mysql_upgrade not working by automatism
Submitted: 27 May 2009 9:03 Modified: 4 Oct 2010 13:21
Reporter: Susanne Ebrecht Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any

[27 May 2009 9:03] Susanne Ebrecht
Description:
Scenario: You want to upgrade from 5.0 to 5.1

You will use mysql_upgrade.

The only think what mysql_upgrade is doing at the moment is giving you a list of error messages that you need to do REPAIR TABLE or dump/restore.

Means you will get a list which you can take for do all the upgrade stuff manually.

Most of our users are using MyISAM and InnoDB.

Instead of just throwing error messages mysql_upgrade should repair MyISAM tables directly by automatism.

Also it should have an automatic method here for InnoDB tables. Somethink like ALTER TABLE ENGINE=InnoDB.

How to repeat:
Use mysql_upgrade for upgrading 5.0 to 5.1

Suggested fix:
Check storage engines. When recognising that repair table is needed for MyISAM tables just start REPAIR TABLE instead of throwing an error message.

Same with InnoDB. When recognising the error just start ALTER TABLE ENGINE=InnoDB or something else to re-create the table by automatism.

Same procedures with all the other storage engines that we support.
[27 May 2009 10:51] Simon Mudd
If possible also fix triggers, and stored functions and thus avoid the DBAs having to drop them prior to the upgrade. If this can not be done please indicate to the DBA the procedure to fix the problem. (URL to documentation).
[22 Sep 2009 15:51] Simon Mudd
I've just been running mysql_upgrade on 5.1.39 (enterprise version) and see that now at least with Innodb tables it's possible to see the db which is affected. This makes it much easier to separate the tables which are OK from those which mysql_upgrade can't handle and manually a ALTER TABLE XXXX ENGINE=InnoDB manually.

So thanks for helping a bit. There is another bug report which I can't find now which suggests automatically doing this. That would be the next step.
[29 Apr 2010 11:41] Sveta Smirnova
mysql_upgrade also doesn't upgrade data in help tables and prints no word about this should be done.
[29 Apr 2010 15:02] Sveta Smirnova
Note for case when mysql_upgrade fixes help table: please ensure SET SQL_LOG_BIN = 0 is called to ENSURE that these commands are NOT replicated downstream.

There also can be new option --upgrade_help_tables[=0|1]
[24 May 2010 10:18] Sveta Smirnova
Help tables part was moved to bug #53938
[4 Oct 2010 13:21] Susanne Ebrecht
This is a duplicate of bug #47205