📋

Description

This error occurs when attempting to create or alter a table with a partitioning scheme that results in zero partitions or subpartitions. MySQL requires at least one partition or subpartition to be defined for a partitioned table. It indicates that a required component count (represented by '%s' in the message) has been set to an invalid zero value.

💬

Error Message

Number of %s = 0 is not an allowed value

🔍

Known Causes

3 known causes

⚠️

Invalid Table Partition Definition

The `CREATE TABLE` or `ALTER TABLE` statement defines a partitioning scheme (e.g., RANGE, LIST, HASH) that inadvertently results in zero actual partitions being created or assigned.

⚠️

Empty Partition Value List

For `LIST` partitioning, the `VALUES IN` clause for a partition is empty, or for `RANGE` partitioning, the defined ranges do not cover any values, effectively creating no partitions.

⚠️

Missing Subpartition Definitions

When using subpartitioning, a partition is defined without any corresponding subpartitions, which is not allowed. Each partition must contain at least one subpartition.

🛠️

Solutions

3 solutions available

1. Specify at Least One Partition easy

Ensure your `PARTITION BY` clause includes at least one partition.

1

When creating or altering a table with partitioning, make sure the `PARTITION BY` clause specifies a valid partitioning method and that the number of partitions defined is greater than zero. For example, if you're using `PARTITION BY RANGE`, you need to define at least one `PARTITION` block.

CREATE TABLE my_table (
    id INT,
    data VARCHAR(255)
) ENGINE=InnoDB
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (100)
);

2

If you are altering an existing table to add partitioning, ensure you provide at least one partition definition.

ALTER TABLE my_table PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (100)
);

2. Correct Partitioning Strategy for `PARTITION BY HASH` or `PARTITION BY KEY` easy

When using `HASH` or `KEY` partitioning, explicitly define the number of partitions using `PARTITIONS`.

1

For `PARTITION BY HASH` or `PARTITION BY KEY`, you must use the `PARTITIONS` keyword followed by an integer greater than zero to specify the number of partitions.

CREATE TABLE my_table (
    id INT,
    data VARCHAR(255)
) ENGINE=InnoDB
PARTITION BY HASH (id)
PARTITIONS 4;

2

Ensure the number specified in `PARTITIONS` is not zero.

CREATE TABLE my_table (
    id INT,
    data VARCHAR(255)
) ENGINE=InnoDB
PARTITION BY KEY (id)
PARTITIONS 8;

3. Review and Adjust Partitioning Definitions medium

Carefully examine your `CREATE TABLE` or `ALTER TABLE` statements for any implicit or explicit zero partition definitions.

1

Go back to the SQL statement that is failing. This is typically a `CREATE TABLE` or `ALTER TABLE` statement that includes a `PARTITION BY` clause.

Example of a problematic statement:
CREATE TABLE my_table (
    id INT,
    data VARCHAR(255)
) ENGINE=InnoDB
PARTITION BY RANGE (id) (
    -- Missing partition definitions here
);

2

Identify the partitioning method being used (e.g., `RANGE`, `LIST`, `HASH`, `KEY`).

3

If using `RANGE` or `LIST` partitioning, ensure there is at least one `PARTITION ... VALUES ...` clause defined. If using `HASH` or `KEY` partitioning, ensure the `PARTITIONS` keyword is followed by a number greater than zero.

Corrected example for RANGE:
CREATE TABLE my_table (
    id INT,
    data VARCHAR(255)
) ENGINE=InnoDB
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN MAXVALUE
);

4

Re-execute the corrected SQL statement.

🔗

Related Errors

5 related errors

MySQL Error 1504: Zero Partitions Not Allowed