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

[fancy-pug]

What happened?

Description

Some entries trigger en error when saving.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '271300-1' for key 'revisions.revisions_sourceId_num_unq_idx'
The SQL being executed was: INSERT INTO revisions (canonicalId, creatorId, num, notes) VALUES (271300, 1, 1, NULL)
Error Info: Array
(
[0] => 23000
[1] => 1062
[2] => Duplicate entry '271300-1' for key 'revisions.revisions_sourceId_num_unq_idx'
)

Steps to reproduce

I dont know how to reproduce the error. The error occurs irregularly.
I tried to reduce the count of revisions to 1 but it does not fix the problem. My temporary solution is to empty the revisions table.
I also disabled all plugins to make sure that the error error is not triggered by one of the plugins.

Expected behavior

Saving the entry.

Actual behavior

Craft CMS version

4.13.7

PHP version

8.2

Operating system and version

Ubuntu & Mac

Database type and version

MySQL 9.0.1 & MariaDB 10.6.20

Image driver and version

No response

Installed plugins and versions

craftcms/cms: "^4",
craftcms/redactor: "3.0.4",
dodecastudio/craft-blurhash: "2.0.3",
dolphiq/redirect: "^2.0.1",
guzzlehttp/guzzle: "^7.0",
lewisjenkins/craft-dynamic-fields: "^4",
nesbot/carbon": "^3",
phpoffice/phpspreadsheet: "^2",
phpoffice/phpword: "^1",
tecnickcom/tcpdf: "^6.7",
vaersaagod/matrixmate: "^2",
verbb/cp-nav: "^4",
verbb/formie: "^2",
verbb/super-table: "^3",
vlucas/phpdotenv: "^5.6"

[brandonkelly]

Hi, please search for that error in your logs and post the stack trace that follows it.

[cg-sthomas]

I just ran into the same problem on a Craft 5 site (upgraded from Craft 4) and happens every time a particular entry is saved. The site is large so the issue may affect other entries as well.

Originally a Craft 4 site, upgraded to latest Craft 5. It has 2 languages and revisions enabled.

Craft CMS version
5.6.4

PHP version
8.3

Operating system and version
Ubuntu

Database type and version
MariaDB 11.6

Installed plugins and versions
CKEditor: 4.5.0
Code Field: 5.0.0
Formie: 3.0.20
Navigation: 3.0.5
Neo: 5.2.23
oEmbed: 3.1.3
Sprout Sitemaps: 5.0.0
Twigpack: 5.0.0
Typed link field: 3.0.0-beta

Logs

