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.
at

4 comments:

  1. Anonymous17 March 2009 at 13:38

    Hi Mark,

    Have you tried OPTIMIZE TABLE instead?

    ReplyDelete
  2. John Dzilvelis17 March 2009 at 20:14

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

    ReplyDelete
  3. Mark Robson18 March 2009 at 17:03

    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))

    ReplyDelete
  4. Sheeri K. Cabral20 March 2009 at 17:08

    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.

    ReplyDelete