This error occurs when you attempt to create or alter a MySQL partitioned table with zero partitions. MySQL requires at least one partition for partitioned tables; if you want to remove all partitions, use DROP TABLE instead.
Fixes ERROR 1504: Number of partitions = 0 not allowed
CREATE TABLE my_table (
id INT PRIMARY KEY,
data VARCHAR(255)
) PARTITION BY HASH(id) PARTITIONS 0;CREATE TABLE my_table (id INT PRIMARY KEY,data VARCHAR(255)) PARTITION BY HASH(id) PARTITIONS 0;ERROR 1504: Number of partitions = 0 not allowed
MySQL's partitioning feature requires tables to have at least one partition. When creating a partitioned table using CREATE TABLE or modifying one with ALTER TABLE, you must specify a positive integer value for the PARTITIONS clause. The error ER_NO_PARTS_ERROR (1504) prevents the invalid state of a table having zero partitions, which would be logically impossible—a table must have data storage somewhere.
Check your CREATE TABLE or ALTER TABLE statement. The PARTITIONS keyword must be followed by a positive integer value (1 or greater).
Invalid (causes error 1504):
CREATE TABLE my_table (
id INT PRIMARY KEY,
data VARCHAR(255)
) PARTITION BY HASH(id) PARTITIONS 0;Valid:
CREATE TABLE my_table (
id INT PRIMARY KEY,
data VARCHAR(255)
) PARTITION BY HASH(id) PARTITIONS 4;Change the PARTITIONS value to a positive integer. For most use cases, a good starting point is 4-8 partitions depending on your data volume and query patterns.
CREATE TABLE my_table (
id INT PRIMARY KEY,
data VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY HASH(id) PARTITIONS 4;Run the updated CREATE TABLE or ALTER TABLE command:
CREATE TABLE my_table (
id INT PRIMARY KEY,
data VARCHAR(255)
) PARTITION BY HASH(id) PARTITIONS 4;If successful, you should see no error and the table will be created with the specified number of partitions.
If you're using MySQL Workbench or another graphical tool, ensure that when you enable partitioning, the partition count field defaults to or is manually set to at least 1 before applying changes. Check your tool's settings to see if there's a minimum partition count validation you can enable.
Error 1504 is sometimes encountered when using database design tools that automatically generate partition DDL. In MySQL Workbench version 5.2.17 and later, a minimum partition limit was added to prevent this error. If you're using an older tool, upgrade to a newer version or manually validate partition counts before execution. Note that while you can create a table with just 1 partition, this defeats the purpose of partitioning for performance optimization. In practice, choose a partition count that aligns with your data distribution and query patterns—typically 4, 8, 16, or matching the number of CPU cores on your server. For RANGE or LIST partitioning, each partition must be explicitly defined; the partition count is implicit in the number of partitions you define.