Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Duplicate entry 'xxx' for key 1

Posted on 2004-08-23
13
Medium Priority
?
974 Views
Last Modified: 2008-02-01
The following statement:

mysql> alter table tblContacts MODIFY ContactID INT AUTO_INCREMENT PRIMARY KEY;

Returns this result:

Duplicate entry '2124' for key 1

The this query:

mysql> select * from tblContacts where ContactID = 2124;

Retuns this along with the data from the table:

1 row in set (0.01 sec)


My question is this: If there is only one row in the table, and I'm not trying to insert, why does MySQL tell me that there are duplicates? I suppose the problem could be in my ALTER statement itself, but I'm not sure. I'm a MySQL n00b.

I've also tried to use the following format, but I get a syntax error in "UNSIGNED INT AUTO_INCREMENT PRIMARY KEY":

alter table [table] CHANGE [field] [field] UNSIGNED INT AUTO_INCREMENT PRIMARY KEY


Any and all suggestions are greatly appreciated.  Thanks in advance for your help.

0
Comment
Question by:lewisig
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11871199
One possibility, from http://dev.mysql.com/doc/mysql/en/ALTER_TABLE_problems.html :
If you get a duplicate-key error during ALTER TABLE, the cause is either that the new character sets maps two keys to the same value or that the table is corrupted. In the latter case, you should run REPAIR TABLE on the table.

If REPAIR TABLE doesn't do anything, you could try adding a new column that is auto_increment, update it with the values from the old column, and then drop the old column and rename the new one.

ALTER TABLE tblContacts ADD COLUMN newId INT AUTO_INCREMENT PRIMARY KEY;
UPDATE tblContacts SET newId = ContactID;
etc.
0
 
LVL 1

Author Comment

by:lewisig
ID: 11871650
Thank you snoyes_jw for your prompt response. I'd already tried REPAIR TABLE which didn't fix the problem.

I just tried your other solution, and the UPDATE statement returns the following error:

ERROR 1062: Duplicate entry '1953' for key 1

This  query -> select * from tblContacts where ContactID = 1953; returns only one row and shows that newId = 1

0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11871720
True, I wouldn't expect it to work, now that I think about it - you're bound to update an entry to an already existing value.  You might try creating a new table with the same structure as tblContacts, except with the auto_increment defined on ContactID, and then INSERT...SELECT everything from tblContacts into this new table.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 1

Author Comment

by:lewisig
ID: 11871932
Ok, I tried the insert select into the new table which gives the same error - Error: Duplicate entry '2124' for key 1

Then I tried just dropping the whole table, recreating the table with the auto_increment and primary key options already on, then re-importing the data from the file, which is where the data came from originally using mysqlimport, which returns with - mysqlimport: Error: Duplicate entry '2124' for key 1, when using table: tblContacts

Here's the first part of the create table statement:

create table tblContacts (
ContactID int auto_increment primary key, ...

In case anyone was wondering, I've tried removing the offending row and I just get the same error on another row.  
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11872009
Hmm...perhaps you could try importing using --replace or --ignore, and then check the difference between the result and a table without the auto_increment, to see which records are causing the duplication.
0
 
LVL 1

Author Comment

by:lewisig
ID: 11872096
I've already successfully imported the data without the auto_increment or primary key options on, and those select statements that I posted above show that there are no duplicates (1 row returned). A repair table has shown that there no errors in the table either, so I don't get it. There are no duplicates, but MySQL is reporting that there are.

wtf

0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11872317
Well, import without auto_increment, then import with auto_increment and ignore duplicates, then count rows in each and see if it's different.
0
 
LVL 1

Author Comment

by:lewisig
ID: 11872448
I'm looking for examples of ignore within an alter table statement, but Google is failing me. Can you IGNORE during an alter table?

0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11872456
No, I don't think so.  You'll do the ignore during the import.
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 2000 total points
ID: 11872500
Well, on reading the documentation, IGNORE is legal.

ALTER IGNORE TABLE blah blah blah

http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html
0
 
LVL 1

Author Comment

by:lewisig
ID: 11872581
Woo-hoo, the IGNORE worked. I wish I'd know about that command when I was first importing the data, it would've saved me a bunch of time. Thanks much, snoyes_jw!

0
 
LVL 1

Author Comment

by:lewisig
ID: 11872626
Oh just to clarify, I used the IGNORE during the import, and the total number of records is exactly the same.

The MySQL guide states that IGNORE during an import will not add any records that violate the contraints.

Strange.

0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11872696
Strange indeed.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question