MySQL Bugs: #36008: Adding a partition after the maxvalue partition breaks table

Bug #36008	Adding a partition after the maxvalue partition breaks table
Submitted:	11 Apr 2008 22:57	Modified:	25 Apr 2008 14:31
Reporter:	Gordon Hopper	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.23-rc-community	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
For range partitioning with a MAXVALUE partition, adding additional partition(s) breaks the table.

See script below.  Note that "alter table ..." returns OK, but should return error.

After the bad partition is added, I am unable to query the table, and unable to drop the bad partition.

(I later discovered that "reorganize partition" is the command I wanted).

How to repeat:
create table part1 (
	n integer,
	primary key (n)
)
partition by range (n) (
	partition p0 values less than (10),
	partition p1 values less than maxvalue
);

alter table part1 add partition (partition p2 values less than (15));

select * from part1;
alter table part1 drop partition p2;

Suggested fix:
The "alter table" command should fail.  If I try to add a partition after an existing maxvalue partition, one of the following errors should occur:

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
ERROR 1064 (42000): MAXVALUE can only be used in last partition definition

Currently, the 1064 error occurs if I subsequently try to access the table, but not for the "alter table" command itself.

Verified on 5.1.23-rc:

mysql> CREATE TABLE sample1 (
    ->         rec_id      int auto_increment,
    ->         insert_date date,
    ->         primary key ( rec_id, insert_date )
    -> ) ENGINE=MySQL
    -> PARTITION BY RANGE ( YEAR(insert_date) ) (
    ->         PARTITION p1 VALUES LESS THAN (1000),
    ->         PARTITION p2 VALUES LESS THAN (2000),
    ->         PARTITION p99 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql>
mysql> ALTER TABLE sample1 ADD PARTITION ( PARTITION p3 VALUES LESS THAN (3000)
);
Query OK, 0 rows affected (0.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> SHOW CREATE TABLE sample1;
ERROR 1064 (42000): MAXVALUE can only be used in last partition definition near
') ENGINE = MyISAM)' at line 1

Instead of adding a new partition, we needed to issue REORGANIZE PARTITION.  But after the .frm is corrupted by the ADD PARTITION statement, this correct statement cannot restore the partitioning:

The server error log states:

080412 11:59:28 [ERROR] bin\mysqld.exe: Incorrect information in file: '.\test\sample1.frm'

The only workaround I identified was to manually alter the partitioning clause in the .frm file to list the MAXVALUE entry last and issue FLUSH TABLES.  Obviously, that's not a viable workaround except for recovery situations.

Execution of ADD PARTITION on table that has defined range-based partition VALUES LESS THAN MAXVALUE should generate an error, at minimum, to prevent corruption of .frm file.

Bug #36150 was marked as a duplicate of this one.

This bug does not exist in 5.1.24-rc. (can be verified in 5.1.23-rc, but not in 5.1.24-rc).

And yes, the proper way to split one partition is REORGANIZE PARTITION.