EC
1489
MariaDBERRORNotablePartitioningHIGH confidence

All partitions must be subpartitioned when subpartitioning is used

Production Risk

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

What this means

ER_PARTITION_SUBPART_MIX_ERROR (1489, SQLSTATE HY000) is raised when subpartitioning is used but not all partitions have subpartitions defined.

Why it happens
  1. 1Some partitions explicitly define subpartitions while others do not
  2. 2Mixing explicit and implicit subpartition definitions
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (id INT, created DATE)
PARTITION BY RANGE(YEAR(created))
SUBPARTITION BY HASH(id) (
  PARTITION p0 VALUES LESS THAN (2020) (
    SUBPARTITION sp0, SUBPARTITION sp1
  ),
  PARTITION p1 VALUES LESS THAN MAXVALUE
  -- p1 has no subpartitions defined while p0 does
);

expected output

ERROR 1489 (HY000): If subpartitioning is used, each partition must be subpartitioned

Fix

Define subpartitions for all partitions or none

Define subpartitions for all partitions or none
CREATE TABLE t (id INT, created DATE)
PARTITION BY RANGE(YEAR(created))
SUBPARTITION BY HASH(id) SUBPARTITIONS 2 (
  PARTITION p0 VALUES LESS THAN (2020),
  PARTITION p1 VALUES LESS THAN MAXVALUE
  -- MySQL auto-creates subpartitions for all partitions
);

Why this works

Either let MySQL automatically create uniform subpartitions or explicitly define them for every partition.

Sources
Official documentation ↗

MySQL 8.0 — 1489 ER_PARTITION_SUBPART_MIX_ERROR

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

← All MariaDB errors