Craft 4: Saving entry will cause PDOException: Integrity constraint violation

[elfacht]

Description

Saving an entry with a pretty simple Neo field will cause a PDOException:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`pcom22`.`pcom_neoblocks_owners`, CONSTRAINT `pcom_fk_ienyuwcdxyjkdparmjxwlxkpxwenmslsmymu` FOREIGN KEY (`blockId`) REFERENCES `pcom_neoblocks` (`id`) ON DELETE CASCADE) in /app/cms/vendor/yiisoft/yii2/db/Command.php:1302
Stack trace:
#0 /app/cms/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute()
#1 /app/cms/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute(NULL)
#2 /app/cms/vendor/craftcms/cms/src/helpers/Db.php(968): yii\db\Command->execute()
#3 /app/cms/vendor/spicyweb/craft-neo/src/services/Fields.php(584): craft\helpers\Db::batchInsert('{{%neoblocks_ow...', Array, Array)
#4 /app/cms/vendor/spicyweb/craft-neo/src/Field.php(773): benf\neo\services\Fields->createRevisionBlocks(Object(benf\neo\Field), Object(craft\elements\Entry), Object(craft\elements\Entry))
#5 /app/cms/vendor/craftcms/cms/src/base/Element.php(4530): benf\neo\Field->afterElementPropagate(Object(craft\elements\Entry), true)
#6 /app/cms/vendor/craftcms/cms/src/elements/Entry.php(2009): craft\base\Element->afterPropagate(true)
#7 /app/cms/vendor/craftcms/cms/src/services/Elements.php(1343): craft\elements\Entry->afterPropagate(true)
#8 /app/cms/vendor/craftcms/cms/src/services/Revisions.php(160): craft\services\Elements->duplicateElement(Object(craft\elements\Entry), Array)
#9 /app/cms/vendor/craftcms/cms/src/elements/Entry.php(2013): craft\services\Revisions->createRevision(Object(craft\elements\Entry), 1, '')
#10 /app/cms/vendor/craftcms/cms/src/services/Elements.php(2734): craft\elements\Entry->afterPropagate(false)
#11 /app/cms/vendor/craftcms/cms/src/services/Elements.php(789): craft\services\Elements->_saveElementInternal(Object(craft\elements\Entry), true, false, true)
#12 /app/cms/vendor/craftcms/cms/src/controllers/ElementsController.php(871): craft\services\Elements->saveElement(Object(craft\elements\Entry))
#13 [internal function]: craft\controllers\ElementsController->actionSave()
#14 /app/cms/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#15 /app/cms/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#16 /app/cms/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('save', Array)
#17 /app/cms/vendor/craftcms/cms/src/web/Application.php(301): yii\base\Module->runAction('elements/save', Array)
#18 /app/cms/vendor/craftcms/cms/src/web/Application.php(625): craft\web\Application->runAction('elements/save', Array)
#19 /app/cms/vendor/craftcms/cms/src/web/Application.php(280): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#20 /app/cms/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#21 /app/cms/web/index.php(21): yii\base\Application->run()
#22 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`pcom22`.`pcom_neoblocks_owners`, CONSTRAINT `pcom_fk_ienyuwcdxyjkdparmjxwlxkpxwenmslsmymu` FOREIGN KEY (`blockId`) REFERENCES `pcom_neoblocks` (`id`) ON DELETE CASCADE)
The SQL being executed was: INSERT INTO `pcom_neoblocks_owners` (`blockId`, `ownerId`, `sortOrder`) VALUES (584, 618, 1), (542, 618, 2), (577, 618, 3), (578, 618, 4), (579, 618, 5), (544, 618, 6) in /app/cms/vendor/yiisoft/yii2/db/Schema.php:676
Stack trace:
#0 /app/cms/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO `pc...')
#1 /app/cms/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('INSERT INTO `pc...')
#2 /app/cms/vendor/craftcms/cms/src/helpers/Db.php(968): yii\db\Command->execute()
#3 /app/cms/vendor/spicyweb/craft-neo/src/services/Fields.php(584): craft\helpers\Db::batchInsert('{{%neoblocks_ow...', Array, Array)
#4 /app/cms/vendor/spicyweb/craft-neo/src/Field.php(773): benf\neo\services\Fields->createRevisionBlocks(Object(benf\neo\Field), Object(craft\elements\Entry), Object(craft\elements\Entry))
#5 /app/cms/vendor/craftcms/cms/src/base/Element.php(4530): benf\neo\Field->afterElementPropagate(Object(craft\elements\Entry), true)
#6 /app/cms/vendor/craftcms/cms/src/elements/Entry.php(2009): craft\base\Element->afterPropagate(true)
#7 /app/cms/vendor/craftcms/cms/src/services/Elements.php(1343): craft\elements\Entry->afterPropagate(true)
#8 /app/cms/vendor/craftcms/cms/src/services/Revisions.php(160): craft\services\Elements->duplicateElement(Object(craft\elements\Entry), Array)
#9 /app/cms/vendor/craftcms/cms/src/elements/Entry.php(2013): craft\services\Revisions->createRevision(Object(craft\elements\Entry), 1, '')
#10 /app/cms/vendor/craftcms/cms/src/services/Elements.php(2734): craft\elements\Entry->afterPropagate(false)
#11 /app/cms/vendor/craftcms/cms/src/services/Elements.php(789): craft\services\Elements->_saveElementInternal(Object(craft\elements\Entry), true, false, true)
#12 /app/cms/vendor/craftcms/cms/src/controllers/ElementsController.php(871): craft\services\Elements->saveElement(Object(craft\elements\Entry))
#13 [internal function]: craft\controllers\ElementsController->actionSave()
#14 /app/cms/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#15 /app/cms/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#16 /app/cms/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('save', Array)
#17 /app/cms/vendor/craftcms/cms/src/web/Application.php(301): yii\base\Module->runAction('elements/save', Array)
#18 /app/cms/vendor/craftcms/cms/src/web/Application.php(625): craft\web\Application->runAction('elements/save', Array)
#19 /app/cms/vendor/craftcms/cms/src/web/Application.php(280): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#20 /app/cms/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#21 /app/cms/web/index.php(21): yii\base\Application->run()
#22 {main}
Additional Information:
Array
(
    [0] => 23000
    [1] => 1452
    [2] => Cannot add or update a child row: a foreign key constraint fails (`pcom22`.`pcom_neoblocks_owners`, CONSTRAINT `pcom_fk_ienyuwcdxyjkdparmjxwlxkpxwenmslsmymu` FOREIGN KEY (`blockId`) REFERENCES `pcom_neoblocks` (`id`) ON DELETE CASCADE)
)