2025-02-04 12:15:58 [web.ERROR] [yii\db\IntegrityException] SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '294236-1' for key 'idx_gymaqaegxdmfkomouwzccmykmnnjrtprmoss' The SQL being executed was: INSERT INTO `revisions` (`canonicalId`, `creatorId`, `num`, `notes`) VALUES (294236, 1, 1, NULL) {"trace":["#0 /home/[REDACTED]/[REDACTED]/vendor/yiisoft/yii2/db/Command.php(1325): yii\\db\\Schema->convertException()","#1 /home/[REDACTED]/[REDACTED]/vendor/yiisoft/yii2/db/Command.php(1120): yii\\db\\Command->internalExecute()","#2 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/helpers/Db.php(1015): yii\\db\\Command->execute()","#3 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/services/Revisions.php(158): craft\\helpers\\Db::insert()","#4 /home/[REDACTED]/[REDACTED]/vendor/spicyweb/craft-neo/src/services/Fields.php(746): craft\\services\\Revisions->createRevision()","#5 /home/[REDACTED]/[REDACTED]/vendor/spicyweb/craft-neo/src/Field.php(1045): benf\\neo\\services\\Fields->createRevisionBlocks()","#6 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/base/Element.php(6331): benf\\neo\\Field->afterElementPropagate()","#7 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/elements/Entry.php(2736): craft\\base\\Element->afterPropagate()","#8 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/services/Elements.php(2004): craft\\elements\\Entry->afterPropagate()","#9 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/services/Elements.php(1217): craft\\services\\Elements->craft\\services\\{closure}()","#10 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/services/Elements.php(1874): craft\\services\\Elements->ensureBulkOp()","#11 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/services/Revisions.php(179): craft\\services\\Elements->duplicateElement()","#12 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/elements/Entry.php(2740): craft\\services\\Revisions->createRevision()","#13 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/services/Elements.php(3828): craft\\elements\\Entry->afterPropagate()","#14 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/services/Elements.php(1217): craft\\services\\Elements->craft\\services\\{closure}()","#15 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/services/Elements.php(3588): craft\\services\\Elements->ensureBulkOp()","#16 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/services/Elements.php(1303): craft\\services\\Elements->_saveElementInternal()","#17 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/controllers/ElementsController.php(1342): craft\\services\\Elements->saveElement()","#18 [internal function]: craft\\controllers\\ElementsController->actionSave()","#19 /home/[REDACTED]/[REDACTED]/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()","#20 /home/[REDACTED]/[REDACTED]/vendor/yiisoft/yii2/base/Controller.php(178): yii\\base\\InlineAction->runWithParams()","#21 /home/[REDACTED]/[REDACTED]/vendor/yiisoft/yii2/base/Module.php(552): yii\\base\\Controller->runAction()","#22 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/web/Application.php(361): yii\\base\\Module->runAction()","#23 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/web/Application.php(660): craft\\web\\Application->runAction()","#24 /home/[REDACTED]/[REDACTED]/vendor/craftcms/cms/src/web/Application.php(323): craft\\web\\Application->_processActionRequest()","#25 /home/[REDACTED]/[REDACTED]/vendor/yiisoft/yii2/base/Application.php(384): craft\\web\\Application->handleRequest()","#26 /home/[REDACTED]/[REDACTED]/web/index.php(12): yii\\base\\Application->run()","#27 {main}"],"memory":48922984,"exception":"[object] (yii\\db\\IntegrityException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '294236-1' for key 'idx_gymaqaegxdmfkomouwzccmykmnnjrtprmoss'\nThe SQL being executed was: INSERT INTO `revisions` (`canonicalId`, `creatorId`, `num`, `notes`) VALUES (294236, 1, 1, NULL) at /home/[REDACTED]/[REDACTED]/vendor/yiisoft/yii2/db/Schema.php:676)\n[previous exception] [object] (PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '294236-1' for key 'idx_gymaqaegxdmfkomouwzccmykmnnjrtprmoss' at /home/[REDACTED]/[REDACTED]/vendor/yiisoft/yii2/db/Command.php:1320)"} 
2025-02-04 12:15:58 [web.WARNING] [application] Request context: {"environment":"production","userId":1,"sessionId":"8tcm1m4dfl004qmpbi3hqi97uo","vars":{"_GET":{"site":"english"},"_POST":{"CRAFT_CSRF_TOKEN":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","elementId":"19741","siteId":"1","title":"HOPWA/ADAP","action":"elements/save","redirect":"d01272f6ae2fe9e3c83043ab2a8701bdd5f27d69bfd9d4cfaa2c38b0c1262701{cpEditUrl}","typeId":"20","slug":"hopwa-and-adap","parentId":["19289"],"authorIds":["221375"],"enabledForSite":{"1":"1"},"notes":""},"_FILES":[],"_COOKIE":{"CraftSessionId":"8tcm1m4dfl004qmpbi3hqi97uo","CRAFT_CSRF_TOKEN":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","75d2515c6ddfa5e948a5106a7d010925_username":"b9062e35c0b12674c6824a40e04e9da04c5158581d8e7b09d6092c2d94d078bea:2:{i:0;s:41:\"75d2515c6ddfa5e948a5106a7d010925_username\";i:1;s:7:\"support\";}","Craft-81e1cfe9-bed7-41bb-824f-12f7f69ec3ba:sidebar":"expanded","Craft-81e1cfe9-bed7-41bb-824f-12f7f69ec3ba:sidebar-details":"expanded","75d2515c6ddfa5e948a5106a7d010925_identity":"e1db9dd4e66e2d18c9ea84b4a3ef1f670b2ef8acaf906c40a0895806f0e2c38da:2:{i:0;s:41:\"75d2515c6ddfa5e948a5106a7d010925_identity\";i:1;s:160:\"[1,\"[\\\"Ql04WkGr5EqlRnTbJobzPXBQ_vTMPL70xeiQbEL-ZBeAZTvKck3PVB3gn8CJhn-DZPKji8wk4G73J_x4r4MSBmd78KuOLksmCNbn\\\",null,\\\"9b330341ce099154b0e9bbd16564f9e5\\\"]\",86400]\";}"},"_SESSION":{"b575bb7cae18bdd7a6d0092be3f4dbeb__flash":[],"__duration":86400,"b575bb7cae18bdd7a6d0092be3f4dbeb__auth_access":["previewElement:19741","saveAssets:d70a0750-03bd-4ec5-a87a-c81c77b52a7c","saveAssets:9fad9052-5b8a-4b95-bcc4-5be4a73a7fda","saveAssets:1ebe11cd-ae00-45fb-9995-0bb0c336acf2","previewElement:98662"],"4d4339c56653a2eaeb089b70cfeb872a__flash":[],"83f24c50baa08605c55f7390b8721fa3__flash":[],"30c68893aeeddba31270538f8c6f4eab__returnUrl":"https://www.[REDACTED]/admin/utilities/project-config","75d2515c6ddfa5e948a5106a7d010925__token":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","75d2515c6ddfa5e948a5106a7d010925__id":1,"__authKey":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","75d2515c6ddfa5e948a5106a7d010925__expire":1738786557,"__elevated_timeout":1738697187},"_SERVER":{"USER":"[REDACTED]","HOME":"/home/[REDACTED]","HTTP_COOKIE":"CraftSessionId=8tcm1m4dfl004qmpbi3hqi97uo; CRAFT_CSRF_TOKEN=195e0ae2b68b0cd7c1cec0bc75741783e49db8e14911085a49d2738a73a87711a%3A2%3A%7Bi%3A0%3Bs%3A16%3A%22CRAFT_CSRF_TOKEN%22%3Bi%3A1%3Bs%3A147%3A%22YedJwgCpJU_UihZAtBbIBYZ8LgUr0ZGtIPIR7QVb%7C67b9fa1767ab56aae436013d40c84bdec1c3e6d2694977a326cdb304fc63ec4dYedJwgCpJU_UihZAtBbIBYZ8LgUr0ZGtIPIR7QVb%7C1%22%3B%7D; 75d2515c6ddfa5e948a5106a7d010925_username=b9062e35c0b12674c6824a40e04e9da04c5158581d8e7b09d6092c2d94d078bea%3A2%3A%7Bi%3A0%3Bs%3A41%3A%2275d2515c6ddfa5e948a5106a7d010925_username%22%3Bi%3A1%3Bs%3A7%3A%22support%22%3B%7D; Craft-81e1cfe9-bed7-41bb-824f-12f7f69ec3ba:sidebar=expanded; Craft-81e1cfe9-bed7-41bb-824f-12f7f69ec3ba:sidebar-details=expanded; 75d2515c6ddfa5e948a5106a7d010925_identity=e1db9dd4e66e2d18c9ea84b4a3ef1f670b2ef8acaf906c40a0895806f0e2c38da%3A2%3A%7Bi%3A0%3Bs%3A41%3A%2275d2515c6ddfa5e948a5106a7d010925_identity%22%3Bi%3A1%3Bs%3A160%3A%22%5B1%2C%22%5B%5C%22Ql04WkGr5EqlRnTbJobzPXBQ_vTMPL70xeiQbEL-ZBeAZTvKck3PVB3gn8CJhn-DZPKji8wk4G73J_x4r4MSBmd78KuOLksmCNbn%5C%22%2Cnull%2C%5C%229b330341ce099154b0e9bbd16564f9e5%5C%22%5D%22%2C86400%5D%22%3B%7D","HTTP_TE":"trailers","HTTP_PRIORITY":"u=0, i","HTTP_SEC_FETCH_USER":"?1","HTTP_SEC_FETCH_SITE":"same-origin","HTTP_SEC_FETCH_MODE":"navigate","HTTP_SEC_FETCH_DEST":"document","HTTP_UPGRADE_INSECURE_REQUESTS":"1","HTTP_SEC_GPC":"1","HTTP_DNT":"1","HTTP_ORIGIN":"https://www.[REDACTED]","HTTP_CONTENT_LENGTH":"830","HTTP_CONTENT_TYPE":"application/x-www-form-urlencoded","HTTP_REFERER":"https://www.[REDACTED]/admin/entries/pages/19741-hopwa-and-adap?site=english","HTTP_ACCEPT_ENCODING":"gzip, deflate, br, zstd","HTTP_ACCEPT_LANGUAGE":"en-US,en;q=0.5","HTTP_ACCEPT":"text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8","HTTP_USER_AGENT":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:134.0) Gecko/20100101 Firefox/134.0","HTTP_HOST":"www.[REDACTED]","REDIRECT_STATUS":"200","SERVER_NAME":"www.[REDACTED]","SERVER_PORT":"443","SERVER_ADDR":"[REDACTED]","REMOTE_PORT":"57759","REMOTE_ADDR":"74.62.22.138","SERVER_SOFTWARE":"nginx/1.26.2","GATEWAY_INTERFACE":"CGI/1.1","HTTPS":"on","REQUEST_SCHEME":"https","SERVER_PROTOCOL":"HTTP/2.0","DOCUMENT_URI":"/index.php","REQUEST_URI":"/admin/entries/pages/19741-hopwa-and-adap?site=english","SCRIPT_NAME":"/index.php","CONTENT_LENGTH":"830","CONTENT_TYPE":"application/x-www-form-urlencoded","REQUEST_METHOD":"POST","QUERY_STRING":"site=english","DOCUMENT_ROOT":"/home/[REDACTED]/[REDACTED]/web","SCRIPT_FILENAME":"/home/[REDACTED]/[REDACTED]/web/index.php","FCGI_ROLE":"RESPONDER","PHP_SELF":"/index.php","REQUEST_TIME_FLOAT":1738700157.482524,"REQUEST_TIME":1738700157,"ENVIRONMENT":"production","APP_ID":"CraftCMS--2470d10e-25f4-4473-a69c-37e8c40ec0a6","SECURITY_KEY":"••••••••••••••••••••••••••••••••","DB_DRIVER":"mysql","DB_SERVER":"127.0.0.1","DB_PORT":"3306","DB_DATABASE":"[REDACTED]","DB_USER":"[REDACTED]","DB_PASSWORD":"••••••••••••••••••••••••","DB_SCHEMA":"","DB_TABLE_PREFIX":"","MAIL_AUTHENTICATION":"true","MAIL_ENCRYPTION":"none","MAIL_FROM_ADDRESS":"no-reply@mg.[REDACTED]","MAIL_FROM_NAME":"No-Reply","MAIL_HOST":"smtp.mailgun.org","MAIL_PASSWORD":"••••••••••••••••••••••••••••••••••••••••••••••••••","MAIL_PORT":"25","MAIL_USERNAME":"no-reply@mg.[REDACTED]","CP_TRIGGER":"admin","PRIMARY_SITE_URL":"https://www.[REDACTED]","TOKEN_MAPBOX_KEY":"•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","MIX_TOKEN_MAPBOX_KEY":"•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","FORMIE_SECURITY_KEY":"••••••••••••••••••••••••••••••••"}}}

[fancy-pug]

@brandonkelly Yes, looks like I did not attached the screenshot of the stack trace :-)

As mentioned I disabled all plugins and saw that the stack trace only uses files in vendor/craft/cms and vendor/yiisoft.

I googled the problem and saw that other developers were getting the same error message. But this had to do with the plugin Craft neo. I dont use Craft Neo.

[brandonkelly]

@cg-sthomas Since it’s reproducible in your case, can you send a database backup and your Composer files over to [email protected] so we can take a look on our end?

[jan-dh]

Running into the same issue:

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 '1299388-3' for key 'elements_sites.idx_ogvabrgubvqigfqpmtifjdfaxcsghecyfmbl'
The SQL being executed was: INSERT INTO `elements_sites` (`elementId`, `siteId`, `slug`, `uri`, `enabled`, `dateCreated`, `dateUpdated`, `uid`) VALUES (1299388, 3, 'xxxx', 'xxxxx', 1, '2025-02-06 13:24:05', '2025-02-06 13:24:05', '64da71e7-87c8-44ba-bf23-cc6955bef778')
Error Info: Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry '1299388-3' for key 'elements_sites.idx_ogvabrgubvqigfqpmtifjdfaxcsghecyfmbl'
)
↵
Caused by: PDOException
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1299388-3' for key 'elements_sites.idx_ogvabrgubvqigfqpmtifjdfaxcsghecyfmbl'
in /app/vendor/yiisoft/yii2/db/Command.php at line 1320

