Issue Bug Closed

#80347sys_category_record_mm misses UNIQUE INDEX on combination of uid_local, uid_foreign, tablenames, fieldname

Frans Saris 03/19/17, 22:23 updated 08/17/17, 17:10

Description

Just updated a site from TYPO3 7.6 to 8.7-dev and run into a sys_refindex error.

Core: Exception handler (WEB): Uncaught TYPO3 Exception: An exception occurred while executing 'INSERT INTO `sys_refindex` (`tablename`, `recuid`, `field`, `flexpointer`, `softref_key`, `softref_id`, `sorting`, `deleted`, `workspace`, `ref_table`, `ref_uid`, `ref_string`, `hash`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["sys_category", 4, "items", "", "", "", 32, 0, 0, "tx_news_domain_model_news", 123, "", "6a70ac7d7874ab8fe730106d85ece2fa"]: Duplicate entry '6a70ac7d7874ab8fe730106d85ece2fa' for key 'PRIMARY' | Doctrine\DBAL\Exception\UniqueConstraintViolationException thrown in file vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php in line 66. Requested URL: /typo3/index.php?M=system_dbint&moduleToken=aabfd830a0c550204cca00fcda79e7f9544

After a long search the reason was some “duplicate” rows in sys_category_record_mm.

To prevent this the sys_category_record_mm should have a UNIQUE index on uid_local, uid_foreign, tablenames, fieldname.

As a temp workarround to cleanup you sys_category_record_mm table you can run:

CREATE TABLE sys_category_record_mm2 LIKE sys_category_record_mm;
INSERT sys_category_record_mm2 SELECT * FROM sys_category_record_mm GROUP BY uid_local, uid_foreign, tablenames, fieldname;
RENAME TABLE sys_category_record_mm TO sys_category_record_mm_fout;
RENAME TABLE sys_category_record_mm2 TO sys_category_record_mm;

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.