How to Resolve ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

In practice, we usually provide a default partition for settling remainders row of tables when we apply range partitions.
mysql> alter table orders partition by range(year(puchase_date)) (
    ->  partition p0 values less than (2006),
    ->  partition p1 values less than (2011),
    ->  partition p_default values less than maxvalue
    -> );
Query OK, 439801 rows affected (2.34 sec)
Records: 439801  Duplicates: 0  Warnings: 0

The trick is to set the VALUES LESS THAN to MAXVALUE which is an upper bound that represents the value greater than the largest possible value in partition keys. We also call it the remainder partition.

In this case, we divide all the records in every 5 years and a remainder partition, therefore, the records that are after 2010 will go to partition p_default no matter how large the dates are.

Next, we want to add a partition by add partition clause:
mysql> alter table orders add partition (
    ->  partition p2 values less than (2016)
    -> );
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

The syntax is correct, but we still got an ERROR 1481. This is because MySQL thought we want to append a partition named p2 right after the p_default, which is not allowed.

The way to solve this is to re-partition on this table, that is, insert a line for the new partition before the line of p_default.
mysql> alter table orders partition by range(year(puchase_date)) (
    ->  partition p0 values less than (2006),
    ->  partition p1 values less than (2011),
    ->  partition p2 values less than (2016),
    ->  partition p_default values less than maxvalue
    -> );
Query OK, 439801 rows affected (2.40 sec)
Records: 439801  Duplicates: 0  Warnings: 0

That’s how we conquer the problem.

1 thought on “How to Resolve ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition”

Leave a Reply

Your email address will not be published. Required fields are marked *