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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.