[4.x]: Integrity constraint violation: 1062 Duplicate entry '4010124-1' for key 'craft_revisions_sourceId_num_unq_idx'

[npluimers]

What happened?

Description

We are still seeing the issues reported in #14309 on the latest Craft 4 version.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4010124-1' for key 'craft_revisions_sourceId_num_unq_idx'
The SQL being executed was: INSERT INTO `craft_revisions` (`canonicalId`, `creatorId`, `num`, `notes`) VALUES (4010124, 4015870, 1, NULL)

I did some investigation, and it seems to be caused by elements not having their own 'revisionId' set inside the craft_elements table, causing the query for the next revision 'num' to fail and fall back to 1.

I don't know if this is of help, but I executed the following query to get an idea of the number of elements with this issue:

SELECT COUNT(*), res.elementType FROM 
(
	SELECT e.type as elementType, e.revisionId, e.dateDeleted, r.num FROM craft_elements e 
		LEFT JOIN craft_revisions r on r.canonicalId = e.id 
		HAVING e.revisionId is null AND e.dateDeleted is null AND r.num is not null
) res
GROUP BY res.elementType

Results:
16494 benf\neo\elements\Block
3322 craft\elements\Entry
2593 craft\elements\MatrixBlock
38119 verbb\supertable\elements\SuperTableBlockElement

Steps to reproduce

1. Have a element with a NULL revisionId inside your elements table, and a number of revisions for this element inside your revision table.
2. Try to save a page with this element

Expected behavior

Saving is possible and succeeds.

Actual behavior

Above error is displayed, page is not saved.

Craft CMS version

4.12.6.1

PHP version

8.1

Operating system and version

AlmaLinux 8.10

Database type and version

10.11.9-MariaDB

Image driver and version

No response

Installed plugins and versions

• Asset Usage 3.3.0
• Block Usage 1.0.3
• Cloudflare 2.1.1
• CodeMirror 2.0.0
• Control Panel CSS 2.6.0
• Cookies 4.0.1
• Forms 5.6.4
• GeoMate 2.1.3
• Image Hotspots 2.0.0
• Navigation 2.1.1
• Neo 4.2.17
• New Relic 4.0.1
• Redactor 3.1.0
• Redirect Manager 2.0.1
• Search powered by Elasticsearch 2.0.0
• SEOmatic 4.1.4
• Site Copy X 1.1.0
• Super Dynamic Fields 2.0.1.1
• Super Table 3.0.15
• Tags 2.0.0
• Translations 4.0.2

[i-just]

Hi, thanks for getting in touch! Would you be able to send your composer.json, composer.lock and database export to support@craftcms.com so we can reproduce and dig deeper?

[svale]

Hi!

For the record (and if it can help the debugging), we are seeing a similar issue (but for a relations index) on a Craft 5.4.8 site running on Craft Cloud

 Integrity constraint violation: 1062 Duplicate entry '41-2885-2-2174' for key 'relations.idx_crgffrkmroqmvyvayuytscgghwkgidokibfp' 
The SQL being executed was: 
  UPDATE `relations` SET `sourceSiteId`=2, 
  `sortOrder`=2, 
  `dateUpdated`='2024-10-31 09:26:41' WHERE `id`=3489 

The issue is not occurring for our admin-user, but persistent for the editor in the production environment and confirmed in my local dev env.

System details

Application Info

PHP version 8.2.22
OS version Linux 5.10.226-235.879.amzn2.x86_64
Database driver & version MySQL 8.0.32
Image driver & version GD 8.2.18
Craft edition & version Craft Pro 5.4.8
Yii version 2.0.51
Twig version v3.14.0
Guzzle version 7.9.2

Plugins

CKEditor 4.2.0
Control Panel Nav 5.0.2
Embedded Assets 5.1.2
Expanded Singles 3.0.0
Feed Me 6.5.0
Field Manager 4.0.2
Formie 3.0.8
Hyper 2.1.1
Imager X 5.0.2
Imager X Craft Cloud Transformer 1.0.0-beta.1
Many to Many 4.0.1
Redirects 5.0.3
Retcon 3.2.0
SEO 5.1.4
Sprig 3.5.0
Table Maker 5.0.3
Vite 5.0.1

Modules

feed modules\feed\Module (<= only a couple of simple twig functions and filters)
closure nystudio107\closure\Closure
codeeditor nystudio107\codeeditor\CodeEditor
cloud craft\cloud\Module
verbb-base verbb\base\Base
sprig-core putyourlightson\sprig\Sprig
debug craft\debug\Module