Craft 4.13.10

[MangoMarcus]

I've had a similar error when upgrading to Craft 5 and merging entry types in the same release, we've had to split the entry type merges into a separate release to get around it

[MangoMarcus]

I've had a similar error when upgrading to Craft 5 and merging entry types in the same release, we've had to split the entry type merges into a separate release to get around it

Actually turned out this isn't working either, so to summarise:

• Take a fresh Craft 4 DB which is already using latest Craft 4 version and has had Craft 4 migrations and craft pc/rebuild run on it
• Checkout a new branch, update composer dependencies for Craft 5 & run composer update then craft up
• Re-import the Craft 4 DB and run craft up - ✅ works fine
• Merge some entry types with craft entry-types/merge foo bar
• Re-import the Craft 4 DB and run craft up - ❌ fails with error error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-5' for key 'elements_sites.idx_dezcgnpdfzqvdtdkaphhalcvcuielawcuysx'

The same error happens if you don't roll the entry type merge into the same branch / release, eg.

• Take a fresh Craft 4 DB which is already using latest Craft 4 version and has had Craft 4 migrations and craft pc/rebuild run on it
• Checkout a new branch, update composer dependencies for Craft 5 & run composer update then craft up
• Checkout another branch and merge some entry types with craft entry-types/merge foo bar
• Checkout the first branch and re-import the Craft 4 DB and run craft up - ✅ works fine
• Checkout the second branch and run craft up - ❌ fails with error error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-5' for key 'elements_sites.idx_dezcgnpdfzqvdtdkaphhalcvcuielawcuysx'

