Getting LOAD DATA to Cough Up More Information
Problem
LOAD
DATA
doesn’t tell you much about problems in the
datafile.
Solution
There is no solution. Well, maybe there is.
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
andSkipped
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, andSkipped
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 stringabc
into a numeric column results in a stored value of0
, 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 ...
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.