📋

Description

This error occurs when attempting to define a `RANGE` partition in MariaDB where the `VALUES LESS THAN` clause includes a `NULL` value. MariaDB's `RANGE` partitioning scheme does not permit `NULL` as a boundary value for defining ranges, as it expects explicit, comparable values.

💬

Error Message

Not allowed to use NULL value in VALUES LESS THAN

🔍

Known Causes

3 known causes

⚠️

Using NULL in RANGE partition definition

The `VALUES LESS THAN` clause for a `RANGE` partition was configured with `NULL` as a boundary, which is an invalid value for this type of partitioning.

⚠️

Misunderstanding RANGE partitioning rules

An attempt was made to apply `NULL` within a `RANGE` partition's boundary, which is fundamentally incompatible with how `RANGE` partitioning operates.

⚠️

Incorrect migration or design

A partition definition might have been migrated from a system with different `NULL` handling or designed without considering MariaDB's specific `RANGE` partitioning limitations.

🛠️

Solutions

3 solutions available

1. Modify Partition Definition to Exclude NULL easy

Adjust the RANGE partitioning to use explicit non-NULL values.

1

Identify the table with the RANGE partition causing the error. You can do this by querying `information_schema.PARTITIONS`.

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_METHOD FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'your_database_name' AND PARTITION_METHOD = 'RANGE';

2

If the partitioning column allows NULLs and you are using `VALUES LESS THAN (NULL)`, you need to redefine the partition. Instead of `VALUES LESS THAN (NULL)`, use a concrete, high-value sentinel if appropriate, or restructure your partitioning strategy.

ALTER TABLE your_table_name PARTITION BY RANGE (your_partition_column) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200),
    PARTITION p_max VALUES LESS THAN MAXVALUE -- Use MAXVALUE for the highest partition if applicable
);

3

If the NULL values represent a distinct category, consider creating a specific partition for them, but this requires a different partitioning strategy (e.g., LIST partitioning for the NULL case, or a hybrid approach if your MariaDB version supports it). For RANGE, you must use concrete values.

--- Example if you need to capture values before a certain point and then handle others.
-- This is a conceptual example, as direct NULL handling in RANGE is not supported.
-- You might need to preprocess data or use a different partition type.
-- For RANGE, you must provide finite values.
ALTER TABLE your_table_name PARTITION BY RANGE (your_partition_column) (
    PARTITION p_early VALUES LESS THAN (50),
    PARTITION p_mid VALUES LESS THAN (150),
    PARTITION p_late VALUES LESS THAN (250)
    -- Any NULLs will not fall into these partitions and will cause an error during insertion/update if not handled.
);

2. Ensure Partitioning Column Does Not Allow NULL medium

Modify the table schema to prevent NULLs in the partitioning column.

1

Identify the table and the column used for RANGE partitioning.

DESCRIBE your_table_name;

2

Alter the column to be `NOT NULL`. This is a significant change and requires careful consideration of existing data.

ALTER TABLE your_table_name MODIFY your_partition_column INT NOT NULL; -- Replace INT with the actual data type

3

Before altering, it's crucial to handle existing NULL values in the `your_partition_column`. You can update them to a default value or a sentinel value that fits your partitioning scheme.

UPDATE your_table_name SET your_partition_column = 0 WHERE your_partition_column IS NULL; -- Replace 0 with an appropriate default value

4

Once the column is `NOT NULL` and existing NULLs are handled, you can then create or recreate the RANGE partitions without issues.

ALTER TABLE your_table_name PARTITION BY RANGE (your_partition_column) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

3. Use `MAXVALUE` for the Last Partition easy

If your intention was to capture all remaining values, use `MAXVALUE` instead of `NULL`.

1

Examine your current `CREATE TABLE` or `ALTER TABLE` statement for the RANGE partitioning.

-- Example of problematic definition:
-- PARTITION p_last VALUES LESS THAN (NULL)

2

Replace `VALUES LESS THAN (NULL)` with `VALUES LESS THAN MAXVALUE` for your highest partition. This captures all values greater than the last defined partition's upper bound up to infinity.

ALTER TABLE your_table_name PARTITION BY RANGE (your_partition_column) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE
);

3

This solution assumes you do not specifically need to partition based on the NULL state itself, but rather want to capture all remaining values.

--- No further code needed, just understanding the concept.

🔗

Related Errors

5 related errors

MariaDB Error 1566: NULL not allowed in RANGE partition