Finding MySQL errors from LOAD DATA INFILE?

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.

Up vote 1 down vote favorite 1 share g+ share fb share tw.

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. Sql mysql load-data-infile link|improve this question edited Dec 27 '09 at 3:07OMG Ponies95.2k1065138 asked Sep 28 '09 at 17:15Xeoncross4,70123076 80% accept rate.

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.

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

Thanks, more info here: dev.mysql.com/doc/refman/5.1/en/show-war... – Xeoncross Sep 28 '09 at 18:01.

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.

Yes, I thought that was it two. However, all the data seems to be being inserted correctly. Also, the file uses tabs to separate values so the default is correct.

The thing that gets me is that out of 800,000 rows perhaps all 64,000 warnings are rows with "Incorrect integer value". I just am not sure how to check this when I can only view so many on the cmd line. – Xeoncross Sep 28 '09 at 18:17 Well, notice the you have exactly 2^16 warnings, which suggests that a counter got maxed out and you actually have more.

I'll get the error applies to every row. Are you sure that you got 800,000 rows inserted? What value do you have for the 'accuracy' column?

– tpdi Sep 28 '09 at 18:35 Thanks, I amended my post with this data. – Xeoncross Sep 28 '09 at 18:41.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.