| Bug #2027 | Error "Duplicate entry ... for key ..." where not expected | ||
|---|---|---|---|
| Submitted: | 5 Dec 2003 13:27 | Modified: | 22 May 2005 16:22 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S1 (Critical) |
| Version: | 4.1.1 (01 Dec 2003) | OS: | Linux (GNU/Linux) |
| Assigned to: | Sergei Golubchik | CPU Architecture: | Any |
[5 Dec 2003 13:39]
Dean Ellis
This looks to be a duplicate of #1721, however as a test case is presented here I will not mark it as such yet. What character set are you using?
[5 Dec 2003 15:47]
Sergei Golubchik
I cannot repeat it, My setup: mysql> show variables like '%chara%'; +--------------------------+-----------------------------------------------+ | Variable_name | Value | +--------------------------+-----------------------------------------------+ | character_set_server | latin1 | | character_set_system | utf8 | | character_set_database | latin1 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_results | latin1 | +--------------------------+-----------------------------------------------+
[5 Dec 2003 18:03]
[ name withheld ]
mysql> show variables like '%chara%'; +--------------------------+----------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------+ | character_set_server | latin1 | | character_set_system | utf8 | | character_set_database | latin1 | | character_set_client | latin1 | | character_set_connection | latin1 | | character-sets-dir | /usr/local/mysql/share/mysql/charsets/ | | character_set_results | latin1 | +--------------------------+----------------------------------------+ 7 rows in set (0.01 sec)
[9 Feb 2004 20:18]
Steven Roussey
I have this problem as well. I can't seem to be able to do any REPLACE INTO ... statements. They all come back as "Duplicate entry" errors. Should it not have these errors when there is a "REPLACE" instead of an insert? MySQL 4.1.1
[18 Mar 2004 3:58]
NOT_FOUND NOT_FOUND
I've got the same error on 4.1.1 while loading data from sql script into table with fulltext index. I'm able to replicate this error on my linux and freebsd box, it is similar, but not the same. Everything is ok, when I remove fulltext index declaration from create table statement, create table, load data and then index it. This is a short description of the bug: The data I'm loading can be downloaded from http://skni.sgh.waw.pl/mysql-bug.tar.gz (5MB). I have also included my.cnf files and config.logs from mysql builds, where you can find exact configuration of my systems. 1) on linux: # mysql -p test < songs.sql Enter password: ERROR 1062 at line 7634: Powtórzone wyst?pienie 'I Remember That Freak Bitch-A' dla klucza 3 (duplicate entry 'I Remember That Freak Bitch-A' for key 3) 2) freebsd # mysql test < songs.sql ERROR 1062 at line 5627: Powtórzone wyst?pienie 'Hand On The Pump-V' dla klucza 3 (duplicate entry 'Hand On The Pump-V' for key 3) As you can see the data is the same, but error ocurrs in different places.
[9 Apr 2004 7:53]
Sergei Golubchik
I still cannot repeat it, using the data/settings provided :( Try 4.1.2 when it will be out or - as you compile yourself anyway, using our daily source snapshot
[21 Apr 2004 6:46]
NOT_FOUND NOT_FOUND
Sergei, I've compiled snapshot of 4.1.2alpha on freebsd and now the bug doesn't appear. I didn't check it on linux, but it seems, that it has been fixed in 4.1.2
[20 May 2004 22:39]
Roman Karshiev
I have some bug also. Simply create table with FULLTEXT index in utf8 or cp1251 encoding and after inserting like ~25000 records have "Duplicate entry..." error.
[2 Sep 2004 10:37]
[ name withheld ]
I have a similar problem loading data using version 4.1.4 gamma. The command: load data concurrent infile '<filename>' replace into table <tablename> fields escaped by '' lines terminated by '\r'; exits with the error: ERROR 1062 (23000): Duplicate entry '12835265' for key 1 It seems that the replace option is ignored !! (12835265 is the primary key of an already present row) mysql version: 4.1.4 gamma After removing fulltext indexes from the table the "load data ... replace" ran well.
[15 Dec 2004 2:39]
John Kielkopf
I'm having this problem using Mysql 4.1.7 x86_64 under linux.
I'm doing a replace into a large table (>500,000 records), from multiple tables.
Target table looks like:
keywords CREATE TABLE `keywords` (
`isbn_id` int(11) NOT NULL default '0',
`title` text,
`authors` text,
`annotations` text,
`subjects` text,
`series` text,
`publisher` text,
`isbn` text,
PRIMARY KEY (`isbn_id`),
FULLTEXT KEY `keywords` (`title`,`authors`,`annotations`,`subjects`,`isbn`,`series`,`publisher`),
FULLTEXT KEY `title` (`title`),
FULLTEXT KEY `authors` (`authors`),
FULLTEXT KEY `subjects` (`subjects`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
The statement I'm using is:
replace into keywords
(select i.isbn_id,
trim(CONCAT_WS(' ', i.leading_article, i.title, i.sub_title)) as title,
GROUP_CONCAT(DISTINCT at.author_full SEPARATOR ' ') as authors,
CONCAT_WS(' ',
GROUP_CONCAT(DISTINCT an.annotation SEPARATOR ' '),
GROUP_CONCAT(DISTINCT pd.products_notes SEPARATOR ' ')
) as annotations,
CONCAT_WS(' ',
GROUP_CONCAT(DISTINCT bs.bisac_desc SEPARATOR ' '),
GROUP_CONCAT(DISTINCT ac.academic_desc SEPARATOR ' '),
GROUP_CONCAT(DISTINCT ls.library_subject SEPARATOR ' ')
) as subjects,
sr.series as series,
pb.publishers_name as publisher,
i.isbn
from products p, isbns i
left outer join products_description pd on (pd.products_id = p.products_id)
left outer join annotations an on (an.isbn_id = p.isbn_id)
left outer join author_to_isbn a2i on (a2i.isbn_id = p.isbn_id)
left outer join authors at on (at.author_id = a2i.author_id)
left outer join bisac_to_isbn b2i on (b2i.isbn_id = p.isbn_id)
left outer join bisac bs on (bs.bisac_id = b2i.bisac_id)
left outer join academic_to_isbn ac2i on (ac2i.isbn_id = p.isbn_id)
left outer join academic ac on (ac.academic_id = ac2i.academic_id)
left outer join library_subject_to_isbn l2i on (l2i.isbn_id = p.isbn_id)
left outer join library_subjects ls on (ls.library_subject_id = l2i.library_subject_id)
left outer join series sr on (sr.series_id = i.series_id)
left outer join publishers pb on (pb.publishers_id = i.publishers_id)
where i.isbn_id = p.isbn_id
and p.products_status = 1
group by p.isbn_id)
The select statment runs fine on it's own, and when used with replace into is able to fill an empty table just fine. Only when running it again does it fail.
This is quite a brick wall I've run into. Any help would be appreciated.
[15 Dec 2004 8:22]
Sergei Golubchik
Quoting http://dev.mysql.com/doc/mysql/en/News-4.1.8.html D.2.2 Changes in release 4.1.8 (to be released soon) ... Bugs fixed: ... * 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. (Bug #6784)
[22 May 2005 16:02]
Cheng Tan
I continue to see this bug with 4.1.12, Windows build. The table has two columns marked as primary keys.
[22 May 2005 16:22]
Sergei Golubchik
it's not the same bug, please submit a separate bugreport.
[22 May 2005 18:19]
Cheng Tan
Hi Sergei, My bad - I tracked it down to a programming error. Very sorry about this.
[27 Apr 2006 20:41]
Jaap van Vliet
It seems to me I have the same problem. I've got a table like this CREATE TABLE `login_rechten` ( `id` int(11) NOT NULL auto_increment, `naam` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ; and I want to excecute the following query ALTER TABLE `login_koppel` DROP PRIMARY KEY, CHANGE `login_id` `login_id` int(11) NOT NULL DEFAULT '0' FIRST, CHANGE `recht_id` `recht_id` int(11) NOT NULL DEFAULT '0' AFTER `login_id`, ADD PRIMARY KEY (`recht_id`), TYPE=MYISAM PACK_KEYS=DEFAULT ROW_FORMAT=DEFAULT then I get the error #1062 - Duplicate entry '1' for key 1 can anyone help me?
[27 Apr 2006 20:44]
Jaap van Vliet
I'm very sorry. I'm using 5.0.18 and this topic is about 4.1.1... :S
[14 Jun 2007 13:25]
Ben Hermer
Just a comment for anyone finding this, this happened to me, and it was due to a crach during an insert. I Dumped the DB to disk using: mysqldump -uroot -p**** --opt DbName>D:\domains\*****\wwwroot\rpm\backup\*****.sql then just deleted the DB, created an empty DB with the same name and loaded the dump file. Hope it helps!!
[15 Feb 2011 16:42]
John Harrison
i am getting this duplicate key error when using replace option on the load data infile I am running server version: 5.1.52 but am calling it from a bash script mysql -u john -e "set foreign_key_checks=0; use packs; load data local infile '/tmp/$filename' replace INTO TABLE $2 FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\r\n' (code, product_id);"; this throws the following error ERROR 1062 (23000) at line 1: Duplicate entry '85694e353d34b4ab284970f22e3bcd66' for key 'idx_code'
Description: there is tmpidx table: create table tmpidx ( id bigint primary key auto_increment NOT NULL, host int, dir bool, path text, clear_path text, size int, idxDate DATETIME, FULLTEXT(clear_path), INDEX index_host (host), INDEX index_idxDate (idxDate) }; "cr" file attached. mysql> source cr [......] Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) ERROR 1062 (23000): Duplicate entry ' ' for key 4 mysql> How to repeat: