Friday, February 17, 2006

New mysql_upgrade program

I just found two interesting entries in the Changelogs for MySQL 5.0.19 and 5.1.7:

Added the mysql_upgrade program that checks all tables for incompatibilities with the current version of MySQL Server and repairs them if necessary. This program should be run for each MySQL upgrade (rather than mysql_fix_privilege_tables).

Added the FOR UPGRADE option for the CHECK TABLE statement. This option checks whether tables are incompatible with the current version of MySQL Server. Also added the --check-upgrade to mysqlcheck that invokes CHECK TABLE with the FOR UPGRADE option.

This seems to be a very useful program, as the table versions change from time to time and it's not always easy for users to keep track of these table versions and to ensure that they are up-to-date (unless you always dump and restore all tables on each upgrade).

I don't know the details about this new program (actually, I don't know more than what the Changelog says), but I guess that with the new program and the new option for the CHECK TABLE command, you can automatically upgrade the table's versions and also ensure that the tables in the mysql schema are correct.

Running this program will probably also reduce the danger of being affected from some bugs, as there might be bugs that only occur when the table versions are outdated.

1 comment:

mfc said...


I have a 4.1 database that is
about 1.4 TB in size. In doing a
practice migration on a smaller
database, I notice that
mysql_upgrade wants to repair all
the tables. I have a lot of
Innodb tables (a couple with
over 400 million rows).

So my question is if I just run
mysql_upgrade on these huge Innodb
tables, what will it do to them?
I can't afford for a migration to
take more than about 12 hrs. Will
mysql_upgrate simply mark headers
in these tables as upgraded to 5.0
(I'm thinking this wouldn't take
long), or does it actually have to
go thru each table and do some
sort of data transformation on
every row which could take days?

I'm trying to get an idea about
what mysql_upgrade is going
to do and how long it will take.
Any idea on a really big database?