📋

Description

MySQL Error 1367, 'Illegal %s '%s' value found during parsing', indicates that a value provided in a SQL statement cannot be correctly converted or interpreted into the target column's data type. This typically occurs during INSERT or UPDATE operations when there's a mismatch between the data being supplied and the data type expected by the database schema.

💬

Error Message

Illegal %s '%s' value found during parsing

🔍

Known Causes

3 known causes

⚠️

Data Type Mismatch

An attempt was made to store a value in a column whose data type is incompatible with the provided value (e.g., inserting text into a numeric column).

⚠️

Incorrect Date/Time Format

A date or time value was provided in a format that MySQL cannot parse for the target DATE, DATETIME, or TIMESTAMP column.

⚠️

Invalid Numeric Value

A string containing non-numeric characters or an out-of-range value was supplied for a numeric column (e.g., INT, DECIMAL).

🛠️

Solutions

3 solutions available

1. Correct Data Type for Insertion easy

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

1

Identify the table and column that is causing the error. The error message 'Illegal %s '%s' value found during parsing' usually indicates the problematic column type and the value that failed parsing.

2

Examine the `CREATE TABLE` statement or use `SHOW CREATE TABLE your_table_name;` to understand the data type of the target column.

SHOW CREATE TABLE your_table_name;

3

Review the data you are trying to insert. If the column expects a number and you're providing text, or if it expects a date and you're providing an invalid format, this error will occur. Adjust the data to match the column's expected type.

4

For example, if a column is `INT` and you try to insert 'abc', you will get this error. Instead, insert a valid integer like 123.

INSERT INTO your_table_name (integer_column) VALUES (123);

5

If a column is `DATE` and you are providing '2023-13-01' (invalid month), correct it to '2023-01-13'.

INSERT INTO your_table_name (date_column) VALUES ('2023-01-13');

2. Adjusting Column Data Type (if appropriate) medium

Modify the column's data type to accommodate the incoming data.

1

If the data you are consistently receiving is of a different type than originally planned for a column, consider altering the table schema. This is a more permanent solution if the data requirements have changed.

2

Use `ALTER TABLE` to change the data type. For instance, if you are trying to insert strings into an `INT` column and this is expected, change the column to `VARCHAR` or `TEXT`.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(255);

3

If you are receiving values that are too large for a specific numeric type (e.g., trying to insert a number larger than `INT` can hold), consider changing to a larger numeric type like `BIGINT`.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name BIGINT;

4

Always back up your data before making schema changes to prevent data loss.

3. Sanitize Input Data Before Insertion medium

Clean and validate data in your application code before sending it to MySQL.

1

This is often the most robust solution as it prevents invalid data from ever reaching the database.

2

In your application's backend (e.g., Python, PHP, Java), before executing an `INSERT` or `UPDATE` statement, validate the data against the expected data types and formats.

3

For example, if you expect a number, use language-specific functions to try converting the input to a number. If the conversion fails, reject the input or handle it gracefully.

Python example:
try:
    numeric_value = int(user_input)
except ValueError:
    print("Invalid input: not a number")
    # Handle error or reject input

4

For dates, use date parsing libraries to ensure the format is correct before attempting to insert.

Python example:
from datetime import datetime
try:
    date_object = datetime.strptime(user_date_string, '%Y-%m-%d')
except ValueError:
    print("Invalid date format")
    # Handle error or reject input

5

Be particularly careful with string inputs that might contain special characters that could be misinterpreted by MySQL if not properly escaped (though this error is more about type than escaping).

🔗

Related Errors

5 related errors

MySQL Error 1367: Invalid Data Type Value