MySQL: ALTER IGNORE TABLE ADD UNIQUE, what will be truncated?
It appears that your problem is one of the very reasons that ALTER IGNORE has been deprecated.
This is from the MySQL notes on the ALTER IGNORE deprecation:
"This feature is badly defined (what is the first row?), causes problems for replication, disables online alter for unique index creation and has caused problems with foreign keys (rows removed in parent table)."
The first record will be kept, the rest deleted §§:
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. IfIGNORE
is not specified, the copy is aborted and rolled back if duplicate-key errors occur. IfIGNORE
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
I am guessing 'first' here means the one with the smallest ID, assuming the ID is the primary key.
Also note:
As of MySQL 5.7.4, the
IGNORE
clause forALTER TABLE
is removed and its use produces an error.