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