MySQL: ALTER IGNORE TABLE gives "Integrity constraint violation"

Question

I'm trying to remove duplicates from a MySQL table using ALTER IGNORE TABLE + an UNIQUE KEY. The MySQL documentation says:

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

When I run the query ...

ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field) 

... I still get the error #1062 - Duplicate entry 'blabla' for key 'dupidx'.

Answer 1

The IGNORE keyword extension to MySQL seems to have a bug in the InnoDB version on some version of MySQL.

You could always, convert to MyISAM, IGNORE-ADD the index and then convert back to InnoDB

ALTER TABLE table ENGINE MyISAM; ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field); ALTER TABLE table ENGINE InnoDB; 

Note, if you have Foreign Key constraints this will not work, you will have to remove those first, and add them back later.

Answer 2

Or try set session old_alter_table=1 (Don't forget to set it back!)

See: http://mysqlolyk.wordpress.com/2012/02/18/alter-ignore-table-add-index-always-give-errors/