Bug #1145 LOAD DATA INFILE" and "REPLACE|IGNORE" keywords
Submitted: 26 Aug 2003 22:38 Modified: 23 Dec 2004 13:21
Reporter: Winfried Kaiser Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.1 OS:Windows (WINDOWS, UNIX)
Assigned to: CPU Architecture:Any

[26 Aug 2003 22:38] Winfried Kaiser
Description:
On "LOAD DATA INFILE" the "REPLACE|IGNORE" keywords do not seem to work as expected.

If I import the same CSV-file twice, I get two times the number of records.

How to repeat:
Import any CSV-file into any table.
[27 Aug 2003 4:21] Sergei Golubchik
what is the table structure ?

(note that REPLACE/IGNORE clauses are only meaningful if there's UNIQUE or PRIMARY key)
[23 May 2004 20:17] H Y
In mySQL4.1.1, I have a table with an auto_increment '_index' column (primary key) and a unique 'url' column. When using LOAD DATA INFILE 'file' REPLACE ... I get this error:

ERROR 1062 : Duplicate entry 'http://www.codeur.org/forum/edit_msg.php?ID=7018&theme=17&css=http://membres.lycos.fr/rebo...' for key 2

It's always key 2 that crashes. Here's SHOW CREATE:
CREATE TABLE `reburls` (
  `clickedTimes` int(11) NOT NULL default '0',
  `title` text,
  `lastModified` varchar(100) default '2003-06-02 22:59:00',
  `eTag` varchar(100) default '',
  `URL` text NOT NULL,
  `lastChecked` datetime NOT NULL default '2003-06-02 22:59:00',
  `unretrievedTimes` int(1) unsigned NOT NULL default '0',
  `mimeType` varchar(100) default '',
  `HTTPHeader` text,
  `content` longtext,
  `metaDescription` text NOT NULL,
  `metaKeywords` text NOT NULL,
  `lastClicked` datetime NOT NULL default '2003-06-02 22:59:00',
  `_index` int(11) NOT NULL auto_increment,
  `rebolHeader` tinyint(1) NOT NULL default '0',
  `pluginable` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`_index`),
  UNIQUE KEY `Unike URL-er` (`URL`(255)),
  KEY `lastClicked` (`lastClicked`),
  KEY `lastChecked` (`lastChecked`),
  FULLTEXT KEY `ekstra fulltekst: url og tittel` (`title`,`URL`,`metaDescription`,`metaKeywords`),
  FULLTEXT KEY `cont` (`URL`,`title`,`metaDescription`,`metaKeywords`,`content`)
) TYPE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED; 

Since I use the REPLACE keyword, this shouldn't happen, right? Maybe this is what ?? meant when posting this bug?
[22 Nov 2004 19:23] H Y
What feedback do you need for this bug? Seems quite easy to reproduce, if you ask me (but I guess you don't). And I am not allowed to look at the progress log... sigh...
[22 Nov 2004 20:47] Sergei Golubchik
What we need is the repeatable test case. In this case it is the table structure (that we have), LOAD DATA statement (ok, let's say we have it too), the file to load (we don't have it).
[24 Nov 2004 18:24] H Y
OK, nice! Just send me an email, (so that I get a return address), and I'll send you all you need.
[24 Nov 2004 19:15] Sergei Golubchik
You can upload the test case to our ftp 

ftp://ftp.mysql.com/pub/mysql/upload/

please make sure that filename includes bug number
(e.g. bug-1145.tar.gz)
[13 Dec 2004 23:36] H Y
OK, I'm working on it. I _could_ put a file there already, but I'm testing a bit first, so that I can cut away all the extra stuff from my table that doesn't have to be there for you to be able to reproduce the bug. (If you would like the whole big thing later on, I'll give it to you).

You may expect a bug-1145.tar.gz sometime in january, I think.
[14 Dec 2004 10:47] Sergei Golubchik
Great!
Thank you.

(by the way, if the version is, indeed, 4.1.1, you may want to try the latest release too)
[22 Dec 2004 22:46] H Y
OK, I have something now. I would still have removed unnecessary stuff from the files, so that the gzipped tarball wouldn't be 30Mb. But then again, I have discovered that the bug is fixed, so I won't bother stripping it down further.

Looking at http://lists.mysql.com/announce/244 («Fixed spurious "duplicate key" error from REPLACE or INSERT ... ON DUPLICATE KEY UPDATE statements performing multiple-row insert in the table that had unique and fulltext indexes.») I think this bug must have been related to bug #6784: http://bugs.mysql.com/bug.php?id=6784, which is now fixed.

I found the bug not to be limited to files with more than 99 update entries. Mine had 50, generally. I can reproduce the bug on 4.1.7 (which I have been using for a while), but not on 4.1.8 (with wich I therefore am very much satisfied).

If you would still like to see the files, please send me an email about it. I cannot promise I will return to this page.

Thanks.
[22 Dec 2004 22:49] H Y
Me again, sorry.

Forgot to say that my table also had both a unique and two fulltext indexes. So that's (also) why I think it might be related to #6784.
[23 Dec 2004 13:21] MySQL Verification Team
Duplicate of #6784