MySQL Bugs: #68223: Manual for ALTER TABLE is wrong about mixing partitioning and other changes

Bug #68223	Manual for ALTER TABLE is wrong about mixing partitioning and other changes
Submitted:	30 Jan 2013 9:34	Modified:	6 Feb 2013 10:01
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1+	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	alter, partitioning

Description:
Manual for ALTER TABLE, http://dev.mysql.com/doc/refman/5.5/en/alter-table.html, says:

"Partitioning-related clauses for ALTER TABLE can be used with partitioned tables for repartitioning, for adding, dropping, merging, and splitting partitions, and for performing partitioning maintenance. For more information, see Section 13.1.7.1, “ALTER TABLE Partition Operations”. If an ALTER TABLE statement contains a partitioning operation, that must be the only operation specified in the statement."

The last statement above is NOT true. See "How to repeat". 

(If it really describes intended behavior, then software has a bug and formal syntax BNF at the beginning of the page should be changed, as it says:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

and this means that there should just be no comma before partition_options, it says nothing about mutual exclusiveness)

How to repeat:
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (100) ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql> alter table t1 engine=InnoDB, DROP PRIMARY KEY, ADD PRIMARY KEY(id), MODI
FY name char(100) partition by range(id) (partition p0 values less than(1) engin
e=InnoDB, partition p1 values less than(100) engine=InnoDB);
Query OK, 3 rows affected (0.59 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` char(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.27-log |
+------------+
1 row in set (0.00 sec)

Suggested fix:
Do not misinform users on what can be done by single ALTER. This is important, as users may waste time running multiple ALTERs instead (and not all of them will be online...).

Highlight correct usage with an example.

Thank you for the bug report. Verified as described.

You can combine 'PARTITION BY ...' and 'REMOVE PARTITION' with other alter_specifications (but the partitioning must be last).

But you cannot combine alter_specifications with ADD/DROP/COALESCE/REORGANIZE/ANALYZE/CHECK/REPAIR/... PARTITION, since those commands can operate on a single partition.

To avoid too many rebuilds during ALTER TABLE, one can add a new PARTITION BY clause to define how it should be partitioned (and then already have added/reorganized partitions).

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Fixed in mysqldoc rev 34199. Closed.