search

craftcms / commerce-stripe

Stripe payment gateway for Craft Commerce
https://plugins.craftcms.com/commerce-stripe
MIT License
30 stars 49 forks source link

SQL Integrity constraint violation error during craft garbage collection task #299

Open damonadigital opened 2 months ago

damonadigital commented 2 months ago

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[bot] commented 2 months ago

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

lukeholder commented 2 months ago

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

damonadigital commented 2 months ago

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`))'