For RANGE partitions each partition must be defined

If you encounter the following error when trying to create a partitioned table in MySQL

Error Code : 1492
For RANGE partitions each partition must be defined

Assuming you have defined your partitions then you probably have a syntax error. Take the following incorrect example.

CREATE TABLE People
(
	PersonId INTEGER NOT NULL AUTO_INCREMENT,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	DateOfBirth DATE NOT NULL,
	Telephone VARCHAR(30) NULL,
	Email VARCHAR(200) NULL,
	GroupId SMALLINT NOT NULL,
	PRIMARY KEY (PersonId, GroupId)
)
PARTITION BY RANGE (GroupId)
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (200),
PARTITION p2 VALUES LESS THAN (300),
PARTITION p3 VALUES LESS THAN (400),
PARTITION p4 VALUES LESS THAN (500),
PARTITION p5 VALUES LESS THAN (600),
PARTITION p6 VALUES LESS THAN (700);

The above statement is not syntactically correct but the error thrown in this case is not particularly helpful. All that is missing here is a couple of braces around the partition range definitions. The below DDL statement is correct.

CREATE TABLE People
(
	PersonId INTEGER NOT NULL AUTO_INCREMENT,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	DateOfBirth DATE NOT NULL,
	Telephone VARCHAR(30) NULL,
	Email VARCHAR(200) NULL,
	GroupId SMALLINT NOT NULL,
	PRIMARY KEY (PersonId, GroupId)
)
PARTITION BY RANGE (GroupId)
(
	PARTITION p0 VALUES LESS THAN (100),
	PARTITION p1 VALUES LESS THAN (200),
	PARTITION p2 VALUES LESS THAN (300),
	PARTITION p3 VALUES LESS THAN (400),
	PARTITION p4 VALUES LESS THAN (500),
	PARTITION p5 VALUES LESS THAN (600),
	PARTITION p6 VALUES LESS THAN (700)
);

Leave a Reply