SEO

Hi,
I just loaded 71,000 records using "load data infile" and when done it
gave no warnings, but said it skipped 4. Is there any record anywhere
of which 4 records didn't get loaded or any other info about it?
I know about the show warnings, but didn't find any "show skipped"
command?
I have also searched all the server log files. I am using mysqld
4.1.11 and Innodb.

I am assuming that either the data had some bad syntax, or that
referential integrity wasn't met. I didn't "set foreign_key_checks=0"
..

If the 4 records got skipped because of referential integrity checks,
will it say so, or does it always only give the message about
"deleted:0 skipped:4 warnings:0"

thanks in advance..
Roshan

roch77@gmail.com wrote:
> Hi,
> I just loaded 71,000 records using "load data infile" and when done it
> gave no warnings, but said it skipped 4.

The docs refer to two cases where input rows may be "skipped." It's not
clear which case contributes to the "skipped" total in the load report,
or if both do.

In one case, if you specify the IGNORE keyword in your LOAD DATA
statement, rows are skipped if they contain values that would conflict
with existing primary key values in the table.

The docs also use the term "skipped" for the case when a row of the
input file doesn't match a prefix string you specify in your LOAD DATA
statement. Here's an excerpt from the doc page
http://dev.mysql.com/doc/refman/5.0/en/load-data.html:

----
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';

If the data file looks like this:

xxx"abc",1
something xxx"def",2
"ghi",3

The resulting rows will be ("abc",1) and ("def",2). The third row in the
file is skipped because it does not contain the prefix.
----

To test which rows were not imported, I can suggest creating a new table
`T2` with identical structure to the table `T` into which you just
loaded the file, but without any existing data, or primary key or
foreign key constraints. Then load your file into that table, and look
for "skipped: 0" in the report. Then run a query like the following to
find mismatches:

SELECT T2.*
FROM T RIGHT OUTER JOIN T2 ON T.primarykey = T2.primarykey
WHERE T.primarykey IS NULL;

Note this will not find cases where the rows loaded into T were skipped
because of the primary key conflict case I mentioned above. There will
be records in both tables with the same primary key, but the attribute
columns may have different values. So if the query above finds no
differences, try this:

SELECT T2.*
FROM T INNER JOIN T2 ON T.primarykey = T2.primarykey
WHERE T.othercolumn <> T2.othercolumn OR T.thirdcolumn <> T2.thirdcolumn
OR ...etc...;

Regards,
Bill K.

Thanks a lot Bill for your reply. After reading your post, I decided
to try to make sure my primary key was unique for each row. Sure
enough I got 4 rows with duplicate entries, so that was my problem.
Back to the drawing board to figure out the primary key.

Roshan