We have a MySQL database with duplicate primary key index entries as a result of a DoS attack. The duplicates cannot be found by selects on the ID (it will only show one row, not two), but if you select by a username or password, it will show the two rows. Is it possible to drop the primary key, remove the duplicate rows and then re-index the primary keys?

We found the duplicates existed after trying to repair the table.

  • I assume this is MyISAM key corruption? Please consider switching to InnoDB. – Rob Olmos Oct 30 '10 at 20:49
  • i think you're right - last night the web hosting company worked some magic on the db and the data was successfully repaired/duplicates removed. (i believe using cPanel) the table and db are indeed using MyISAM. i'll do some research on the benefits of InnoDB, thanks. – taber Oct 31 '10 at 4:11
  • Consider switching to innodb, in careful consulation with the application developers, as part of a properly managed change which is fully tested. Be careful. It is a good idea though, in the broader sense. – MarkR Nov 29 '10 at 22:17

Some options off the top of my head:

  • Create a new column with a unique constraint, drop the original column, and make the new one your PK.

  • Alternatively, create a new table with a new PK, copy across everything except the duplicate column, drop the original table and rename the new.


Why don't you make a backup of the table and try it out?

  • the backups won't import due to the duplicate keys – taber Oct 31 '10 at 4:08
  • 1
    If you make a mysqldump, you can edit the resulting SQL file. – Peter Stuifzand Nov 1 '10 at 14:34

If you can delete records from table, the sql command below deletes the youngest duplicate record.

delete from table1 where ID in (select max(ID), username from table1 group by username)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.