MySQL too long varchar truncation/error setting

mysql settings varchar
Maksym Polshcha picture Maksym Polshcha · Aug 27, 2013 · Viewed 40.7k times · Source

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?

Answer

bansi picture bansi · Aug 27, 2013

You can disable STRICT_TRANS_TABLES and STRICT_ALL_TABLES. This allows the automatic truncation of the inserted string.

Quote from MySQL Documantation.

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

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More