Ошибка 1265 mysql

I have table in mysql table
table looks like

create table Pickup
(
PickupID int not null,
ClientID int not null,
PickupDate date not null,
PickupProxy  varchar (40) ,
PickupHispanic bit default 0,
EthnCode varchar(2),
CategCode varchar (2) not null,
AgencyID int(3) not null,

Primary Key (PickupID),
FOREIGN KEY (CategCode) REFERENCES Category(CategCode),
FOREIGN KEY (AgencyID) REFERENCES Agency(AgencyID),
FOREIGN KEY (ClientID) REFERENCES Clients (ClientID),
FOREIGN KEY (EthnCode) REFERENCES Ethnicity (EthnCode)
);

sample data from my txt file 
1065535,7709,1/1/2006,,0,,SR,6
1065536,7198,1/1/2006,,0,,SR,7
1065537,11641,1/1/2006,,0,W,SR,24
1065538,9805,1/1/2006,,0,N,SR,17
1065539,7709,2/1/2006,,0,,SR,6
1065540,7198,2/1/2006,,0,,SR,7
1065541,11641,2/1/2006,,0,W,SR,24

when I am trying to submit it by using

LOAD DATA INFILE 'Pickup_withoutproxy2.txt' INTO TABLE pickup;

it throws error

Error Code: 1265. Data truncated for column ‘PickupID’ at row 1

I am using MySQL 5.2

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:

1. 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.
2. 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.

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