MySQL Bugs: #40334: LOAD DATA INFILE - Skipping rows in large data files

Bug #40334	LOAD DATA INFILE - Skipping rows in large data files
Submitted:	26 Oct 2008 14:09	Modified:	29 Oct 2008 6:39
Reporter:	Greg Hicks	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	5.0.67 community nt	OS:	Windows (XP SP 2)
Assigned to:		CPU Architecture:	Any
Tags:	LOAD DATA skips records

Description:
When running this feature from a script, I noticed a number of errors cropping up.

Comparing data extracted from Solid Server I found a number of records missing from tables with more than 50 records.

I try this in Query Browser and I can see a number of errors run up the screen which relate to data and table misalignment. This sample has 1464 records, but only 833 load correctly.

LOAD DATA
INFILE 'd:\\develop\\CMIS\\CMISDAT\\CMIS_CUSTODIAN.dat'
IGNORE
INTO TABLE CUSTODIAN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'";

'(NAS)ELATS          ','(',NULL,NULL,'RAAF                ','FLT                 ',NULL,'AIR                 ','NOWR                ',NULL,NULL,NULL,'AVIONICS SECTION              ','NAS NOWRA                     ',NULL,NULL,'CMIS User   ','2000-06-15 07:58:57',NULL,'2005-03-03 12:27:00'
'01                  ','%','TENIX TRAINING AID FUNCTIONAL TEST CENTRE         ','1808','NAVY                ','EST                 ','CER                 ','TRG                 ','CERB                ','3026',NULL,NULL,'FACILITIES BUILDING           ','HMAS CERBERUS                 ','WESTERN POINT VIC 3920        ','DENNIS NOLAN                  ','DEVELOPER   ','1997-03-06 10:46:38',NULL,'2002-09-18 15:23:20'
'02                  ','!','HMAS CERBERUS BLD 28 SYS                          ',NULL,'NAVY                ','EST                 ','CER                 ','TRG                 ','CERB                ','3026',NULL,NULL,'SAME AS 01                    ',NULL,NULL,NULL,'DEVELOPER   ','1997-03-06 10:46:39','Noel        ','2000-11-22 10:41:05'
'04                  ','!','HMAS CERBERUS RADIO HF                            ',NULL,'NAVY                ','EST                 ','CER                 ','TRG                 ','CERB                ','3026',NULL,NULL,'SAME AS 01                    ',NULL,NULL,NULL,'DEVELOPER   ','1997-03-06 10:46:41','Noel        ','2000-11-22 10:41:05'
'05                  ','!','HMAS CERBERUS RADIO UHF                           ',NULL,'NAVY                ','EST                 ','CER                 ','TRG                 ','CERB                ','3026',NULL,NULL,'SAME AS 01                    ',NULL,NULL,NULL,'DEVELOPER   ','1997-03-06 10:46:42','Noel        ','2000-11-22 10:41:05'
'06                  ','!','HMAS CERBERUS SECURE VOICE                        ',NULL,'NAVY                ','EST                 ','CER                 ','TRG                 ','CERB                ','3026',NULL,NULL,'SAME AS 01                    ',NULL,NULL,NULL,'DEVELOPER   ','1997-03-06 10:46:42','Noel        ','2000-11-22 10:41:05'
'07                  ','!','HMAS CERBERUS SIGNAL CONV/CRH-11                  ',NULL,'NAVY                ','EST                 ','CER                 ','TRG                 ','CERB                ','3026',NULL,NULL,'SAME AS 01                    ',NULL,NULL,NULL,'DEVELOPER   ','1997-03-06 10:46:43','Noel        ','2000-11-22 10:41:06'
'08                  ','!','HMAS CERBERUS BLD 32                              ',NULL,'NAVY                ','EST                 ','CER                 ','TRG                 ','CERB                ','3026',NULL,NULL,'SAME AS 01                    ',NULL,NULL,NULL,'DEVELOPER   ','1997-03-06 10:46:44','Noel        ','2000-11-22 10:41:06'
'09                  ','!','LOST INSTRUMENTS CEREBERUS                        ',NULL,'NAVY                ','EST                 ','CER                 ','TRG                 ','CERB                ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'DEVELOPER   ','1997-03-06 10:45:37','Noel        ','2000-11-22 10:41:06'

manually applying a missing record with insert does not cause an error.

How to repeat:
Ask me for a copy of the scripts and files (too big to pass in here).

Suggested fix:
Repeated attempts suggest that thread synchronisation is root cause.

Database structure

Attachment: cmiscreate.sql (application/octet-stream, text), 168.14 KiB.

Thank you for the bug report. See below command:

mysql 5.0 > LOAD DATA
    -> INFILE 'C:\\BUG\\CMIS_CUSTODIAN.dat'
    -> IGNORE
    -> INTO TABLE CUSTODIAN
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" LINES TERMINATED BY '\r\n';
Query OK, 1464 rows affected (0.13 sec)
Records: 1464  Deleted: 0  Skipped: 0  Warnings: 0

mysql 5.0 >

HI and thanks

I see that I should have used "lines terminated by".  I'll retry the database with that change.

Hi

only one table has skipped 73 records one other 1 record, no doubt a prob with the data.

Thanks for your assistance :)