📋

Description

This error indicates that the `VALUES` clause used in a partition definition is incompatible with the specified partitioning type. It typically occurs when attempting to use `VALUES IN` or `VALUES LESS THAN` with partitioning types like `HASH` or `KEY`, which do not support explicit value ranges or lists for partition assignment. The error signifies a fundamental mismatch in how partitions are defined versus how data is expected to be distributed.

💬

Error Message

Only %s PARTITIONING can use VALUES %s in partition definition

🔍

Known Causes

4 known causes

⚠️

Using VALUES with HASH or KEY Partitioning

`HASH` and `KEY` partitioning distribute data based on an expression or column's hash value, not explicit value lists or ranges. Attempting to include a `VALUES` clause with these partitioning types is syntactically incorrect.

⚠️

Mismatched VALUES Clause for RANGE Partitioning

`RANGE` partitioning requires `VALUES LESS THAN` (or implicitly `VALUES BETWEEN`) to define partition boundaries. Using `VALUES IN`, which is intended for `LIST` partitioning, with `RANGE` will trigger this error.

⚠️

Mismatched VALUES Clause for LIST Partitioning

`LIST` partitioning requires `VALUES IN` with explicit, discrete values to assign rows to partitions. Using `VALUES LESS THAN`, which is designed for `RANGE` partitioning, with `LIST` will cause this error.

⚠️

Malformed Syntax in VALUES Clause

Even if the partitioning type is correct, any syntax error within the `VALUES` clause itself (e.g., missing parentheses, incorrect data types, or improper value formatting) can lead to this error.

🛠️

Solutions

4 solutions available

1. Correct Partitioning Type for VALUES Clause easy

Ensure you are using RANGE or LIST partitioning when using the VALUES clause.

1

Identify the type of partitioning you are attempting to use. The error message indicates you are using `VALUES` which is only permissible with `RANGE` or `LIST` partitioning.

2

Review your `CREATE TABLE` or `ALTER TABLE` statement. If you intended to use `RANGE` partitioning, ensure the syntax specifies `PARTITION BY RANGE (...)` and the `VALUES` clause is correctly formatted for ranges.

CREATE TABLE your_table (
    id INT NOT NULL,
    data VARCHAR(255)
)
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

3

If you intended to use `LIST` partitioning, ensure the syntax specifies `PARTITION BY LIST (...)` and the `VALUES` clause is correctly formatted for discrete values.

CREATE TABLE your_table (
    status_code INT NOT NULL,
    description VARCHAR(255)
)
PARTITION BY LIST (status_code) (
    PARTITION p_active VALUES IN (1, 2, 3),
    PARTITION p_inactive VALUES IN (4, 5),
    PARTITION p_pending VALUES IN (6)
);

4

If you are using `HASH` or `KEY` partitioning, the `VALUES` clause is not applicable. Remove it and define your partitions based on the hash or key function.

CREATE TABLE your_table (
    id INT NOT NULL,
    data VARCHAR(255)
)
PARTITION BY HASH(id)
PARTITIONS 4;

2. Correct Syntax for VALUES LESS THAN (RANGE Partitioning) easy

Ensure the `VALUES LESS THAN` clause for RANGE partitioning is correctly formatted.

1

Verify that you are using `RANGE` partitioning. The `VALUES` clause in this context should be `VALUES LESS THAN (...)`.

2

Check the syntax of your `CREATE TABLE` or `ALTER TABLE` statement. The values specified within the `LESS THAN` parenthesis should be constants or expressions that evaluate to a constant for each partition.

CREATE TABLE sales (
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

3

Pay close attention to the data type of the partitioning key and the values provided. They should be compatible. For example, if `sale_date` is a DATE, `2021` will be interpreted correctly as the beginning of the year 2021.

3. Correct Syntax for VALUES IN (LIST Partitioning) easy

Ensure the `VALUES IN` clause for LIST partitioning is correctly formatted.

1

Verify that you are using `LIST` partitioning. The `VALUES` clause in this context should be `VALUES IN (...)`.

2

Check the syntax of your `CREATE TABLE` or `ALTER TABLE` statement. The values specified within the `IN` parenthesis should be a comma-separated list of constants that match the data type of the partitioning key.

CREATE TABLE users (
    user_id INT NOT NULL,
    country_code VARCHAR(2) NOT NULL
)
PARTITION BY LIST (country_code) (
    PARTITION p_europe VALUES IN ('DE', 'FR', 'UK'),
    PARTITION p_asia VALUES IN ('CN', 'JP', 'IN'),
    PARTITION p_north_america VALUES IN ('US', 'CA', 'MX')
);

3

Ensure that each partition has a distinct set of values. Overlapping values between partitions are not allowed in `LIST` partitioning.

4. Review and Correct Partitioning Definition in Existing Tables medium

Analyze and modify the partition definition of an existing table if it's causing the error.

1

First, understand which partitioning type your table is using. Use the following command to get the table's `CREATE TABLE` statement.

SHOW CREATE TABLE your_table_name;

2

Examine the output of `SHOW CREATE TABLE`. Locate the `PARTITION BY` clause and the partition definitions. Identify if the `VALUES` clause is being used with an incorrect partitioning type.

3

If the partitioning type is incorrect for the `VALUES` clause, you will need to redefine the partitioning. This typically involves dropping and recreating the table with the correct partitioning scheme, or in some cases, using `ALTER TABLE ... PARTITION BY ...` if the operation is supported and safe for your MySQL version.

-- Example: If you found HASH partitioning with a VALUES clause
-- You would need to change it to RANGE or LIST

-- Option 1: Recreate the table (safer for complex changes)
CREATE TABLE your_table_new (
    -- your columns
)
PARTITION BY RANGE (column_name) (
    -- your RANGE partitions
);

INSERT INTO your_table_new SELECT * FROM your_table_old;
DROP TABLE your_table_old;
RENAME TABLE your_table_new TO your_table_old;

4

If the partitioning type is correct (RANGE or LIST) but the `VALUES` clause syntax is wrong, correct the syntax within the `ALTER TABLE` statement or the `CREATE TABLE` statement.

-- Example: Correcting a typo in VALUES LESS THAN
ALTER TABLE your_table_name
PARTITION BY RANGE (column_name) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200)
);

🔗

Related Errors

5 related errors

MySQL Error 1480: Invalid Partition VALUES Clause