📋

Description

This error indicates that a value provided for a column during an INSERT or UPDATE operation does not conform to the column's defined data type or other constraints. It typically occurs when attempting to store data in an incompatible format.

💬

Error Message

Incorrect %s value: '%s' for column '%s' at row %ld

🔍

Known Causes

4 known causes

⚠️

Data Type Mismatch

Attempting to store a value that is incompatible with the target column's defined data type (e.g., text into an integer column).

⚠️

Invalid Date/Time Format

Providing a date or time string that does not adhere to the expected format for a DATE, TIME, or DATETIME column.

⚠️

Enum/Set Value Violation

A value supplied for an ENUM or SET column is not among its predefined list of valid options.

⚠️

Numeric Format Issues

Supplying a numeric value with incorrect formatting (e.g., non-numeric characters) for a numeric column.

🛠️

Solutions

4 solutions available

1. Correct Data Type and Format easy

Ensure the data being inserted matches the column's defined data type and format.

1

Identify the column and the problematic value from the error message. The error message will explicitly state the column name and the value that caused the issue.

2

Examine the table schema to understand the expected data type for that column. Use the `DESCRIBE` or `SHOW COLUMNS` command.

DESCRIBE your_table_name;
-- or
SHOW COLUMNS FROM your_table_name;

3

Compare the value you are trying to insert with the expected data type. For example, if the column is an `INT`, ensure the value is a valid integer. If it's a `DATE` or `DATETIME`, ensure it's in the correct format (e.g., 'YYYY-MM-DD' or 'YYYY-MM-DD HH:MM:SS').

4

Modify your `INSERT` or `UPDATE` statement to provide data in the correct format and type. If you're inserting a string that looks like a number into a numeric column, you'll need to cast it or ensure it's a clean numeric string.

-- Example: Inserting a string that should be an integer
INSERT INTO your_table_name (int_column, varchar_column) VALUES (CAST('123' AS UNSIGNED), 'some text');

-- Example: Inserting a date in the wrong format
-- If column is DATE, '2023/10/27' might fail, '2023-10-27' should work.

2. Handle Nullable Columns and Default Values easy

Address cases where a value is missing for a column that does not allow NULLs.

1

Check the table schema for the column in question to see if it's defined as `NOT NULL`. If it is, and you're attempting to insert a `NULL` value or omitting the column entirely without a `DEFAULT` value, this error can occur.

DESCRIBE your_table_name;
-- Look for 'Null' column being 'NO'

2

If the column is `NOT NULL` and you intend to insert a value, ensure you are providing one. If the value is legitimately missing, consider if the column should be nullable or if a default value should be set.

-- Add a value for the NOT NULL column
INSERT INTO your_table_name (id, not_null_column, other_column) VALUES (1, 'some_value', 'another_value');

3

Alternatively, if the column can accept `NULL` values, modify the schema to allow `NULL`s.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(255) NULL;

4

If the column should have a default value when no value is provided, add a `DEFAULT` constraint.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name INT NOT NULL DEFAULT 0;

3. Adjust Character Set and Collation medium

Resolve issues arising from character encoding mismatches between data and column.

1

The error message might indicate a character encoding problem if the problematic value contains special characters or non-ASCII characters. Check the character set and collation of the column and the table.

SHOW FULL COLUMNS FROM your_table_name;
-- Look at 'Collation' for the column and table

2

If the character set of the column is too restrictive (e.g., `latin1`) and you are trying to insert data with characters not supported by that set (e.g., emojis, certain Unicode characters), you'll need to change the column's character set to a more capable one like `utf8mb4`.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3

Ensure your client connection is also using a compatible character set. You can set this in your client application or during the connection.

-- Example for MySQL client:
SET NAMES utf8mb4;

4

If the issue is with specific data, you might need to clean or encode the data before insertion. For example, if you are receiving data from an external source that has incorrect encoding, you may need to convert it to `utf8mb4`.

4. Review Enum and Set Data Types medium

Verify that values for ENUM and SET columns are valid choices.

1

If the error occurs on a column defined as `ENUM` or `SET`, the problematic value must be one of the allowed values defined for that type.

DESCRIBE your_table_name;
-- Look for 'Type' column showing ENUM(...) or SET(...)

2

List the allowed values for the `ENUM` or `SET` column.

SHOW COLUMNS FROM your_table_name LIKE 'your_enum_column';
-- The 'Type' field will show the defined values, e.g., 'enum('A','B','C')'

3

Ensure the value you are trying to insert is present in the list of allowed values. Case sensitivity can also be a factor depending on the collation.

-- Example: If ENUM is ('active', 'inactive')
-- This will fail: INSERT INTO your_table_name (status) VALUES ('Active');
-- This will work: INSERT INTO your_table_name (status) VALUES ('active');

4

If necessary, modify the table to add new allowed values to the `ENUM` or `SET` definition.

ALTER TABLE your_table_name MODIFY COLUMN your_enum_column ENUM('active', 'inactive', 'pending') NOT NULL;

🔗

Related Errors

5 related errors

MariaDB Error 1366: Incorrect Column Value