10.9 Getting LOAD DATA to Cough Up More Information
10.9.1 Problem
LOAD DATA
doesn't tell you much about problems in the
datafile.
10.9.2 Solution
There is no solution. Well, maybe there is.
10.9.3 Discussion
When a LOAD DATA statement
finishes, it returns a line of information that tells you how many
errors or data conversion problems occurred. Suppose you load a file
into a table and see the following message when
LOAD DATA finishes.
Records: 134 Deleted: 0 Skipped: 2 Warnings: 13
These values provide some general information about the import
operation:
Records indicates the number of records found in
the file.
Deleted and Skipped are related
to treatment of input records that duplicate existing table records
on unique index values. Deleted indicates how many
records were deleted from the table and replaced by input records,
and Skipped indicates how many input records were
ignored in favor of existing records.
Warnings is something of a catch-all that
indicates the number of problems found while loading data values into
columns. Either a value stores into a column properly, or it
doesn't. In the latter case, the value ends up in
MySQL as something different and MySQL counts it as a warning.
(Storing a string abc into a numeric column
results in a stored value of 0, for example.)
What do these values tell you? The Records value
normally should match the number of lines in the input file. If it is
different than the file's line count,
that's a sign that MySQL is interpreting the file as
having a format that differs from the format it actually has. In this
case, you're likely also to see a high
Warnings value, which indicates that many values
had to be converted because they didn't match the
expected data type. (The solution to this problem often is to specify
the proper FIELDS and LINES
clauses.) Otherwise, the values may not tell you a lot. You
can't tell from these numbers which input records
had problems or which columns were bad. There is some work being done
for MySQL 4 to make additional warning information available. In the
meantime, see Recipe 10.38 for a script that examines
your datafile and attempts to pinpoint troublesome data values.
|
