How does one list warnings from the 'mysqlimport' utility?

Question

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)

Solution 1

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.

Solution 2

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. :)

Solution 3

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

Comments

[brink]

Sweet, thank you! I had just begun writing a statement like this after seeing Eduard's comment. Thanks yo! Thanks

[brink]

Thanks! Kind of irritating mysqlimport doesn't really play nice :/

[Eduard Wirch]

Use the "SHOW WARNINGS;" command after "LOAD".

[Pradeesh Francis]

Does this work if tables.csv is a local file (i.e., not on the server of the database)?

[bar5um]

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/…

[bar5um]

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/…

[pkaramol]

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 (?)