1

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.

voretaq7
  • 79,879
  • 17
  • 130
  • 214
taber
  • 131
  • 6
  • 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 04: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 Answers3

0

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.

0

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

Peter Stuifzand
  • 740
  • 2
  • 8
  • 10
0

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)
lg.
  • 4,649
  • 3
  • 21
  • 20