Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Duplicate entry 'xxx' for key 1

Posted on 2004-08-23
13
Medium Priority
?
979 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your 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 Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses
Course of the Month11 days, 21 hours left to enroll

916 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