[4.x]: Craft cant prune matrix block revisions

[LeoWie93]

What happened?

Description

We had a problem with a Craft 5 Update and started researching. Right now we found out that downgraded back to Craft 4.12.5. We are not able to clean all revisions from the DB.

Steps to reproduce

1. ./craft utils/prune-revisions --max-revisions=0

Expected behavior

All revisions all cleaned / or marked to delete for the 'garbage collector'

Actual behavior

Craft CMS version

4.12.5

PHP version

8.2

Operating system and version

No response

Database type and version

MySQL 8.0.32

Image driver and version

GD 8.2.24

Installed plugins and versions

• ckeditor
• Fix-Fks

[brandonkelly]

This can happen if the revision Matrix block gets orphaned, because its parent revision was deleted.

The extra data should be harmless though. Are you seeing a correlation with these revisions and your Craft 5 upgrade issues?

[LeoWie93]

I think their is a correlation. This page only has 1 section and a single page homepage. Which use the same matrix.

• updating to craft5 works without a problem
• changing matrix the first time works
• after that we get the following error every time we try to add or remove a matrix block
  • but changing the contents of a present matrix block works (it still shows an error while autosaving, but works on manual save)

This only happens on certaint entries.

2024-10-09 06:09:11 [web.ERROR] [yii\db\IntegrityException] SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`aerni_craft3`.`elements_owners`, CONSTRAINT `craft_fk_ceaqebrrnpcqgrfcdqyumrbitblcnnaveldu` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE) The SQL being executed was: INSERT INTO `elements_owners` (`elementId`, `ownerId`, `sortOrder`) SELECT `o`.`elementId`, 48816, `o`.`sortOrder` FROM `elements_owners` AS `o` WHERE `o`.`ownerId` = 18 {"trace":["#0 /vendor/yiisoft/yii2/db/Command.php(1325): yii\\db\\Schema->convertException()","#1 /vendor/yiisoft/yii2/db/Command.php(1120): yii\\db\\Command->internalExecute()","#2 /vendor/craftcms/cms/src/services/Drafts.php(183): yii\\db\\Command->execute()","#3 /vendor/craftcms/cms/src/controllers/ElementsController.php(1505): craft\\services\\Drafts->createDraft()","#4 [internal function]: craft\\controllers\\ElementsController->actionSaveDraft()","#5 /vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()","#6 /vendor/yiisoft/yii2/base/Controller.php(178): yii\\base\\InlineAction->runWithParams()","#7 /vendor/yiisoft/yii2/base/Module.php(552): yii\\base\\Controller->runAction()","#8 /vendor/craftcms/cms/src/web/Application.php(350): yii\\base\\Module->runAction()","#9 /vendor/craftcms/cms/src/web/Application.php(649): craft\\web\\Application->runAction()","#10 /vendor/craftcms/cms/src/web/Application.php(312): craft\\web\\Application->_processActionRequest()","#11 /vendor/yiisoft/yii2/base/Application.php(384): craft\\web\\Application->handleRequest()","#12 /web/index.php(29): yii\\base\\Application->run()","#13 {main}"],"memory":30391696,"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 (`aerni_craft3`.`elements_owners`, CONSTRAINT `craft_fk_ceaqebrrnpcqgrfcdqyumrbitblcnnaveldu` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE)\nThe SQL being executed was: INSERT INTO `elements_owners` (`elementId`, `ownerId`, `sortOrder`)\nSELECT `o`.`elementId`, 48816, `o`.`sortOrder`\nFROM `elements_owners` AS `o`\nWHERE `o`.`ownerId` = 18 at /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 (`aerni_craft3`.`elements_owners`, CONSTRAINT `craft_fk_ceaqebrrnpcqgrfcdqyumrbitblcnnaveldu` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE) at /vendor/yiisoft/yii2/db/Command.php:1320)"}

FYI: Running crafts fix-fks plugin yielded no results.

[LeoWie93]

@brandonkelly I just saw that the mentioned issues is talking about the "supertableplugin". We are not using it.
Shoud we send an export of our db to the craft support?

[boscho87]

@brandonkelly this will not be realted to the super-table plugin.

[brandonkelly]

Would either of you be able to share your Craft 5 database backup and Composer files, so we can reproduce locally and fix this? If so, please send them into [email protected]