Bug #42563 Message tells me to repair table though Storage Engine doesn't allow me to.
Submitted: 2 Feb 2009 22:54 Modified: 16 Nov 2009 7:25
Reporter: Markus Popp Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.x to 5.1.x upgrades OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any

[2 Feb 2009 22:54] Markus Popp
Description:
After updating from 5.1.30 to 5.1.31 I got several messages like this running "mysqlcheck --check-upgrade --all-databases":

error    : Table upgrade required. Please do "REPAIR TABLE `[table_name]`" to fix it!

The tables have been created in a much earlier version and the message could be related to issues http://bugs.mysql.com/bug.php?id=27877 and http://bugs.mysql.com/bug.php?id=40053

The problem is that I received this message for InnoDB tables which don't allow REPAIR TABLE ("The storage engine for the table doesn't support repair").

How to repeat:
* Create an InnoDB table in a version < 5.1.24
* update to 5.1.31
* run mysqlcheck --check-repair on this table

You should get a message telling you to repair the table, though if you do it won't be allowed.

Suggested fix:
The message should be appropriate according to whether or not a REPAIR is allowed.
[3 Feb 2009 10:08] Sveta Smirnova
Thank you for the report.

Though it can be feature request.

I can not repeat described behavior. Please provide output of SHOW CREATE TABLE for the problem table.
[3 Feb 2009 22:01] Markus Popp
Table definition is:

