[5.x]: Applying a long-lived provisional draft to its canonical entry throws a yii\db\IntegrityException

[MGxpwr]

What happened?

What happened?

Applying a long-lived provisional draft to its canonical entry throws a yii\db\IntegrityException (unique constraint violation on elements_sites) when the draft owns a nested craft\fields\ContentBlock (translationMethod: site) element whose per-site rows had drifted out of sync with canonical's during the life of the draft.

Description

We have a multi-site (4 sites: en/nl/da/de) install with a craft\fields\ContentBlock field (dualImageHero, translationMethod: site) nested on entries in a sector section. A user had a provisional (autosave) draft open continuously for about 3 months. During that window, a new site (da) was added to the canonical entry's dualImageHero block. When the draft was finally applied (clicked "Save"), Craft threw:

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "idx_nkajbtexsyszivwuczcgooaacgnbvtxcains"
DETAIL: Key ("elementId", "siteId")=(39849, 4) already exists.
The SQL being executed was: INSERT INTO "elements_sites" ("elementId", "siteId", "title", "slug", "uri", "enabled", "content", "dateCreated", "dateUpdated", "uid") VALUES (39849, 4, NULL, NULL, NULL, TRUE, '{...}'::jsonb, ...)

39849 is the canonical nested ContentBlock element, not the top-level entry. Forensics on a copy of the production DB show:

• Canonical element 39849 has had elements_sites rows for sites 1/3/5 since February, and gained a row for site 4 (da) on 2026-05-28.
• The owning entry's provisional draft (element id 66368, drafts.id 5561, provisional = true) has been open since 2026-04-21 — before site 4 was added to the block.
• A snapshot of the draft's own copy of the block, taken 2026-07-15 (element id 110433, canonicalId = 39849), has elements_sites rows for sites 1/3/5 only — missing site 4 — even though canonical already had it by then.
• A later snapshot from 2026-07-20 14:50 (element id 112847) does have all 4 sites.
• The apply-draft request that threw the error ran at 2026-07-20 16:29, ~99 minutes after that last snapshot.

This looks like the same underlying issue as #17644 (draft/canonical site-set divergence causing a duplicate INSERT instead of an UPDATE during applyDraft), but recurring one level deeper — inside NestedElementManager::duplicateNestedElements() / Elements::duplicateElement()'s per-site propagation for a nested ContentBlock element — rather than at the top-level entry. #17644 was fixed in 4.16.6/5.8.9 for the entry-level case; we're on 5.10.9 and still hit it for this nested-element path.

Steps to reproduce (best effort — reconstructed from forensic DB evidence, not yet manually reproduced)

1. Multi-site install with a ContentBlock field (translationMethod: site) nested on an entry.
2. User A opens the entry, creating a provisional draft. Leave it open/autosaving.
3. Add a new site to the environment (or otherwise cause the ContentBlock field to propagate to an additional site) on the canonical entry, independent of the open draft.
4. Continue autosaving the draft over an extended period.
5. Apply the draft (click Save). Expect: content merges cleanly. Actual: IntegrityException on elements_sites for the nested block, for the site added in step 3.

Craft CMS version

5.10.9 (craftcms/cms), PostgreSQL 17.10.

Full stack trace

