Tuesday, 17 March 2009

REPAIR TABLE does not always upgrade it

MySQL 5.0 uses a new MyISAM table format than 4.1. So when upgrading, the tables need to be upgraded.

This can be done using REPAIR TABLE. Or can it?

  • On 5.0.67 (I've been testing with), REPAIR TABLE does indeed upgrade it
  • On 5.0.22 (Earlier version, used on a backup slave), REPAIR TABLE does NOT upgrade it
Oh dear. That breaks my plan for how to upgrade the slave. Not a disaster, but annoying nonetheless

NB: this is a non-production system, and this is the kind of thing that we discover by testing it.
Posted by at
Labels:

4 comments:

Anonymous said...

Hi Mark,

Have you tried OPTIMIZE TABLE instead?

17 March 2009 at 13:38
John Dzilvelis said...

Have you tried CHECK TABLE....FOR UPGRADE? This should work for the versions you specified.

17 March 2009 at 20:14
Mark Robson said...

I tried CHECK TABLE FOR UPGRADE, and it reported "Ok". It didn't seem to think an upgrade was necessary (although the Version column shows a different value (9) than newly created tables (10))

18 March 2009 at 17:03
Sheeri K. Cabral said...

Mark -- actually the first step when upgrading is to read all the release notes for each intermediate version, which would have pointed that out to you.

It takes a long time for me to read all those when I upgrade, but it's always well worth it, especially because of this.

Testing is good, of course, and necessary, but you could have spared a lot of pain by reading the very very very good release notes.

20 March 2009 at 17:08

Post a Comment

Subscribe to: Post Comments (Atom)