cms
cms copied to clipboard
craftcms
[4.x]: Integrity constraint violation on resaving entries
What happened?
Description
Got this error today while resaving entries :
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db`.`searchindexqueue_fields`, CONSTRAINT `fk_pvcxbujjvsoxwphmterhoupwikguxokpulcw` FOREIGN KEY (`jobId`) REFERENCES `searchindexqueue` (`id`) ON DELETE CASCADE)
The SQL being executed was: INSERT INTO `searchindexqueue_fields` (`jobId`, `fieldHandle`) VALUES (20753, 'packageSearchValues') ON DUPLICATE KEY UPDATE `jobId`=`searchindexqueue_fields`.`jobId`
This was triggered by a webhook (paypal) related to an order, this order has related entries that need resaved when the order status changes.
The entries are resaved with a simple Craft::$app->elements->saveElement($entry, false);
I don't understand what this mean or what we can do to avoid it. Could you share some light on it ?
This might be related to some other issues we have with updating search indexes : https://github.com/craftcms/commerce/issues/3711
Craft CMS version
4.5.15
PHP version
8.3
Operating system and version
No response
Database type and version
No response
Image driver and version
No response
Installed plugins and versions
No response
Hi, thanks for getting in touch! The searchindexqueue_fields table was added in version 4.15.0 (via https://github.com/craftcms/cms/pull/16644), but your report says you’re on 4.5.15? Can you please share a full stack trace for when you’re getting this error?
@i-just stack trace :
#40 /***/httpdocs/vendor/yiisoft/yii2/db/Command.php(1320): PDOStatement::execute
#39 /***/httpdocs/vendor/yiisoft/yii2/db/Command.php(1320): yii\db\Command::internalExecute
#38 /***/httpdocs/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command::execute
#37 /***/httpdocs/vendor/craftcms/cms/src/helpers/Db.php(1047): craft\helpers\Db::upsert
#36 /***/httpdocs/vendor/craftcms/cms/src/services/Search.php(241): craft\services\Search::createOrUpdateIndexJob
#35 /***/httpdocs/vendor/craftcms/cms/src/services/Search.php(213): craft\services\Search::queueIndexElement
#34 /***/httpdocs/vendor/craftcms/cms/src/services/Elements.php(3573): craft\services\Elements::_saveElementInternal
#33 /***/httpdocs/vendor/craftcms/cms/src/services/Elements.php(1108): craft\services\Elements::saveElement
#32 /***/httpdocs/modules/site/src/services/Packages.php(760): modules\site\services\Packages::resavePackages
#31 /***/httpdocs/modules/site/src/services/Packages.php(324): modules\site\services\Packages::resaveOrderPackages
#30 /***/httpdocs/modules/site/src/services/Orders.php(942): modules\site\services\Orders::orderStatusChange
#29 [internal](0): call_user_func
#28 /***/httpdocs/vendor/yiisoft/yii2/base/Event.php(312): yii\base\Event::trigger
#27 /***/httpdocs/vendor/yiisoft/yii2/base/Component.php(654): yii\base\Component::trigger
#26 /***/httpdocs/vendor/craftcms/commerce/src/services/OrderHistories.php(143): craft\commerce\services\OrderHistories::createOrderHistoryFromOrder
#25 /***/httpdocs/vendor/craftcms/commerce/src/elements/Order.php(3612): craft\commerce\elements\Order::_saveOrderHistory
#24 /***/httpdocs/vendor/craftcms/commerce/src/elements/Order.php(2218): craft\commerce\elements\Order::afterSave
#23 /***/httpdocs/vendor/craftcms/cms/src/services/Elements.php(3462): craft\services\Elements::_saveElementInternal
#22 /***/httpdocs/vendor/craftcms/cms/src/services/Elements.php(1108): craft\services\Elements::saveElement
#21 /***/httpdocs/vendor/craftcms/commerce/src/elements/Order.php(1760): craft\commerce\elements\Order::markAsComplete
#20 /***/httpdocs/vendor/craftcms/commerce/src/elements/Order.php(1669): craft\commerce\elements\Order::updateOrderPaidInformation
#19 /***/httpdocs/vendor/craftcms/commerce/src/services/Transactions.php(475): craft\commerce\services\Transactions::saveTransaction
#18 /***/httpdocs/vendor/craftcms/commerce/src/services/Payments.php(616): craft\commerce\services\Payments::_saveTransaction
#17 /***/httpdocs/vendor/craftcms/commerce/src/services/Payments.php(641): craft\commerce\services\Payments::_updateTransaction
#16 /***/httpdocs/vendor/craftcms/commerce/src/services/Payments.php(296): craft\commerce\services\Payments::processPayment
#15 /***/httpdocs/vendor/craftcms/commerce/src/controllers/PaymentsController.php(468): craft\commerce\controllers\PaymentsController::actionPay
We're also seeing this error on a site running Craft 5.7.6. It seems to happen when content editors are working on entries, and there's a lot of "Updating search indexes" jobs.
Stack trace for reference:
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails
(`srhd`.`craft_searchindexqueue_fields`,
CONSTRAINT `craft_fk_lnpmkcyjlpeebmqyhgngrpasvlmbgyrdkxsi`
FOREIGN KEY (`jobId`) REFERENCES `craft_searchindexqueue` (`id`) ON DELETE CASCADE)
in /home/user/site/vendor/yiisoft/yii2/db/Command.php:1320
Stack trace:
#0 /home/user/site/vendor/yiisoft/yii2/db/Command.php(1320): PDOStatement->execute()
#1 /home/user/site/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command->internalExecute()
#2 /home/user/site/vendor/craftcms/cms/src/helpers/Db.php(1098): yii\db\Command->execute()
#3 /home/user/site/vendor/craftcms/cms/src/services/Search.php(252): craft\helpers\Db::upsert()
#4 /home/user/site/vendor/craftcms/cms/src/services/Search.php(224): craft\services\Search->createOrUpdateIndexJob()
#5 /home/user/site/vendor/craftcms/cms/src/services/Elements.php(4116): craft\services\Search->queueIndexElement()
#6 /home/user/site/vendor/craftcms/cms/src/services/Elements.php(4051): craft\services\Elements->updateSearchIndex()
#7 /home/user/site/vendor/craftcms/cms/src/services/Elements.php(1251): craft\services\Elements->craft\services\{closure}()
#8 /home/user/site/vendor/craftcms/cms/src/services/Elements.php(3744): craft\services\Elements->ensureBulkOp()
#9 /home/user/site/vendor/craftcms/cms/src/services/Elements.php(1337): craft\services\Elements->_saveElementInternal()
#10 /home/user/site/vendor/craftcms/cms/src/elements/NestedElementManager.php(802): craft\services\Elements->saveElement()
#11 /home/user/site/vendor/craftcms/cms/src/elements/NestedElementManager.php(681): craft\elements\NestedElementManager->saveNestedElements()
#12 /home/user/site/vendor/craftcms/cms/src/fields/Matrix.php(1447): craft\elements\NestedElementManager->maintainNestedElements()
#13 /home/user/site/vendor/craftcms/cms/src/base/Element.php(6474): craft\fields\Matrix->afterElementPropagate()
#14 /home/user/site/vendor/craftcms/cms/src/elements/Entry.php(2856): craft\base\Element->afterPropagate()
#15 /home/user/site/vendor/craftcms/cms/src/services/Elements.php(3990): craft\elements\Entry->afterPropagate()
#16 /home/user/site/vendor/craftcms/cms/src/services/Elements.php(1251): craft\services\Elements->craft\services\{closure}()
#17 /home/user/site/vendor/craftcms/cms/src/services/Elements.php(3744): craft\services\Elements->ensureBulkOp()
#18 /home/user/site/vendor/craftcms/cms/src/services/Elements.php(1337): craft\services\Elements->_saveElementInternal()
#19 /home/user/site/vendor/craftcms/cms/src/controllers/ElementsController.php(1887): craft\services\Elements->saveElement()
#20 [internal function]: craft\controllers\ElementsController->actionSaveDraft()
#21 /home/user/site/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#22 /home/user/site/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()
#23 /home/user/site/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction()
#24 /home/user/site/vendor/craftcms/cms/src/web/Application.php(361): yii\base\Module->runAction()
#25 /home/user/site/vendor/craftcms/cms/src/web/Application.php(660): craft\web\Application->runAction()
#26 /home/user/site/vendor/craftcms/cms/src/web/Application.php(323): craft\web\Application->_processActionRequest()
#27 /home/user/site/vendor/yiisoft/yii2/base/Application.php(384): craft\web/Application->handleRequest()
#28 /home/user/site/web/index.php(21): yii\base\Application->run()
#29 {main}
Edit: I see an update might resolve this: https://github.com/craftcms/cms/issues/17268#issuecomment-2922856795. Will give that a go.
