Integrity constraint violation: Duplicate entry […] INSERT INTO revisions

[Abromeit]

Bug Description

The specific problem is as follows.

We create a new page in the admin panel and fill it with initial content.
If additional content is added later on, the page can sometimes no longer be saved.
(Regardless of whether we have actually added further content or not. A simple click on the "Save" button is usually enough to trigger the error on the affected pages).

We usually receive feedback from our content team such as "I edited the page two days ago and everything was fine. Today I wanted to add something there and got the error."

We have since observed that the error sometimes disappears by itself after a few days. However, it then resurfaces in new places (pages).

Here is a dump of the error:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '176409-11' for key 'xxxxx_idx_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxoben' in /[…]/vendor/yiisoft/yii2/db/Command.php:1302
Stack trace:
#0 /[…]/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute()
#1 /[…]/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('INSERT INTO `xx...')
#2 /[…]/vendor/craftcms/cms/src/helpers/Db.php(984): yii\db\Command->execute()
#3 /[…]/vendor/craftcms/cms/src/services/Revisions.php(145): craft\helpers\Db::insert('{{%revisions}}', Array)
#4 /[…]/vendor/spicyweb/craft-neo/src/services/Fields.php(693): craft\services\Revisions->createRevision(Object(benf\neo\elements\Block), 2, NULL, Array)
#5 /[…]/vendor/spicyweb/craft-neo/src/Field.php(1040): benf\neo\services\Fields->createRevisionBlocks(Object(benf\neo\Field), Object(craft\elements\Entry), Object(craft\elements\Entry))
#6 /[…]/vendor/craftcms/cms/src/base/Element.php(5181): benf\neo\Field->afterElementPropagate(Object(craft\elements\Entry), true)
#7 /[…]/vendor/craftcms/cms/src/elements/Entry.php(2064): craft\base\Element->afterPropagate(true)
#8 /[…]/vendor/craftcms/cms/src/services/Elements.php(1722): craft\elements\Entry->afterPropagate(true)
#9 /[…]/vendor/craftcms/cms/src/services/Revisions.php(162): craft\services\Elements->duplicateElement(Object(craft\elements\Entry), Array)
#10 /[…]/vendor/craftcms/cms/src/elements/Entry.php(2068): craft\services\Revisions->createRevision(Object(craft\elements\Entry), 2, '')
#11 /[…]/vendor/craftcms/cms/src/services/Elements.php(3423): craft\elements\Entry->afterPropagate(false)
#12 /[…]/vendor/craftcms/cms/src/services/Elements.php(1097): craft\services\Elements->_saveElementInternal(Object(craft\elements\Entry), true, true, true, Array, false, true)
#13 /[…]/vendor/craftcms/cms/src/controllers/ElementsController.php(1139): craft\services\Elements->saveElement(Object(craft\elements\Entry), true, true, NULL, false, true)
#14 [internal function]: craft\controllers\ElementsController->actionSave()
#15 /[…]/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#16 /[…]/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#17 /[…]/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('save', Array)
#18 /[…]/vendor/craftcms/cms/src/web/Application.php(305): yii\base\Module->runAction('elements/save', Array)
#19 /[…]/vendor/craftcms/cms/src/web/Application.php(606): craft\web\Application->runAction('elements/save', Array)
#20 /[…]/vendor/craftcms/cms/src/web/Application.php(284): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#21 /[…]/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#22 /[…]/web/index.php(12): yii\base\Application->run()
#23 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '176409-11' for key 'xxxxx_idx_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxoben'
The SQL being executed was: INSERT INTO `xxxxx_revisions` (`canonicalId`, `creatorId`, `num`, `notes`) VALUES (176409, 2, 11, NULL) in /[…]/vendor/yiisoft/yii2/db/Schema.php:676
Stack trace:
#0 /[…]/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO `xx...')
#1 /[…]/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('INSERT INTO `xx...')
#2 /[…]/vendor/craftcms/cms/src/helpers/Db.php(984): yii\db\Command->execute()
#3 /[…]/vendor/craftcms/cms/src/services/Revisions.php(145): craft\helpers\Db::insert('{{%revisions}}', Array)
#4 /[…]/vendor/spicyweb/craft-neo/src/services/Fields.php(693): craft\services\Revisions->createRevision(Object(benf\neo\elements\Block), 2, NULL, Array)
#5 /[…]/vendor/spicyweb/craft-neo/src/Field.php(1040): benf\neo\services\Fields->createRevisionBlocks(Object(benf\neo\Field), Object(craft\elements\Entry), Object(craft\elements\Entry))
#6 /[…]/vendor/craftcms/cms/src/base/Element.php(5181): benf\neo\Field->afterElementPropagate(Object(craft\elements\Entry), true)
#7 /[…]/vendor/craftcms/cms/src/elements/Entry.php(2064): craft\base\Element->afterPropagate(true)
#8 /[…]/vendor/craftcms/cms/src/services/Elements.php(1722): craft\elements\Entry->afterPropagate(true)
#9 /[…]/vendor/craftcms/cms/src/services/Revisions.php(162): craft\services\Elements->duplicateElement(Object(craft\elements\Entry), Array)
#10 /[…]/vendor/craftcms/cms/src/elements/Entry.php(2068): craft\services\Revisions->createRevision(Object(craft\elements\Entry), 2, '')
#11 /[…]/vendor/craftcms/cms/src/services/Elements.php(3423): craft\elements\Entry->afterPropagate(false)
#12 /[…]/vendor/craftcms/cms/src/services/Elements.php(1097): craft\services\Elements->_saveElementInternal(Object(craft\elements\Entry), true, true, true, Array, false, true)
#13 /[…]/vendor/craftcms/cms/src/controllers/ElementsController.php(1139): craft\services\Elements->saveElement(Object(craft\elements\Entry), true, true, NULL, false, true)
#14 [internal function]: craft\controllers\ElementsController->actionSave()
#15 /[…]/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#16 /[…]/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#17 /[…]/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('save', Array)
#18 /[…]/vendor/craftcms/cms/src/web/Application.php(305): yii\base\Module->runAction('elements/save', Array)
#19 /[…]/vendor/craftcms/cms/src/web/Application.php(606): craft\web\Application->runAction('elements/save', Array)
#20 /[…]/vendor/craftcms/cms/src/web/Application.php(284): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#21 /[…]/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#22 /[…]/web/index.php(12): yii\base\Application->run()
#23 {main}
Additional Information:
Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry '176409-11' for key 'xxxxx_idx_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxoben'
)

