How to Solve MySQL Error Message ERROR 1265 (01000): Data truncated for column ‘column_name’ at row

By Posted on

This is an article where the main discussion or its written to solve the error problem specified in the title of this article. The article is triggered upon inserting new rows to a table in a database which all the data are extracted fro another table located in another database. For more information, below is the actual output shown as an error message upon inserting new records : 

user@hostname:~$ mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2537
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> insert into newdb.table1 from select * from olddb.table1;
ERROR 1265 (01000): Data truncated for column 'column_name' at row 1
mysql>

The above SQL Query command, it is actually an SQL Query command executed in order to insert a new record in a table located in a database from another table located in other database. In order to solve it, one way to solve it is to actually set SQL_MODE to nothing. Below is the actual SQL_MODE value : 

mysql> select @@SQL_MODE;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SQL_MODE                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)
mysql>

So, in order to set SQL_MODE to nothing, below is the configuration on it : 

mysql> SET SQL_MODE='';
Query OK, 0 rows affected, 1 warning (0,00 sec)
mysql>

To prove it whether it has already changed, in this context the value of SQL_MODE, below is another query command executed in order to preview the value of SQL_MODE : 

mysql> select @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+
1 row in set (0,00 sec)
mysql>

After changing the value of SQL_MODE, just re-execute the query as shown below : 

mysql> insert into newdb.table1 from select * from olddb.table1;
Query OK, 59 rows affected, 118 warnings (0,05 sec)
Records: 59  Duplicates: 0  Warnings: 118
mysql> quit                                                                                                                                                                                                                       
Bye                                                                                                                                                                                                                               
user@hostname:~$

Leave a Reply