Finding MySQL errors from LOAD DATA INFILE

Question

I have a very large set of files and one of them is showing errors when I import it into mysql from the commandline mysql> prompt. I'm not sure how to check the data so the only thing I have to go by is the fact that the prompt reports 65,535 warnings on import.

mysql> use dbname;
Database changed
mysql> LOAD DATA LOCAL INFILE '/dump.txt'
    -> INTO TABLE table
    -> (id, title, name, accuracy);
Query OK, 897306 rows affected, 65535 warnings (16.09 sec)
Records: 897306  Deleted: 0  Skipped: 0  Warnings: 0

How do I get mysql to show me what those warnings are? I looked in the error log but I couldn't find them. Running the "SHOW WARNINGS" command only returned 64 results which means that the remaining 65,000 warnings must be somewhere else.

2 |
| Warning | 1366 | Incorrect integer value: '' for column 'accuracy' at row 2038
3 |
| Warning | 1366 | Incorrect integer value: '' for column 'accuracy' at row 2038
4 |
| Warning | 1366 | Incorrect integer value: '' for column 'accuracy' at row 2038
6 |
| Warning | 1366 | Incorrect integer value: '' for column 'accuracy' at row 2038
7 |
+---------+------+--------------------------------------------------------------
--+
64 rows in set (0.00 sec)

How do I find these errors?

EDIT:

Here is some more data on the table. There are 897,306 rows total in the table and in the text file.

428,351 rows = SELECT COUNT(*) FROM `table` WHERE accuracy = '' OR accuracy IS NULL

and 65,535 warnings with the last 64 complaining about bad integer values.

Answer 1

'SHOW WARNINGS' only shows you a subset of them. You can change the limit of warning shown by modifying the parameter max_error_count.

Answer 2

Getting that many errors suggests that you have the wrong delimiter or extraneous quote marks that are making MySQL read the wrong columns from your input.

You can probably fix that by adding

[{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]

after the tablename and before the column list.

Something like:

LOAD DATA LOCAL INFILE '/dump.txt' INTO TABLE table fields terminated by ' ' optionally enclosed by '"' (id, title, name, accuracy);

By default, if you don't specific this, MySQL expects the tab character to terminate fields.

Answer 3

I have seen problems like this before, where there is a blank entry in the data file, and the target table doesn't allow null values, or doesn't have a valid default value for the field in question.

I'd check that the table has a default for accuracy - and if it doesn't, set it to zero and see if that clears up the errors.

Or you could pre-process the file with 'awk' or similar and ensure there is a valid numeric value for the accuracy field in all rows.

Hope that helps