Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 987
  • Last Modified:

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.

0
lewisig
Asked:
lewisig
  • 7
  • 6
1 Solution
 
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
 
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
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

 
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
 
snoyes_jwCommented:
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
 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now