ProblemWhen you issue a LOAD DATA statement, you want to know whether any input values are bad and what's wrong with them. SolutionUse 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. DiscussionWhen 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:
What do these values tell you? The Records value normally should match the number of lines in the input file. If it is different from 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.) Assuming that your FIELDS and LINES format specifiers are correct, a nonzero Warnings count indicates the presence of bad input values. You can't tell from the numbers in the LOAD DATA information line which input records had problems or which columns were bad. To get that information, issue a SHOW WARNINGS statement. Suppose that a table t has this structure: CREATE TABLE t ( i INT, c CHAR(3), d DATE ); And suppose that a datafile data.txt looks like this: 1 1 1 abc abc abc 2010-10-10 2010-10-10 2010-10-10 Loading the file into the table causes a number, a string, and a date to be loaded into each of the three columns. Doing so results in a number of data conversions and warnings: mysql> LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t; Query OK, 3 rows affected, 5 warnings (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 5 To see the warning messages, use SHOW WARNINGS immediately after the LOAD DATA statement: mysql> SHOW WARNINGS; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1265 | Data truncated for column 'd' at row 1 | | Warning | 1264 | Out of range value for column 'i' at row 2 | | Warning | 1265 | Data truncated for column 'd' at row 2 | | Warning | 1265 | Data truncated for column 'i' at row 3 | | Warning | 1265 | Data truncated for column 'c' at row 3 | +---------+------+--------------------------------------------+ 5 rows in set (0.00 sec) The SHOW WARNINGS output helps you determine which values were converted and why. The resulting table looks like this: mysql> SELECT * FROM t; +------+------+------------+ | i | c | d | +------+------+------------+ | 1 | 1 | 0000-00-00 | | 0 | abc | 0000-00-00 | | 2010 | 201 | 2010-10-10 | +------+------+------------+ |