MySQL Upgrade and Compressed Tables

Recently one of our MySQL servers crashed.  Luckily there wasn't any damage but after a support call it was found it was due to a bug with the version we are running.  We decided to upgrade to the latest version of 5.5.  We have one physical server running 10 small to medium MySQL instances (no replications).  Whereas 9 of the upgrades went as planned one didn't (there is always one!).

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>

For example,
$ 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

Stating the obvious, I would recommend uncompressing all the compressed tables before the upgrade so the process is quicker

1 comment: