Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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

3 Answers 3

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.

share|improve this answer

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

share|improve this answer
    
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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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