[5.x]: Queue job creation for search index fails

[apitel]

What happened?

Description

We have a rather complex entry. After the latest update to 5.7.5, we are now experiencing an integrity constraint violation when trying to create this entry.

When craft creates a job for search index, it's trying to attach relationship from a number field to a queue job table and it fails a constraint.

Steps to reproduce

Can provide database and composer file.

Actual behavior

Craft CMS version

Craft Pro 5.7.5

PHP version

8.3.13

Operating system and version

Linux 5.15.0-139-generic

Database type and version

MySQL 8.0.42

Image driver and version

GD 8.3.13

Installed plugins and versions

[brandonkelly]

Not a lot of us to go on here. Are you able to reproduce the issue from a fresh Craft install? If so, what are the actual steps to reproduce?

If you’re unable to do that, please send your Composer files and a database dump to support@craftcms.com, and provide the steps to reproduce from your site, and we can try to look into it from there.

[scottwakefield]

We've seen some instances of this recently on Craft 4.15.0.2 (upgraded from Craft 2), MySQL 8, and PHP 8.3.16.

We haven't been able to reproduce it consistently. But, we've also had some regular MySQL crashes around similar times, so we suspect it might be resource-related. The searchindex table on our project is currently > 1.5 million rows, so we're looking to see whether optimising that table relieves some of the stress on the database.

For context, here's a graph of when the error has occurred recently:

[scottwakefield]

@brandonkelly, we're still seeing instances of this after:

• Upgrading to Craft 4.15.5
• Installing any pending plugin updates
• Truncating and rebuilding the searchindex table
• Doubling the available RAM to the MySQL instance on Servd

We are struggling to reproduce it locally, despite it occurring >400 times in production this month.

Here's an example of one of the PDO exceptions logged:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (craft_searchindexqueue_fields, CONSTRAINT craft_fk_fxxfttaafwochuzrdxidaxonzwihmwseblgk FOREIGN KEY (jobId) REFERENCES craft_searchindexqueue (id) ON DELET)

The endpoint triggering the error is: /admin/actions/elements/save-draft. The stack trace is pointing at the upsert in craft\services\Search::createOrUpdateIndexJob at line 241 being the culprit.

While it might not be related, we did recently add another Site to this particular Craft installation and had no reports of this particular error before then. 🤷🏻‍♂️

Is there anything else I can share that might help debug this?

[brandonkelly]

@scottwakefield Do you happen to have read/write splitting set up for your DB?

[scottwakefield]

@brandonkelly We don't have read/write splitting set up, no.

[brandonkelly]

Alright, I think I see what’s going on. The searchindexqueue table has a foreign key to the elements table with ON DELETE CASCADE. So if the referenced element happens to get hard-deleted in the middle of updating the upserts on the searchindexqueue_fields table (most likely from another request), that SQL error would occur.

Made the code more defensive for that scenario, for the next release.

[scottwakefield]

Thanks @brandonkelly!

[brandonkelly]

Craft 4.15.6.1 and 5.7.8.2 are out with that fix.

[maxfenton]

Thank you for fixing this. I was trying to write up a report for exactly the same thing.

[yonatangdev]

@brandonkelly I have found the same error with environment 5.10.8.1. It started appearing for the last 30 days or so.

[brandonkelly]

@yonatangdev Can you post the full error message + stack trace?

[yonatangdev]

