Duplicate entry 'xxx' for key 1

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.

LVL 1
lewisigAsked:
Who is Participating?
 
snoyes_jwConnect With a Mentor Commented:
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
 
snoyes_jwCommented:
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
 
lewisigAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
snoyes_jwCommented:
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
 
lewisigAuthor Commented:
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
 
snoyes_jwCommented:
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
 
lewisigAuthor Commented:
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
 
snoyes_jwCommented:
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
 
lewisigAuthor Commented:
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
 
snoyes_jwCommented:
No, I don't think so.  You'll do the ignore during the import.
0
 
lewisigAuthor Commented:
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
 
lewisigAuthor Commented:
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
 
snoyes_jwCommented:
Strange indeed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.