Bug #44945 New tool for pre-check upgrading
Submitted: 19 May 2009 6:13 Modified: 19 May 2009 6:13
Reporter: Susanne Ebrecht Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.1, 5.4, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[19 May 2009 6:13] Susanne Ebrecht
Description:
Imagine that you have a huge and complex instance.

You want to upgrade to a newer version.

Before you will start upgrading on your productive system, you want to test upgrading. You want to know what is necessary to do. Also your boss want to know this because he want to get a time and cost summary. 

Means, he want to know how long you expect that you will need for ugrading and more important: how long the production will fail/stall.

For getting all this informations and simulate the upgrade process you don't want to stop production.

When you will use mysql_upgrade/mysqlcheck/CHECK TABLES here this means that you have to stop production and start and run the instance with newer version server. Means you will have production lost, which is usually really expensive.

It would be nice, to have a tool for every upcomming version, that you can download separately and which will check your instance and let you know what you have to do for upgrading.

Means, which will tell you, which parts you need to dump, which tables will get corrupted, which routines, triggers won't work and so on.

The output from mysql_upgrade but by using the old server and not the new one.

Something like mysql_upgrade but with using old server instead of new server.

How to repeat:
It is a feature request.

Suggested fix:
A new tool
[19 May 2009 11:59] Simon Mudd
Oracle provide an upgrade procedure which if followed upgrades the database to the new version.
Sybase provide an upgrade procedure which if followed upgrades the database to the new version.

In both cases these upgrade procedures are done online with the active database, not by doing a logical backup and restore.

These procedures check if any changes are needed on the currently running installation. They also check after performing the upgrade if further steps are required to finish the upgrade.

Some issues may require manual solution but the tool provides the opportunity for this.

So why can't you do this with MySQL? The current documentation is missing and the current mysqlcheck/mysql_upgrade scripts only do the absolute minimum needed to do the upgrade and are incomplete.

The documentation organisation for an upgrade is not complete.

So a facility like this would be most useful and would make it easier for people to upgrade and avoid problems.
[8 Nov 2010 10:22] Shane Bester
such a tool could be a stored procedure that queries information_schema and decides which tables will be repaired by mysql_upgrade, which will need manual rebuilding [aka, innodb], and which should be ok in theory..

This SP should be packaged with each new version of mysql server.

This reference should be accurate too:
http://dev.mysql.com/doc/refman/5.5/en/checking-table-incompatibilities.html

But in fact, I'd like the stored procedure to be close to the exact replica of the server code: int handler::ha_check_for_upgrade(HA_CHECK_OPT *check_opt)