Data truncation error 1406 - Data too long for column …

When inserting values to a table in MySQL, you might run into this error:

Error Code: 1406. 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.

Example

We have a simple table employees:

CREATE TABLE employees (
 id int(5),
 full_name varchar(5)
);

And you insert the first row of data:

INSERT INTO employees
 VALUES(1, 'Alex with an extra long full name, longer than 10');

Since the full_name value is longer than the defined length, MySQL will throw the error Data too long for column. To resolve that, modify the column size:

ALTER TABLE employees 
  MODIFY COLUMN full_name varchar(50);

Another workaround is to disable the STRICT mode, MySQL will automatically truncate any insert values that exceed the specified column width instead of showing the error. The trade-off is, you might not be aware that your data won’t fit and end up losing original data.

To disable the STRICT mode, you can:

Edit your MySQL configuration file:

Open your my.ini (Windows) or my.cnf (Unix) file and look for “sql-mode”, and remove the tag STRICT_TRANS_TABLES

Or execute this command:

SET @@global.sql_mode= '';

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.