Some of the things we already tried, without success:

• Changing the maximum number of allowed revisions
• Executing console commands like the following
  • ./craft gc/run
  • ./craft resave/neo-blocks
  • ./craft utils/fix-element-uids/index
  • ./craft utils/fix-field-layout-uids/index
  • ./craft utils/prune-provisional-drafts/index
  • ./craft utils/repair/section-structure produkte
  • ./craft utils/repair/project-config
• Deleting all pages and adding them again
• Involving specialized 3rd party developers
• Involving craft support

Further information:

There is no custom PHP code in our system. We work exclusively with the field builders of the admin area and also with Twig.

Temporary fixes we've found:

So far, we have found two (unfavorable) solutions that allow pages in question to be saved correctly again:

• Duplicating faulty pages and deleting the original version. (Error might reoccur at any time.)
• Deactivating the revision function for all sections via the admin panel. (Error is gone.)

Steps to reproduce

Unfortunately, we have not yet found a direct way to reproduce the error.

Information about errors like these is therefore passed on to us by our content team as they occur.

---

Please let us know if there is anything else we can do to help. For example, the provision of a DB dump, repository access or the deployment of an additional server instance with the affected system wouldn't be a problem.

Expected behaviour

No response

Neo version

3.9.8

Craft CMS version

4.5.11

What is the affected Neo field's propagation method?

Only save blocks to the site they were created in ("none")

Does this issue involve templating, and if so, is eager-loading used?

This is not a templating issue

[ttempleton]

I'm unable to reproduce this right now, but I've seen it come up rarely in the past. I suspect it isn't a Neo bug since Craft controls the value of num; in particular, it sounds a lot like craftcms/cms#7598.

I can't rule out that Neo is doing something to make Craft set the wrong num, though, so if no information in the above Craft issue resolves the error for you, could you please send in your composer.json/lock files and database backup where this error occurs to [email protected], and let us know which entry it's happening with, and we'll have a look.

[Abromeit]

Thanks for your quick response – Your explanation makes absolute sense.

We do now have another possible option for a fix, based on suggestions from the craft support team.

• In step 1, we deactivated the revision functionality for all sections so that our content team can get back to work. (In parallel, we have increased the frequency of our db backup jobs.)
• After business hours, we will carry out step 2, which has already been tested successfully on a local environment: we will deactivate the neo plugin, run the garbage collection, resave all entries and reactivate neo.

If the error reoccurs after that, I can only report here again and let you take a closer look. (Thank you so much for the offer!) Fortunately, I can save us all the time at the moment.

[ttempleton]

No worries - will leave this issue open for a while in case it happens again.

[brandonkelly]

@Abromeit Are you using the Site Copy plugin?

[Abromeit]

No. Site Copy hasn't been considered for any of our systems.

Here is our list of plugins:

"craftcms/ckeditor",
"craftcms/feed-me",
"nystudio107/craft-retour",
"nystudio107/craft-seomatic",
"putyourlightson/craft-blitz",
"putyourlightson/craft-blitz-recommendations",
"spicyweb/craft-neo"

Just to let you know - the issue came back. Several days after we applied everything that craft support suggested. So we put the website live with revisions disabled.

[i-just]

I’m helping someone with this issue, and the steps from the previous comments help.

In the case that I’m working on, it seems to be caused by this: https://github.com/craftcms/cms/blob/develop/src/services/Revisions.php#L88-L96 not returning any results and therefore going with num set to 1. The reason it’s not returning any results is that in the elements table, there are no elements with the matching canonicalId where the revisionId is also filled out.

For newly created blocks & then their revisions, the canonicalId and revisionId are correctly populated in the elements table.

I’m not quite sure what could be causing it, but I thought I’d share just in case you know why this could happen.

[alexanderloewe]

@ttempleton Should I send you our DB, in our case it is very easy to reproduce the scenario?

[ttempleton]

Thanks @i-just, sounds like some revision Neo block data has somehow incorrectly been deleted.

Yes @alexanderloewe, please send the database backup and composer files to [email protected] and let us know the steps to reproduce the error, and we'll have a look at it.

[ttempleton]

We reproduced this again today, on a project where php craft utils/prune-revisions --max-revisions=0 had been run, followed by php craft gc/run.

I have determined that the core of the issue is that the garbage collection event is triggered after the deletion of orphaned draft and revision data, meaning that orphaned Neo revision data is not cleared out. I tested moving the deletion of partial Matrix block data after the deletion of orphaned draft and revision data, and confirmed that the same error occurs. So I believe the proper fix to this error is for Craft to delete orphaned draft and revision data after the event has been triggered.

As a short term solution, running php craft gc/run again will clean up that old revision data.

Closing this issue in favour of craftcms/cms#14309.