MySQL Cookbook

A LOAD DATA Diagnostic Utility

Problem

LOAD DATA or mysqlimport indicates a nonzero warning count when you load a datafile into MySQL, but you have no idea which rows or columns were problematic.

Solution

Run the file through a utility that diagnoses which data values caused the warnings.

Discussion

As a bulk loader, LOAD DATA is very efficient; it can run many times faster than a set of INSERT statements that adds the same rows. However, LOAD DATA also is not very informative. It returns only a message that indicates the number of records processed, and a few other status counts. For example, in the previous section, we generated a datafile managers.txt to use with guess_table.pl for guessing the structure of the baseball1.com managers table. If you create that table using the resulting CREATE TABLE statement and then load the datafile into it, you will observe the following result:

mysql> LOAD DATA LOCAL INFILE 'managers.txt' INTO TABLE managers
    -> IGNORE 1 LINES;
Query OK, 2841 rows affected (0.06 sec)
Records: 2841  Deleted: 0  Skipped: 0  Warnings: 5082

Evidently, there were a quite a few problems with the file. Unfortunately, the message produced by LOAD DATA doesn’t tell you anything about which rows and columns caused them. The mysqlimport program is similarly terse, because its message is the same as the one returned by LOAD DATA.

We’ll revisit this example at the end of the section, but first consider LOAD DATA’s output style. On the one hand, the minimal-report approach is the ...