[3.7.32]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

[bransinanderson]

What happened?

Description

Running a custom content migration to merge entries into other entries belonging to a different section.

Propagation settings for the section we are merging the entry into is set to: "Let each entry choose which sites it should be saved to".

Running a custom content migration, I'm unable to update elementId row records in content table, and the elements_sites table.

Steps to reproduce

Each row in the ENTRY_MAP array defines a key (canonical entry we are moving to) and the value is the old entry id belonging to the old section. In this case, 538007 and 873687 are being moved into entry 894118.

$php ./craft migrate
Checking for pending content migrations ...
Total 1 new migration to be applied:
    m220328_101148_migrate_entries

Apply the above migration? (yes|no) [no]:yes

A snippet of the code in the migration.

namespace craft\contentmigrations;

use Craft;
use craft\db\Migration;
use craft\db\Query;
use craft\elements\Entry;
use verbb\supertable\SuperTable;
use craft\helpers\DateTimeHelper;
use craft\console\controllers\GcController;

.......
// within the safeUp() method below

const ENTRY_MAP = [
    [ 894118 => 538007 ],
    [ 894118 => 873687 ],
    [ 902485 => 846741 ]
];

foreach(ENTRY_MAP as $entry_map_row) {

    $canonical_entry_id = key($entry_map_row);
    $old_entry_id = $entry_map_row[$canonical_entry_id];

    $this->update(
        'content',
        ['elementId' => $canonical_entry_id],
        'elementId = ' . $old_entry_id
    );

}

Expected behavior

Expected to run without error and the data below to be updated.

To update all rows in the content table by setting elementId to 894118 where elementId = 538007. To update all rows in the content table by setting elementId to 894118 where elementId = 873687. To update all rows in the content table by setting elementId to 902485 where elementId = 846741.

Actual behavior

update in content ...Exception: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '894118-6' for key 'content_elementId_siteId_unq_idx' The SQL being executed was: UPDATE content SET elementId=894118, dateUpdated='2022-03-28 12:46:36' WHERE elementId = 873687 (/mysite/vendor/yiisoft/yii2/db/Schema.php:676)

Running the SQL being executed above in my SQL client runs just fine and updates the records. For some reason this only happens when executing $this->update in a loop.

Craft CMS version

Craft Pro 3.7.32

PHP version

7.4.27

Operating system and version

Linux 5.4.0-105-generic

Database type and version

MySQL 5.5.5

Image driver and version

GD 7.4.27

Installed plugins and versions

Amazon S3 1.3.0 Blitz 3.11.1 Element API 2.8.4 GeoMate v1.3.1.2 Imager X v3.5.5 Reasons 2.2.6 Redactor 2.9.1 Retcon 2.3.0 Retour 3.1.70 SEOmatic 3.4.24 Servd Assets and Helpers 2.5.0 Super Table 2.7.1 Table Maker 2.0.1

[bransinanderson]

Looking over the DDL for the content and element_sites table, I noticed there is a unique key defined for both the elementId and siteId. UNIQUE KEY `content_elementId_siteId_unq_idx` (`elementId`,`siteId`),

What I did was run the update through each site.

$supported_sites = array();
foreach($old_entry_obj->supportedSites as $site) {
    $supported_sites[] = $site['siteId'];
}

foreach($supported_sites as $supported_site_id) {
    $this->update(
        'content',
        ['elementId' => $canonical_entry_id, 'siteId' => $supported_site_id],
        'elementId = ' . $old_entry_id . ' AND siteId = ' . $supported_site_id
    );
}