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)

3 Answers 3

32

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.

5
  • Sweet, thank you! I had just begun writing a statement like this after seeing Eduard's comment. Thanks yo! Thanks
    – brink
    Dec 21, 2009 at 19:50
  • Does this work if tables.csv is a local file (i.e., not on the server of the database)?
    – khatchad
    Jun 7, 2016 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 stackoverflow.com/questions/14127529/…
    – barlop
    Oct 21, 2016 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 stackoverflow.com/questions/4957900/…
    – barlop
    Oct 24, 2016 at 7: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, 2019 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. :)

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"?

2
  • Thanks! Kind of irritating mysqlimport doesn't really play nice :/
    – brink
    Dec 21, 2009 at 19:51
  • Use the "SHOW WARNINGS;" command after "LOAD". Dec 27, 2009 at 15:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .