Solved

Duplicate entry 'xxx' for key 1

Posted on 2004-08-23
13
956 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
  • 7
  • 6
13 Comments
 
LVL 33

Expert Comment

by:snoyes_jw
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:lewisig
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 33

Expert Comment

by:snoyes_jw
Comment Utility
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
Comment Utility
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
Comment Utility
No, I don't think so.  You'll do the ignore during the import.
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Strange indeed.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now