I have two MySQL instances. The 1st one truncates strings on insert when data is too long. The 2nd one raises an error:
ERROR 1406 (22001): Data too long for column 'xxx' at row 1
I want the 2nd one to truncate the data as well. Is there any MySQL setting to manage this behavior?
That error message means you are inserting a value that is greater than the defined maximum size of the column. The solution to resolve this error is to update the table and change the column size.
Solution. To avoid this error and to insert the string with truncation, use the ANSI_WARNINGS option. On setting ANSI_WARNINGS to OFF, the error message will not be displayed and the data will be automatically truncated to the length of the destination column and inserted.
When you load data from file to a MySQL table, you might run into this error: Data truncated for column 'column_name' at row # That error means the data is too large for the data type of the MySQL table column.
TRUNCATE() Function in MySQL The TRUNCATE function in MySQL is used to truncate a number to a specified number of decimal places. Parameter : TRUNCATE() function accepts two parameters as mentioned above and described below. X –The number which to be truncated.
You can disable STRICT_TRANS_TABLES
and STRICT_ALL_TABLES
. This allows the automatic truncation of the inserted string.
Quote from MySQL Documentation.
Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)
Reference: MySQL Server SQL Modes
If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 6.1.7, “Server SQL Modes”.
How you can change it: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
Found two ways to disable strict mode:
add below to my.cnf
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
way is using mysql console.
SET @@global.sql_mode= '';
Please test them before running on production environment.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With