This article demonstrates the possible reasons and solutions for an error, saying that Data is truncated for a column in MySQL.
Fix Data is Truncated for a Column Error in MySQL
Here, we will explore the possible reasons and solutions to get rid of an error saying MySQL data is truncated for a column.
Data Is Too Large
There are some scenarios when we have to load the data from a file instead of inserting manually into the MySQL table. While loading, the Data truncated for a column 'columnName' at row # error may occur. Why?
This is because the given data seems too big for a data type of the column in the MySQL table. For instance, you have a table where a value attribute is of type text which can only accommodate 65K characters of the provided data.
You will get this error when you insert data for the value column that exceeds 65K.
To solve this problem, you need to change the type that can accept more data, such as MEDIUMTEXT or LONGTEXT, which can accommodate 16MB and 4GB. You can also find some optimization tips here.
We can also truncate the data using SET ANSI_WARNINGS OFF and insert the records again, considering the maximum length of the column’s data type.
Invalid Data Is Passed
The Error Code: 1265. Data truncated for column 'a' at row 1 also occurs in MySQL if we try to insert invalid data.
For example, we have a table where the price field is type float and accepts the NULL values. See the following:
#create a table named `prices`
CREATE TABLE prices (ID INT NOT NULL, price FLOAT NULL);
#insert the first record
INSERT prices VALUES (1, '');
Here, we insert an empty string in the price column, a float type that takes NULL values. Remember, NULL and '' are not the same thing.
Still, if we try to insert an empty string in a column that takes NULL values, it would generate the Error Code: 1265. Data truncated for column 'a' at row 1. The simplest solution to this problem is passing the correct value of the right data type as required by the respective column of the price table.
We can solve it by specifying the NULL values explicitly instead of an empty string ('') or do not even write the values for that particular column in the INSERT statement. See the following snippet considering our example.
INSERT prices VALUES (1, NULL); #insert null explicitly
INSERT prices (ID) VALUES (2); #don't specify values for `price` column
Incorrect Terminating Character
If we insert every line manually in the MySQL table, everything goes well. The error occurs when we try to load more than one line at once and don’t get the terminating character properly.
To solve this, we need to check the terminating character of the file and specify in the LOAD command as follows.
#if the terminating character is `tab`
FIELDS TERMINATED BY 't'
#if the terminating character is a `comma`
FIELDS TERMINATED BY ','
If you are running a small business and currently using a @gmail.com address, check out our new Virtual Email Hosting service. It maps a custom email address onto your existing Gmail mailbox, so you get to send and receive emails using the new address whilst keeping the same Gmail mailbox — no migrations needed.
Find out more →
Page content continues below:
Recently, while working on a project, I ran into a warning telling me that my “data was truncated for” one of my columns when I was importing a CSV file into one of my SQL tables.
Concerned that I had done something wrong, I Googled for a solution. Unfortunately, I didn’t find any answers there, so I ended up having to find the source of this warning myself.
What does “data truncated” mean?
Truncated means “cut short”, and “data truncated” warnings or errors refer to a value’s data being cut off at the end during the importing process (e.g. a value of “2.9823” being imported as “2.98”). These errors are important warnings, because it notifies us that our data has not been imported accurately.
Data truncated warnings usually happen because the imported value:
- Does not match the data type of the column they are being imported into, e.g. inserting “120e” into an
INT(i.e. integer) column will cause the data to be inserted as 120, truncating the e at the back. - Exceeds the maximum length of the column, e.g. a string “abcdef” being imported into a
VARCHAR(2)will be truncated into “ab”.
Which was why my problem was so perplexing.
The problem
I wrote an SQL query to load the CSV file on the left into my table on the right (pictured below):
This was the SQL query I used:
LOAD DATA INFILE '/path/to/test.csv' INTO TABLE tmp FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 ROWS (id,test);
All my values have been imported correctly to the table, so what exactly is being truncated?
After some research and asking around, I found the answer: the r character at the end of “11” was being truncated.
Article continues after the advertisement:
The r character
The r character is part of the rn series of characters, used in Windows to denote a newline character. In all other operating systems, newlines are denoted using n, but because the CSV file was generated in Windows, it uses rn for newlines instead.
CR (carriage return) represents r, while LF (line feed) represents n.Why r was being read into the database
The r character was being read into the database because of this line in my query:
LOAD DATA INFILE '/path/to/test.csv' INTO TABLE tmp FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 ROWS (id,test);
The first row in my data has the following characters:
1;11rn
Which was split into 1 and 11r. As r is not considered an integer, it cannot be entered into the test column, which is defined as an INTEGER. Hence, it got truncated.
To fix this warning, all I had to do was to change the problematic line in my SQL query:
LINES TERMINATED BY 'rn'
And I would be fine even if I didn’t, because this is one of those rare cases where the data truncated warning is harmless!
Conclusion
In conclusion, this is an exploration of an interesting warning I had found while trying to import CSV files into a MySQL table. Remember to always be careful and check your queries before you start importing!
Leave a comment below if the article helped you!
Article continues after the advertisement:
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:~$