[4.x]: IntegrityException when Saving Entry w/ Matrix

[leigeber]

What happened?

Description

On an entry that includes a Matrix, we're unable to save. The exception is as follows:

SQLSTATE[23503]: Foreign key violation: 7 ERROR:  insert or update on table "craft_matrixblocks_owners" violates foreign key constraint "craft_fk_deyrrvwupfesqucuaqxzqlztlzanicbbljma"
DETAIL:  Key (blockId)=(76030) is not present in table "craft_matrixblocks".
The SQL being executed was: INSERT INTO "craft_matrixblocks_owners" ("blockId", "ownerId", "sortOrder") VALUES (76030, 76094, 1), (76031, 76094, 2), (76032, 76094, 3), (76033, 76094, 4), (76034, 76094, 5), (76035, 76094, 6), (76037, 76094, 7), (76036, 76094, 8)

The 76030, 76031, etc IDs do exist as elementIds on the craft_matrixcontent_homefeatures table but no reference in craft_matrixcontent. Testing the entry in other environments it seems certain edits will trigger the problem, for instance adding a new Matrix block but possibly other edits on other non-Matrix fields in the entry.

Once triggered even if you discard the changes the entry will continue to fail saving.

Craft CMS version

4.0.1

PHP version

8.1.0

Operating system and version

No response

Database type and version

PostgreSQL 13.4

Image driver and version

No response

Installed plugins and versions

[brandonkelly]

Not able to reproduce this. Any chance you can send in a database backup, your Composer files, and steps to reproduce, to [email protected]?

[elfacht]

I experience the same, thought at first that this would be a Neo issue, but it turns out I have the same issue with Matrix blocks:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`paracudacom`.`pcom_matrixblocks_owners`, CONSTRAINT `pcom_fk_mmdfgretkjrkhgxkmftuigpzjcpikxensjno` FOREIGN KEY (`blockId`) REFERENCES `pcom_matrixblocks` (`id`) ON DELETE CASCADE)
The SQL being executed was: INSERT INTO `pcom_matrixblocks_owners` (`blockId`, `ownerId`, `sortOrder`) VALUES (352, 400, 1), (338, 400, 2), (341, 400, 3)

Here are the affected fields and sections, but I can provide more data if needed.
project_matrix.zip

Also, it happens after a few successful saves, not with the first one. Totally random, sometimes after hours of editing, sometimes after the second save.

Steps to reproduce

• Create a post section with at least to entry types.
• Create a Matrix field with 3 simple blocks, like text fields and asset fields.
• Create a post entry, add blocks, go wild, change orders, save.
• Save a few times, change stuff, save again.

I couldn't find a pattern yet.

[smcyr]

I have the same error when saving an entry with a matrix field:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`craft`.`matrixblocks_owners`, CONSTRAINT `fk_sxvukfeizgfxyyewixwwbftzwgnehjyqunro` FOREIGN KEY (`blockId`) REFERENCES `matrixblocks` (`id`) ON DELETE CASCADE)
The SQL being executed was: INSERT INTO `matrixblocks_owners` (`blockId`, `ownerId`, `sortOrder`) VALUES (25, 1618, 1), (26, 1618, 2)

I'm on Windows 11 with MariaDB 10.6.5, PHP 8.0.18, Craft 4.0.2.

I have sent an email with my database and composer.json/composer.lock to [email protected].

[brandonkelly]

The 76030, 76031, etc IDs do exist as elementIds on the craft_matrixcontent_homefeatures table but no reference in craft_matrixcontent.

@leigeber Did you mean craft_matrixblocks for that last table? What about within the craft_elements table?

[elfacht]

It does happen sometimes even without Matrix fields. I just set up a fresh installation, created an page entry type with a tab without Matrix field and the exception occured (just once this time)

The field setup:

• Content tab
  • title text
• Teaser tab
  • teaserHeadline text
  • teaserExcerpt text
  • teaserImage asset
  • teaserButton text

Setup:

• Multi-site: yes (de / en)
• Craft 4.0.2
• Plugins:
  • Field Manager 3.0.0-beta.1
  • Minify 4.0.0-beta.2
  • Redactor 3.0.0

I'm using NItro (latest)

[brandonkelly]

@elfacht Please check the actual error message carefully. It’s almost certainly not the same as this one. And assuming it’s not, please post it as a new GH issue, with as much info as you can, including the full error message and the stack trace that should get logged in storage/logs/ immediately following the error message log.

[smcyr]

Here is the stack trace for me:

