MySQL Bugs: #20130: ALTER TABLE ... ADD PARTITION needs a force option

Bug #20130	ALTER TABLE ... ADD PARTITION needs a force option
Submitted:	29 May 2006 19:06	Modified:	2 Nov 2007 16:06
Reporter:	Jeremy Cole (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	5.1.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
After running out of disk space loading data, I wanted to remove all the data from one partition, so that I could re-start the load at the beginning of a month of data.

I dropped the partition (in the middle of a table) and attempted to re-add it, but was not allowed.  There should be a "force" option for this.

I realize I should probably be using LIST partitioning for this, and then I would likely not have a problem.  Live and learn.

How to repeat:
CREATE TABLE t (
  i INT
)
PARTITION BY RANGE(i) (
  PARTITION p_0 VALUES LESS THAN (1),
  PARTITION p_1 VALUES LESS THAN (2),
  PARTITION p_2 VALUES LESS THAN (3)
);

mysql> ALTER TABLE t DROP PARTITION p_1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t ADD PARTITION (PARTITION p_1 VALUES LESS THAN (2));
ERROR 1472 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

Suggested fix:
Add a force option to allow the partitioning to be changed (data integrity be damned).  In this case I think it's best to let the DBA do what he wants, it could take a very very long time to re-load or re-partition data to do it the "safe" way.

Changing Category to Partitioning.

Thank you for a problem report. Sorry, but this is a documented behaviour, not a bug. Please, read the manual (http://dev.mysql.com/doc/refman/5.1/en/partitioning-management-range-list.html):

"Important: With tables that are partitioned by range, you can use ADD PARTITION to add new partitions to the high end of the partitions list only. Trying to add a new partition in this manner between or before existing partitions will result in an error as shown here:

mysql> ALTER TABLE members
     >     ADD PARTITION (
     >     PARTITION p3 VALUES LESS THAN (1960));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition"

Although, there should be some way to get rid of partition's data without problems like this. So, I'll mark this report as a verified feature request.

Looks like there is a way to do what you want already:

ALTER TABLE t
  REORGANIZE PARTITION p_2 INTO
  (PARTITION p_1 VALUES LESS THEN (2),
   PARTITION p_2 VALUES LESS THEN (3));

Closing this since it is possible to do ALTER TABLE t REORGANIZE PARTITION instead.