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.


leithal said...

Hi Mark,

Have you tried OPTIMIZE TABLE instead?

John Dzilvelis said...

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

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

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.