{
  "stackTrace": "[yii\\db\\IntegrityException] SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`[REDACTED_DB]`.`craft_elementactivity`, CONSTRAINT `craft_fk_[REDACTED]` FOREIGN KEY (`draftId`) REFERENCES `craft_drafts` (`id`) ON DELETE CASCADE)\nThe SQL being executed was: INSERT INTO `craft_elementactivity` (`elementId`, `userId`, `siteId`, `draftId`, `type`, `timestamp`) VALUES ([REDACTED], [REDACTED], 1, [REDACTED], 'view', '[REDACTED]') ON DUPLICATE KEY UPDATE `siteId`=VALUES(`siteId`), `draftId`=VALUES(`draftId`), `timestamp`=VALUES(`timestamp`) in /app/vendor/yiisoft/yii2/db/Schema.php:677\n#0 /app/vendor/yiisoft/yii2/db/Command.php(1325): yii\\db\\Schema->convertException(Object(PDOException), 'INSERT INTO `cr...')\n#1 /app/vendor/yiisoft/yii2/db/Command.php(1120): yii\\db\\Command->internalExecute('INSERT INTO `cr...')\n#2 /app/vendor/craftcms/cms/src/helpers/Db.php(1111): yii\\db\\Command->execute()\n#3 /app/vendor/craftcms/cms/src/services/Elements.php(2998): craft\\helpers\\Db::upsert('{{%elementactiv...', Array)\n#4 /app/vendor/craftcms/cms/src/controllers/ElementsController.php(2556): craft\\services\\Elements->trackActivity(Object(craft\\elements\\Entry), 'view', Object(craft\\elements\\User))\n#5 [internal function]: craft\\controllers\\ElementsController->actionRecentActivity()\n#6 /app/vendor/yiisoft/yii2/base/InlineAction.php(60): call_user_func_array(Array, Array)\n#7 /app/vendor/yiisoft/yii2/base/Controller.php(182): yii\\base\\InlineAction->runWithParams(Array)\n#8 /app/vendor/yiisoft/yii2/base/Module.php(547): yii\\base\\Controller->runAction('recent-activity', Array)\n#9 /app/vendor/craftcms/cms/src/web/Application.php(369): yii\\base\\Module->runAction('elements/recent...', Array)\n#10 /app/vendor/craftcms/cms/src/web/Application.php(636): craft\\web\\Application->runAction('elements/recent...', Array)\n#11 /app/vendor/craftcms/cms/src/web/Application.php(331): craft\\web\\Application->_processActionRequest(Object(craft\\web\\Request))\n#12 /app/vendor/yiisoft/yii2/base/Application.php(382): craft\\web\\Application->handleRequest(Object(craft\\web\\Request))\n#13 /app/web/index.php(13): yii\\base\\Application->run()\n#14 {main}\nCaused by:\n[PDOException] SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`[REDACTED_DB]`.`craft_elementactivity`, CONSTRAINT `craft_fk_[REDACTED]` FOREIGN KEY (`draftId`) REFERENCES `craft_drafts` (`id`) ON DELETE CASCADE) in /app/vendor/yiisoft/yii2/db/Command.php:1320\n#0 /app/vendor/yiisoft/yii2/db/Command.php(1320): PDOStatement->execute()\n#1 /app/vendor/yiisoft/yii2/db/Command.php(1120): yii\\db\\Command->internalExecute('INSERT INTO `cr...')\n#2 /app/vendor/craftcms/cms/src/helpers/Db.php(1111): yii\\db\\Command->execute()\n#3 /app/vendor/craftcms/cms/src/services/Elements.php(2998): craft\\helpers\\Db::upsert('{{%elementactiv...', Array)\n#4 /app/vendor/craftcms/cms/src/controllers/ElementsController.php(2556): craft\\services\\Elements->trackActivity(Object(craft\\elements\\Entry), 'view', Object(craft\\elements\\User))\n#5 [internal function]: craft\\controllers\\ElementsController->actionRecentActivity()\n#6 /app/vendor/yiisoft/yii2/base/InlineAction.php(60): call_user_func_array(Array, Array)\n#7 /app/vendor/yiisoft/yii2/base/Controller.php(182): yii\\base\\InlineAction->runWithParams(Array)\n#8 /app/vendor/yiisoft/yii2/base/Module.php(547): yii\\base\\Controller->runAction('recent-activity', Array)\n#9 /app/vendor/craftcms/cms/src/web/Application.php(369): yii\\base\\Module->runAction('elements/recent...', Array)\n#10 /app/vendor/craftcms/cms/src/web/Application.php(636): craft\\web\\Application->runAction('elements/recent...', Array)\n#11 /app/vendor/craftcms/cms/src/web/Application.php(331): craft\\web\\Application->_processActionRequest(Object(craft\\web\\Request))\n#12 /app/vendor/yiisoft/yii2/base/Application.php(382): craft\\web\\Application->handleRequest(Object(craft\\web\\Request))\n#13 /app/web/index.php(13): yii\\base\\Application->run()\n#14 {main}",
  "message": "SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`[REDACTED_DB]`.`craft_elementactivity`, CONSTRAINT `craft_fk_[REDACTED]` FOREIGN KEY (`draftId`) REFERENCES `craft_drafts` (`id`) ON DELETE CASCADE)\nThe SQL being executed was: INSERT INTO `craft_elementactivity` (`elementId`, `userId`, `siteId`, `draftId`, `type`, `timestamp`) VALUES ([REDACTED], [REDACTED], 1, [REDACTED], 'view', '[REDACTED]') ON DUPLICATE KEY UPDATE `siteId`=VALUES(`siteId`), `draftId`=VALUES(`draftId`), `timestamp`=VALUES(`timestamp`)\n--- stack ---\n[yii\\db\\IntegrityException] SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`[REDACTED_DB]`.`craft_elementactivity`, CONSTRAINT `craft_fk_[REDACTED]` FOREIGN KEY (`draftId`) REFERENCES `craft_drafts` (`id`) ON DELETE CASCADE)\nThe SQL being executed was: INSERT INTO `craft_elementactivity` (`elementId`, `userId`, `siteId`, `draftId`, `type`, `timestamp`) VALUES ([REDACTED], [REDACTED], 1, [REDACTED], 'view', '[REDACTED]') ON DUPLICATE KEY UPDATE `siteId`=VALUES(`siteId`), `draftId`=VALUES(`draftId`), `timestamp`=VALUES(`timestamp`) in /app/vendor/yiisoft/yii2/db/Schema.php:677\n#0 /app/vendor/yiisoft/yii2/db/Command.php(1325): yii\\db\\Schema->convertException(Object(PDOException), 'INSERT INTO `cr...')\n#1 /app/vendor/yiisoft/yii2/db/Command.php(1120): yii\\db\\Command->internalExecute('INSERT INTO `cr...')\n#2 /app/vendor/craftcms/cms/src/helpers/Db.php(1111): yii\\db\\Command->execute()\n#3 /app/vendor/craftcms/cms/src/services/Elements.php(2998): craft\\helpers\\Db::upsert('{{%elementactiv...', Array)\n#4 /app/vendor/craftcms/cms/src/controllers/ElementsController.php(2556): craft\\services\\Elements->trackActivity(Object(craft\\elements\\Entry), 'view', Object(craft\\elements\\User))\n#5 [internal function]: craft\\controllers\\ElementsController->actionRecentActivity()\n#6 /app/vendor/yiisoft/yii2/base/InlineAction.php(60): call_user_func_array(Array, Array)\n#7 /app/vendor/yiisoft/yii2/base/Controller.php(182): yii\\base\\InlineAction->runWithParams(Array)\n#8 /app/vendor/yiisoft/yii2/base/Module.php(547): yii\\base\\Controller->runAction('recent-activity', Array)\n#9 /app/vendor/craftcms/cms/src/web/Application.php(369): yii\\base\\Module->runAction('elements/recent...', Array)\n#10 /app/vendor/craftcms/cms/src/web/Application.php(636): craft\\web\\Application->runAction('elements/recent...', Array)\n#11 /app/vendor/craftcms/cms/src/web/Application.php(331): craft\\web\\Application->_processActionRequest(Object(craft\\web\\Request))\n#12 /app/vendor/yiisoft/yii2/base/Application.php(382): craft\\web\\Application->handleRequest(Object(craft\\web\\Request))\n#13 /app/web/index.php(13): yii\\base\\Application->run()\n#14 {main}\nCaused by:\n[PDOException] SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`[REDACTED_DB]`.`craft_elementactivity`, CONSTRAINT `craft_fk_[REDACTED]` FOREIGN KEY (`draftId`) REFERENCES `craft_drafts` (`id`) ON DELETE CASCADE) in /app/vendor/yiisoft/yii2/db/Command.php:1320\n#0 /app/vendor/yiisoft/yii2/db/Command.php(1320): PDOStatement->execute()\n#1 /app/vendor/yiisoft/yii2/db/Command.php(1120): yii\\db\\Command->internalExecute('INSERT INTO `cr...')\n#2 /app/vendor/craftcms/cms/src/helpers/Db.php(1111): yii\\db\\Command->execute()\n#3 /app/vendor/craftcms/cms/src/services/Elements.php(2998): craft\\helpers\\Db::upsert('{{%elementactiv...', Array)\n#4 /app/vendor/craftcms/cms/src/controllers/ElementsController.php(2556): craft\\services\\Elements->trackActivity(Object(craft\\elements\\Entry), 'view', Object(craft\\elements\\User))\n#5 [internal function]: craft\\controllers\\ElementsController->actionRecentActivity()\n#6 /app/vendor/yiisoft/yii2/base/InlineAction.php(60): call_user_func_array(Array, Array)\n#7 /app/vendor/yiisoft/yii2/base/Controller.php(182): yii\\base\\InlineAction->runWithParams(Array)\n#8 /app/vendor/yiisoft/yii2/base/Module.php(547): yii\\base\\Controller->runAction('recent-activity', Array)\n#9 /app/vendor/craftcms/cms/src/web/Application.php(369): yii\\base\\Module->runAction('elements/recent...', Array)\n#10 /app/vendor/craftcms/cms/src/web/Application.php(636): craft\\web\\Application->runAction('elements/recent...', Array)\n#11 /app/vendor/craftcms/cms/src/web/Application.php(331): craft\\web\\Application->_processActionRequest(Object(craft\\web\\Request))\n#12 /app/vendor/yiisoft/yii2/base/Application.php(382): craft\\web\\Application->handleRequest(Object(craft\\web\\Request))\n#13 /app/web/index.php(13): yii\\base\\Application->run()\n#14 {main}",
  "context": {
    "memory": 7099416,
    "trace": [
      "#0 /app/vendor/yiisoft/yii2/db/Command.php(1325): yii\\db\\Schema->convertException(Object(PDOException), 'INSERT INTO `cr...')",
      "#1 /app/vendor/yiisoft/yii2/db/Command.php(1120): yii\\db\\Command->internalExecute('INSERT INTO `cr...')",
      "#2 /app/vendor/craftcms/cms/src/helpers/Db.php(1111): yii\\db\\Command->execute()",
      "#3 /app/vendor/craftcms/cms/src/services/Elements.php(2998): craft\\helpers\\Db::upsert('{{%elementactiv...', Array)",
      "#4 /app/vendor/craftcms/cms/src/controllers/ElementsController.php(2556): craft\\services\\Elements->trackActivity(Object(craft\\elements\\Entry), 'view', Object(craft\\elements\\User))",
      "#5 [internal function]: craft\\controllers\\ElementsController->actionRecentActivity()",
      "#6 /app/vendor/yiisoft/yii2/base/InlineAction.php(60): call_user_func_array(Array, Array)",
      "#7 /app/vendor/yiisoft/yii2/base/Controller.php(182): yii\\base\\InlineAction->runWithParams(Array)",
      "#8 /app/vendor/yiisoft/yii2/base/Module.php(547): yii\\base\\Controller->runAction('recent-activity', Array)",
      "#9 /app/vendor/craftcms/cms/src/web/Application.php(369): yii\\base\\Module->runAction('elements/recent...', Array)",
      "#10 /app/vendor/craftcms/cms/src/web/Application.php(636): craft\\web\\Application->runAction('elements/recent...', Array)",
      "#11 /app/vendor/craftcms/cms/src/web/Application.php(331): craft\\web\\Application->_processActionRequest(Object(craft\\web\\Request))",
      "#12 /app/vendor/yiisoft/yii2/base/Application.php(382): craft\\web\\Application->handleRequest(Object(craft\\web\\Request))",
      "#13 /app/web/index.php(13): yii\\base\\Application->run()",
      "#14 {main}"
    ],
    "exception": {
      "file": "/app/vendor/yiisoft/yii2/db/Schema.php:677",
      "class": "yii\\db\\IntegrityException",
      "message": "SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`[REDACTED_DB]`.`craft_elementactivity`, CONSTRAINT `craft_fk_[REDACTED]` FOREIGN KEY (`draftId`) REFERENCES `craft_drafts` (`id`) ON DELETE CASCADE)\nThe SQL being executed was: INSERT INTO `craft_elementactivity` (`elementId`, `userId`, `siteId`, `draftId`, `type`, `timestamp`) VALUES ([REDACTED], [REDACTED], 1, [REDACTED], 'view', '[REDACTED]') ON DUPLICATE KEY UPDATE `siteId`=VALUES(`siteId`), `draftId`=VALUES(`draftId`), `timestamp`=VALUES(`timestamp`)",
      "trace": [
        "/app/vendor/yiisoft/yii2/db/Command.php:1325",
        "/app/vendor/yiisoft/yii2/db/Command.php:1120",
        "/app/vendor/craftcms/cms/src/helpers/Db.php:1111",
        "/app/vendor/craftcms/cms/src/services/Elements.php:2998",
        "/app/vendor/craftcms/cms/src/controllers/ElementsController.php:2556",
        "/app/vendor/yiisoft/yii2/base/InlineAction.php:60",
        "/app/vendor/yiisoft/yii2/base/Controller.php:182",
        "/app/vendor/yiisoft/yii2/base/Module.php:547",
        "/app/vendor/craftcms/cms/src/web/Application.php:369",
        "/app/vendor/craftcms/cms/src/web/Application.php:636",
        "/app/vendor/craftcms/cms/src/web/Application.php:331",
        "/app/vendor/yiisoft/yii2/base/Application.php:382",
        "/app/web/index.php:13"
      ],
      "code": 23000,
      "previous": {
        "class": "PDOException",
        "file": "/app/vendor/yiisoft/yii2/db/Command.php:1320",
        "code": 23000,
        "trace": [
          "/app/vendor/yiisoft/yii2/db/Command.php:1320",
          "/app/vendor/yiisoft/yii2/db/Command.php:1120",
          "/app/vendor/craftcms/cms/src/helpers/Db.php:1111",
          "/app/vendor/craftcms/cms/src/services/Elements.php:2998",
          "/app/vendor/craftcms/cms/src/controllers/ElementsController.php:2556",
          "/app/vendor/yiisoft/yii2/base/InlineAction.php:60",
          "/app/vendor/yiisoft/yii2/base/Controller.php:182",
          "/app/vendor/yiisoft/yii2/base/Module.php:547",
          "/app/vendor/craftcms/cms/src/web/Application.php:369",
          "/app/vendor/craftcms/cms/src/web/Application.php:636",
          "/app/vendor/craftcms/cms/src/web/Application.php:331",
          "/app/vendor/yiisoft/yii2/base/Application.php:382",
          "/app/web/index.php:13"
        ],
        "message": "SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`[REDACTED_DB]`.`craft_elementactivity`, CONSTRAINT `craft_fk_[REDACTED]` FOREIGN KEY (`draftId`) REFERENCES `craft_drafts` (`id`) ON DELETE CASCADE)"
      }
    }
  }
}