[5.x]: C4 -> C5 post upgrade, adding new matrix blocks gives FK error on first click, works on 2nd click

[chadcrowell]

What happened?

Description

On C4 -> C5 upgraded site when trying to add matrix blocks to existing entries that had existing matrix (or possibly super table) blocks before the upgrade, getting a FK constraint error. Second click to add the block successfully adds it and can add content and save. So, only on first click.

Video with demonstration of what is happening

Initial Chat GPT Chat

Claude Chat

I decided not to get into doing a composer patch to see if there's a fix for this or if I am overlooking something somewhere. So, pausing for now to see what the response is.

Steps to reproduce

1. See video above

Expected behavior

No FK error to be shown

Actual behavior

FK errors:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (db.craft_elements_owners, CONSTRAINT craft_fk_qbhiiwsnvxpysusastgppytrauyrjehnikns FOREIGN KEY (elementId) REFERENCES craft_elements (id) ON DELETE CASCADE) The SQL being executed was: INSERT INTO craft_elements_owners (elementId, ownerId, sortOrder) SELECT o.elementId, 7090464, o.sortOrder FROM craft_elements_owners AS o WHERE o.ownerId = 6

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (db.craft_elements_owners, CONSTRAINT craft_fk_qbhiiwsnvxpysusastgppytrauyrjehnikns FOREIGN KEY (elementId) REFERENCES craft_elements (id) ON DELETE CASCADE) The SQL being executed was: INSERT INTO craft_elements_owners (elementId, ownerId, sortOrder) SELECT o.elementId, 7090467, o.sortOrder FROM craft_elements_owners AS o WHERE o.ownerId = 486111

Craft CMS version

5.19.6

PHP version

8.2.30 and 8.4.18 (ddev) and 8.2.28 (servd)

Operating system and version

MacOS recent

Database type and version

MySql 8.0 ddev and Servd

Image driver and version

Imagick 3.8.1 (ImageMagick 7.1.1-43) and Imagick 3.7.0 (ImageMagick 7.1.1-41)

Installed plugins and versions

Plugins

Asset Rev 8.0.0
Child Me! 2.2.3
CKEditor 4.11.2
Control Panel Nav 5.0.6
Environment Label 5.0.0
Feed Me 6.11.0
Icon Picker 3.0.7
Publisher X 5.0.3
Retour 5.0.14
SEOmatic 5.1.20
Servd Assets and Helpers 4.2.3
Wordsmith 5.0.0

[linear]

CMS-1895

[chadcrowell]

To summarize what we found for the bug report:

Root cause: duplicateOwnershipAfterPropagate behavior fires EVENT_AFTER_PROPAGATE inside the outer Drafts::createDraft transaction, but the draft element it references as ownerId is only committed to a Yii2 savepoint at that point — not the real transaction — so MySQL's FK constraint on craft_elements_owners.ownerId rejects the INSERT

Affected: Any existing entry with Matrix fields when clicking "New entry" (block) for the first time in an editing session

Not affected: Brand new entries (no provisional draft creation needed)

Workaround: Second click always succeeds because Craft takes the matrix/create-entry path instead of elements/save-draft

Craft version: 5.9.16

[i-just]

Hi, thanks for getting in touch! We’ve seen this before if there were some orphaned entries/blocks in the v4 installation. For example: #15988. Can you please run garbage collection on v4 before upgrading to v5 and see if that helps?