If somehow (f.e. because of a migration from DAM to FAL) you face with duplicate records in MM table you need a small trick to be able to delete them.
The solution is quite simple:
ALTER TABLE sys_category_record_mm ENGINE MyISAM;
ALTER IGNORE TABLE sys_category_record_mm ADD UNIQUE KEY uidx(uid_local, uid_foreign, tablenames);
ALTER TABLE sys_category_record_mm ENGINE InnoDB;
With this MySql command deletes the server all duplicates but only one line.
The change in the database engine is needed because of a bug in specific InnoDB versions. See http://bugs.mysql.com/bug.php?id=40344
Danke für den (inzwischen etwas gealterten) Beitrag.
Zur Info:
„IGNORE“ wurde in MySQL 5.6 „deprecated“ und in MySQL 5.7 entfernt (https://dev.mysql.com/worklog/task/?id=7395).
Wie man die Problematik dennoch angehen kann, überlegt Morgan in einem Blogbeitrag ganz gut:
http://www.tocker.ca/the-future-of-alter-ignore-table-syntax.html
„ALTER IGNORE TABLE“ wurde in MySQL 5.7 entfernt (und in 5.6 als „deprecated“ eingestuft).
# Working with MySQL 5.7 and newer
CREATE TABLE tx_example_category_mm_new LIKE tx_example_category_mm;
ALTER TABLE tx_example_category_mm_new ADD UNIQUE KEY uidx(uid_local, uid_foreign);
INSERT IGNORE INTO tx_example_category_mm_new SELECT * FROM tx_example_category_mm;
DROP TABLE tx_example_category_mm;
RENAME TABLE tx_example_category_mm_new TO tx_example_category_mm;