MySQL Cookbook, 2nd Edition

Obtaining Diagnostics About Bad Input Data

Problem

When you issue a LOAD DATA statement, you want to know whether any input values are bad and what’s wrong with them.

Solution

Use the information line displayed by LOAD DATA to determine whether there are any problematic input values. If so, use SHOW WARNINGS to find where they are and what the problems are.

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 that 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 rows on unique index values. Deleted indicates how many rows were deleted from the table and replaced by input records, and Skipped indicates how many input records were ignored in favor of existing rows.

• Warnings is something of a catchall 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 ...