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) 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
Tags: bfsm_2007_10_18, qc
Triage: D1 (Critical) / R4 (High) / E4 (High)

[29 May 2006 18:54] Jeremy Cole
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.
[29 May 2006 18:55] Jeremy Cole
Just changing the Category to Partitioning.  Why can't you do this when you originally create the bug?
[11 Jun 2006 8:30] Valerii Kravchuk
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.
[11 Jul 2006 23:00] Bugs System
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".
[24 Sep 2007 23:45] Mark Garcia
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.
[15 Oct 2007 21:11] Harrison Fisk
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;
[1 Feb 2008 9:53] 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/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
[8 Feb 2008 11:06] 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/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
[11 Feb 2008 10:33] 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/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
[24 Feb 2008 20:18] Mattias Jonsson
pushed into mysql-5.1-engines and mysql-6.0-engines
[25 Feb 2008 23:18] 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/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.
[27 Mar 2008 11:18] Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 17:50] Bugs System
Pushed into 6.0.5-alpha
[29 Mar 2008 21:51] Jon Stephens
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.
[5 Jun 2008 10:25] Mattias Jonsson
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.

[20 Jun 2008 10:51] Ingo Strüwing
Ok to push by me.
[17 Jul 2008 15:46] Joe Grasse
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?
[17 Jul 2008 15:55] Joe Grasse
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.
[24 Jul 2008 5:09] Jon Stephens
The indicated statement in the 5.1.24 changelog was an error and has been removed.
[12 Aug 2008 8:03] 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/51382

2692 Mattias Jonsson	2008-08-12
      Bug#20129: ALTER TABLE ... REPAIR PARTITION ... complains that
      partition is corrupt
      
      Updated result files after merge
[13 Aug 2008 15:58] Mattias Jonsson
Pushed into mysql-6.0-bugteam and mysql-5.1-bugteam
[13 Aug 2008 18:50] 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/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
[13 Aug 2008 20:18] 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/51570
[18 Aug 2008 20:10] Mattias Jonsson
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.
[20 Aug 2008 18:06] 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/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)
[25 Aug 2008 10:53] Georgi Kodinov
Pushed in 5.1.28
[2 Sep 2008 12:17] Jon Stephens
Documented new fix in 5.1.27 changelog: http://lists.mysql.com/commits/53051
[12 Sep 2008 1:44] 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/53909

2682 He Zhenxing	2008-09-12 [merge]
      Auto merge
      Update plugin.h.pp for WL#4398
[13 Sep 2008 20:11] Bugs System
Pushed into 6.0.6-alpha  (revid:mattiasj@mysql.com-20080812080230-ncl3cq6dc3gd60lf) (version source revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (pib:3)
[30 Sep 2008 13:25] 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/54774

2667 He Zhenxing	2008-09-29 [merge]
      Auto Merge
[10 Dec 2008 16:32] Joe Grasse
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)
[10 Dec 2008 16:40] Jon Stephens
Does the optimisation actually fail, or is this a case of a spurious error message?
[10 Dec 2008 17:07] Joe Grasse
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
[10 Dec 2008 17:41] Mattias Jonsson
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.
[12 Dec 2008 10:14] Jon Stephens
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.
[12 Dec 2008 10:38] Jon Stephens
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)
        
        See also Bug#39434.

Updated 5.1.27/6.0.6 changelog entry to read:

        Partitioning: Important Change: The statements ANALYZE TABLE, 
        CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE are now 
        supported for partitioned tables.
            
        Also as a result of this fix, the following statements which
        were disabled in MySQL 5.1.24 have been re-enabled:
                
            * ALTER TABLE ... ANALYZE PARTITION
            
            * ALTER TABLE ... CHECK PARTITION
            
            * ALTER TABLE ... OPTIMIZE PARTITION
            
            * ALTER TABLE ... REPAIR PARTITION

        See also Bug#39434.

Also updated descriptions of affected statements in the 5.1/6.0 Manual as well as "Maintenance of Partitions" section of Partitioning chapter.

See http://lists.mysql.com/commits/61425 and http://lists.mysql.com/commits/61461 for the commits in which the bulk of the documentation changes were done.
[6 Mar 2009 8:10] Mattias Jonsson
summary of this bug:
The command for ALTER TABLE t ANALYZE/CHECK/OPTIMIZE/REPAIR PARTITION ...
was added in 5.1.6

These commands was not working with corrupt tables (and the output was not in line with A/C/O/R TABLE) and was therefore disabled in 5.1.24

The bug was fixed and the commands was enabled again (as well as supporting A/C/O/R TABLE for partitioned tables) in 5.1.28

Notes:

In the fix in 5.1.28, there was a regression that allowed the ALTER TABLE t A/C/O/R PARTITION p on non partitioned tables (acted just like A/C/O/R TABLE) This was fixed in bug#39434 in 5.1.31.

USE_FRM is not supported.

In the fix in 5.1.28, I added more information to the output for OPTIMIZE TABLE and ALTER TABLE t OPTIMIZE PARTITION p:
Table does not support optimize, doing recreate + analyze instead

This is only done for storage engines that does have native support for OPTIMIZE, such as InnoDB (InnoDB itself does not support optimize, but replies with 'try alter', which results in recreate + analyze)
which means that OPTIMIZE is simulated for InnoDB by 'ALTER TABLE t ENGINE = InnoDB' + 'ANALYZE TABLE t'/'ALTER TABLE t ANALYZE PARTITION p'

See my comment from [10 Dec 2008].

In bug#42822 it was discovered that it always does recreate the full table, even if not all partitions was listed, the workaround for this is to do:
ALTER TABLE t REBUILD PARTITION p;
ALTER TABLE t ANALYZE PARTITION p;
instead of
ALTER TABLE t OPTIMIZE PARTITION p;
for partitioned InnoDB tables.
[5 May 2010 15:17] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 2:13] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:56] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:25] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:53] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[30 May 2010 1:06] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:58] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:38] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:24] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)