Bug #84639

Defining a FOREIGN KEY constraint on a 1:n relation ends in an SQL failure when editing rows in these tables via backend or extbase

Added by Stefan P over 1 year ago. Updated about 1 year ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
-
Start date:
2018-04-06
Due date:
% Done:

0%

TYPO3 Version:
8
PHP Version:
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

We have a lot of 1:n relation configured as TCA type "inline" where the uid of the parent is stored in a field in the child table. That works fine. Since we are using MySQL with InnoDB only, we wanted to actually define foreign key constraints on these fields (instead of a pure index). Adding this to the child table
FOREIGN KEY (parent) REFERENCES parent_table (uid) ON DELETE CASCADE
to ext_tables.sql works in the install tool (it correctly adds the key and the constraint - as long as the initial data integrity is given, of course). But as soon as this constraint is present, it is not possible to edit the parent datasets anymore (neither in backend or Extbase persistence) - it always fails with a MySQL exception saying that the foreign key constraint fails on this constraint.

Working with pure SQL on these tables works fine (inserting and deleting correctly referenced rows is working, also the cascading is working). So I suspect that there's some kind of "race condition" in the DataHandler or some other persistence layer that is doing some inserts/deletes/updates in the wrong order (or wrongly doing multiple querys at all, that should actually be a single query in an ideal world), preventing this from working.

Side note: having foreign key constraints on uid_local and uid_foreign in *_mm tables works perfectly fine in both, backend and extbase, contexts.

So, I'm not sure which category is correct here (DataHandler, Doctrine, Extbase?)

History

#1 Updated by Benni Mack over 1 year ago

  • Target version changed from 8.7.13 to 8.7.19

#2 Updated by Susanne Moog over 1 year ago

  • Target version changed from 8.7.19 to Candidate for patchlevel

#3 Updated by Tymoteusz Motylewski about 1 year ago

Thanks for the report @Stefan.
Can you please provide a minimal code allowing to reproduce the issue with vanilla core?
e.g. please attach a test extension and steps to reproduce.
Thanks

#4 Updated by Stefan P about 1 year ago

Sorry, don't have time for this. Just put an index on any simple 1:n (IRRE) relation to test it.

Also available in: Atom PDF