This happens since the update to Craft 4. The Neo field has two child blocks containing assets and text fields:

childBlocks:
  - mediaboxImage
  - footnote
field: 13953d81-6ba1-43ab-bdc5-ca496efd7535 # Blocks
fieldLayouts:
  a071de16-1feb-463c-b765-a2a0b0be3ed2:
    tabs:
      -
        elements:
          -
            fieldUid: 91f56b97-8b36-4c72-af13-5aaf2687bafe # Heading
            instructions: null
            label: null
            required: false
            tip: null
            type: craft\fieldlayoutelements\CustomField
            uid: cf9d4adc-5e27-40ba-b0b1-28a113344b4c
            warning: null
            width: 100
          -
            fieldUid: 3d1b9b41-5e8e-448f-a6fc-245351f28e6f # Textbox
            instructions: null
            label: null
            required: false
            tip: null
            type: craft\fieldlayoutelements\CustomField
            uid: c51fa347-baff-41ac-9813-3a41efcac596
            warning: null
            width: 100
        name: Content
        uid: ffb85b0e-de16-4c0c-9f57-c13d638be281
group: null
handle: mediabox
maxBlocks: 0
maxChildBlocks: 0
maxSiblingBlocks: 0
name: Mediabox
sortOrder: 1
topLevel: true

The setup is pretty fresh and new, so no wild things are going on.
The entry will be saved though (sometimes), but you have to deal with the internal server error message first!

