Upgrading MySQL to 5.5.32 and compressed tables

Recently I was tasked with upgrade some MySQL instances from 5.5.16 to 5.5.32.  The task seemed easy enough but when I started the process on one of the larger databases on the server (just under 1TB), it is made up of a lot smaller tables and most over 90% are 'myisam'.  I ran into issues when I ran mysql_upgrade.

error : Table upgrade required. Please do "REPAIR TABLE `<name>`" or dump/reload to fix it!

When I tried to repair the table:
POOL_DEV> repair table <name>;
| Table | Op | Msg_type | Msg_text |
| <name> | repair | Error | Table 'cmo_prices_060103' is read only |
| <name> | repair | status | Operation failed |
2 rows in set (0.00 sec)

This issues has two possible causes:
1.  Permissions at the o/s level
(Permissions should be /db/data owner and group whatever the mysql user is.  For my it is mysql:mysql.  The files within /db/data are need permission level 660 ).
2.  The table is compressed.

My problem was table compression.

So I uncompressed the table:
myisamchk --unpack /db/HIST/data/hist/corp_prices_060106
Ran the mysql_upgrade script
Recompressed the table:
myisampack /db/HIST/data/hist/cmo_prices_060103

On a side note - the compression rate is excellent.

No comments:

Post a Comment