2022-05-12 15:01:21 [web.ERROR] [yii\db\IntegrityException] PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`craft`.`matrixblocks_owners`, CONSTRAINT `fk_sxvukfeizgfxyyewixwwbftzwgnehjyqunro` FOREIGN KEY (`blockId`) REFERENCES `matrixblocks` (`id`) ON DELETE CASCADE) in C:\projects\craft\vendor\yiisoft\yii2\db\Command.php:1302
Stack trace:
#0 C:\projects\craft\vendor\yiisoft\yii2\db\Command.php(1302): PDOStatement->execute()
#1 C:\projects\craft\vendor\yiisoft\yii2\db\Command.php(1102): yii\db\Command->internalExecute('INSERT INTO `ma...')
#2 C:\projects\craft\vendor\craftcms\cms\src\helpers\Db.php(968): yii\db\Command->execute()
#3 C:\projects\craft\vendor\craftcms\cms\src\services\Matrix.php(991): craft\helpers\Db::batchInsert('{{%matrixblocks...', Array, Array)
#4 C:\projects\craft\vendor\craftcms\cms\src\fields\Matrix.php(1048): craft\services\Matrix->createRevisionBlocks(Object(craft\fields\Matrix), Object(craft\elements\Entry), Object(craft\elements\Entry))
#5 C:\projects\craft\vendor\craftcms\cms\src\base\Element.php(4530): craft\fields\Matrix->afterElementPropagate(Object(craft\elements\Entry), true)
#6 C:\projects\craft\vendor\craftcms\cms\src\elements\Entry.php(2009): craft\base\Element->afterPropagate(true)
#7 C:\projects\craft\vendor\craftcms\cms\src\services\Elements.php(1324): craft\elements\Entry->afterPropagate(true)
#8 C:\projects\craft\vendor\craftcms\cms\src\services\Revisions.php(160): craft\services\Elements->duplicateElement(Object(craft\elements\Entry), Array)
#9 C:\projects\craft\vendor\craftcms\cms\src\elements\Entry.php(2013): craft\services\Revisions->createRevision(Object(craft\elements\Entry), 4, 'Applied \xE2\x80\x9CDraf...')
#10 C:\projects\craft\vendor\craftcms\cms\src\services\Elements.php(1324): craft\elements\Entry->afterPropagate(false)
#11 C:\projects\craft\vendor\craftcms\cms\src\services\Elements.php(878): craft\services\Elements->duplicateElement(Object(craft\elements\Entry), Array)
#12 C:\projects\craft\vendor\craftcms\cms\src\services\Drafts.php(304): craft\services\Elements->updateCanonicalElement(Object(craft\elements\Entry), Array)
#13 C:\projects\craft\vendor\craftcms\cms\src\controllers\ElementsController.php(1260): craft\services\Drafts->applyDraft(Object(craft\elements\Entry))
#14 [internal function]: craft\controllers\ElementsController->actionApplyDraft()
#15 C:\projects\craft\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)
#16 C:\projects\craft\vendor\yiisoft\yii2\base\Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#17 C:\projects\craft\vendor\yiisoft\yii2\base\Module.php(552): yii\base\Controller->runAction('apply-draft', Array)
#18 C:\projects\craft\vendor\craftcms\cms\src\web\Application.php(301): yii\base\Module->runAction('elements/apply-...', Array)
#19 C:\projects\craft\vendor\craftcms\cms\src\web\Application.php(625): craft\web\Application->runAction('elements/apply-...', Array)
#20 C:\projects\craft\vendor\craftcms\cms\src\web\Application.php(280): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#21 C:\projects\craft\vendor\yiisoft\yii2\base\Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#22 C:\projects\craft\web\index.php(12): yii\base\Application->run()
#23 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`craft`.`matrixblocks_owners`, CONSTRAINT `fk_sxvukfeizgfxyyewixwwbftzwgnehjyqunro` FOREIGN KEY (`blockId`) REFERENCES `matrixblocks` (`id`) ON DELETE CASCADE)
The SQL being executed was: INSERT INTO `matrixblocks_owners` (`blockId`, `ownerId`, `sortOrder`) VALUES (25, 1667, 1), (26, 1667, 2) in C:\projects\craft\vendor\yiisoft\yii2\db\Schema.php:676
Stack trace:
#0 C:\projects\craft\vendor\yiisoft\yii2\db\Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO `ma...')
#1 C:\projects\craft\vendor\yiisoft\yii2\db\Command.php(1102): yii\db\Command->internalExecute('INSERT INTO `ma...')
#2 C:\projects\craft\vendor\craftcms\cms\src\helpers\Db.php(968): yii\db\Command->execute()
#3 C:\projects\craft\vendor\craftcms\cms\src\services\Matrix.php(991): craft\helpers\Db::batchInsert('{{%matrixblocks...', Array, Array)
#4 C:\projects\craft\vendor\craftcms\cms\src\fields\Matrix.php(1048): craft\services\Matrix->createRevisionBlocks(Object(craft\fields\Matrix), Object(craft\elements\Entry), Object(craft\elements\Entry))
#5 C:\projects\craft\vendor\craftcms\cms\src\base\Element.php(4530): craft\fields\Matrix->afterElementPropagate(Object(craft\elements\Entry), true)
#6 C:\projects\craft\vendor\craftcms\cms\src\elements\Entry.php(2009): craft\base\Element->afterPropagate(true)
#7 C:\projects\craft\vendor\craftcms\cms\src\services\Elements.php(1324): craft\elements\Entry->afterPropagate(true)
#8 C:\projects\craft\vendor\craftcms\cms\src\services\Revisions.php(160): craft\services\Elements->duplicateElement(Object(craft\elements\Entry), Array)
#9 C:\projects\craft\vendor\craftcms\cms\src\elements\Entry.php(2013): craft\services\Revisions->createRevision(Object(craft\elements\Entry), 4, 'Applied \xE2\x80\x9CDraf...')
#10 C:\projects\craft\vendor\craftcms\cms\src\services\Elements.php(1324): craft\elements\Entry->afterPropagate(false)
#11 C:\projects\craft\vendor\craftcms\cms\src\services\Elements.php(878): craft\services\Elements->duplicateElement(Object(craft\elements\Entry), Array)
#12 C:\projects\craft\vendor\craftcms\cms\src\services\Drafts.php(304): craft\services\Elements->updateCanonicalElement(Object(craft\elements\Entry), Array)
#13 C:\projects\craft\vendor\craftcms\cms\src\controllers\ElementsController.php(1260): craft\services\Drafts->applyDraft(Object(craft\elements\Entry))
#14 [internal function]: craft\controllers\ElementsController->actionApplyDraft()
#15 C:\projects\craft\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)
#16 C:\projects\craft\vendor\yiisoft\yii2\base\Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#17 C:\projects\craft\vendor\yiisoft\yii2\base\Module.php(552): yii\base\Controller->runAction('apply-draft', Array)
#18 C:\projects\craft\vendor\craftcms\cms\src\web\Application.php(301): yii\base\Module->runAction('elements/apply-...', Array)
#19 C:\projects\craft\vendor\craftcms\cms\src\web\Application.php(625): craft\web\Application->runAction('elements/apply-...', Array)
#20 C:\projects\craft\vendor\craftcms\cms\src\web\Application.php(280): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#21 C:\projects\craft\vendor\yiisoft\yii2\base\Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#22 C:\projects\craft\web\index.php(12): yii\base\Application->run()
#23 {main}
Additional Information:
Array
(
    [0] => 23000
    [1] => 1452
    [2] => Cannot add or update a child row: a foreign key constraint fails (`craft`.`matrixblocks_owners`, CONSTRAINT `fk_sxvukfeizgfxyyewixwwbftzwgnehjyqunro` FOREIGN KEY (`blockId`) REFERENCES `matrixblocks` (`id`) ON DELETE CASCADE)
)
 {"memory":11387832,"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 (`craft`.`matrixblocks_owners`, CONSTRAINT `fk_sxvukfeizgfxyyewixwwbftzwgnehjyqunro` FOREIGN KEY (`blockId`) REFERENCES `matrixblocks` (`id`) ON DELETE CASCADE)
The SQL being executed was: INSERT INTO `matrixblocks_owners` (`blockId`, `ownerId`, `sortOrder`) VALUES (25, 1667, 1), (26, 1667, 2) at C:\\projects\\craft\\vendor\\yiisoft\\yii2\\db\\Schema.php:676)
[previous exception] [object] (PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`craft`.`matrixblocks_owners`, CONSTRAINT `fk_sxvukfeizgfxyyewixwwbftzwgnehjyqunro` FOREIGN KEY (`blockId`) REFERENCES `matrixblocks` (`id`) ON DELETE CASCADE) at C:\\projects\\craft\\vendor\\yiisoft\\yii2\\db\\Command.php:1302)"} 

[smcyr]

@brandonkelly I created a new issue here: #11220 which is similar and probably the error that you had @elfacht.

[leigeber]

The 76030, 76031, etc IDs do exist as elementIds on the craft_matrixcontent_homefeatures table but no reference in craft_matrixcontent.

@leigeber Did you mean craft_matrixblocks for that last table? What about within the craft_elements table?

Yes, not present in the craft_matrixblocks table but they are in the craft_elements table. Shooting a zip to support now. Thanks.

[brandonkelly]

Got to the bottom of this and fixed for the next release!

To get the fix early, change your craftcms/cms requirement in composer.json to "dev-develop as 4.0.2" and run composer update.

[brandonkelly]

Craft 4.0.3 is out now with that fix.