MySQL Duplicate error with ALTER IGNORE TABLE

Question

I have a table in my MySQL with duplicates. I try to delete the duplicates and keep one entry. I don't have a primary key

I can finde the duplicates by:

select user_id, server_id, count(*) as NumDuplicates
from user_server
group by user_id, server_id
having NumDuplicates > 1

But can't delete them with:

ALTER IGNORE TABLE `user_server`  
ADD UNIQUE INDEX (`user_id`, `server_id`);

Even SET foreign_key_checks = 0; is not working. Error Code: 1062. Duplicate entry '142-20' for key 'user_id_3'

MySQL version: 5.5.18 Engine: InnoDB

Is there an other way?

Answer 1

It's because you use INNO DB,

A simple trick :

set session old_alter_table=1;
ALTER IGNORE TABLE `user_server`  
ADD UNIQUE INDEX (`user_id`, `server_id`);
set session old_alter_table=0;

This do the job

Answer 2

Probably the easiest way is to copy the structure of the table, add the unique index to the new table and then do:

INSERT IGNORE INTO new_table SELECT * FROM old_table

---

To delete the duplicates (except one of each) without creating a temp table, you can do that:

ALTER TABLE `user_server` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

DELETE us2 FROM user_server us1
 JOIN user_server us2 USING (user_id, server_id)
 WHERE us1.id < us2.id;

ALTER TABLE `user_server` DROP `id`;

Answer 3

Another solution, which keeps any indexes that were on the user_server is:

CREATE TABLE new_user_server LIKE user_server;
ALTER TABLE new_user_server ADD UNIQUE INDEX (`user_id`, `server_id`);
INSERT IGNORE INTO new_user_server SELECT * FROM user_server;
DROP TABLE user_server;
RENAME TABLE new_user_server TO user_server;