EC
1486
MySQLERRORNotablePartitioningHIGH confidence

Only LIST/RANGE partitions can use VALUES IN/LESS THAN

Production Risk

Low — DDL error; the table will not be created.

What this means

ER_PARTITION_WRONG_VALUES_ERROR (1486, SQLSTATE HY000) is raised when VALUES IN or VALUES LESS THAN syntax is used with a partition type that does not support it.

Why it happens
  1. 1Using VALUES LESS THAN in a HASH or KEY partition definition
  2. 2Using VALUES IN in a RANGE partition definition
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (id INT)
PARTITION BY HASH(id)
PARTITIONS 4 (
  PARTITION p0 VALUES LESS THAN (100)  -- Not valid for HASH
);

expected output

ERROR 1486 (HY000): Only LIST and RANGE partitions can use VALUES IN and VALUES LESS THAN

Fix

Use the correct syntax for the partition type

Use the correct syntax for the partition type
-- For RANGE partitioning:
CREATE TABLE t (id INT)
PARTITION BY RANGE(id) (
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN MAXVALUE
);

-- For HASH partitioning (no VALUES clause):
CREATE TABLE t (id INT)
PARTITION BY HASH(id) PARTITIONS 4;

Why this works

Each partition type uses specific syntax: RANGE uses VALUES LESS THAN, LIST uses VALUES IN, HASH/KEY use PARTITIONS N.

Sources
Official documentation ↗

MySQL 8.0 — 1486 ER_PARTITION_WRONG_VALUES_ERROR

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors