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:Microsoft Windows (windows xp)
Assigned to: CPU Architecture:Any
Triage: D4 (Minor)

[16 Apr 2008 19:02] Hao Tang
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.
[16 Apr 2008 19:18] Hao Tang
make the version more details
[17 Apr 2008 6:29] Valeriy Kravchuk
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.
[17 Apr 2008 17:57] Valeriy Kravchuk
Actually, this looks like a duplicate of Bug #36008.
[30 Jul 2008 9:24] Adrian Chapela Cordeiro
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 ?
[30 Jul 2008 9:28] Adrian Chapela Cordeiro
In Linux Also.