SQL Integrity constraint violation error during craft garbage collection task

[damonadigital]

Description

We've experienced an SQL Integrity constraint violation error a few times last week during crafts garbage collection task. Here's the full output:

t9n:["app","Updating search indexes"] was pushed at 07/04/2024 12:40:48 and reserved at 07/04/2024 11:40:49 It was updated at 07/04/2024 12:40:49. This was attempt number 1. The task failed at 07/04/2024 11:41:21. The error was The command "'/usr/local/bin/php' '/var/www/html/craft' 'queue/exec' '15559040' '900' '1' '14349' '--verbose=1' '--color='" failed.

Exit Code: 1(General error)

Working directory: /var/www/html/web

Output:
purging unsaved drafts that have gone stale ... done
deleting stale user sessions ... done
deleting stale feature announcements ... done
deleting stale element activity records ... done
deleting trashed elements ... done
deleting trashed rows in the {{%categorygroups}} table ... done
deleting trashed rows in the {{%entrytypes}} table ... done
deleting trashed rows in the {{%fieldgroups}} table ... done
deleting trashed rows in the {{%sections}} table ... done
deleting trashed rows in the {{%taggroups}} table ... done
deleting partial asset data in the {{%assets}} table ... done
deleting partial category data in the {{%categories}} table ... done
deleting partial entry data in the {{%entries}} table ... done
deleting partial global set data in the {{%globalsets}} table ... done
deleting partial Matrix block data in the {{%matrixblocks}} table ... done
deleting partial tag data in the {{%tags}} table ... done
deleting partial user data in the {{%users}} table ... done
deleting partial asset data in the {{%content}} table ... done
deleting partial category data in the {{%content}} table ... done
deleting partial entry data in the {{%content}} table ... done
deleting partial global set data in the {{%content}} table ... done
deleting partial tag data in the {{%content}} table ... done
deleting partial user data in the {{%content}} table ... done
deleting entries in unsupported sites ... done
deleting orphaned drafts and revisions ... done
deleting orphaned search indexes ... done
deleting orphaned relations ... done
deleting orphaned structure elements ... done
purging inactive carts ...

