MySQL Bugs: #32154: ALTER TABLE supports only a single partitioning clause

Bug #32154	ALTER TABLE supports only a single partitioning clause
Submitted:	6 Nov 2007 22:30	Modified:	11 Nov 2007 18:01
Reporter:	Benjamin Burleson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	ALTER TABLE, partitioning

Description:
MySQL returns syntax error when attempting to combine two partitioning specifications in an ALTER TABLE command.

How to repeat:
# Create the table
CREATE TABLE `part` (
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` int(11) NOT NULL DEFAULT '0',
  `c3` varbinary(64) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (c1)
 (PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM,
  PARTITION p2 VALUES LESS THAN (20) ENGINE = MyISAM,
  PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

# Attempt to drop and reorganize partitions
# in single ALTER TABLE command.
#  !This results in syntax error
ALTER TABLE `part`
   DROP PARTITION p1,
   REORGANIZE PARTITION p3 INTO
    (PARTITION p3 VALUES LESS THAN (30),
     PARTITION P4 VALUES LESS THAN MAXVALUE);

# Drop and reorganize partitions by separating
# specifications to independent ALTER TABLE commands.
ALTER TABLE `part`
   DROP PARTITION p1;
ALTER TABLE `part`
   REORGANIZE PARTITION p3 INTO
    (PARTITION p3 VALUES LESS THAN (30),
     PARTITION P4 VALUES LESS THAN MAXVALUE);

Thank you for the bug report. Verified as described.

This is the intended behaviour. I read the docs though and I didn't find this explicitly
documented so I set the status to Documenting and ask the Docs team to clearly
document that only one partition change command can be performed per ALTER TABLE.

When the the issue's with the documentation, the Category/Status should be changed to Documentation/Verified.

I've done so, and assigned this bug to myself; set lead to Stefan.

Also changed affected version to 5.1+.

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.

Updated ALTER TABLE and Partitioning Management sections of 5.1/6.0 Manual.