EC
1516
MySQLERRORNotablePartitioningHIGH confidence

COALESCE PARTITION can only be used on HASH/KEY partitions

Production Risk

Low — the ALTER TABLE fails; use the correct command for the partition type.

What this means

ER_COALESCE_ONLY_ON_HASH_PARTITION (1516, SQLSTATE HY000) is raised when COALESCE PARTITION is used on a RANGE or LIST partitioned table.

Why it happens
  1. 1ALTER TABLE COALESCE PARTITION used on a RANGE or LIST partitioned table
  2. 2COALESCE is only valid for HASH and KEY partitioned tables
How to reproduce
trigger — this will error
trigger — this will error
ALTER TABLE range_table COALESCE PARTITION 2;
-- range_table is RANGE partitioned, not HASH/KEY

expected output

ERROR 1516 (HY000): COALESCE PARTITION can only be used on HASH/KEY partitions

Fix

Use ALTER TABLE DROP PARTITION for RANGE/LIST tables

Use ALTER TABLE DROP PARTITION for RANGE/LIST tables
-- For RANGE/LIST: use DROP PARTITION
ALTER TABLE range_table DROP PARTITION p0;

-- COALESCE is only for HASH/KEY:
ALTER TABLE hash_table COALESCE PARTITION 2;

Why this works

COALESCE PARTITION reduces the number of HASH/KEY partitions; for RANGE/LIST, use DROP PARTITION.

Sources
Official documentation ↗

MySQL 8.0 — 1516 ER_COALESCE_ONLY_ON_HASH_PARTITION

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

← All MySQL errors