29

To start off, this is not about loading data from within MySQL itself, but using the command-line tool "mysqlimport".

I am using it to load a CSV directly into a table and need to see the warnings it has generated. I cannot seem to get warnings to display with verbose nor debugging turned on. Any ideas?

(MySQL 5.0.5)

Bryan Ash
  • 103
  • 4
brink
  • 293
  • 1
  • 3
  • 5

3 Answers3

31

It's not possible with mysqlimport, however as an alternative you can do the following:

mysql --execute="LOAD DATA LOCAL INFILE '$WORKDIR/$table.csv' INTO TABLE $table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES (listOfColumnNames); SHOW WARNINGS"

Replace listOfColumnNames with an appropriate seperated list of columns.

The magic is (as Eduard previously mentioned) is to execute the LOAD DATA INFILE and the SHOW WARNINGS commands together in the same session, as mysqlimport doesn't provide a way to get at the warnings directly.

Chris Stryczynski
  • 1,176
  • 2
  • 15
  • 23
shollyman
  • 656
  • 5
  • 5
  • Sweet, thank you! I had just begun writing a statement like this after seeing Eduard's comment. Thanks yo! Thanks – brink Dec 21 '09 at 19:50
  • Does this work if `tables.csv` is a local file (i.e., not on the server of the database)? – Raffi Khatchadourian Jun 07 '16 at 18:00
  • the following works for me create the table first then do `mysql -u root --execute="LOAD DATA LOCAL INFILE 'c:\\crp\\blah3.csv' INTO TABLE blah3 FIELDS TERMINATED BY ','; SHOW WARNINGS" whateverdbnameegcsv_db` related http://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile – barlop Oct 21 '16 at 15:59
  • and can add e.g. about utf8 `mysql> LOAD DATA LOCAL INFILE 'c:\\crp\\blah5.csv' INTO TABLE blah5 CHARACTER SET UTF8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'; SHOW WARNINGS;` as mentioned http://stackoverflow.com/questions/4957900/loading-utf-8-encoded-text-into-mysql-table – barlop Oct 24 '16 at 07:09
  • a problem is that the exit code is always `0` so I guess you have to parse the command output to check whether the import succeeded when the above is scripted (?) – pkaramol Feb 14 '19 at 15:22
5

Like the others said the real problem is that for SHOW WARNINGS to work you have to be in the same connected session. mysqlimport disconnects from the session as soon as it's finished importing, making the capture of it's warnings impossible in it's current version. Sounds like an opportunity to donate a patch. :)

Vinny
  • 151
  • 1
  • 2
1

I've found no way to print warnings with mysqlimport either. But since mysqlimport does nothing but "LOAD DATA INFILE" why don't you use the mysql command to import the data followed by a "SHOW WARNINGS"?

Eduard Wirch
  • 332
  • 4
  • 13