MySQL warnings while loading integer data into table (SOLVED)

Question

For some reason, after loading a large amount of data into the following table (from a single file), all of the values register as 0.

CREATE TABLE secondTable
(
freq int NULL,
w1 int NULL,
w2 int NULL,
w3 int NULL
)

Then loading the data:

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE secondTable

For some reason, all of the data comes up as 0. The data file is 1.0GB (hardly unprecedented). The datafile also has appropriately formatted headers in the same style as other data files that have successfully been loaded into other tables.

EDIT: The problem is clearly tied to some sort of warnings during loading:

Query OK, 18976277 rows affected, 65535 warnings (5 min 49.74 sec)
Records: 18976277  Deleted: 0  Skipped: 0  Warnings: 75905108

Here are the type of warnings I'm getting:

************* 1. row ************* Level: Warning Code: 1265 Message: Data truncated for column 'freq' at row 1 ************* 2. row ************* Level: Warning Code: 1366 Message: Incorrect integer value: '' for column 'w1' at row 1 ************* 3. row ************* Level: Warning Code: 1366 Message: Incorrect integer value: '' for column 'w2' at row 1 ************* 4. row ************* Level: Warning Code: 1261 Message: Row 1 doesn't contain data for all columns ************* 5. row ************* Level: Warning Code: 1366 Message: Incorrect integer value: '' for column 'freq' at row 2 ************* 6. row ************* Level: Warning Code: 1366 Message: Incorrect integer value: '' for column 'w1' at row 2 ************* 7. row ************* Level: Warning Code: 1366 Message: Incorrect integer value: '' for column 'w2' at row 2 ************* 8. row ************* Level: Warning Code: 1366 Message: Incorrect integer value: '' for column 'w3' at row 2 ************* 9. row *************

In Python, the lines look like this: \x001\x00\t\x006\x00\t\x002\x008\x001\x005\x001\x00\t\x002\x007\x004\x003\x002\x‌​004\x00\r\x00\n

UPDATE: UPDATE: I solved the problem by using

file = codecs.open('data.txt','r','utf16') 

in Python, then writing to a new file to open in MySQL. Apparently, it was formatted in Little Endian UTF-16.

Answer 1

Based on the error messages provided. It would appear that MySQL is reading the values as an empty string '', in other words NULL which is why you are seeing the 0 values.

I would look closely at the data format in the file. Providing a small sample of the actual data would be helpful.