Error Output:
Error: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (quarrelsome_insect_production.craft_stripe_paymentintents, CONSTRAINT craft_stripe_paymentintents_craft_commerce_orders_id_fk FOREIGN KEY (orderId) REFERENCES craft_commerce_orders ()
The SQL being executed was: DELETE FROM craft_elements WHERE id IN (SELECT orders.id
FROM craft_commerce_orders orders
WHERE (NOT (isCompleted=TRUE)) AND (orders.dateUpdated <= '2024-01-07 11:41:14'))
2024-04-07 12:41:21 [console.ERROR] [yii\db\IntegrityException] SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (quarrelsome_insect_production.craft_stripe_paymentintents, CONSTRAINT craft_stripe_paymentintents_craft_commerce_orders_id_fk FOREIGN KEY (orderId) REFERENCES craft_commerce_orders () The SQL being executed was: DELETE FROM craft_elements WHERE id IN (SELECT orders.id FROM craft_commerce_orders orders WHERE (NOT (isCompleted=TRUE)) AND (orders.dateUpdated <= '2024-01-07 11:41:14')) {"trace":["#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'DELETE FROM cr...')","#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('DELETE FROMcr...')","#2 /var/www/html/vendor/craftcms/commerce/src/services/Carts.php(385): yii\db\Command->execute()","#3 /var/www/html/vendor/craftcms/commerce/src/Plugin.php(883): craft\commerce\services\Carts->purgeIncompleteCarts()","#4 [internal function]: craft\commerce\Plugin->craft\commerce{closure}(Object(yii\base\Event))","#5 /var/www/html/vendor/yiisoft/yii2/base/Event.php(312): call_user_func(Object(Closure), Object(yii\base\Event))","#6 /var/www/html/vendor/yiisoft/yii2/base/Component.php(642): yii\base\Event::trigger('craft\services\...', 'run', Object(yii\base\Event))","#7 /var/www/html/vendor/craftcms/cms/src/services/Gc.php(143): yii\base\Component->trigger('run')","#8 /var/www/html/vendor/craftcms/cms/src/base/ApplicationTrait.php(1660): craft\services\Gc->run()","#9 /var/www/html/vendor/craftcms/cms/src/console/Application.php(53): craft\console\Application->_postInit()","#10 /var/www/html/vendor/yiisoft/yii2/base/BaseObject.php(109): craft\console\Application->init()","#11 /var/www/html/vendor/yiisoft/yii2/base/Application.php(204): yii\base\BaseObject->construct(Array)","#12 /var/www/html/vendor/yiisoft/yii2/console/Application.php(89): yii\base\Application->construct(Array)","#13 [internal function]: yii\console\Application->__construct(Array)","#14 /var/www/html/vendor/yiisoft/yii2/di/Container.php(419): ReflectionClass->newInstanceArgs(Array)","#15 /var/www/html/vendor/yiisoft/yii2/di/Container.php(170): yii\di\Container->build('craft\console\A...', Array, Array)","#16 /var/www/html/vendor/yiisoft/yii2/BaseYii.php(365): yii\di\Container->get('craft\console\A...', Array, Array)","#17 /var/www/html/vendor/craftcms/cms/src/Craft.php(59): yii\BaseYii::createObject(Array, Array)","#18 /var/www/html/vendor/craftcms/cms/bootstrap/bootstrap.php(250): Craft::createObject(Array)","#19 /var/www/html/vendor/craftcms/cms/bootstrap/console.php(42): require('/var/www/html/v...')","#20 /var/www/html/craft(12): require('/var/www/html/v...')","#21 {main}"],"memory":38137976,"exception":"[object] (yii\db\IntegrityException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (quarrelsome_insect_production.craft_stripe_paymentintents, CONSTRAINT craft_stripe_paymentintents_craft_commerce_orders_id_fk FOREIGN KEY (orderId) REFERENCES craft_commerce_orders ()\nThe SQL being executed was: DELETE FROM craft_elements WHERE id IN (SELECT orders.id\nFROM craft_commerce_orders orders\nWHERE (NOT (isCompleted=TRUE)) AND (orders.dateUpdated <= '2024-01-07 11:41:14')) at /var/www/html/vendor/yiisoft/yii2/db/Schema.php:676)\n[previous exception] [object] (PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (quarrelsome_insect_production.craft_stripe_paymentintents, CONSTRAINT craft_stripe_paymentintents_craft_commerce_orders_id_fk FOREIGN KEY (orderId) REFERENCES craft_commerce_orders () at /var/www/html/vendor/yiisoft/yii2/db/Command.php:1302)"}
2024-04-07 12:41:21 [console.WARNING] [application] Request context: {"environment":"production","vars":{"_GET":[],"_POST":[],"_FILES":[],"_COOKIE":[]}}

Steps to reproduce

I've not managed to reproduce it manually and we haven't had the problem over the last few days. I thought it would be worth reporting incase you have any insight into the issue or if anyone else has had a similar problem.

Additional info

Craft CMS version: Craft Pro 4.8.3
Craft Commerce version: 4.5.2
Stripe for Craft Commerce version: 3.1.1
PHP version: 8.0.30

Thanks

[linear]

PT-1633 SQL Integrity constraint violation error during "Updating search index" task

[lukeholder]

This looks like it was the Craft garbage collection gc command and not the update search indexes command?

[damonadigital]

Yes you are right, I will amend the title.

In trying to understand the error it looks like it is related to a constraint on a stripe payments intent table.

I can reproduce the issue by running php craft gc
and I get the following:

 purging inactive carts ... Exception 'yii\db\IntegrityException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`cicerone_dev`.`craft_stripe_paymentintents`, CONSTRAINT `craft_stripe_paymentintents_craft_commerce_orders_id_fk` FOREIGN KEY (`orderId`) REFERENCES `craft_commerce_orders` (`id`))
The SQL being executed was: DELETE FROM `craft_elements` WHERE `id` IN (SELECT `orders`.`id`
FROM `craft_commerce_orders` `orders`
WHERE (NOT (`isCompleted`=TRUE)) AND (`orders`.`dateUpdated` <= '2024-01-22 09:32:46'))'

in /srv/users/cicerone/apps/cicerone-dev/vendor/yiisoft/yii2/db/Schema.php:676

Error Info:
Array
(
    [0] => 23000
    [1] => 1451
    [2] => Cannot delete or update a parent row: a foreign key constraint fails (`cicerone_dev`.`craft_stripe_paymentintents`, CONSTRAINT `craft_stripe_paymentintents_craft_commerce_orders_id_fk` FOREIGN KEY (`orderId`) REFERENCES `craft_commerce_orders` (`id`))
)

Caused by: Exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`cicerone_dev`.`craft_stripe_paymentintents`, CONSTRAINT `craft_stripe_paymentintents_craft_commerce_orders_id_fk` FOREIGN KEY (`orderId`) REFERENCES `craft_commerce_orders` (`id`))'