How to Fix Error: Data Is Truncated for a Column Error in MySQL

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 ','