Bug #56909 REORGANIZE PARTITION is allowed on HASH/KEY partitioned tables
Submitted: 21 Sep 2010 22:16 Modified: 18 Dec 2011 22:33
Reporter: Mattias Jonsson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1+ OS:Any
Assigned to:
Triage: Triaged: D1 (Critical) / R2 (Low) / E2 (Low)

[21 Sep 2010 22:16] Mattias Jonsson
Description:
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (PARTITION pNewName)
and
ALTER TABLE t1 REORGANIZE PARTITION
is allowed on HASH/KEY partitions, which make no sense and can corrupt the table.

How to repeat:
Test script:
-- source include/have_partition.inc
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

let $MYSQLD_DATADIR= `SELECT @@datadir`;
CREATE TABLE t1 (a INT PRIMARY KEY)
ENGINE MYISAM
PARTITION BY HASH (a)
PARTITIONS 1;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
ALTER TABLE t1 REORGANIZE PARTITION;
--list_files $MYSQLD_DATADIR/test
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (PARTITION pHidden);
--list_files $MYSQLD_DATADIR/test
SELECT * FROM t1;
SHOW CREATE TABLE t1;
ALTER TABLE t1 ADD PARTITION PARTITIONS 1;

Fails with:
mysqltest: At line 18: query 'ALTER TABLE t1 ADD PARTITION PARTITIONS 1' failed: 6: Error on delete of './test/t1#P#p0.MYI' (Errcode: 2)

Warnings from just before the error:
Error 6 Error on delete of './test/t1#P#p0.MYI' (Errcode: 2) 
Error 1017 Can't find file: 't1' (errno: 2)

Suggested fix:
Disable REORGANIZE PARTITION for HASH/KEY partitioned tables, since it makes no sense and opens for bugs. There are still REBUILD PARTITION for HASH/KEY partitioned tables.
[22 Sep 2010 0:36] Miguel Solorzano
Thank you for the bug report.

mysql 5.1 >ALTER TABLE t1 ADD PARTITION PARTITIONS 1;
ERROR 6 (HY000): Error on delete of '.\test\t1#P#p0.MYI' (Errcode: 2)
mysql 5.1 >
[18 Dec 2011 22:33] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[18 Dec 2011 22:55] Jon Stephens
Documented as follows in the 5.6.5 changelog:

      An ALTER TABLE ... ADD PARTITION statement subsequent to ALTER TABLE ...
      REORGANIZE PARTITION failed on a table partitioned by HASH or KEY.

Closed.