MySQL Bugs: #5835: load data infile...replace error with InnoDB table

Bug #5835	load data infile...replace error with InnoDB table
Submitted:	1 Oct 2004 0:17	Modified:	21 Dec 2004 17:52
Reporter:	Patrick Kenny	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.20, 4.1.4	OS:	Any (any)
Assigned to:		CPU Architecture:	Any

Description:
I am getting an unexpected error after multiple 'load data infile...replace' statements into a InnoDB table with an auto_increment primary key.

I do not get these errors when I do the same statements into a MyISAM table.

I do not get these errors when using 'replace ....' statement instead of 'load data infile...replace'

How to repeat:
Create a file /var/tmp/foo.txt composed of:
--BEGIN--
foo	bar	foobar
--END--

Execute the following sql commands:
create table foo (
  foo_id int(10) unsigned NOT NULL auto_increment,
  `a` varchar(200) NOT NULL default '',
  `b` varchar(200) NOT NULL default '',
  `d` varchar(200) NOT NULL,
  `updated_tm` timestamp(14) NOT NULL,
  PRIMARY KEY  (`foo_id`),
  UNIQUE KEY `u_a_b` (`a`,`b`)
) type=InnoDB;

LOAD DATA INFILE '/var/tmp/foo.txt' REPLACE INTO TABLE foo (a,b,d);
LOAD DATA INFILE '/var/tmp/foo.txt' REPLACE INTO TABLE foo (a,b,d);
LOAD DATA INFILE '/var/tmp/foo.txt' REPLACE INTO TABLE foo (a,b,d);

I expect all the load data statements to succeed, but the third 'load data infile' will fail with the error:
ERROR 1062: Duplicate entry '2' for key 1

Hi!

Fixed in 4.1.7 and 4.0.23.

InnoDB forgot in ::write_row, in auto-incs, that LOAD DATA ... REPLACE is a kind of REPLACE, and handles duplicate key errors itself.

Thank you,

Heikki

Jan Lindström has now been added to bugs.mysql.com as a MySQL developer. Assigning this bug report to him.

--Heikki