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

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

Start by reproducing the failure while saving a page with an element whose craft_elements.revisionId is NULL and existing craft_revisions rows, then inspect the save and revision queries involved. Done means the page saves successfully without a duplicate craft_revisions.sourceId/num entry, including for the affected element types.