📋

Description

Error 1494 indicates a mismatch between the data type of a value provided for a partitioned column and the data type required by the table's partition function. This typically occurs during `INSERT` or `ALTER TABLE` statements when the partitioning key's data type doesn't align with the partition definition.

💬

Error Message

VALUES value must be of same type as partition function

🔍

Known Causes

3 known causes

⚠️

Incorrect INSERT Data Type

The data value provided in an `INSERT` statement for the partitioning column does not match the data type defined in the partition function.

⚠️

Partition Function Type Mismatch

The chosen partition function expects a specific data type (e.g., INT), but the column it's applied to or the values being inserted are of a different type (e.g., VARCHAR).

⚠️

Schema Alteration Discrepancy

The underlying table schema or column data type was altered without updating the partition function definition to reflect the new type, leading to a mismatch.

🛠️

Solutions

3 solutions available

1. Correct Partition Definition Data Type easy

Ensure the data type of values in the PARTITION BY RANGE or PARTITION BY LIST clause matches the column's data type.

1

Identify the column used for partitioning and its data type. Use `SHOW CREATE TABLE your_table_name;` to see the table definition.

SHOW CREATE TABLE your_table_name;

2

Examine the `PARTITION BY` clause in the `SHOW CREATE TABLE` output. Check the data types of the values specified within the `VALUES` or `END VALUES` clauses.

-- Example of an incorrect definition:
CREATE TABLE your_table_name (
    id INT,
    event_date DATE
)
PARTITION BY RANGE (YEAR(event_date))
(PARTITION p0 VALUES LESS THAN (2022),
 PARTITION p1 VALUES LESS THAN ('2023-01-01')); -- '2023-01-01' is a string, YEAR(event_date) returns an integer.

3

Modify the `CREATE TABLE` statement or use `ALTER TABLE` to align the data types. For `PARTITION BY RANGE`, ensure numeric values are used for integer/date parts, and for `PARTITION BY LIST`, ensure exact matches (e.g., strings for VARCHAR).

-- Corrected example:
CREATE TABLE your_table_name (
    id INT,
    event_date DATE
)
PARTITION BY RANGE (YEAR(event_date))
(PARTITION p0 VALUES LESS THAN (2022),
 PARTITION p1 VALUES LESS THAN (2023)); -- 2023 is an integer, matching YEAR() output.

4

If the table already exists and contains data, you'll need to repartition it. This often involves creating a new table with the correct definition, copying data, and then dropping the old table. For large tables, consider a staged approach.

-- Steps for repartitioning an existing table:
-- 1. Create a new table with the correct partition definition.
CREATE TABLE your_table_name_new (
    id INT,
    event_date DATE
)
PARTITION BY RANGE (YEAR(event_date))
(PARTITION p0 VALUES LESS THAN (2022),
 PARTITION p1 VALUES LESS THAN (2023));

-- 2. Copy data from the old table to the new table.
INSERT INTO your_table_name_new SELECT * FROM your_table_name;

-- 3. Rename the tables (after verifying the new table is correct).
RENAME TABLE your_table_name TO your_table_name_old, your_table_name_new TO your_table_name;

-- 4. Drop the old table.
DROP TABLE your_table_name_old;

2. Handle Date/Time Partitioning Carefully medium

When partitioning by date or time functions, ensure the values used in the partition definition are compatible with the output of those functions.

1

Identify the date/time column and the function used in the `PARTITION BY` clause (e.g., `YEAR()`, `MONTH()`, `DATE_FORMAT()`).

SHOW CREATE TABLE your_table_name;

2

Understand the return type of the date/time function. `YEAR()` returns an integer, `MONTH()` returns an integer, `DATE_FORMAT('%Y-%m-%d')` returns a string. The `VALUES` must match this.

-- Example with YEAR():
PARTITION BY RANGE (YEAR(order_date))
(PARTITION p_2022 VALUES LESS THAN (2023), -- Correct, 2023 is an integer
 PARTITION p_2023 VALUES LESS THAN ('2024')); -- Incorrect, '2024' is a string.

3

Adjust the `VALUES` in your `PARTITION BY` clause to match the expected output type. For integer-based functions, use integers. For string-based formatting, use strings.

-- Corrected example for YEAR():
PARTITION BY RANGE (YEAR(order_date))
(PARTITION p_2022 VALUES LESS THAN (2023),
 PARTITION p_2023 VALUES LESS THAN (2024));

4

If using `PARTITION BY LIST` with date/time values, ensure you're providing the exact format expected by MySQL for comparison, or use explicit casting if necessary.

-- Example for PARTITION BY LIST (less common with dates directly):
PARTITION BY LIST COLUMNS(status)
(PARTITION p_active VALUES IN ('active', 'pending'),
 PARTITION p_closed VALUES IN ('closed')); -- Here, 'active', 'pending', 'closed' are strings.

3. Review Partitioning Strategy for Complex Types advanced

For partitioning on columns with complex or composite data types, ensure the values provided in the partition definition are correctly represented.

1

Use `SHOW CREATE TABLE your_table_name;` to inspect the table structure and the `PARTITION BY` clause.

SHOW CREATE TABLE your_table_name;

2

If the partitioning is based on a generated column or a complex expression (e.g., using `CONCAT()`, `SUBSTRING()`, or multiple columns), verify that the data type of the *result* of that expression is consistent with the `VALUES` provided in the partition definition.

-- Example with a generated column:
CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    order_year_month VARCHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(order_date, '%Y-%m')) STORED
)
PARTITION BY LIST COLUMNS(order_year_month)
(PARTITION p_2023_01 VALUES IN ('2023-01'),
 PARTITION p_2023_02 VALUES IN ('2023-02')); -- '2023-01' and '2023-02' are strings, matching VARCHAR(7).

3

If the partitioning uses `PARTITION BY RANGE COLUMNS` or `PARTITION BY LIST COLUMNS`, ensure the data types of the specified columns in the `PARTITION BY` clause are consistent with the data types of the `VALUES` provided for each partition.

-- Example with PARTITION BY RANGE COLUMNS:
CREATE TABLE sales (
    sale_id INT,
    sale_amount DECIMAL(10, 2)
)
PARTITION BY RANGE COLUMNS(sale_amount)
(PARTITION p0 VALUES LESS THAN (100.00),
 PARTITION p1 VALUES LESS THAN (500.00)); -- 100.00 and 500.00 are DECIMAL, matching sale_amount.

4

If you encounter issues with composite types or expressions, consider simplifying the partitioning strategy or creating an intermediate column with a clear, single data type that can be reliably partitioned.

🔗

Related Errors

5 related errors

MySQL Error 1494: Inconsistent Partition Data Type