Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
I looked at a line using Python and it came out 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 For some reason text editors are showing what the actual data should look like (four integers on each line, tab delimited). –  Judith Feb 13 at 17:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.