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 20:54	Modified:	19 Jun 12:20
Reporter:	Jeremy Cole (Basic Quality Contributor)
Status:	In review
Category:	Server: Partition	Severity:	S2 (Serious)
Version:	5.1.19	OS:	Any
Assigned to:	Mattias Jonsson	Target Version:	5.1+
Tags:	bfsm_2007_10_18, qc
Triage:	D1 (Critical) / R4 (High) / E4 (High)

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.

triage: based on e/r values as agreed in triage meeting pushing to be fixed after GA.

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.