Duplicate entry '3216-10' for key 'revisions_sourceId_num_unq_idx'

[truszko1]

Description

We are encountering an integrity constraint violation when saving Entries. If the same entry is being saved by two users simultaneously, one of them will be shown a SQL error

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '3216-10' for key 'revisions_sourceId_num_unq_idx'
The SQL being executed was: INSERT INTO `revisions` (`sourceId`, `creatorId`, `num`, `notes`) VALUES (3216, 49, 10, '')

The exception is being thrown by in /var/www/vendor/craftcms/cms/src/services/Revisions.php at line 142– craft\helpers\Db::insert() when the Revisions class is attempting to insert a revision.

Is this an expected behavior? This seems new with Craft 3.6. We could probably "fix" this error by removing the index, but surely the index was added intentionally to protect against something.

I don't see how this might be specific to our instance configuration, but maybe? I'd appreciate some feedback.

Thanks!

Steps to reproduce

1. Open an existing entry in two tabs.
2. Click save in tab 1.
3. Quickly switch to tab 2 and also click save.
4. Tab 2 will see the exception.

Additional info

• Craft version: 3.6.6
• PHP version: 7.4.2
• Database driver & version: MySQL 5.5.5
• Plugins & versions: N/A

[samhibberd]

We are also running into this integrity constraint violation when a resave task is being run in the queue. Has it been establish as a bug? Anything we can provide to help debug?

[brandonkelly]

Sorry, I missed the original post.

This shouldn’t be possible as craft\services\Revisions::createRevision() will create a mutex lock for the source entry before it goes on to determine a unique revision number:

cms/src/services/Revisions.php

Lines 75 to 79 in 669dc0f

	$lockKey = 'revision:' . $source->id;
	$mutex = Craft::$app->getMutex();
	if (!$mutex->acquire($lockKey)) {
	throw new Exception('Could not acquire a lock to save a revision for element ' . $source->id);
	}

Have either of you overridden the mutex component in config/app.php?

[samhibberd]

@brandonkelly not here no.

[brandonkelly]

Found a way to reproduce this, if read/write splitting was in use, as there can be a slight delay in the time it takes to update the replica(s) after the primary DB is updated. Just released 3.6.10 with a fix for that.

Any chance that could have been the issue here?

[samhibberd]

Not using read / write splitting but this release fixed it on our project. Thanks!

[brandonkelly]

Going to close this… @truszko1 Let me know if you’re still having issues on 3.6.10+.

[engram-design]

I'm seeing this behaviour on a 3.6.12 install. Anything I can do to help troubleshooting?

[brandonkelly]

@engram-design Can you provide steps to reproduce, starting with a fresh Craft install?

[engram-design]

@brandonkelly Damn, it's gonna be one of those issues. I couldn't replicate it locally, and now on the server it's no longer doing it. I was also using the sitecopy plugin, and when disabling that, it seemed to save.

I'll keep an eye on it, but it seems environmental.

[alexanderbuergin]

We have the same problem with some entries, under Craft CMS 4.7.1

#14309

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'
    ```