MySQL Bugs: #36150: Partitioned table in range is inaccessible

Bug #36150	Partitioned table in range is inaccessible
Submitted:	16 Apr 2008 19:02	Modified:	17 Apr 2008 17:57
Reporter:	Hao Tang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.23-rc-win32, 5.1.24-rc	OS:	Windows (windows xp)
Assigned to:		CPU Architecture:	Any

Description:
You can never add a new partition into a range partitioned table when you have maxvalue in your table definition. You will get an error like: 
ERROR 1064 (42000): MAXVALUE can only be used in last partition ...
In fact, this partitioned table is inaccessible afterwards.

How to repeat:
1. create a range partition table: by_year
create table by_year(d date)
partition by range(year(d))
(
partition p1 values less than (2001),
partition p2 values less than (2002),
partition p3 values less than (2003),
partition p4 values less than (maxvalue)
);
2. insert few rows into table by_year
3. do this: 
alter table by_year add partition(partition p5 values less than (2008));
4. you will get errors like followings:
ERROR 1064 (42000): MAXVALUE can only be used in last partition definition near
') ENGINE = InnoDB)' at line 1
5. table by_year will not accessible afterwards

Suggested fix:
When trying to add a partition if maxvalue is there, it's ok to raise up the errors, but, it should not make the table inaccessible at all. I could not find any ways to turn the table back to use.

make the version more details

Thank you for a problem report. On 5.1.24 table is still accessible:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3310
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: Y
ES)

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot test -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.24-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table by_year(d date)
    -> partition by range(year(d))
    -> (
    -> partition p1 values less than (2001),
    -> partition p2 values less than (2002),
    -> partition p3 values less than (2003),
    -> partition p4 values less than (maxvalue)
    -> );
Query OK, 0 rows affected (0.41 sec)

mysql> insert into by_year values ('2001-01-01'), ('2002-01-01'), ('2007-01-01')
;
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table by_year add partition(partition p5 values less than (2008));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
mysql> select * from by_year;
+------------+
| d          |
+------------+
| 2001-01-01 |
| 2002-01-01 |
| 2007-01-01 |
+------------+
3 rows in set (0.00 sec)

In any case, error message is misleading.

Actually, this looks like a duplicate of Bug #36008.

MySQL 5.1.25-rc-log has the same issue:

alter table by_date add partition (PARTITION p31072008 VALUES LESS THAN (TO_DAYS('2008-07-31')));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

Some workaround ?

In Linux Also.