MySQL Bugs: #20129: ALTER TABLE ... REPAIR PARTITION ... complains that partition is corrupt

Bug #20129	ALTER TABLE ... REPAIR PARTITION ... complains that partition is corrupt
Submitted:	29 May 2006 18:54	Modified:	20 Jun 2010 22:54
Reporter:	Jeremy Cole (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.19	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any
Tags:	bfsm_2007_10_18, qc

Description:
I managed to corrupt one of the partitions in a table (by running out of disk space, oops) on my test server.  I freed up some space, restarted the server, and went to figure out how to fix things with partitioning.

I was unable to repair the partition using any of the available commands, so had to shut down and myisamchk it manually.

How to repeat:
Get a single partition of a table to be marked as "crashed".  I ran out of disk space and accomplished this nicely.

mysql> alter table ontime repair partition p_199609;
ERROR 145 (HY000): Table './flightstats/ontime#P#p_199609' is marked as crashed and should be repaired

Two bugs:

1. Why is it complaining instead of fixing it?  Kind of defeats the point!

2. Why it it showing me the underlying table name instead of "Partition 'p_1996_09' on table 'ontime' is marked as crashed and should be repaired"?  Not very user friendly.

Suggested fix:
Make ALTER TABLE ... REPAIR PARTITION smarter.

Just changing the Category to Partitioning.  Why can't you do this when you originally create the bug?

Thank you for a problem report. Please, try to repeat with a newer version, 5.1.11-beta (now officially released), and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This bug currently exists on 5.1.19-beta-linux-x86_64-glibc23.  I was able to successfully duplicate this issue.

However, the crash was not due to diskspace, but instead the mysqld segfaulting.

This can also occur if you are missing the MYI.  You can not use ALTER TABLE ... REPAIR PARTITION ... USE_FRM, even though the syntax is accepted.  It always gives an error regarding missing the file.

Test case:

CREATE TABLE `t1` (`id` int, KEY (`id`)) ENGINE=MyISAM PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (5000), PARTITION p1 VALUES LESS THAN MAXVALUE);
INSERT INTO t1 values (5), (10), (6000);

Delete the t1#P#p0.MYI from your data directory.

Try running:

ALTER TABLE t1 REPAIR PARTITION p0 USE_FRM;

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/41546

ChangeSet@1.2516, 2008-02-01 10:53:32+01:00, mattiasj@client-10-129-10-147.upp.off.mysql.com +14 -0
  Bug#20129: ALTER TABLE ... ANALYZE/CHECK/OPTIMIZE/REPAIR PARTITION
  
  Problem was that it did not work with corrupted/crashed tables.
  
  Solution is to disable these commands until WL#4176 is completed

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/41924

ChangeSet@1.2516, 2008-02-08 12:06:19+01:00, mattiasj@client-10-129-10-147.upp.off.mysql.com +14 -0
  Bug#20129: ALTER TABLE ... ANALYZE/CHECK/OPTIMIZE/REPAIR PARTITION
  
  Problem was that it did not work with corrupted/crashed tables.
  
  Solution is to disable these commands until WL#4176 is completed

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/42021

ChangeSet@1.2516, 2008-02-11 11:32:46+01:00, mattiasj@client-10-129-10-147.upp.off.mysql.com +14 -0
  Bug#20129: ALTER TABLE ... ANALYZE/CHECK/OPTIMIZE/REPAIR PARTITION
  
  Problem was that it did not work with corrupted/crashed tables.
  
  Solution is to disable these commands until WL#4176 is completed

pushed into mysql-5.1-engines and mysql-6.0-engines

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/42968

