[5.x]: Problem with saving existing entries (Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails)

FabioIaderosa commented 3 weeks ago

What happened?

After updating to version 5, certain entries in the backend generate the following error:

[web.ERROR] [yii\db\IntegrityException] SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`test_gslivecraft5`.`craft_elements_owners`, CONSTRAINT `craft_fk_jorzyinwmellvvxytawssbkkluyinojdqgek` 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`, 777171, `o`.`sortOrder` FROM `craft_elements_owners` AS `o` WHERE `o`.`ownerId` = 617212 {"trace":["#0 /home/test/www/test.test.ch/craft5/vendor/yiisoft/yii2/db/Command.php(1325): yii\\db\\Schema->convertException(Object(PDOException), 'INSERT INTO `cr...')","#1 /home/test/www/test.test.ch/craft5/vendor/yiisoft/yii2/db/Command.php(1120): yii\\db\\Command->internalExecute('INSERT INTO `cr...')","#2 /home/test/www/test.test.ch/craft5/vendor/craftcms/cms/src/services/Drafts.php(183): yii\\db\\Command->execute()","#3 /home/test/www/test.test.ch/craft5/vendor/craftcms/cms/src/controllers/ElementsController.php(1522): craft\\services\\Drafts->createDraft(Object(craft\\elements\\Entry), 1, 'Entwurf 1', NULL, Array, true)","#4 [internal function]: craft\\controllers\\ElementsController->actionSaveDraft()","#5 /home/test/www/test.test.ch/craft5/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)","#6 /home/test/www/test.test.ch/craft5/vendor/yiisoft/yii2/base/Controller.php(178): yii\\base\\InlineAction->runWithParams(Array)","#7 /home/test/www/test.test.ch/craft5/vendor/yiisoft/yii2/base/Module.php(552): yii\\base\\Controller->runAction('save-draft', Array)","#8 /home/test/www/test.test.ch/craft5/vendor/craftcms/cms/src/web/Application.php(350): yii\\base\\Module->runAction('elements/save-d...', Array)","#9 /home/test/www/test.test.ch/craft5/vendor/craftcms/cms/src/web/Application.php(649): craft\\web\\Application->runAction('elements/save-d...', Array)","#10 /home/test/www/test.test.ch/craft5/vendor/craftcms/cms/src/web/Application.php(312): craft\\web\\Application->_processActionRequest(Object(craft\\web\\Request))","#11 /home/test/www/test.test.ch/craft5/vendor/yiisoft/yii2/base/Application.php(384): craft\\web\\Application->handleRequest(Object(craft\\web\\Request))","#12 /home/test/www/test.test.ch/craft5/web/index.php(12): yii\\base\\Application->run()","#13 {main}"],"memory":13047960,"exception":"[object] (yii\\db\\IntegrityException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`test_gslivecraft5`.`craft_elements_owners`, CONSTRAINT `craft_fk_jorzyinwmellvvxytawssbkkluyinojdqgek` FOREIGN KEY (`elementId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE)\nThe SQL being executed was: INSERT INTO `craft_elements_owners` (`elementId`, `ownerId`, `sortOrder`)\nSELECT `o`.`elementId`, 777171, `o`.`sortOrder`\nFROM `craft_elements_owners` AS `o`\nWHERE `o`.`ownerId` = 617212 at /home/test/www/test.test.ch/craft5/vendor/yiisoft/yii2/db/Schema.php:676)\n[previous exception] [object] (PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`test_gslivecraft5`.`craft_elements_owners`, CONSTRAINT `craft_fk_jorzyinwmellvvxytawssbkkluyinojdqgek` FOREIGN KEY (`elementId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE) at /home/test/www/test.test.ch/craft5/vendor/yiisoft/yii2/db/Command.php:1320)"}

How to fix the error?

Login in the backend.
Edit or extend an existing entry with a new field.
The following error message appears (see screenshot).

Craft CMS version

5.4.9

PHP version

8.2.24

Operating system and version

macos sonoma 14.6.1

Database type and version

MariaDB 10.6.19

Image driver and version

Imagick 3.7.0 (ImageMagick 7.1.1-26)

Installed plugins and versions

CKEditor 4.3.1
Expanded Singles 3.0.1
Freeform 5.6.8
Neo 5.2.14
Retour 5.0.3
SEOmatic 5.1.5
Typogrify 5.0.1
Vite 5.0.1

i-just commented 3 weeks ago

Hi, thanks for getting in touch! I am not able to reproduce this. Could you please send your composer.json, composer.lock and database export (post upgrade to v5) to support@craftcms.com so we can try to replicate and dig deeper?

FabioIaderosa commented 3 weeks ago

Hi, we were able to solve it with the cli command php gc run. Thanks for the support.

craftcms / cms