Oddities with the MySQL “Table upgrade required” message

Here’s an obscure thing we spent some time on today that doesn’t affect our customers, but might be useful to someone else searching the Internet. The problem was a MySQL database table that, as far as we can tell, worked perfectly… except that it shows this message when we run CHECK TABLE:

Table upgrade required. Please do "REPAIR TABLE `users`" to fix it!

Hmm. Well, that error message seems odd, but what the heck. After making sure there’s a current backup, let’s give it a shot:

mysql> REPAIR TABLE users;

+---------------------+--------+----------+-----------------------------+
| Table               | Op     | Msg_type | Msg_text                    |
+---------------------+--------+----------+-----------------------------+
| database.users      | repair | status   | Table is already up to date |
+---------------------+--------+----------+-----------------------------+

And yet, CHECK TABLE still says there’s a problem:

mysql> CHECK TABLE users;

+---------------------+-------+----------+-------------------------------------+
| Table               | Op    | Msg_type | Msg_text                            |
+---------------------+-------+----------+-------------------------------------+
| database.users      | check | error    | Table upgrade required. Please do   |
|                     |       |          |  "REPAIR TABLE `users`" to fix it!  |
+---------------------+-------+----------+-------------------------------------+

Hmmm. We tried every reasonable combination of REPAIR TABLE and myisamchk options we could think of, but nothing helped. Then we tried unreasonable options, including the frightening REPAIR TABLE table USE_FRM option. In our experience USE_FRM usually ends up doing the same thing as DELETE FROM table, and this time was no exception. (And that’s why you make a backup first.)

Well, let’s take a look at the table status (with the uninteresting bits removed):

mysql> show table status;

+-------+--------+---------+-----+---------------------+
| Name  | Engine | Version | ... | Create_time         |
+-------+--------+---------+-----+---------------------+
| good  | MyISAM |      10 | ... | 2007-06-08 09:44:01 |
| users | MyISAM |       7 | ... | 2005-03-15 12:03:11 |
+-------+--------+---------+-----+---------------------+

Sure enough, the bad “users” table is a different version than a good table that doesn’t have this problem. And the bad table was created when we were still using MySQL 4.1.x. This table should have been changed when we upgraded to MySQL 5, and it should be possible to fix now. Why on earth won’t REPAIR TABLE fix it?

We puzzled over this for some time, and eventually just decided to reload it from a dump. But here’s the strange thing: it wouldn’t reload from the dump, either!

ERROR 1060 (42S21) at line 20: Duplicate column name 'user_id'

What the heck? So we looked closely at the dump file, and it somehow contains this line:

PRIMARY KEY (user_id,user_id)

MySQL thinks this table has a composite (two-column) primary key, but on the same column twice, which doesn’t make any sense. So we fix the dump to look like this:

PRIMARY KEY (user_id)

And everything works perfectly when the table is reloaded.

So if CHECK TABLE tells you a table upgrade is required, but REPAIR TABLE won’t fix it, check whether you can actually reload a dump of that table. You might find something unusual and easily fixable in the dump.