Steps to reproduce

1. Create a Neo field in Craft 3.7.40.1 with simple text block and field
2. Create child blocks with simple asset fields, maybe a text field
3. Update to Craft 4
4. Save entry with Neo field
5. First save may work, after that: it won't

Other information

• Neo version: 3.0.1
• Craft version: 4.0.0.1
• Is eager-loading used? No

[ttempleton]

I'm unable to reproduce this so far — can you reproduce this upgrading from a brand new (other than the Neo field being setup for this) Craft 3.7 install, and if so, can you confirm whether Matrix fields work as expected?

[elfacht]

@ttempleton

I just created a new site, using Nitro (latest). I have this error even on 3.7.40.1 I attached the db dump and the composer.json, I hope that helps.

craftneotest-2022-05-06-090624.sql.zip composer.json.zip Repository: https://github.com/elfacht/craft-neo-test

Steps to reproduce:

1. Create simple Neo field with 1 block include a simple text field
2. Create pages section with neo template including this field
3. Create entry template in same section with Matrix field including 1 block with a simple text field.
4. Create a page in entries
5. Choose matrix template, save entry (works)
6. Choose neo template
7. Save entry
8. Receive this error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`craftneotest`.`changedfields`, CONSTRAINT `fk_bwctfshqbczvcsbsgtaiccdnwwfbjwbnimhv` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
The SQL being executed was: INSERT INTO `changedfields` (`dateUpdated`, `propagated`, `userId`, `elementId`, `siteId`, `fieldId`) VALUES ('2022-05-06 07:04:54', 0, 1, 7, 1, 1) ON DUPLICATE KEY UPDATE `dateUpdated`='2022-05-06 07:04:54', `propagated`=0, `userId`=1

