Error
Error Code:
78
MySQL Error 78: Incorrect Integer Value
Description
This error indicates that MySQL received a value that it cannot interpret as an integer where an integer was expected. This commonly occurs during data manipulation (INSERT/UPDATE) or when setting integer-based configuration options.
Error Message
Incorrect integer value: '%s'.
Known Causes
3 known causesData Type Mismatch
Attempting to store a non-integer string or invalid numeric format into a database column defined as an integer type.
Invalid Configuration Setting
Supplying a non-integer value for a MySQL server variable or option that specifically requires an integer.
Application Input Error
Application code passes non-integer data to SQL queries expecting an integer, often due to insufficient input validation.
Solutions
3 solutions available1. Sanitize and Cast Input Data medium
Ensure the data being inserted or updated is a valid integer or can be safely converted.
1
Identify the column and the problematic value causing the error. The error message '%s' usually indicates the specific value that couldn't be converted to an integer.
2
Before inserting or updating, validate the input data. If the data is coming from user input or an external source, clean it to remove non-numeric characters that are not part of a valid integer representation (e.g., leading/trailing whitespace, currency symbols).
SELECT '123' REGEXP '^[+-]?[0-9]+$'; -- Example of regex validation3
If the data might be a string representation of an integer, explicitly cast it to an integer type using `CAST()` or `CONVERT()` in your SQL statements.
UPDATE your_table SET integer_column = CAST('123' AS UNSIGNED INTEGER) WHERE id = 1;
INSERT INTO your_table (integer_column) VALUES (CONVERT('456', SIGNED INTEGER));4
If the problematic value is a floating-point number, decide if you want to truncate it (using `FLOOR()` or `CEIL()`) or if it indicates a data type mismatch for the column.
UPDATE your_table SET integer_column = FLOOR(3.14) WHERE id = 2;
UPDATE your_table SET integer_column = CEIL(3.14) WHERE id = 3;2. Review Column Data Type and Constraints medium
Verify that the target column's data type is appropriate and that no other constraints are violated.
1
Use `DESCRIBE` or `SHOW CREATE TABLE` to inspect the data type of the column you are trying to insert into or update. Ensure it's an integer type (e.g., `INT`, `BIGINT`, `SMALLINT`, `TINYINT`, `UNSIGNED` variants).
DESCRIBE your_table;
SHOW CREATE TABLE your_table;2
Check for any `CHECK` constraints or `TRIGGER`s that might be enforcing specific integer formats or ranges, and which could be causing the rejection.
SHOW CREATE TABLE your_table;3
If the column is defined as `UNSIGNED`, ensure you are not trying to insert a negative integer. If it has a specific range (e.g., `TINYINT` is -128 to 127), verify the value falls within that range.
3. Handle NULL Values Appropriately easy
Address cases where the input value might be NULL but the column does not allow it.
1
Determine if the column is defined to allow `NULL` values. If it's `NOT NULL`, you cannot insert or update it with a `NULL` value.
DESCRIBE your_table;
SHOW CREATE TABLE your_table;2
If the column is `NOT NULL` and you are encountering `NULL` values, either modify the column definition to allow `NULL`s (if appropriate) or provide a default integer value.
ALTER TABLE your_table MODIFY integer_column INT NULL;
-- OR provide a default value:
ALTER TABLE your_table MODIFY integer_column INT NOT NULL DEFAULT 0;3
When inserting or updating, ensure that `NULL` values are explicitly handled or avoided if the column does not permit them. Use `COALESCE()` to substitute a default value for `NULL` if needed.
UPDATE your_table SET integer_column = COALESCE(your_input_value, 0) WHERE id = 4;