1

Randomly getting the "Integrity constraint violation" sql error on saving entry from backend. There no apparent cause of this at least not that I can see, because the same post saves without errors when I try again.

Craft version is : 2.6.3017

Here is the error and Stack trace:

[error] [system.db.CDbCommand] CDbCommand::execute() failed: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_production`.`craft_relations`, CONSTRAINT `craft_relations_targetId_fk` FOREIGN KEY (`targetId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE). The SQL statement executed was: INSERT INTO `craft_relations` (`fieldId`, `sourceId`, `sourceLocale`, `targetId`, `sortOrder`, `dateCreated`, `dateUpdated`, `uid`) VALUES (:row0_col0, :row0_col1, NULL, :row0_col3, :row0_col4, :row0_col5, :row0_col6, :row0_col7), (:row1_col0, :row1_col1, NULL, :row1_col3, :row1_col4, :row1_col5, :row1_col6, :row1_col7), (:row2_col0, :row2_col1, NULL, :row2_col3, :row2_col4, :row2_col5, :row2_col6, :row2_col7), (:row3_col0, :row3_col1, NULL, :row3_col3, :row3_col4, :row3_col5, :row3_col6, :row3_col7), (:row4_col0, :row4_col1, NULL, :row4_col3, :row4_col4, :row4_col5, :row4_col6, :row4_col7), (:row5_col0, :row5_col1, NULL, :row5_col3, :row5_col4, :row5_col5, :row5_col6, :row5_col7).

[error] [exception.CDbException] CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_production`.`craft_relations`, CONSTRAINT `craft_relations_targetId_fk` FOREIGN KEY (`targetId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE) in /var/www/mysite.com/craft/app/framework/db/CDbCommand.php:358

Stack trace:
#0 /var/www/mysite.com/craft/app/etc/db/DbCommand.php(370): CDbCommand->execute(Array)
#1 /var/www/mysite.com/craft/app/services/RelationsService.php(74): Craft\DbCommand->insertAll('craft_relations', Array, Array)
#2 /var/www/mysite.com/craft/app/fieldtypes/BaseElementFieldType.php(291): Craft\RelationsService->saveRelations(Object(Craft\FieldModel), Object(Craft\EntryModel), Array)
#3 /var/www/mysite.com/craft/app/services/ElementsService.php(1608): Craft\BaseElementFieldType->onAfterElementSave()
#4 /var/www/mysite.com/craft/app/services/EntriesService.php(176): Craft\ElementsService->saveElement(Object(Craft\EntryModel))
#5 /var/www/mysite.com/craft/app/controllers/EntriesController.php(462): Craft\EntriesService->saveEntry(Object(Craft\EntryModel))
#6 /var/www/mysite.com/craft/app/framework/web/actions/CInlineAction.php(49): Craft\EntriesController->actionSaveEntry()
#7 /var/www/mysite.com/craft/app/framework/web/CController.php(308): CInlineAction->runWithParams(Array)
#8 /var/www/mysite.com/craft/app/framework/web/CController.php(286): CController->runAction(Object(CInlineAction))
#9 /var/www/mysite.com/craft/app/framework/web/CController.php(265): CController->runActionWithFilters(Object(CInlineAction), Array)
#10 /var/www/mysite.com/craft/app/framework/web/CWebApplication.php(282): CController->run('saveEntry')
#11 /var/www/mysite.com/craft/app/etc/web/WebApp.php(836): CWebApplication->runController('entries/saveEnt...')
#12 /var/www/mysite.com/craft/app/etc/web/WebApp.php(287): Craft\WebApp->_processActionRequest()
#13 /var/www/mysite.com/craft/app/framework/base/CApplication.php(185): Craft\WebApp->processRequest()
#14 /var/www/mysite.com/craft/app/index.php(73): CApplication->run()
#15 /var/www/mysite.com/public/index.php(15): require_once('/var/www/mysite...')
#16 {main}
REQUEST_URI=/admin/entries/blog/new
HTTP_REFERER=https://mysite.com/admin/entries/blog/new

Anyone ever faced this error? is this a Craft bug?

| improve this question | |
2

It's not a Craft bug.

The error means that the data in your craft_relations table is out of sync with the data in the your craft_elements table. i.e. an ID exists in craft_relations.targetId that doesn't exist in craft_elements.id or visa-versa.

How it got that way is hard to say, but here are some common causes:

  1. Someone manually edits database data without realizing the implications.
  2. A database was imported into a MySQL box that only supported the MyISAM storage engine, which doesn't support foreign keys, so all of the foreign keys that Craft relies on to keep data consistent between tables are silently dropped. If this is the case, you can use https://github.com/pixelandtonic/craft-fixfks to try and restore Craft's foreign keys.
  3. A botched import/export script.
  4. A plugin behaving poorly.

In the context of these two tables, if you want to find all rows that have a value in craft_relations.targetId that doesn't exist in craft_elements.id, the SQL would be similar to:

SELECT * FROM craft_relations r
LEFT OUTER JOIN craft_elements e
ON r.targetId = e.id
WHERE e.id IS NULL
| improve this answer | |
  • Is there any way to figure out exactly what id is it, that doesn't exist in one of these tables? – Jahanzeb Khan Jul 18 '18 at 8:19
  • Added some SQL to the answer. – Brad Bell Jul 18 '18 at 19:10
  • Hey @brad-bell , I ran the SQL and it found no results. That means there aren't any targetId in "craft_relations" that don't exist in "craft_elements". Which is I think expected, because the error is thrown when its trying to add a row in "craft_relations" using and "targetId" which doesn't exist in "craft_elements" table so the row is never added. – Jahanzeb Khan Jul 19 '18 at 8:47
  • So you'll need to adjust the query to go in the other direction and look for results where r.targetId is null. – Brad Bell Jul 19 '18 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.