Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL too long varchar truncation/error setting

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?

like image 588
Maksym Polshcha Avatar asked Aug 27 '13 07:08

Maksym Polshcha


People also ask

How do I fix data truncation data too long for column?

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.

How do I ignore truncation error?

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.

What is data truncation error in MySQL?

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.

How do I truncate a column in MySQL?

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.


2 Answers

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

like image 133
bansi Avatar answered Sep 19 '22 16:09

bansi


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:

  1. add below to my.cnf

    sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

  2. way is using mysql console.

    SET @@global.sql_mode= '';

Please test them before running on production environment.

like image 40
metalfight - user868766 Avatar answered Sep 18 '22 16:09

metalfight - user868766