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?
There could be 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.