MySQL Bugs: #31975: LOAD DATA LOCAL INFILE - Duplicate entry error should come

Bug #31975	LOAD DATA LOCAL INFILE - Duplicate entry error should come
Submitted:	31 Oct 2007 7:36	Modified:	1 Sep 2009 13:19
Reporter:	Ashish Parkhi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S4 (Feature request)
Version:	mysql-connector-java-3.1.8-bin.jar	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If I use LOAD DATA INFILE query to insert data into a table, and if there are duplicate records, it gives an error
Error: Duplicate entry 'xyz' for key 1 (State:23000, Native Code: 426)

But If I use LOAD DATA LOCAL INFILE query to insert data into a table, and if there are duplicate records, it does not give an error. It returns no of rows affected as 0.

If it has to be handled programatically, then I need to know how many records I am trying to insert and how many records actually got inserted. Only after comparison I will come to know if there was any issue inserting the data.

It would be gr8 if LOAD DATA LOCAL INFILE query also fails with "Duplicate entry " error instead of executing successfully with 0 records affected.

How to repeat:
1) execute once
LOAD DATA INFILE 'd:/ashish/somefile.txt'
into TABLE sometable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\''
LINES TERMINATED BY '-\n' 
Result Successful e.g. inserted 100 records
2) execute above again, will give "Duplicate entry" exception. (Hoping primary and unique keys defined.)

Now

1) delete all records from table "sometable". execute once
LOAD DATA LOCAL INFILE 'd:/ashish/somefile.txt'
into TABLE sometable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\''
LINES TERMINATED BY '-\n' 
Result Successful e.g. inserted 100 records.
2) execute above qury again, it will not fail. Will return inserted 0 records.

This report is misfiled under c/J section while it's actually mysql client.
The behavior of LOAD DATA is described in http://dev.mysql.com/doc/refman/5.1/en/load-data.html. Particular case is described as:
  With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation. IGNORE is explained further later in this section. 

and

  If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you do not specify either option, the behavior depends on whether the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation. 

So, if you wish, you might change options in LOAD DATA or file a mysql client feature request.

Behavior is well documented and configurable thus !Bg.