EC
1507
MySQLERRORNotablePartitioningHIGH confidence

Subpartitioning only allowed with RANGE/LIST + HASH/KEY combination

Production Risk

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

What this means

ER_SUBPARTITION_ERROR (1507, SQLSTATE HY000) is raised when subpartitioning is specified with an invalid combination of partition types.

Why it happens
  1. 1Using subpartitioning with HASH+HASH or KEY+RANGE combinations
  2. 2Subpartitioning is only valid for RANGE/LIST as the primary and HASH/KEY as secondary
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (id INT, region INT)
PARTITION BY HASH(id)
SUBPARTITION BY HASH(region) SUBPARTITIONS 2 (
  PARTITION p0  -- HASH cannot be subpartitioned by HASH
);

expected output

ERROR 1507 (HY000): It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning

Fix

Use RANGE or LIST as the primary partition type

Use RANGE or LIST as the primary partition type
CREATE TABLE t (id INT, region INT)
PARTITION BY RANGE(id)
SUBPARTITION BY HASH(region) SUBPARTITIONS 2 (
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN MAXVALUE
);

Why this works

Valid subpartitioning combinations: RANGE+HASH, RANGE+KEY, LIST+HASH, LIST+KEY.

Sources
Official documentation ↗

MySQL 8.0 — 1507 ER_SUBPARTITION_ERROR

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

← All MySQL errors