MySQL Bugs: #65184: Impossible to optimize subpartitions

Bug #65184	Impossible to optimize subpartitions
Submitted:	2 May 2012 15:09	Modified:	18 Sep 2013 13:15
Reporter:	Михаил Веселов	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.5.9, 5.5.25	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
We have an actively used big table with 500M records in it.
For fast data access table has partitions by RANGE and subpartitions by HASH.

We need to optimize table after periodical bulk data deletions.
We can execute 'ALTER TABLE `bigtable` OPTIMIZE PARTITION `p2000`;' to optimize one partition. But it can take a long time and cause some blocking issues.

So we need to optimize table by subpartitions. 

After check commands like 'ALTER TABLE `bigtable` OPTIMIZE PARTITION `p2000sp1`;' doesn't work.

How to repeat:
CREATE TABLE IF NOT EXISTS `bigtable` (
`userid` int(11) unsigned NOT NULL,
`status` tinyint(1) unsigned NOT NULL DEFAULT '0',
`dt` int(11) DEFAULT NULL, -- represents timestamp
KEY `dt` (`dt`),
KEY `ud` (`userid`,`dt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (dt)
SUBPARTITION BY HASH (userid)
SUBPARTITIONS 96
(PARTITION p2000 VALUES LESS THAN (946684800) ENGINE = MyISAM,
PARTITION p2011q2 VALUES LESS THAN (1309478400) ENGINE = MyISAM,
PARTITION p2011q3 VALUES LESS THAN (1317427200) ENGINE = MyISAM,
PARTITION p2011q4 VALUES LESS THAN (1325376000) ENGINE = MyISAM,
PARTITION p2012q1 VALUES LESS THAN (1333238400) ENGINE = MyISAM,
PARTITION p2012q2 VALUES LESS THAN (1341100800) ENGINE = MyISAM,
PARTITION p2012q3 VALUES LESS THAN (1349049600) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = MyISAM); 

INSERT INTO `bigtable` (`userid`, `status`, `dt`) VALUES
(15, 4, 1305636033),
(15, 4, 1305636092),
(6938, 4, 1305636147),
(6938, 0, 1305636154),
(6938, 0, 1305637792),
(6938, 4, 1305637809),
(6938, 4, 1305637839),
(6938, 4, 1305637869),
(6938, 4, 1305637900),
(6938, 4, 1305637930);

Thank you for the report.

Verified as described.

Fixed in 5.7. Documented as follows in the 5.7.2 changelog:

        The following operations are now supported for individual
        subpartitions as well as partitions: ANALYZE, CHECK, OPTIMIZE,
        REBUILD, REPAIR, and TRUNCATE.

Closed.

Updated changelog entry to read as follows:

      The following operations are now supported for individual
      subpartitions as well as partitions: ANALYZE, CHECK, OPTIMIZE,
      REPAIR, and TRUNCATE.

      Note. This fix also allows the use of REPAIR with individual
      subpartitions, even though this is not actually supported by
      MySQL, and has no effect. This issue is fixed in MySQL 5.7.5 and
      later by disallowing REPAIR with subpartitions in ALTER TABLE
      statements.

      See also BUG#19075411 and BUG#73130.

See BUG#65184 for additional docs info.