MySQL Bugs: #45121: mysql_upgrade not working by automatism

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

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.

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

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.

mysql_upgrade also doesn't upgrade data in help tables and prints no word about this should be done.

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]

Help tables part was moved to bug #53938

This is a duplicate of bug #47205