ChangeSet@1.2549, 2008-02-26 00:18:30+01:00, mattiasj@witty. +1 -0
  Post push patch, disabling one falcon test case
  (dependent of partitioning maintenance commands, which has been
  disabled in the patch for Bug#20129)
  
  6.0 only patch.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Documented bugfix/feature change in the 5.1.23-ndb-6.3.11, 5.1.24 and 6.0.5 changelogs as follows:

The following statements did not function correctly with corrupted or
crashed tables and have been removed:

ALTER TABLE ... ANALYZE PARTITION

ALTER TABLE ... CHECK PARTITION

ALTER TABLE ... OPTIMIZE PARTITION

ALTER TABLE ... REPAIR PARTITION

ALTER TABLE ... REBUILD PARTITION is unaffected by
this change and continues to be available. This statement and
ALTER TABLE ... REORGANIZE PARTITIONS may be used to
analyze and optimize partitioned tables, since these operations cause
the partition files to be rebuilt. In addition, it remains possible to
use mysqlcheck on partitioned tables and myisamchk on partitioned MyISAM
tables.

-----

Additional Notes:

1. I am updating the 5.1/6.0 Partitioning and SQL Syntax docs to reflect the fact that these partition maintenance statements are not currently supported and have in fact been removed in 5.1.24+/6.0.5+. We have never supported CHECK|ANALYZE|OPTIMIZE|REPAIR TABLE for partitioned tables and the docs already state this.

2. I've done some limited testing of mysqlcheck/myisamchk on partitioned MyISAM tables after messing with some of the partition .MYD and .MYI files using a hex editor, and they do appear to work (including mysqlcheck --repair --use-frm). The docs already state that they can be used, so they also remain unchanged in this regard.

3. As regards any possibly future reinstatement of the statements in question, we're not in the habit of making promises in the docs that we might later have to retract, so I've stated in the 5.1 Manual only that these statements are no longer available. In the 6.0 Manual noted the removal whilst adding that we intend to re-implement them in a future release without specifying when exactly this might occur.

Patch (not sent out to commits@lists.mysql.com since it was too big, due to all test case updates)

Attachment: wl4176.commit-mail.gz (application/x-gzip, text), 89.85 KiB.

Ok to push by me.

I have noticed that in the comment made by Jon Stephens on Mar 29 22:51 and in the change history for MySQL 5.1.24 (http://dev.mysql.com/doc/refman/5.1/en/news-5-1-24.html) it states...

"In addition, it remains possible to use mysqlcheck on partitioned tables and myisamchk on partitioned MyISAM tables."

However, on "21.3.3. Maintenance of Partitions" (http://dev.mysql.com/doc/refman/5.1/en/partitioning-maintenance.html) and "4.5.3. mysqlcheck — A Table Maintenance and Repair Program" (http://dev.mysql.com/doc/refman/5.1/en/partitioning-maintenance.html) and "4.6.3. myisamchk — MyISAM Table-Maintenance Utility" (http://dev.mysql.com/doc/refman/5.1/en/myisamchk.html) it states that...

"The use of mysqlcheck or myisamchk is also not supported with partitioned tables."

and 

"The use of mysqlcheck with partitioned tables is not supported."

and 

"The use of myisamchk with partitioned tables is not supported."

My question is which is correct? Are mysqlcheck and myisamchk with partitioned tables supported or not?

Just a quick follow up, it appears by Note 3 in Jon Stephens post on 29 Mar 22:51 that the mysqlcheck and myisamchk might not work with partitioned tables. If that is the case, the statement should probably be removed from (http://dev.mysql.com/doc/refman/5.1/en/news-5-1-24.html) just to eliminate confusion by conflicting documentation.

The indicated statement in the 5.1.24 changelog was an error and has been removed.

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/51382

2692 Mattias Jonsson	2008-08-12
      Bug#20129: ALTER TABLE ... REPAIR PARTITION ... complains that
      partition is corrupt
      
      Updated result files after merge

Pushed into mysql-6.0-bugteam and mysql-5.1-bugteam

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/51553

2696 Mattias Jonsson	2008-08-13
      Bug#20129: ALTER TABLE ... REPAIR PARTITION ... complains that
      partition is corrupt
      
      Post push fix
      
      an DBUG_ASSERT broke the embedded server, fixed by initializing
      it in the embedded version of Protocol_text::prepare_for_resend

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/51570

For doc:
This patch also allows ANALYZE/CHECK/OPTIMIZE/REPAIR TABLE on partitioned tables.
(is equivalent with 'ALTER TABLE t A/C/O/R PARTITION all')

So a update of:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-maintenance.html
is needed.

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/52082

2791 Mattias Jonsson	2008-08-20 [merge]
      merge (manual mysql-5.1-bugteam
      + bug#34604 + post push fix for bug#20129 -> mysql-6.0-bugteam)

Pushed in 5.1.28

Documented new fix in 5.1.27 changelog: http://lists.mysql.com/commits/53051

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/53909

2682 He Zhenxing	2008-09-12 [merge]
      Auto merge
      Update plugin.h.pp for WL#4398

Pushed into 6.0.6-alpha  (revid:mattiasj@mysql.com-20080812080230-ncl3cq6dc3gd60lf) (version source revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (pib:3)

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/54774

2667 He Zhenxing	2008-09-29 [merge]
      Auto Merge

It looks like this patch is supposed to allow OPTIMIZE TABLE on partitioned tables. However, I get the following when running on an InnoDB partitioned table in 5.1.30.

mysql > OPTIMIZE TABLE foo1;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.foo1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead | 
| test.foo1 | optimize | status   | OK                                                                | 
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (17.79 sec)

mysql > ALTER TABLE foo1 OPTIMIZE PARTITION ALL;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.foo1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead | 
| test.foo1 | optimize | status   | OK                                                                | 
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (17.65 sec)

Does the optimisation actually fail, or is this a case of a spurious error message?

I am not sure. The command does not come back right away (above example took 17 secs), and you will find temp tables in the database dir while it is running.

-rw-rw---- 1 root root     8586 Dec 10 10:28 foo1.frm
-rw-rw---- 1 root root       60 Dec 10 10:28 foo1.par
-rw-rw---- 1 root root 25165824 Dec 10 10:28 foo1#P#p0.ibd
-rw-rw---- 1 root root 25165824 Dec 10 10:28 foo1#P#p1.ibd
-rw-rw---- 1 root root 25165824 Dec 10 10:28 foo1#P#p2.ibd
-rw-rw---- 1 root root 25165824 Dec 10 10:28 foo1#P#p3.ibd
-rw-rw---- 1 root root 25165824 Dec 10 10:28 foo1#P#p4.ibd
-rw-rw---- 1 root root 25165824 Dec 10 10:28 foo1#P#p5.ibd
-rw-rw---- 1 root root 25165824 Dec 10 10:28 foo1#P#p6.ibd
-rw-rw---- 1 root root 25165824 Dec 10 10:28 foo1#P#p7.ibd
-rw-rw---- 1 root root 25165824 Dec 10 10:28 foo1#P#p8.ibd
-rw-rw---- 1 root root 25165824 Dec 10 10:28 foo1#P#p9.ibd
-rw-rw---- 1 root root     8586 Dec 10 11:02 #sql-1d04_1.frm
-rw-rw---- 1 root root       60 Dec 10 11:02 #sql-1d04_1.par
-rw-rw---- 1 root root 15728640 Dec 10 11:02 #sql-1d04_1#P#p0.ibd
-rw-rw---- 1 root root    98304 Dec 10 11:02 #sql-1d04_1#P#p1.ibd
-rw-rw---- 1 root root    98304 Dec 10 11:02 #sql-1d04_1#P#p2.ibd
-rw-rw---- 1 root root    98304 Dec 10 11:02 #sql-1d04_1#P#p3.ibd
-rw-rw---- 1 root root    98304 Dec 10 11:02 #sql-1d04_1#P#p4.ibd
-rw-rw---- 1 root root    98304 Dec 10 11:02 #sql-1d04_1#P#p5.ibd
-rw-rw---- 1 root root    98304 Dec 10 11:02 #sql-1d04_1#P#p6.ibd
-rw-rw---- 1 root root    98304 Dec 10 11:02 #sql-1d04_1#P#p7.ibd
-rw-rw---- 1 root root    98304 Dec 10 11:02 #sql-1d04_1#P#p8.ibd
-rw-rw---- 1 root root    98304 Dec 10 11:02 #sql-1d04_1#P#p9.ibd

What I did regarding to OPTIMIZE was simply to add a 'note' row, to explain that the engine itself (InnoDB) does not support the handler call ::optimize, and because of that the server simulates this by recreating the table and then running analyze on it (see http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html)

In ha_innodb.cc:
/**************...
This is mapped to "ALTER TABLE tablename ENGINE=InnoDB", which rebuilds
the table in MySQL. */

int
ha_innobase::optimize(
/*==================*/
        THD*            thd,            /* in: connection thread handle */
        HA_CHECK_OPT*   check_opt)      /* in: currently ignored */
{
        return(HA_ADMIN_TRY_ALTER);
}

So it does not say that it is not supported anymore, it only gives more info on what the OPTIMIZE command is doing.

InnoDB has always done a recreate + analyze instead of optimize, so the only thing I have done regarding this is to inform the user that it will recreate the table and then do an analyze on the table (i.e. no change of behavior, but only give more information to the user).

So yes, innodb does still support optimize, and it is done by doing recreate + analyze instead. However there is no native optimize function in InnoDB.

This is true regardless if the table is partitioned or not.

I've updated the documentation to reflect the fact that the two-line message is expected behaviour when using OPTIMIZE on InnoDB tables beginning with 5.1.27/6.0.6.

Updated 5.1.24/6.0.5 changelog entry to read:

Partitioning: Important Change: The following statements
did not function correctly with corrupted or crashed tables
and have been disabled:

* ALTER TABLE ... ANALYZE PARTITION

* ALTER TABLE ... CHECK PARTITION

* ALTER TABLE ... OPTIMIZE PARTITION

* ALTER TABLE ... REPAIR PARTITION

ALTER TABLE ... REBUILD PARTITION is unaffected by this
change and continues to be available. This statement and
ALTER TABLE ... REORGANIZE PARTITION may be used to analyze
and optimize partitioned tables, since these operations
cause the partition files to be rebuilt. (Bug#20129)