EC
1497
MySQLERRORNotablePartitioningHIGH confidence

Partitioning function is not appropriate for this partitioning type

Production Risk

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

What this means

ER_PARTITION_FUNC_NOT_ALLOWED_ERROR (1497, SQLSTATE HY000) is raised when the expression used in the partitioning function is not valid for the chosen partition type.

Why it happens
  1. 1Using a non-integer expression in RANGE or HASH partitioning without RANGE COLUMNS
  2. 2Using a string column directly in RANGE partitioning without RANGE COLUMNS
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (name VARCHAR(100))
PARTITION BY RANGE(name) (  -- name is not integer
  PARTITION p0 VALUES LESS THAN ('M'),
  PARTITION p1 VALUES LESS THAN MAXVALUE
);

expected output

ERROR 1497 (HY000): The chosen partitioning function is not appropriate for this partitioning type

Fix

Use RANGE COLUMNS for non-integer partitioning

Use RANGE COLUMNS for non-integer partitioning
-- Use RANGE COLUMNS for string or date columns:
CREATE TABLE t (name VARCHAR(100))
PARTITION BY RANGE COLUMNS(name) (
  PARTITION p0 VALUES LESS THAN ('M'),
  PARTITION p1 VALUES LESS THAN MAXVALUE
);

Why this works

RANGE COLUMNS (MySQL 5.5+) supports non-integer and multi-column partitioning; RANGE requires an integer expression.

Version notes
MySQL 5.5+

RANGE COLUMNS and LIST COLUMNS were introduced for non-integer partitioning.

Sources
Official documentation ↗

MySQL 8.0 — 1497 ER_PARTITION_FUNC_NOT_ALLOWED_ERROR

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

← All MySQL errors