Bug #80347
closed
sys_category_record_mm misses UNIQUE INDEX on combination of uid_local, uid_foreign, tablenames, fieldname
0%
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;
Updated by Thomas Hohn about 7 years ago
I agree it could be a "work-around" - but do you have any indications of what creates these double records?
It would of course be better to find the "bug" and then fix that ;-)
Updated by Frans Saris about 7 years ago
I'm afraid I would be able to track down the origin of this in my installation. This installation was upgradet from 4.4 .... 8.7-dev through the years, so maybe it was already broken in 6.2 I'm not sure. Thing is I never saw this sys_refindex issue until the update to 8.7-dev yesterday.
The work around is mostly as reference for others that run into this issue :)
But should the INDEX on this table be UNIQUE?
Updated by Thomas Hohn about 7 years ago
The records I could find in my system have the the same sort value - maybe that the case for you too?
At least the Index would then make it "easier" to track down the cause of the error...
Updated by Frans Saris about 7 years ago
Yes, sort should indeed be added. As that makes it UNIQUE
Updated by Thomas Hohn about 7 years ago
Frans Saris wrote:
Yes, sort should indeed be added. As that makes it UNIQUE
I will makes a PR for it at see what happens ;-)
Updated by Oliver Hader about 7 years ago
Frans, can you by chance post a full stack-trace of the exception?
Besides that, it would be interesting if there was a particular table duplicated, e.g. find those duplicates withSELECT * FROM `sys_category_record_mm` GROUP BY `uid_local`, `uid_foreign`, `tablenames`, `fieldname` HAVING COUNT(*) > 1
Updated by Thomas Hohn about 7 years ago
I have the same problem on a custom table with mm relations.
Just ensure you have 2 identical records in sys_category_record_mm and run the indexer from command line and the it will break
Updated by Oliver Hader about 7 years ago
- Status changed from New to Needs Feedback
What's the scenario to reproduce this issue?
Updated by Xavier Perseguers almost 7 years ago
My client gets his error regularly, cannot reproduce locally:
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", 31, "items", "", "", "", 1057, 0, 0, "tx_news_domain_model_news", 4673, "", "3c6ce2ea2d72fdce5c26beedaa58b0f5"]: Duplicate entry '3c6ce2ea2d72fdce5c26beedaa58b0f5' for key 'PRIMARY'
Updated by Xavier Perseguers almost 7 years ago
fyi, problem is with a custom field which got added to EXT:news news records. over 107034 rows in this table, I had 5 duplicates.
Updated by Thomas Hohn almost 7 years ago
Are you running 8.7.1? I think this patch was merged recently https://review.typo3.org/#/c/52398/ - might solve your issue?
Updated by Xavier Perseguers almost 7 years ago
- Related to Bug #80763: New hash version in ReferenceIndex can cause non-unique keys added
Updated by Riccardo De Contardi over 6 years ago
- Status changed from Needs Feedback to Closed
No feedback within the last 90 days => closing this issue.
If you think that this is the wrong decision or experience this issue again, then please reopen it or open a new ticket and add a relation to this ticket number. Thank you!