Error code 1265 data truncated for column

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

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

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!

CHANGE HISTORY