MySQL Bugs: #47205: map 'repair table' to recreate +analyze for engines not supporting native repair

Bug #47205	map 'repair table' to recreate +analyze for engines not supporting native repair
Submitted:	9 Sep 2009 7:08	Modified:	20 Apr 2011 17:01
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.37	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	repair table

Description:
Innodb tables can be optimized using 'optimize table';

mysql> optimize table t\G
*************************** 1. row ***************************
   Table: test.t
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: test.t
      Op: optimize
Msg_type: status
Msg_text: OK

This is a feature request to allow "repair table t" to work as well.  It would
simply recreate the table, similar to a null 'alter table t engine=innodb'.

This recreating of tables behind the scenes needs to be done for any
engine needing a repair, but of course most important is that innodb tables can be repaired.

How to repeat:
try to repair a table claiming to require a repair after an upgrade. Or just:

create table t(a char(20) charset utf8)engine=innodb;
repair table t;

Suggested fix:
Make the server do alter table engine=<engine> then analyze table.
Will make it easier to have a client such as mysql_upgrade not have to care about
each engine's problems and limitations..

We really need this in order to have mysql_upgrade work smoothly on innodb tables.
If the repair table for innodb needs to be properly implemented to fix *known incompatibilities*, then it should be considered (bug #42563 is not good enough)...

Understood, introducing incompatibilities is needed due to a bugfix usually, but both and old and new formats should be readable by the newer version of the server so that it can correct things.  I'd be happy to mark tables readonly until the user can dump/reload, but they have to be readable in order to do this.

From the bug #42563 page:

>  [13 Jan 18: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.

If a myslqdump and reload isn't acceptable you can use a master-slave setup and upgrade the slave before the master then fail over to the slave, upgrade the master and use replication to get the master caught up to the slave.

The replication approach is the one normally used by those with high uptime requirements. The slave can be on the same hardware as the master if required.

I don't understand why this is not a feature request.
Can't mysqlcheck look at the table storage engine and produce a correct help string depending on the storage engine?

R5/E5, have you seen the mess of mysql_admin_table()?

Well, Shane did say "This is a feature request to allow "repair table t" to work as well" so there's agreement on that. :)

There are already different error messages for different storage engines via bug #42563 but what's missing is the upgrade process simply doing the work for InnoDB as it does for other storage engines: not a warning, but an actual REPAIR TABLE or other fix that is automatic.

It's natural that problems in the upgrade process will be more prominent at the moment. We can't even upgrade our new default InnoDB storage engine automatically when that's required because of the collation changes between 5.0 and 5.1.

The Support team knows that in practice so far OPTIMIZE TABLE or null ALTER TABLE has worked for the problems users typically see during upgrades but what's really needed is InnoDB internally supporting doing the rest of REPAIR TABLE work, or at least doing a table rebuild with ALTER TABLE and noting any problems it didn't handle.

So far as I know, for the 5.5 timespan mapping REPAIR TABLE to a null ALTER TABLE or OPTIMIZE TABLE for InnoDB will be sufficient for upgrade purposes. If that's easy and no developer knows of any unfixable trouble that will result, that's the approach I suggest we take for the moment.

It won't repair all of the possible issues with InnoDB tables, like checksum failures, but there's no requirement that it handle everything when we're considering upgrade issues. That sort of work can be deferred.

But it seems almost as useful to have the upgrade checking process just issue a null ALTER TABLE or OPTIMIZE TABLE command for an InnoDB table, since it's the upgrade checker that's supposed to be version-aware, not the main server. Or even write a script that the user can execute at their discretion.

However it's done, from the Support side of things, we simply don't want upgrade to not handle the upgrade for InnoDB in some way. The change in this bug report is only one of the possible ways and probably not the best one. Any reasonable solution that achieves this will be enough to make upgrading easy enough.

Longer term it'd be nice for REPAIR TABLE for InnoDB to actually do a repair as far as it can (skipping rows it can't read, say) but that's not at all easy and not necessary now.

> I don't understand why this is not a feature request.
> Can't mysqlcheck look at the table storage engine and produce a correct help string depending on the storage engine?

No. Reason is in bug #42563:

---- 
[6 May 2009 18: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.
----

This negates James' remark (except the word "unfixable"): "If [...] no developer knows of any unfixable trouble that will result": 

Though ALTER TABLE **may** work, Sergei already pointed out that it is not guaranteed to work in all cases.

Because of this, we should not simply 'give a message to run ALTER TABLE', 'change the upgrade tool to execute ALTER TABLE', or similar.

o Bug #42563 was never "fully" fixed: the issue remains: mysql_upgrade does not work for upgrades from 5.0 to 5.1
o Many are affected by this
o Fixing what Shane proposes to fix (note "We really need this in order to have mysql_upgrade work smoothly on innodb tables.") would resolve this whole situation once and for all

Please let's fix this properly this time. 

As an alternative, we could find out exactly what these cases are that Sergei is mentioning and fix them.

In regards ALTER TABLE there are two considerations:

1. It possibly has current issues, as per Sergei's comment.

2. It could be remapped to recreate + analyze, as per this bug from Shane.

Both are also possible solutions to the mysql_upgrade problem, and there is a third one not related to ALTER TABLE:

3. Fix mysql_upgrade to handle tables correctly.

And discussing these points further (using the same number points);

1. In terms of using ALTER TABLE (as it is today) in mysql_upgrade, a concern is that there are cases for which Sergei thinks ALTER TABLE (as a way to upgrade a table) might fail. As per the above, we could find out exactly what these cases are and fix them, then change mysql_upgrade to use ALTER TABLE automatically.

2. This would be an ideal fix. Then change mysql_upgrade to use ALTER TABLE automatically.

3. To me it doesn't matter whether the final fix is done in the upgrade tool or in REPAIR TABLE, as long as mysql_upgrade from 5.0 to 5.1 works without issues (except maybe some collation issues as per bug #37046).

In regards ALTER TABLE there are two considerations:

1. It possibly has current issues, as per Sergei's comment.

2. It could be remapped to recreate + analyze, as per this bug from Shane.

Both are also possible solutions to the mysql_upgrade problem, and there is a third one not related to ALTER TABLE:

3. Fix mysql_upgrade to handle tables correctly.

And dicussing these points further (using the same number points);

2. This would be an ideal fix. Then change mysql_upgrade to use ALTER TABLE automatically.

In any case, mysql_upgrade should work properly, without any "do/try this" messages.

Either Sergey's comment or the bug synopsis are incorrect.
For InnoDB, all admin operations (repair, optimize, analyze), has always been
done by means of mysql_recreate_table, which internally calls ALTER TABLE
with no arguments.
Thus there is no other way to admin an InnoDB table except zero-alter it.
At the same time, for some unknown reason, REPAIR does not map to recreate:
mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t1 (a int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

I believe the decision to give an error message, rather than try an empty ALTER, was made because REPAIR, generally, may mean a lot of things, i.e.
restore some partially corrupted data. Something, that a crude
copy-to-tmp-table-and-swap approach, performed by ALTER, can't possibly
do.

However, for the problem in question (broken mysqlupgrade), it is quite
likely that a zero ALTER can be a satisfactory solution. This needs to be tested, but since the table is not corrupt, I expect the server
to be able to successfully read all data from the old format and copy
to the new format.
However, that would not be a true full repair, and won't work for real-world
broken and corrupted tables.

So, what does support actually want here:
- fixed mysql upgrade procedure? This is likely to be possible to do by fixing mysqlupgrade tool to automatically call ALTER TABLE <name>; for InnoDB tables.
- a mapping of REPAIR to ALTER, implemented on the server side? The
reason this mapping is not made I gave above.
- a true repair for InnoDB? But in that case it's not a server bug, but an InnoDB bug.

All in all, seems like a yet another example when solution-oriented reporting
stalls the progress on the real customer issue.

Looking forward to your feedback,
-- kostja

There is not a single doubt in my mind that REPAIR for InnoDB should NOT be translated into ALTER without options. Simply, because if some hard error is reported, this ALTER would not work.

Regarding the incompatibilities that cause to extend CHECK FOR UPGRADE in 5.5, it would be nice to have a list of those incompatibilities.

James,

Can you write that list ???

Here's an alternative to fix collation change:

delimiter //
CREATE PROCEDURE rebuild_wrong_indexes()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE db VARCHAR(64);
  DECLARE tbl VARCHAR(64);
  DECLARE eng VARCHAR(64);
  DECLARE cur CURSOR FOR SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.COLUMNS JOIN INFORMATION_SCHEMA.TABLES USING(TABLE_SCHEMA, TABLE_NAME) WHERE COLUMN_KEY != '' AND CHARACTER_SET_NAME IN ('eucjpms', 'euc_kr', 'gb2312', 'latin7', 'macce', 'ujis', 'ascii') AND TABLE_TYPE = 'BASE TABLE';
  DECLARE EXIT HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur;
  REPEAT
    FETCH cur INTO db, tbl, eng;
    SET @st_str := CONCAT('ALTER TABLE ', db, '.', tbl, ' ENGINE ', eng);
    PREPARE stmt FROM @st_str;
    EXECUTE stmt;
  UNTIL done END REPEAT;
END;//
delimiter ;

My alternative suggestion is to remove all logics related to collation fix from mysql_upgrade command, and run the stored procedure like above from mysql_upgrade instead :)

Kostja, when ALTER TABLE is executed, can't we do something like:

IF CHECK TABLE(table) = CORRUPT 
  Handle per normal InnoDB corruption routines
ELSE
  null 'ALTER TABLE table ENGINE=innodb'

Roel, normal process when InnoDB detects corruption is to shut down the server immediately and write checksum failure messages into the error log file. This is something that is best done before an upgrade attempt, not during it, because you really don't want to be doing data recovery in the middle of an upgrade when some tables may be converted and other not.

James, thank you, but this is outside the scope of what my question intended.

This:

IF CHECK TABLE(table) = CORRUPT 
  Handle per normal InnoDB corruption routines

Is *already* the case now.

This:

ELSE
  null 'ALTER TABLE table ENGINE=innodb'

is what I am proposing for when ALTER TABLE is executed.

The result will be that we can map REPAIR TABLE to ALTER TABLE (and thereby also indirectly fix mysql_upgrade) without the valid concern that Sinisa raised.

"Is *already* the case now.": not literally by a CHECK TABLE, but indirect "when InnoDB detects corruption" - as you say (thank you!)

Maybe in 5.5 the server can support an actual UPGRADE TABLE command.  A lot of the problems surrounding this upgrade issue come from overloading the use of REPAIR, which should fix a table that has been broken.  A lot of the comments on this bug report are pointing out semantic differences.  Separate the functionality of repairing and upgrading, and I believe many of the problems go away.  And the principle of least surprise is not violated, too.

I'm surprised that nobody has clarified the rather vague comments about cases where ALTER won't work.  What are those cases?  Either list them out, or stop scaring people with shadows :-)  I don't believe such cases exist for purposes of this issue report (upgrading, NOT repairing, tables).

Alexey, as you can see from the discussion that follows the original complaint we agreed to fix mysql_upgrade command to properly ugprade these problematic InnoDB tables. 
This is an SR55 bug and mysql_upgrade AFAIC is in your domain.
Please fix.
Setting R/E appropriately for a fix in mysql_ugprade.

I agree with Shane on how to fix this. Empty ALTER TABLE <innodb_table> ENGINE=InnodB, followed by ANALYZE .... is a good enough solution.

A small addition to my comment ....

There should be a small difference in how should ALTER treat the errors. If run as REPAIR, it should return diagnostic info and abort, for the cases that I mentioned in my comments on June 10th ...

In our environment, we need to upgrade from 32-bit MySQL 5.0.90 to 64-bit MySQL 5.5.8. We use character set utf8 and collation utf8_general_ci across the board. Since some of our InnoDB tables contain tens of millions of rows, we wish there would exist a direct, in-place upgrade path, which is faster than table rebuild via above-suggested "ALTER TABLE <innodb_table> ENGINE=InnodB".

It looks like it's possible to perform an "instantaneous" upgrade of affected InnoDB tables of any size by issuing a "fast" ALTER TABLE operation instead; I guess only their .frm metadata is being updated. For example, no-op modification of column to its existing definition does the trick:

Your MySQL connection id is 1
Server version: 5.5.8 MySQL Community Server (GPL)

...

mysql> CHECK TABLE t_timeline FOR UPGRADE;
+----------------------------------+-------+----------+-----------------------------------------------------------------------------------------+
| Table                            | Op    | Msg_type | Msg_text                                                                                |
+----------------------------------+-------+----------+-----------------------------------------------------------------------------------------+
| esadb_lds_appliance_2.t_timeline | check | error    | Table upgrade required. Please do "REPAIR TABLE `t_timeline`" or dump/reload to fix it! |
+----------------------------------+-------+----------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DESCRIBE t_timeline;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| ID    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| NAME  | varchar(255) | NO   | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE t_timeline MODIFY COLUMN ID bigint not null auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CHECK TABLE t_timeline FOR UPGRADE;
+----------------------------------+-------+----------+----------+
| Table                            | Op    | Msg_type | Msg_text |
+----------------------------------+-------+----------+----------+
| esadb_lds_appliance_2.t_timeline | check | status   | OK       |
+----------------------------------+-------+----------+----------+
1 row in set (0.00 sec)

To reiterate, in our production environment we have been using MySQL 5.0.90, having populated a lot of data into InnoDB databases configured with utf8_general_ci collation. We are planning an upgrade to MySQL 5.5.8, and were hoping for an upgrade path for existing data, which is as quick as possible. If I understand bug #27877 and documentation chapter "2.11.3. Checking Whether Tables or Indexes Must Be Rebuilt" correctly, then there should be no _real_ need to rebuild indexes on those tables, for which we are 100% certain that none of their columns contain 'ß' LATIN SMALL LETTER SHARP S (German). It appears that CHECK TABLE ... FOR UPGRADE logic is being overly aggressive in this scenario, and marks any table [created before 50124, with collation utf8_general_ci or ucs2_general_ci] as requiring upgrade (relevant delta to CHECK TABLE ... FOR UPGRADE behavior has been checked in as http://lists.mysql.com/commits/56470 under bug #40053). Could someone please confirm/deny my assumptions, and weigh on on whether the "fast" ALTER TABLE approach is safe in the described scenario?

Vlad, you're right. There are some other characters that you need to check, see the documentation for the list. If you get it wrong the server will either crash or give wrong results when it finds values in impossible positions in an index.

Do note that the only way that we guarantee will work is the 5.0 to 5.1 and then on to 5.5 way. That's because there are system tables that need to be upgraded by mysql_upgrade in the 5.1 upgrade step.

While it's not guaranteed, you can do the upgrade to 5.1, run mysql_upgrade to get the system table updates, skip the InnoDB repairs, upgrade to 5.5 and run its mysql_upgrade for system tables updating and then use the method you've described for the InnoDB tables. You can't skip running both 5.1 and 5.5 mysql_upgrade versions without some pain, best to just do both rather than try to clean up later.

Again, this isn't officially supported. It'll just work so long as you are certain about the data values.

James Day, MySQL Principal Support Engineer, Oracle

Noted in 5.5.11, 5.6.3 changelogs.

When used to upgrade tables, mysqlcheck (and mysql_upgrade, which
invokes mysqlcheck) did not upgrade some tables for which table
repair was found to be necessary. In particular, it failed to upgrade
InnoDB tables that needed repair, leaving them in a nonupgraded
state. This occurred because:

* mysqlcheck --check-upgrade ---auto-repair checks for tables that are
  incompatible with the current version of MySQL. It does this using the
  CHECK TABLE ... FOR UPGRADE statement and examining the result.

* For any table found to be incompatible, mysqlcheck issues a REPAIR
  TABLE statement. But this fails for storage engines such as InnoDB
  that do not support the repair operation. Consequently, the table
  remains unchanged.

To fix the problem, the following changes were made to CHECK TABLE
... FOR UPGRADE and mysqlcheck. Because mysql_upgrade invokes mysqlcheck,
these changes also fix the problem for mysql_upgrade.

* CHECK TABLE ... FOR UPGRADE returns a different error if a table needs
repair but its storage engine does not support REPAIR TABLE:
    
  Previous:
    Error: ER_TABLE_NEEDS_UPGRADE
    Table upgrade required. Please do "REPAIR TABLE `tbl_name`" or 
    dump/reload to fix it!

  Now:
    Error: ER_TABLE_NEEDS_REBUILD
    Table rebuild required. Please do "ALTER TABLE `tbl_name` FORCE" or
    dump/reload to fix it!

* mysqlcheck recognizes the new error and issues an ALTER TABLE ...
  FORCE statement.  The FORCE option for ALTER TABLE was previously
  unuused; now it is implemented and acts as a "null" alter operation
  that rebuilds the table.

CHANGESET - http://lists.mysql.com/commits/132572