[5.x]: Integrity constraint violation after updating to 5.2.9/fixing #3805

[gopeter]

What happened?

Description

I want to create a new product (under special circumstances which are described in "Steps to reproduce") but get a "Integrity constraint violation" error once I want to save it.

I did a lot of local debugging and this commit breaks our current shop: cdb0426

Steps to reproduce

1. Create a multi-site (important!) shop
2. Create a product where revisions are enabled (important!)
3. Create a matrix field with a simple entry type. This entry type needs to contain a field which can be translated for each site/site group
4. Add this matrix field to the variant fields of this product and set propagation method to "save everywhere the owner is saved" (important!)

I was also able to reproduce it in the demo shop: https://living-eft-ae150c3.demo.craft.cloud/admin/commerce/products/bike/820960?site=default&draftId=83&fresh=1#tab04-variants (I've added a second test site and a test matrix field called "Settings")

Craft CMS version

5.5.9

Craft Commerce version

>= 5.2.9

PHP version

No response

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

No response

[linear]

PT-2396 [5.x]: Integrity constraint violation after updating to 5.2.9/fixing #3805

[lukeholder]

Thanks for reporting, I am unable to reproduce:

CleanShot.2025-01-24.at.12.47.58.mp4

Can you share your logs for the error or turn on devMode and share the screenshot of the error again?

[gopeter]

Hi Luke,

maybe it helps if I share credentials for the demo page I've created? (https://living-eft-ae150c3.demo.craft.cloud/admin/commerce/products/bike/820960?site=default&draftId=83&fresh=1#tab04-variants)

Have you enabled revisions for that product type?

Here's the error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db`.`elementactivity`, CONSTRAINT `fk_qzxfqvcndpghknhtivgvqprhbheenhzvhgej` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE)
The SQL being executed was: INSERT INTO `elementactivity` (`elementId`, `userId`, `siteId`, `draftId`, `type`, `timestamp`) VALUES (780343, 1, 15, NULL, 'save', '2025-01-24 08:57:15') ON DUPLICATE KEY UPDATE `siteId`=VALUES(`siteId`), `draftId`=VALUES(`draftId`), `timestamp`=VALUES(`timestamp`)

[lukeholder]

Looks like an error when dealing with the element activity table which is a core Craft table and not Commerce. I confirm I have revisions on for the product type but still can't reproduce it.

I don't have access to that test site. Could you send in a support ticket with more info and reference this ticket? Thanks.

[lukeholder]

@gopeter can you test this on Commerce 5.3 and if the issue is persisting, send a DB backup, composer.json and composer.lock to [email protected] and we will attempt to reproduce. Please mention this github number in your email. Thanks!

[gopeter]

Hi @lukeholder,
yes, we are still getting this error. Going to send you the backup and composer files today.

/edit

Files are sent 👍