Код ошибки 1265

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:

Доброго времени суток! Проект на Yii2. При попытке обновить запись мне выдает:

Database Exception – yiidbException
SQLSTATE[01000]: Warning: 1265 Data truncated for column 'sale' at row 1
The SQL being executed was: UPDATE `house` SET `area`=100, `kitchen`=NULL, `live_area`=97, `room`=3, `floor`=NULL, `floors_qty`=NULL, `sale`='' WHERE `id`=1

Error Info: Array
(
    [0] => 01000
    [1] => 1265
    [2] => Data truncated for column 'sale' at row 1
)

↵
Caused by: PDOException
SQLSTATE[01000]: Warning: 1265 Data truncated for column 'sale' at row 1

in /home/segnava/sites/goldhouse-new/vendor/yiisoft/yii2/db/Command.php at line 844

Когда разрабатывал на OpenServer(Windows) и после загрузки на сервер всё работало. После того как перекинул все на Linux (LAMP), выдает вот такую ошибку. В чем может быть проблема. Читал что то на счет Strict SQL Mode, но не могу понять как это исправить. Подскажите пожалуйста в чем проблема? Заранее благодарен!