📋

Description

This error occurs when defining or altering a table with `PARTITION BY RANGE` where the `LESS THAN` values for partitions are not strictly increasing. Each subsequent partition's `LESS THAN` value must be greater than the previous one to ensure valid, non-overlapping data ranges.

💬

Error Message

VALUES LESS THAN value must be strictly increasing for each partition

🔍

Known Causes

3 known causes

⚠️

Incorrect Partition Value Order

The `LESS THAN` values specified for partitions are not arranged in strictly ascending order, leading to an invalid or illogical range definition.

⚠️

Duplicate Partition Values

Two or more `PARTITION` clauses define the same `LESS THAN` value, violating the requirement for distinct and increasing ranges.

⚠️

Typographical Errors

Mistakes in entering numerical or date values for `LESS THAN` clauses can inadvertently result in a non-increasing sequence.

🛠️

Solutions

3 solutions available

1. Correctly Define Range Partition Boundaries easy

Ensure that the `VALUES LESS THAN` clause for each partition is strictly increasing.

1

Examine your `CREATE TABLE` or `ALTER TABLE` statement where you define the range partitions. Identify the `VALUES LESS THAN` clause for each partition.

2

Verify that the values in the `VALUES LESS THAN` clauses are in ascending order. For example, if you have partitions for `VALUES LESS THAN (10)`, `VALUES LESS THAN (20)`, and `VALUES LESS THAN (30)`, this is correct. However, if you have `VALUES LESS THAN (20)` followed by `VALUES LESS THAN (10)`, it's incorrect.

3

Modify the `VALUES LESS THAN` clauses to ensure they are strictly increasing. If the table already exists and is partitioned incorrectly, you will need to drop and recreate the partitions or the table.

ALTER TABLE your_table PARTITION BY RANGE (column_name) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN (30)
);

2. Recreate Table with Corrected Partitioning Scheme medium

If the table already exists with invalid partitions, recreate it with a correct definition.

1

Create a temporary table with the same schema as your original table, but with the corrected range partition definition.

CREATE TABLE your_table_temp LIKE your_table;
ALTER TABLE your_table_temp PARTITION BY RANGE (column_name) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN (30) -- Ensure these values are strictly increasing
);

2

Copy the data from the original table to the temporary table.

INSERT INTO your_table_temp SELECT * FROM your_table;

3

Drop the original table.

DROP TABLE your_table;

4

Rename the temporary table to the original table name.

RENAME TABLE your_table_temp TO your_table;

3. Use a Catch-All Partition for Unmatched Data easy

Include a final partition with `MAXVALUE` to capture any data that falls outside the defined ranges.

1

When defining your range partitions, add a final partition that uses `MAXVALUE` as its upper bound. This partition will catch all values greater than or equal to the last explicit upper bound.

ALTER TABLE your_table PARTITION BY RANGE (column_name) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN (30),
    PARTITION p_max VALUES LESS THAN MAXVALUE -- This is a common requirement for range partitioning
);

2

Ensure that the explicit `VALUES LESS THAN` clauses before `MAXVALUE` are still strictly increasing.

🔗

Related Errors

5 related errors

MySQL Error 1493: Invalid Range Partition Values