Error

Error Code: 1697

MariaDB Error 1697: Incorrect Partition Value Type

📦 MariaDB

📋

Description

This error indicates that a partition key value, expected to be an integer, was provided with a non-integer data type. It commonly arises when defining or altering table partitions using `CREATE TABLE ... PARTITION BY` or `ALTER TABLE ... ADD PARTITION` statements, specifically for `RANGE` or `LIST` partitioning where the partition expression evaluates to an integer.

💬

Error Message

VALUES value for partition '%s' must have type INT

🔍

Known Causes

3 known causes

⚠️

Mismatched Partition Value Type

The value specified in the `VALUES` clause for a partition (e.g., `VALUES LESS THAN` or `VALUES IN`) is not an integer, but the partition key expects an integer.

⚠️

Non-Integer Partition Expression

The expression used as the partition key (e.g., in `PARTITION BY RANGE (expression)`) implicitly evaluates to a non-integer data type, conflicting with the integer requirement for `VALUES` clauses.

⚠️

Syntax or Quoting Error

An accidental typo or incorrect use of quotes around a numeric value might cause it to be interpreted as a string instead of an integer by MariaDB.

🛠️

Solutions

3 solutions available

1. Correct Partition Value Type to INT easy

Modify the partition definition to use integer values for the specified partition.

Identify the table and the specific partition causing the error. The error message `VALUES value for partition '%s' must have type INT` will usually indicate the partition name.

Review the `CREATE TABLE` or `ALTER TABLE` statement used to define or modify the partitioned table. Look for the `PARTITION BY RANGE` or `PARTITION BY LIST` clause and the `VALUES` for the problematic partition.

If the `VALUES` for the partition are not integers (e.g., strings, dates without explicit casting), change them to valid integer literals.

ALTER TABLE your_table PARTITION BY RANGE (column_name) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p_error VALUES LESS THAN ('abc') -- Incorrect type
);

Re-execute the `ALTER TABLE` or `CREATE TABLE` statement with the corrected integer `VALUES`.

ALTER TABLE your_table PARTITION BY RANGE (column_name) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p_correct VALUES LESS THAN (30) -- Corrected to integer
);

2. Change Partitioning Column Type to INT medium

Alter the data type of the partitioning column to INT if it's currently a different type.

Determine the column used for partitioning. This is specified in the `PARTITION BY` clause of your `CREATE TABLE` or `ALTER TABLE` statement.

Check the current data type of the partitioning column using `SHOW CREATE TABLE` or `DESCRIBE`.

DESCRIBE your_table;

If the partitioning column is not an `INT` type (e.g., `VARCHAR`, `DATE`), you need to change its type to `INT`. This might involve data migration or data transformation.

ALTER TABLE your_table MODIFY COLUMN partitioning_column INT;

Once the column type is `INT`, ensure that your partition `VALUES` are also integers. If you previously tried to use non-integer `VALUES`, you'll need to correct those as well, as per Solution 1.

Re-apply the partitioning strategy after the column type change.

ALTER TABLE your_table PARTITION BY RANGE (partitioning_column) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200)
);

3. Use a Helper Column for Partitioning advanced

Introduce a new integer column that derives its value from the original non-integer partitioning column.

Identify the non-integer column currently used for partitioning and the desired integer values for your partitions.

Add a new `INT` column to your table. This column will store the integer representation of your partitioning logic.

ALTER TABLE your_table ADD COLUMN partitioning_int_value INT;

Populate the new `partitioning_int_value` column based on the data in your original partitioning column. This often involves case statements or other logical transformations. For example, if partitioning by a date range, you might extract the year or a specific integer representation.

UPDATE your_table SET partitioning_int_value = YEAR(your_date_column); -- Example for date partitioning

Recreate the table with partitioning using the new `partitioning_int_value` column. You will need to drop the old table and create a new one, or use a more complex `ALTER TABLE` sequence involving `REORGANIZE PARTITION` if possible.

CREATE TABLE your_table_new (
    ... -- original columns
    partitioning_int_value INT
) PARTITION BY RANGE (partitioning_int_value) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021)
);

After creating the new table, copy the data from the old table to the new table. Ensure the `partitioning_int_value` is correctly calculated during the insert.

INSERT INTO your_table_new (...) SELECT ..., YEAR(your_date_column) FROM your_table_old;

Once data is migrated and verified, drop the old table and rename the new table.

DROP TABLE your_table_old;
RENAME TABLE your_table_new TO your_table;

🔗

Related Errors

5 related errors