[4.x]: IntegrityException when saving entry at same time as automatic saving

[smcyr]

What happened?

Description

When I have an entry with a couple of simple fields. Sometimes, when I save rapidly at the same time as the automatic saving (which happens often for me when I save with ctrl+s), I get this error:

2022-05-13 12:39:22 [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`.`changedfields`, CONSTRAINT `fk_xsporzogfzkxbycxlxpfdeebgkxthtbdvczt` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE ON UPDATE 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 `ch...')
#2 C:\projects\craft\vendor\craftcms\cms\src\helpers\Db.php(1010): yii\db\Command->execute()
#3 C:\projects\craft\vendor\craftcms\cms\src\fields\BaseRelationField.php(838): craft\helpers\Db::upsert('{{%changedfield...', Array)
#4 [internal function]: craft\fields\BaseRelationField->craft\fields\{closure}(Object(yii\base\Event))
#5 C:\projects\craft\vendor\yiisoft\yii2\base\Component.php(633): call_user_func(Object(Closure), Object(yii\base\Event))
#6 C:\projects\craft\vendor\yiisoft\yii2\base\Application.php(387): yii\base\Component->trigger('afterRequest')
#7 C:\projects\craft\web\index.php(12): yii\base\Application->run()
#8 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`craft`.`changedfields`, CONSTRAINT `fk_xsporzogfzkxbycxlxpfdeebgkxthtbdvczt` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
The SQL being executed was: INSERT INTO `changedfields` (`elementId`, `siteId`, `fieldId`, `dateUpdated`, `propagated`, `userId`) VALUES (1695, 2, 39, '2022-05-13 12:39:22', 0, 4) ON DUPLICATE KEY UPDATE `elementId`=1695, `siteId`=2, `fieldId`=39, `dateUpdated`='2022-05-13 12:39:22', `propagated`=0, `userId`=4 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 `ch...')
#1 C:\projects\craft\vendor\yiisoft\yii2\db\Command.php(1102): yii\db\Command->internalExecute('INSERT INTO `ch...')
#2 C:\projects\craft\vendor\craftcms\cms\src\helpers\Db.php(1010): yii\db\Command->execute()
#3 C:\projects\craft\vendor\craftcms\cms\src\fields\BaseRelationField.php(838): craft\helpers\Db::upsert('{{%changedfield...', Array)
#4 [internal function]: craft\fields\BaseRelationField->craft\fields\{closure}(Object(yii\base\Event))
#5 C:\projects\craft\vendor\yiisoft\yii2\base\Component.php(633): call_user_func(Object(Closure), Object(yii\base\Event))
#6 C:\projects\craft\vendor\yiisoft\yii2\base\Application.php(387): yii\base\Component->trigger('afterRequest')
#7 C:\projects\craft\web\index.php(12): yii\base\Application->run()
#8 {main}
Additional Information:
Array
(
    [0] => 23000
    [1] => 1452
    [2] => Cannot add or update a child row: a foreign key constraint fails (`craft`.`changedfields`, CONSTRAINT `fk_xsporzogfzkxbycxlxpfdeebgkxthtbdvczt` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
)
 {"memory":9972248,"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`.`changedfields`, CONSTRAINT `fk_xsporzogfzkxbycxlxpfdeebgkxthtbdvczt` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
The SQL being executed was: INSERT INTO `changedfields` (`elementId`, `siteId`, `fieldId`, `dateUpdated`, `propagated`, `userId`) VALUES (1695, 2, 39, '2022-05-13 12:39:22', 0, 4) ON DUPLICATE KEY UPDATE `elementId`=1695, `siteId`=2, `fieldId`=39, `dateUpdated`='2022-05-13 12:39:22', `propagated`=0, `userId`=4 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`.`changedfields`, CONSTRAINT `fk_xsporzogfzkxbycxlxpfdeebgkxthtbdvczt` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) at C:\\projects\\craft\\vendor\\yiisoft\\yii2\\db\\Command.php:1302)"} 

If I reload the page and save again, it works fine.

Craft CMS version

4.0.2

PHP version

8.0.18

Operating system and version

Windows 11

Database type and version

MariaDB 10.6.5

Image driver and version

GD 8.0.18

Installed plugins and versions

No response

[darylknight]

This also happens in Craft 3.7.x. I've just had this twice in the last 10 minutes when quick-saving while writing an article.

[adrienpicard]

Same as @darylknight, I just faced that exact error on a 3.7.40.1 install.

[FransvdWijst]

We encounter similar issues in Craft 3.7.42 and are able te reproduce it in a fresh install of Craft.

This error occurs in a very specific situation.

Needed to reproduce:
Craft version: 3.7.42
Plugins: Vizy (1.0.13)

What we did:
1: Create a second website in /admin/settins/sites, so we have 2 websites.
2: Create a file volume, we need that to upload images.
2: Create a text field, file field, and a vizy field (with previously created text and file field (or any other)).
3: Create a new single and set as homepage.
4: Assign Vizy field and image field to this single (in this order: Vizy / Text / File )
5: Try adding Content (in every field). -> wait when draft is saved
6: Save entry.

The error enclosed will occur,

What you can do to not show this error is one of the following:

• Disable vizy plugin
• Disable second website of the homepage single
• remove the file field from homepage (not from a vizy block, this can stay)
• set autosaveDrafts to false in config file

[JohnCBent]

We're also seeing this issue.
Craft version - 3.7.40

It only occurs with a specific entry type. All other entry types save without throwing this error.

The error thrown.

Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`klr_prod`.`changedfields`, CONSTRAINT `changedfields_elementId_fk` 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-19 17:56:21', 0, 1, 167141, 1, 78) ON DUPLICATE KEY UPDATE `dateUpdated`='2022-05-19 17:56:21', `propagated`=0, `userId`=1 in /home/forge/kahnlitwin.com/vendor/yiisoft/yii2/db/Schema.php:67

If I hit the back button and reload the entry edit screen I see the "Entry Saved" cp message and the data I've edited has been saved.

[brandonkelly]

@JohnCBent that sounds more like #11083, which was fixed in 3.7.41.

[domstubbs]

I’m seeing the same errors when saving entries with 3.7.42.

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (DB.craft_changedfields, CONSTRAINT craft_changedfields_elementId_fk FOREIGN KEY (elementId) REFERENCES craft_elements (id) ON DELETE CASCADE ON UPDATE CASCADE)

Plugin info below in case that’s useful.

Plugins →

Amazon S3 1.3.0
Api2Pdf 0.5.0
Asset Rev 6.0.2
Contact Form 2.5.1
Cookies 1.1.15
Dumper 2.0.0
Element API 2.8.5
Elements Panel 1.1.1
Feed Me 4.5.2
Fetch 1.3.0
GeoMate v1.3.1.2
Gold SiteCopy 0.7.0
Guest Entries 2.4.0
Icon Picker 1.1.12
Mailgun 2.0.1
Neo 2.13.10
NSM Fields 2.0.0-RC5
reCAPTCHA 1.5.3
Redactor 2.10.8
SEOMate dev-master
Typed link field 1.0.25
Typogrify 1.1.19

[brandonkelly]

Thanks all! Just released Craft 3.7.43 and 4.0.3 with a fix for this.