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)	Email Updates:
Status:	Closed
Category:	Server: Partition	Severity:	S4 (Feature request)
Version:	5.1.9	OS:	Any
Assigned to:		Target Version:
Triage:	D5 (Feature request)

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.