Error Info: Array
(
    [0] => 23000
    [1] => 1452
    [2] => Cannot add or update a child row: a foreign key constraint fails (`craftneotest`.`changedfields`, CONSTRAINT `fk_bwctfshqbczvcsbsgtaiccdnwwfbjwbnimhv` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
)

[ttempleton]

Thanks for attaching those files. At first, I was getting that error (among others, including a bunch of JavaScript errors) with the Neo entry type, as well as with a non-default Matrix entry type that I added, when I was running it with PHP 8. When I ran it with PHP 7.4, it was working fine; then when I went back to PHP 8 and upgraded vlucas/phpdotenv to ^5.4.0, it worked fine. So I'm guessing you're running PHP 8 and that this issue is being caused by an old version of vlucas/phpdotenv that isn't fully compatible with PHP 8.

Going to close this issue for now and recommend that you upgrade vlucas/phpdotenv (along with following these instructions for updating the relevant files in your project), but please let me know if that doesn't resolve the issue for you.

[ttempleton]

It turns out that the just-released Craft 3.7.41 and 4.0.1 include a fix for a similar error (https://github.com/craftcms/cms/issues/11083), so try upgrading Craft first.

[elfacht]

Updating to Craft 3.7.41 seems to be the solution. I will have a look at it and thanks for your quick feedback!

[elfacht]

Unfortunetely it doesn't work. It did for a few times, but adding more Neo blocks to a page will cause the same exception again. I updated Craft to 3.7.41, than to 4.0.1, had no problems two days ago, but today the problems came back.

I tried to update vlucas/phpdotenv to ^5.4.0 as you recommended, but all I got is:

Fatal error: Uncaught TypeError: Dotenv\Dotenv::__construct(): Argument #1 ($store) must be of type Dotenv\Store\StoreInterface, string given, called in /app/cms/web/index.php on line 15 and defined in /app/cms/vendor/vlucas/phpdotenv/src/Dotenv.php:60 Stack trace: #0 /app/cms/web/index.php(15): Dotenv\Dotenv->__construct('/app/cms') #1 {main} thrown in /app/cms/vendor/vlucas/phpdotenv/src/Dotenv.php on line 60

So I rolled it back to v2.6.9.

I don't now if it's a Craft issue, but it only happens with Neo blocks.

Update:
I think I've found the culprit. I changed the asset location path once, had a problem after emptying the cache manually and had to rebuild the asset index. I tested it with assets I hav uploaded it before that and after that. Turned it that the exception only occurs with the older images (as of today). I will investigate further.

Update #2:
It's not what I thought a few lines above. It happens completely random now.

[ttempleton]

Updating vlucas/phpdotenv will require you to update your project's craft, bootstrap.php, and web/index.php files.

That said, the issues I was having related to that were pretty consistent...

Is that still using the above-attached database backup, just upgraded to Craft 4?

[elfacht]

@ttempleton Updating these files would mean a temporary fix that would be overwritten with the next Craft update. So I didn't change anything there and wait until Craft does.

Is that still using the above-attached database backup, just upgraded to Craft 4?

No, I continued working on my project which has a pretty similiar setup. I first upgraded to 3.7.41 and then updated as recommended by Craft. At first everything went well, but then the error shows up randomly on save. I thought I've found a pattern here, but well …

[ttempleton]

Updating these files would mean a temporary fix that would be overwritten with the next Craft update. So I didn't change anything there and wait until Craft does.

I don't think those files are touched by Craft updates, otherwise Craft updates would enforce vlucas/phpdotenv updates along with the new versions of those files.

At this point, I'm not convinced that that's related to the error, though, if the error happens randomly. I'll keep an eye out for it today and see if I can get it to happen.

[elfacht]

@ttempleton Thanks! I will try to update these files. I also can provide the project db dump later this day.

[ttempleton]

Just to clarify, is the error you're getting at the moment for the changedfields or neoblocks_owners table?

[elfacht]

I'm not at my private machine right now, but I'm pretty sure that it's the neoblocks_owners table. I can tell for sure later this day.

[elfacht]

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`pcom22`.`pcom_neoblocks_owners`, CONSTRAINT `pcom_fk_cppnicwkchyyhcqacsqsvazgaekgxmictqww` FOREIGN KEY (`blockId`) REFERENCES `pcom_neoblocks` (`id`) ON DELETE CASCADE) in /app/cms/vendor/yiisoft/yii2/db/Command.php:1302

[elfacht]

I also updated vlucas/phpdotenv and all required files as well and still got this.

[elfacht]

Ok, last try:

• I freshly installed Craft 4 from the official source, created a new database.

• Using Nitro with PHP 8.0 and MySQL.

• I installed all the fields, sections with the project files: project.zip / composer.json.zip

• I created a post, using the entry type Wandern, added a bunch of Blocks:

  • 1 Mediabox
  • 1 Gallery with 3 images

• Saving the post works fine, until I changed the order of the images in the Gallery block. This might be random, but the error pops up again.

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`paracudacom`.`pcom_neoblocks_owners`, CONSTRAINT `pcom_fk_wmmpvntwkbnofotjbgwfldxlhbxszpphqdzq` FOREIGN KEY (`blockId`) REFERENCES `pcom_neoblocks` (`id`) ON DELETE CASCADE)
The SQL being executed was: INSERT INTO `pcom_neoblocks_owners` (`blockId`, `ownerId`, `sortOrder`) VALUES (244, 268, 1), (229, 268, 2), (250, 268, 3), (231, 268, 4), (264, 268, 5), (263, 268, 6)

Once this error occures, every save would throw this exception – and the entry remains in unsaved draft mode:

If you need more data or files to reproduce, just let me know.

[ttempleton]

I'm still unable to reproduce the error, but there has been a Craft issue opened recently about the same error with the matrixblocks_owners table (https://github.com/craftcms/cms/issues/11155). How Neo batch-inserts revision block ownership data is very similar to how Matrix does it, so I suspect the two errors could have the same cause. Could you please double-check whether you can get this error to happen with a Matrix field on Craft 4?

[elfacht]

I tested it with Matrix fields and you're right, I have the same problem. I will head to the Craft issue, thanks!

[ttempleton]

I'll close this issue again for now, but happy to reopen it if the Neo error turns out to be unrelated.