[jan-dh]

My issue appeared on saving an entry, nothing fancy but the entry is now completly bricked.

[jan-dh]

Updated to Craft 4.14.4, entry still impossible to save, any tips on how to get past this?

[brandonkelly]

@jan-dh If you send your database and Composer files into [email protected], and point out which entry is having issues, we can look into it from there.

[brandonkelly]

It looks like the common culprit with these issues is revision data for elements that no longer exist.

That can happen if elements are deleted while the database is missing its foreign keys, or foreign keys are temporarily disabled.

As of Craft 4.14.0 and 5.6.0, the craft gc command will delete any database rows that have invalid foreign key references, which will fix this error.

If that doesn’t help, your database may be missing its foreign keys, which you can restore using the Fix FKs plugin.

[MangoMarcus]

@brandonkelly I'm still getting this issue on Craft v5.6.17 after running craft entry-types/merge, I've tried running craft gc and the Fix FKs plugin

Steps to reproduce:

1. Backup db with ddev craft db/backup
2. Merge entry types with ddev craft entry-types/merge general home
3. Restore db with ddev craft db/restore and the path from step 1
4. Apply project config changes with ddev craft pc/apply

Gives

error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-5' for key 'elements_sites.idx_dezcgnpdfzqvdtdkaphhalcvcuielawcuysx'
The SQL being executed was: INSERT INTO `elements_sites` (`elementId`, `siteId`, `title`, `slug`, `uri`, `enabled`, `content`, `dateCreated`, `dateUpdated`, `uid`) VALUES (2, 5, 'Home', 'home', '__home__', 1, ...

I've emailed a DB dump and composer files to [email protected] if you're able to advise, thanks in advance 😊

[brandonkelly]

Thanks @MangoMarcus! Fixed for the next release. The error occurred because the outgoing entry type was used by a Single section, which apparently hasn’t been done before!

[brandonkelly]

Craft 5.7.5 is out with that fix. Thanks again!

[MangoMarcus]

Thanks as ever @brandonkelly !