EC
1498
MySQLERRORNotablePartitioningHIGH confidence

For LIST partitions each partition must be defined

Production Risk

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

What this means

ER_PARTITIONS_MUST_BE_DEFINED_ERROR (1498, SQLSTATE HY000) is raised when a LIST-partitioned table is created without explicitly defining all partitions with VALUES IN clauses.

Why it happens
  1. 1LIST partitioning without explicit partition definitions
  2. 2Omitting the VALUES IN clause for one or more LIST partitions
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (status INT)
PARTITION BY LIST(status)
PARTITIONS 3;  -- LIST partitions must be explicitly defined

expected output

ERROR 1498 (HY000): For LIST partitions each partition must be defined

Fix

Define all partitions with VALUES IN

Define all partitions with VALUES IN
CREATE TABLE t (status INT)
PARTITION BY LIST(status) (
  PARTITION p_active VALUES IN (1, 2),
  PARTITION p_inactive VALUES IN (0),
  PARTITION p_other VALUES IN (3, 4, 5)
);

Why this works

LIST partitioning requires explicit VALUES IN for each partition; unlike HASH/KEY, it cannot auto-generate partitions.

Sources
Official documentation ↗

MySQL 8.0 — 1498 ER_PARTITIONS_MUST_BE_DEFINED_ERROR

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

← All MySQL errors