2026-07-20 16:29:03 [web.ERROR] [yii\db\IntegrityException] SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "idx_nkajbtexsyszivwuczcgooaacgnbvtxcains" DETAIL:  Key ("elementId", "siteId")=(39849, 4) already exists. The SQL being executed was: INSERT INTO "elements_sites" ("elementId", "siteId", "title", "slug", "uri", "enabled", "content", "dateCreated", "dateUpdated", "uid") VALUES (39849, 4, NULL, NULL, NULL, TRUE, '{"5ea19bcd-bada-4574-9535-5d1cfcae9c79":[41564],"05ec6380-ee2f-40ae-b881-7f31ebd80bb7":[30451]}'::jsonb, '2026-07-20 14:29:02', '2026-07-20 14:29:02', 'c8cb7ff7-8ed8-4e4c-b479-38d5a18dc838') RETURNING "id" {"trace":["#0 /projects/bredenoor_b7/vendor/yiisoft/yii2/db/Command.php(1325): yii\\db\\Schema->convertException()","#1 /projects/bredenoor_b7/vendor/yiisoft/yii2/db/Command.php(1186): yii\\db\\Command->internalExecute()","#2 /projects/bredenoor_b7/vendor/yiisoft/yii2/db/Command.php(431): yii\\db\\Command->queryInternal()","#3 /projects/bredenoor_b7/vendor/yiisoft/yii2/db/pgsql/Schema.php(652): yii\\db\\Command->queryOne()","#4 /projects/bredenoor_b7/vendor/yiisoft/yii2/db/ActiveRecord.php(627): yii\\db\\pgsql\\Schema->insert()","#5 /projects/bredenoor_b7/vendor/yiisoft/yii2/db/ActiveRecord.php(565): yii\\db\\ActiveRecord->insertInternal()","#6 /projects/bredenoor_b7/vendor/yiisoft/yii2/db/BaseActiveRecord.php(695): yii\\db\\ActiveRecord->insert()","#7 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(4152): yii\\db\\BaseActiveRecord->save()","#8 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(1270): craft\\services\\Elements->craft\\services\\{closure}()","#9 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(3945): craft\\services\\Elements->ensureBulkOp()","#10 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(4587): craft\\services\\Elements->_saveElementInternal()","#11 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(2068): craft\\services\\Elements->_propagateElement()","#12 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(1270): craft\\services\\Elements->craft\\services\\{closure}()","#13 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(1941): craft\\services\\Elements->ensureBulkOp()","#14 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(1565): craft\\services\\Elements->duplicateElement()","#15 /projects/bredenoor_b7/vendor/craftcms/cms/src/elements/NestedElementManager.php(1118): craft\\services\\Elements->updateCanonicalElement()","#16 /projects/bredenoor_b7/vendor/craftcms/cms/src/elements/NestedElementManager.php(701): craft\\elements\\NestedElementManager->duplicateNestedElements()","#17 /projects/bredenoor_b7/vendor/craftcms/cms/src/fields/ContentBlock.php(818): craft\\elements\\NestedElementManager->maintainNestedElements()","#18 /projects/bredenoor_b7/vendor/craftcms/cms/src/base/Element.php(6905): craft\\fields\\ContentBlock->afterElementPropagate()","#19 /projects/bredenoor_b7/vendor/craftcms/cms/src/elements/Entry.php(3205): craft\\base\\Element->afterPropagate()","#20 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(2081): craft\\elements\\Entry->afterPropagate()","#21 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(1270): craft\\services\\Elements->craft\\services\\{closure}()","#22 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(1941): craft\\services\\Elements->ensureBulkOp()","#23 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Elements.php(1565): craft\\services\\Elements->duplicateElement()","#24 /projects/bredenoor_b7/vendor/craftcms/cms/src/services/Drafts.php(330): craft\\services\\Elements->updateCanonicalElement()","#25 /projects/bredenoor_b7/vendor/craftcms/cms/src/controllers/ElementsController.php(2258): craft\\services\\Drafts->applyDraft()","#26 [internal function]: craft\\controllers\\ElementsController->actionApplyDraft()","#27 /projects/bredenoor_b7/vendor/yiisoft/yii2/base/InlineAction.php(60): call_user_func_array()","#28 /projects/bredenoor_b7/vendor/yiisoft/yii2/base/Controller.php(182): yii\\base\\InlineAction->runWithParams()","#29 /projects/bredenoor_b7/vendor/yiisoft/yii2/base/Module.php(547): yii\\base\\Controller->runAction()","#30 /projects/bredenoor_b7/vendor/craftcms/cms/src/web/Application.php(369): yii\\base\\Module->runAction()","#31 /projects/bredenoor_b7/vendor/craftcms/cms/src/web/Application.php(636): craft\\web\\Application->runAction()","#32 /projects/bredenoor_b7/vendor/craftcms/cms/src/web/Application.php(331): craft\\web\\Application->_processActionRequest()","#33 /projects/bredenoor_b7/vendor/yiisoft/yii2/base/Application.php(382): craft\\web\\Application->handleRequest()","#34 /projects/bredenoor_b7/public_html/index.php(12): yii\\base\\Application->run()","#35 {main}"],"memory":21794232,"exception":"[object] (yii\\db\\IntegrityException(code: 23505): SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint \"idx_nkajbtexsyszivwuczcgooaacgnbvtxcains\"\nDETAIL:  Key (\"elementId\", \"siteId\")=(39849, 4) already exists.\nThe SQL being executed was: INSERT INTO \"elements_sites\" (\"elementId\", \"siteId\", \"title\", \"slug\", \"uri\", \"enabled\", \"content\", \"dateCreated\", \"dateUpdated\", \"uid\") VALUES (39849, 4, NULL, NULL, NULL, TRUE, '{\"5ea19bcd-bada-4574-9535-5d1cfcae9c79\":[41564],\"05ec6380-ee2f-40ae-b881-7f31ebd80bb7\":[30451]}'::jsonb, '2026-07-20 14:29:02', '2026-07-20 14:29:02', 'c8cb7ff7-8ed8-4e4c-b479-38d5a18dc838') RETURNING \"id\" at /projects/bredenoor_b7/vendor/yiisoft/yii2/db/Schema.php:677)\n[previous exception] [object] (PDOException(code: 23505): SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint \"idx_nkajbtexsyszivwuczcgooaacgnbvtxcains\"\nDETAIL:  Key (\"elementId\", \"siteId\")=(39849, 4) already exists. at /projects/bredenoor_b7/vendor/yiisoft/yii2/db/Command.php:1320)"}

Craft CMS version

5.10.9

PHP version

8.3.32

Operating system and version

Linux 6.1.0-50-amd64

Database type and version

PostgreSQL 17.10

Image driver and version

Imagick 3.8.1 (ImageMagick 6.9.11-60)

Installed plugins and versions

• Amazon S3 2.3.1
• CKEditor 5.6.1
• Control Panel JS 3.0.0
• Cookies 5.0.0
• Expanded Singles 3.0.3
• Feed Me 6.13.0.1
• Formie 3.1.30
• Icon Picker 3.0.12
• ImageOptimize 5.0.7
• LLM Ready 1.5.1
• Multi Translator 2.28.3
• Navigation 3.0.22
• Scout 5.0.10
• SEOmatic 5.1.21
• Shortcodes 4.0.2
• Slugsmith 1.0.1
• Video Picker 2.0.9
• Vite 5.0.1

[linear-code]

CMS-2289