Error
Error Code:
1292
MariaDB Error 1292: Data Type Conversion Issue
Description
Error 1292 indicates that MariaDB attempted to convert a value into a different data type, but the value was incorrect or too large for the target type, resulting in truncation. This typically occurs during data manipulation operations like INSERT or UPDATE statements, or when using functions that implicitly or explicitly change data types.
Error Message
Truncated incorrect %s value: '%s'
Known Causes
3 known causesMismatched Data Types
Attempting to store a value of one data type (e.g., string) into a column defined with an incompatible data type (e.g., integer or date).
Value Exceeds Column Capacity
Providing a numeric or string value that is too large or too long to fit within the defined precision or length of the target column.
Invalid Date or Time Format
Supplying a date or time string that does not conform to MariaDB's expected format for DATE, TIME, or DATETIME columns.
Solutions
3 solutions available1. Correct Data Type in INSERT/UPDATE Statement easy
Ensure values match the target column's data type during data modification.
1
Identify the column and table that are causing the error. The error message 'Truncated incorrect %s value: '%s'' will often provide clues about the problematic data type and the value itself.
2
Examine the `INSERT` or `UPDATE` statement that is failing. Check the data type of the value you are trying to insert or update against the defined data type of the target column.
Example: If you are trying to insert 'abc' into an INT column, this will fail.3
Modify the `INSERT` or `UPDATE` statement to provide a value that conforms to the target column's data type. For example, if inserting into an `INT` column, ensure the value is a valid integer.
INSERT INTO my_table (int_column, varchar_column) VALUES (123, 'some text');
-- Incorrect: INSERT INTO my_table (int_column, varchar_column) VALUES ('abc', 'some text');4
If the value is intended to be a string but is being misinterpreted, explicitly cast it to a string or ensure it's enclosed in single quotes.
UPDATE my_table SET date_column = '2023-10-27' WHERE id = 1;
-- If '2023-10-27' was being treated as a number, this explicit string assignment helps.2. Modify Table Schema to Accommodate Data medium
Alter the column's data type to be more permissive if the incoming data is valid but doesn't fit the current schema.
1
Determine if the data you are trying to insert or update is actually valid and if the current column data type is too restrictive. For example, if you are consistently getting errors trying to insert numbers that are too large for an `INT` column, consider changing it to `BIGINT`.
2
Use the `ALTER TABLE` statement to modify the data type of the problematic column. Be cautious with this as it can impact existing data or performance.
ALTER TABLE your_table_name MODIFY COLUMN your_column_name NEW_DATA_TYPE;3
Example: Changing an `INT` to a `BIGINT`.
sql
ALTER TABLE users MODIFY COLUMN user_id BIGINT;
sql
ALTER TABLE users MODIFY COLUMN user_id BIGINT;
ALTER TABLE users MODIFY COLUMN user_id BIGINT;4
Example: Changing a `VARCHAR(50)` to a `VARCHAR(255)` if string truncation is the issue.
sql
ALTER TABLE products MODIFY COLUMN description VARCHAR(255);
sql
ALTER TABLE products MODIFY COLUMN description VARCHAR(255);
ALTER TABLE products MODIFY COLUMN description VARCHAR(255);3. Disable Strict SQL Mode Temporarily easy
Temporarily relax SQL mode to allow data that would otherwise be rejected, useful for quick debugging or migration.
1
Understand the risks: Disabling strict mode can lead to data integrity issues if not managed carefully. This is often a temporary measure.
2
Check the current SQL mode to see if strict mode is enabled. Look for `STRICT_TRANS_TABLES` or `STRICT_ALL_TABLES`.
SHOW VARIABLES LIKE 'sql_mode';3
Temporarily disable strict mode for the current session. This change will be lost when the session ends.
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';4
Alternatively, to disable it permanently (not recommended without full understanding), you would edit the MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`) and remove the strict mode flags from the `sql_mode` variable. Then, restart the MariaDB service.
Example in `my.cnf`:
ini
[mysqld]
sql_mode = "NO_ENGINE_SUBSTITUTION"
5
After performing the operation that was failing, it's highly recommended to re-enable strict mode.
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'; -- Or your original strict mode settings