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