MySQL Bugs: #1808: LOAD DATA INFILE AUTO_INCREMENT + IGNORE conflict

Bug #1808	LOAD DATA INFILE AUTO_INCREMENT + IGNORE conflict
Submitted:	11 Nov 2003 14:05	Modified:	13 Nov 2003 16:10
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.23.54	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
While loading records from a text-file, it's not possible to ignore duplicates when there's an auto_increment field in the table. Even when the auto_increment field is not part of the selected field-list that are loaded. As such, every record is treated as unique, so duplicates are loaded as well.

How to repeat:
Try this

Here's an example of the txt-file (post, key1, key2) 

20031105120000|k1|k2 
20031105120011|k1|k2 
20031105120000|k1|k3 

This is the table 

CREATE TABLE broker_inbound ( 
rid int(11) NOT NULL auto_increment, 
post timestamp(14) NOT NULL, 
cycle timestamp(14) NOT NULL, 
key1 char(32) NOT NULL default '', 
key2 char(32) NOT NULL default '', 
PRIMARY KEY (rid,post,key1,key2) 
) TYPE=MyISAM;

Here's the LOAD DATA INFILE cmd 

load data infile 'inbound.txt' 
ignore 
into table broker_inbound 
fields terminated by '|' 
(post,key1,key2);

Perform the LOAD cmd twice and you'll see that all records are inserted again.

Suggested fix:
Two suggestions:

[1] Allow an auto_increment field that is not a key
[2] Only mind the selected field-list in IGNORE / REPLACE judgement

This "feature" is necessary to track the load sequence of records from text-files.

IGNORE and REPLACE can only function if there is a unique index of some sort in order for duplicates to be identified.  You have no unique index on your three incoming columns, so this is functioning as designed.

Thank you.

I'd imagine that one can specify it's own unique key by specifying fields in the selected fields part. In this way, you can leave out the auto_increment field as part of the key.

Is there a workaround for this?