Finding MySQL errors from LOAD DATA INFILE

Question

I am running a LOAD DATA INFILE command in MySQL and one of the files is showing errors at the mysql prompt.

How do I check the warnings and errors? Right now 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?

Answer 1

The MySQL SHOW WARNINGS command only shows you a subset of the warnings. 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 specify this, MySQL expects the tab character to terminate fields.