<p>MySQL raises error 1566 when a partition definition uses NULL inside a VALUES LESS THAN clause, which is not permitted.</p>
<p>MySQL Error 1566 ER_NULL_IN_VALUES_LESS_THAN appears when a partition range list contains NULL in the VALUES LESS THAN clause. Replace NULL with MAXVALUE or a concrete constant, or add NOT NULL constraints, to resolve the issue.</p>
Not allowed to use NULL value in VALUES LESS THAN
MySQL throws error 1566 when you attempt to create or alter a RANGE or LIST partition and specify NULL in the VALUES LESS THAN list. The partitioning engine expects only concrete constants or the special MAXVALUE keyword.
The server halts the DDL statement to prevent undefined placement of rows containing NULL, because NULL is never equal or less than any numeric value in MySQL.
The error commonly arises during CREATE TABLE, ALTER TABLE, or EXCHANGE PARTITION commands that include a VALUES LESS THAN clause with NULL.
It also appears when executing mysqldump scripts generated from another system that unintentionally wrote NULL into partition boundaries.
Leaving the definition invalid blocks table creation or modification, stopping deployments and delaying schema migrations.
Fixing the error ensures partitions compile, improves query performance, and keeps data correctly routed across partitions.
Developers mistakenly insert NULL directly inside VALUES LESS THAN (NULL).
Application code inserts NULL into boundary values when dynamic DDL builds partition lists.
Migrations cloned from other databases may include NULL boundaries that are valid elsewhere but not in MySQL.
Teams think NULL works like MAXVALUE, causing them to substitute NULL for an open-ended range.
Triggered when you use non-deterministic functions in partition expressions.
Occurs if MAXVALUE appears in a non-last partition.
Raised when attempting to drop a partition that does not exist.
Yes. Ensure the partition key column is NOT NULL or add a default value. NULL rows can then be inserted; the storage engine routes them to the appropriate partition based on other rules.
No. Similar to RANGE, LIST partitioning forbids NULL in VALUES IN lists. Use MAXVALUE or a placeholder value instead.
No. Error 1566 is enforced at the partition parser level and is independent of sql_mode settings.
Galaxy highlights invalid partition syntax in real time, suggests MAXVALUE replacements through its AI copilot, and lets teams review DDL collaboratively to catch NULL boundaries before deployment.
.avif)
.avif){kind=logo}
