MySQL Bugs: #240: LOAD DATA INFILE fails to give any details in case of error

Bug #240	LOAD DATA INFILE fails to give any details in case of error
Submitted:	7 Apr 2003 7:05	Modified:	8 Apr 2003 7:55
Reporter:	Bryce Nesbitt	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:		OS:
Assigned to:		CPU Architecture:	Any

Description:
"LOAD DATA" can be used to import text files (Spreadsheets, .csv, .txt, tab separated, excel files, etc.) into MySQL.  But if there are any warnings or errors during the import, all you get is a count of the number of bad lines.  There is no indication which line caused the problem!

Records: 1000000  Deleted: 0  Skipped: 2  Warnings: 1

How to repeat:
Import 1 millon records using LOAD DATA.
Chances are you'll have at least 1 or 2 errors.
Try to find those errors.

Suggested fix:
MySQL could simply log the full error.  The administrator needs to know which line of the input file, and which database key, created the problem.

See http://www.mysql.com/doc/en/LOAD_DATA.html

This is fixed in 4.1, where a SHOW WARNINGS command is introduced.

It would be nice to know more on this other than that it is "Closed".   I personally would like to see this extended to "warnings" and have a verbose option of some form in both LOAD DATA INFILE and via the commandline "mysql dbname < filename" data input methods so that "Warnings: 23" could show what those errors were, or at least what lines they occurred on.

this shouldn't be closed yet:
mysql> LOAD DATA INFILE "memberIDs.txt" REPLACE INTO TABLE profiles (memnum, password);
Query OK, 690832 rows affected (35 min 56.44 sec)
Records: 690823  Deleted: 9  Skipped: 0  Warnings: 690919

mysql> SHOW WARNINGS;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARNINGS' at line 1
mysql> SHOW WARNINGS limit 2;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARNINGS limit 2' at line 1
mysql> SHOW ERRORS limit 2;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ERRORS limit 2' at line 1
etc...

[root@localhost root]#  /usr/local/mysql/bin/mysql --version
/usr/local/mysql/bin/mysql  Ver 12.18 Distrib 4.0.12, for pc-linux (i586)

er nm I had 4.0.12 not 4.1

How can i load data infile in table without "Terminated" with "position" of the fields on file txt?

How can I set SHOW WARNINGS using mysqlimport command line ?

Same is probably true for mysqlimport and cat file | mysql -u ...

We have exported and imported a big database.
During the import using 'cat file | mysql -u ..." construction no errors or warnings where shown. Weeks later it came out that there had been (invisible) warnings.

For a serious platform this default behaviour is NOT alright :-(
By the way this is still the case in MySQL 5.0.18.