[4.x]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1329758-1' for key 'revisions_sourceId_num_unq_idx'

[alexanderbuergin]

What happened?

Description

Unfortunately, an error occurs when saving some entries. But with others we can save the entries without any problems.

Integrity constraint violation – [yii\db\IntegrityException](https://www.yiiframework.com/doc-2.0/yii-db-integrityexception.html)
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1329758-1' for key 'revisions_sourceId_num_unq_idx'
The SQL being executed was: INSERT INTO `revisions` (`canonicalId`, `creatorId`, `num`, `notes`) VALUES (1329758, 1325965, 1, NULL)
Error Info: Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry '1329758-1' for key 'revisions_sourceId_num_unq_idx'

Steps to reproduce

1. One of my ideas was to remove all revisions from the DB, but unfortunately this didn't make things any better, but rather worse.
2. Save an entry as draft and then save it into the main entry

Craft CMS version

4.7.1

PHP version

8.1

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

No response

[i-just]

Hi, thanks for getting in touch! I’m unable to reproduce this on a clean 4.7.1 installation. Could you please send your composer.lock, composer.json and database export to [email protected] and point to one or two entries that give you this error when saving so we can replicate and see what’s going on?

[alexanderloewe]

Hello i-just

I send you/craft the db and the files.

Thanks.

[i-just]

@alexanderbuergin - I replied to your email.

For posterity, it looks to be related to this one: spicywebau/craft-neo#813

[ttempleton]

Per my comments from spicywebau/craft-neo#813 (comment), 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 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.

[stillhartkonzept]

also getting this.

[DavidKabelitz]

me too

[brandonkelly]

Thanks @ttempleton! Just made that change for the next v4 and v5 releases.

[brandonkelly]

Craft 4.9.3 and 5.1.3 are out with that fix. Thanks again.

[alexanderloewe]

Thx

[aaronELBorg]

Getting this error on Craft 4.10.4. Perhaps I'm missing something? Any help is appreciated.