Ошибка 1265 mysql workbench

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

I am trying to load a set of data from a .txt file to MySQL database. However, i am unable to load the date_time dataset into the table. it is returning 0000-00-00 00:00:00. Can anyone advise me what i am doing wrong?

I have created the following taxi_movement_data table into a taxiapp schema.

CREATE TABLE `taxiapp`.`taxi_movement_data` (
`tracked_datetime` DATETIME NOT NULL,
`longitude` DOUBLE NOT NULL,
`lattitude` DOUBLE NOT NULL,
`id` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), UNIQUE INDEX `id_UNIQUE` (`id` ASC));

My .txt file sample is as follows.

2018-06-01T23:51:09+08:00,103.62926,1.30081
2018-06-01T23:51:09+08:00,103.63598,1.27931
2018-06-01T23:51:09+08:00,103.6375,1.34143

My SQL query is as follows

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/uploads/ltasampledata.txt' 
INTO TABLE taxi_movement_data 
FIELDS TERMINATED BY ',' enclosed by '"'
LINES TERMINATED BY 'rn'
SET tracked_datetime = DATE_ADD(DATE_FORMAT(substring(@tracked_datetime,1,19), '%Y-%m-%d %H:%i:%s'),INTERVAL 8 HOUR);

The result is as follows with the datetime being unable to be loaded. The ‘tracked_datetime’ returns 0000-00-00 00:00:00. The longitude, lattitude and id is working fine and as it should.

Table results after Query.

Sorry, i am not authorise to load images directly yet on Stackoverflow so it has become a link.

Error Message as follows

3 row(s) affected, 9 warning(s): 1265 Data truncated for column 'tracked_datetime' at row 1 1261 Row 1 doesn't contain data for all columns 1048 Column 'tracked_datetime' cannot be null 1265 Data truncated for column 'tracked_datetime' at row 2 1261 Row 2 doesn't contain data for all columns 1048 Column 'tracked_datetime' cannot be null 1265 Data truncated for column 'tracked_datetime' at row 3 1261 Row 3 doesn't contain data for all columns 1048 Column 'tracked_datetime' cannot be null Records: 3  Deleted: 0  Skipped: 0  Warnings: 9 0.078 sec

I created a table in mysql like this :

create table if not exists  data
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
execute_time DATETIME NOT NULL default current_timestamp,
db VARCHAR(25) NOT NULL,
book varchar(6),
`open` float(20,3) not null,
`close` float(20,3) not null)

and when I want to insert value into this table, it fails and show the exception as DataError: (1265, «Data truncated for column ‘open’ at row 1»)

and the values I want to insert into open is 3535929.559. I code the insert data in python like this:

insertData = """INSERT INTO {table_name} (execute_time,db,book,`open`,`close`) values 
                        (%(execute_time)s,%(db)s,%(book)s,%(open)s,%(close)s)""" .format(table_name = settings["dumpTable"]["data"])

    conn.cursor().executemany(insertData,data_in_dict)
    conn.commit()

The most strange thing is when I insert the same value in MySQL workbench ,it went smoothly, the query is like this :

insert into data (db,book,`open`,`close`,execute_time) values ('au','book','3535929.559','1079339.851','2016-7-22');  

But the data shown in the table is:

 '5', '2016-07-05 00:00:00', 'au', 'book', '3535929.500', '1079339.875'

I think the problem maybe insertion from python is more restricted? Then how to set it to be less restricted? Any help would be appreciated!

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: