The process of upgrading was:
- · Stop all the databases on the server
- · Upgrade the MySQL binaries (rpm -Uvh)
- · Start the databases
- · Run 'mysql_upgrade' for each database
All was going well then suddenly I started to get:
error : Table upgrade required. Please do "REPAIR TABLE `<table name`" or dump/reload to fix it!
I had not encountered this before so off to do some research I go. After a few forum posts I find something about row_format. I run a quick query to and find that I have alot of compressed tables (no I did not compress them).
select substr(TABLE_NAME,1,5), table_schema, row_format, count(*) from information_schema.tables where row_format = 'Compressed' group by substr(TABLE_NAME,1,5), row_format;
So the simple solution is too uncompress them, run mysql_upgrade and compress them. I am careful and find out what is accessing them and am told the application is down for the upgrade so I crack on.
[mysql@prod1-lnx $ myisamchk --unpack <point to the table without an extention>
$ myisamchk --unpack /db/PTOT/data/pricing_comp/price_Jun_2010
- recovering (with sort) MyISAM-table '/db/PTOT/data/pricing_comp/price_Jun_2010'
Data records: 193693
- Fixing index 1
Once I had uncompressed all tables I then reran the mysql_upgrade command. I got an 'OK' for all tables. The upgrade was complete.
After the upgrade I had to compress all the tables I had uncompressed.
[mysql@prod1-lnx $ myisampack /db/PTOT/data/pricing_comp/price_Jun_2010
Compressing /db/PTOT/data/pricing_comp/price_Jun_2010.MYD: (193693 records)
- Calculating statistics
- Compressing file