CREATE TABLE `cities` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(100) NOT NULL,
  `city` varchar(100) NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `city` (`city`,`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
[4 Feb 2009 9:12] Sveta Smirnova
Thank you for the feedback.

Verified as described.

Workaround: dump/reload problem table.

To repeat:

1. Start 5.1.23
2. Load world database from dev.mysql.com/docs
3. Create table cities as described
4. insert into cities (id, country, city) select ID, CountryCode, Name from City group by Name;
5. Stop 5.1.23
6. Start 5.1.31 is datadir from 5.1.23
7. $ ./bin/mysqlcheck --check-upgrade --all-databases --socket=/tmp/mysql_ssmirnova.sock -uroot
...
world.City                                         OK
world.Country                                      OK
world.CountryLanguage                              OK
world.cities
error    : Table upgrade required. Please do "REPAIR TABLE `cities`" to fix it!

$./bin/mysql  --socket=/tmp/mysql_ssmirnova.sock -uroot world 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.31 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> check table cities;
+--------------+-------+----------+----------------------------------------------------------------------+
| Table        | Op    | Msg_type | Msg_text                                                             |
+--------------+-------+----------+----------------------------------------------------------------------+
| world.cities | check | error    | Table upgrade required. Please do "REPAIR TABLE `cities`" to fix it! | 
+--------------+-------+----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> repair table cities;
+--------------+--------+----------+---------------------------------------------------------+
| Table        | Op     | Msg_type | Msg_text                                                |
+--------------+--------+----------+---------------------------------------------------------+
| world.cities | repair | note     | The storage engine for the table doesn't support repair | 
+--------------+--------+----------+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
[16 Feb 2009 19:16] Gabriel LANDON
I had the exact same behavior while upgrading from 5.0.51a to 5.1.31 under linux.
I've reloaded my data (from a previous dump done before the upgrade) and it was fixed.
[8 Apr 2009 7:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/71609

2855 Gleb Shchepa	2009-04-08
      Bug #42563: Message tells me to repair table though Storage
                  Engine doesn't allow me to.
      
      In case of incompatible changes between old and new table 
      versions, the mysqlcheck program prints error messages like 
      this:
        error: Table upgrade required. Please do
               "REPAIR TABLE `table_name`" to fix it!
      
      However, InnoDB doesn't support REPAIR TABLE query, so the 
      message is confusing.
      
      
      New error message has been added:
      
        error: Table upgrade required. 
               Please do mysqldump/reload to fix it!
      modified:
        sql/share/errmsg.txt
        sql/sql_table.cc
[8 Apr 2009 13:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/71653

2855 Gleb Shchepa	2009-04-08
      Bug #42563: Message tells me to repair table though Storage
                  Engine doesn't allow me to.
      
      In case of incompatible changes between old and new table
      versions, the mysqlcheck program prints error messages like
      this:
        error: Table upgrade required. Please do
               "REPAIR TABLE `table_name`" to fix it!
      
      However, InnoDB doesn't support REPAIR TABLE query, so the
      message is confusing.
      
      
      Error message text has been changed to:
      
        Table upgrade required. Please do "REPAIR TABLE `table_name`"
        or dump/reload to fix it!"
      modified:
        sql/share/errmsg.txt
[16 Apr 2009 11:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/72257
[5 May 2009 19:39] Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 9:18] Susanne Ebrecht
Bug #44639 was set as duplicate of this bug here.
[6 May 2009 14:06] Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:azundris@mysql.com-20090414214857-7zbsbyylr8axi7xg) (merge vers: 6.0.11-alpha) (pib:6)
[6 May 2009 15:56] Susanne Ebrecht
To recommend using mysqldump/reload makes less sense here.

Consider, especially on RPM Linux this means downgrading first.

Let me try to explain:

You remove MySQL 5.0 and install MySQL 5.1

Now you run mysql_upgrade and you get the error for an InnoDB table that you should use mysqldump/reload.

Our documentation recommends dumping from the old version.

This means, now you do remove 5.1 and install 5.0 again. Downgrade, dump.
Remove 5.0, install 5.1, reload.

This is not really effective.

It would be much better here when you would recommend to use "ALTER TABLE" instead of "dump/reload".
[6 May 2009 16:44] Sergei Golubchik
No, we don't recommend ALTER.
ALTER isn't a proper way to upgrade a table, it is not guaranteed to work in all cases.
[6 May 2009 18:04] Gabriel LANDON
Anyway, isn't it a good pratice to dump your databases before any upgrades?

So procedure might be (that's what I've done - works great!) :
-dump your databases (5.0)
-uninstall 5.0 RPMs
-install 5.1 RPMs
-upgrade to 5.1
-reload your databases from the dump (if tell to do so)
[6 May 2009 20:24] Simon Mudd
Try doing what you suggest with a database instance that has database(s) of over 100GB, or TB even. Dumping with MySQL is very slow, loading it is even slower. If this is a replicated instance then replication will have to be down during this whole procedure which has other implications.

So most of us want to avoid dump/loads unless absolutely necessary. In fact LVM snapshots may be used if something goes wrong. Doing a mysqldump is often just too slow and inconvenient.

So please consider that if this can be avoided there are many reasons why the DBA may WANT to avoid this.
[11 May 2009 11:09] Susanne Ebrecht
Consider, when I use dump/restore I don't need to use mysql_upgrade/mysqlcheck at all.

The recommended best way to upgrade from 5.0 to 5.1 still is using dump/restore. But usually, the users have lots of data storage and not enough space for dumping. And these is the exact usecase for which you need mysql_upgrade. To avoid dumping.
[12 May 2009 17:05] Paul DuBois
Noted in 5.1.35, 6.0.12 changelogs.

CHECK TABLE suggested use of REPAIR TABLE for corrupt tables for
storage engines not supported by REPAIR TABLE. Now CHECK TABLE 
suggests that the user dump and reload the table.
[15 Jun 2009 8:25] Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:05] Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:45] Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)
[15 Jun 2009 9:57] Simon Mudd
I've just looked at http://lists.mysql.com/commits/72257 and this commit comment does not take into account the possible situation where the upgrade is being done from an older version of mysql (for example from 5.0 to 5.1).

In this situation the error message change:
----
 Error message text has been changed to:
      
        Table upgrade required. Please do "REPAIR TABLE `table_name`"
        or dump/reload to fix it!"

----
is insufficient. mysqldump 5.1 (running against the upgraded 5.1 mysqld) will not be able to dump the 5.0 table. So if possible please add some sort of extra comment indicating that it will be necessary to stop the instance, downgrade to the older version of the binary and then use the older mysqldump to dump the table.

Also if mysqldump is able to determine that the table is not a MyISAM table then please DO NOT suggest using the REPAIR table command. That is pointless and misleading.

It might also be convenient to add the SCHEMA name to the output.
[13 Aug 2009 9:23] Eugene Varnav
I have upgraded MySQL from 5.1.33 to 5.1.36
mysql_upgrade says about some tables: 
Table upgrade required. Please do "REPAIR TABLE `table_name`" or dump/reload to fix it!"

But when I try to do REPAIR TABLE i get a message:
The storage engine for the table doesn't support
repair

This way version 5.1.36 still has this bug.
[13 Nov 2009 0:20] Roel Van de Paar
Re-opening this bug. Seeing this in 5.1.40 (when upgrading from or 5.0)
[13 Nov 2009 0:40] Roel Van de Paar
My mistake, ignore some of the above. (The updated message is showing in 5.1.40)

The implemented solution for this bug was:

- "Table upgrade required. Please do "REPAIR TABLE `%-.32s`" to fix it!"
+ "Table upgrade required. Please do "REPAIR TABLE `%-.32s`" or dump/reload to fix it!"

Should not rather mysql_upgrade be patched so that it does correctly fix (and/or automatically re-dump) the tables in question?
[13 Nov 2009 4:53] MySQL Verification Team
I already have a feature request for the server that will handle this: bug #47205
[13 Nov 2009 5:08] Gleb Shchepa
Thank you Shane!
Since this is an additional feature request,
closing the bug entry again and moving the discussion to bug #47205 page.
[13 Nov 2009 7:34] Simon Mudd
"Should not rather mysql_upgrade be patched so that it does correctly fix (and/or
automatically re-dump) the tables in question?"

Yes, of course. I've been advocating before that really the mysql_upgrade should do as much as it can to do the whole upgrade. If there's anything really weird or hard for it to do then it should report the problem and where to find instructions on how to resolve the problem.

Expecting the current manual process for upgrading tables, but also other parts of the upgrade process is not reasonable for any large database, especially if stored procedures, triggers and the like are involved.

So this ticket was originally about getting the message corrected, but really the whole problem should be resolved better.
[16 Nov 2009 23:19] Roel Van de Paar
Bug #28664 was marked as a duplicate of this one
[13 Jan 2010 17:21] Yingkuan Liu
Seeing the same problem on 5.1.42 upgrade.

For some of our big database which is over 300G in size, dump and reload is not good option. Please provide a Bug fix asap.
[13 Jan 2010 19:18] Gleb Shchepa
> [13 Jan 18:21] Yingkuan Liu
>
> Seeing the same problem on 5.1.42 upgrade.

Thank you for your report.

However, this bug entry is closed (because the bug report is about a confusing error message, not about a lack of the REPAIR TABLE feature for the InnoDB engine).

There is another bug (actually, feature request) entry for your issue: bug #47205. I'll cite your message there.