MYSQL: Display Skipped records after LOAD DATA INFILE? – This article will take you through the common SQL errors that you might encounter while working with , , . The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
In MySQL I’ve used LOAD DATA LOCAL INFILE which works fine. At the end I get a message like:
Records: 460377 Deleted: 0 Skipped: 145280 Warnings: 0
How can I view the line number of the records that were skipped? SHOW warnings doesn’t work:
mysql> show warnings;
Empty set (0.00 sec)
Solution :
If there was no warnings, but some rows were skipped, then it may mean that the primary key was duplicated for the skipped rows.
The easiest way to find out duplicates is by openning the local file in excel and performing a duplicate removal on the primary key column to see if there are any.
You could create a temp table removing the primary key items so that it allows duplications, and then insert the data.
Construct a SQL statement like
select count(column_with_duplicates) AS num_duplicates,column_with_duplicates
from table
group by column_with_duplicates
having num_duplicates > 1;
This will show you the rows with redundancies. Another way is to just dump out the rows that were actually inserted into the table, and run a file difference command against the original to see which ones weren’t included.
For anyone stumbling onto to this:
Another option would be to do a SELECT INTO and diff the two files. For example:
LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE my_table FIELDS TERMINATED BY 't' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'r' IGNORE 1 LINES (title, desc, is_viewable)
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.