MySQL Bugs: #56909: REORGANIZE PARTITION is allowed on HASH/KEY partitioned tables

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:		CPU Architecture:	Any

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.

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 >

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

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.