Integrity constraint violation: 1062 Duplicate entry for key 'craft_content_elementId_siteId_unq_idx'

[jsmrtn]

Description

I'm working on a plugin that, amongst other things, takes data from a CSV and lets the user select a site to upload the data into (Title, Slug, basic text fields, Matrix, etc.). All of the fields it's trying to put the data into are translatable and the entry exists for both sites (set to propagate into all sites that the section is enabled for).

If I set the site ID to the primary site then the saveElement runs successfully and all of the fields are correctly updated, but if I run it for the other site then it throws the error.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '3636-4' for key 'craft_content_elementId_siteId_unq_idx'
The SQL being executed was: UPDATE `craft_content` SET `elementId`=3636, `siteId`=4, `title`='21', `field_customTitle`='1', `field_summary`='2', `field_seoMetaTitle`='18', `field_seoMetaDescription`='19', `field_seoKeywords`='20', `dateUpdated`='2021-07-06 15:45:12' WHERE `id`=1459
Error Info: Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry '3636-4' for key 'craft_content_elementId_siteId_unq_idx'
)
↵
Caused by: PDOException
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '3636-4' for key 'craft_content_elementId_siteId_unq_idx'

It's a fairly simple function-$syncData is the field layout for the entry from the primary site, with the data from the CSV injected into it at the correct array keys to overwrite the current value with the value from the CSV

$element->siteId = 3; // Primary Site, works fine
// $element->siteId = 4; // Second site, errors out

if (isset($syncData['title'])) {
    $element->title = $syncData['title'];
    unset($syncData['title']);
}
if (isset($syncData['slug'])) {
     $element->slug = $syncData['slug'];
     unset($syncData['slug']);
}

$element->setFieldValues($syncData);
$success = Craft::$app->elements->saveElement($element, true, false);

I've tried to resave the entries, and also tried installing the fix FK plugin, both to no avail.

Additional info

• Craft version: Craft Pro 3.6.17
• PHP version: 7.4.2
• Database driver & version: MySQL 5.7.26

[brandonkelly]

Search for the error in your logs (storage/logs/). There should be a stack trace that follows it. Can you please post that?

[jsmrtn]

Sure thing

2021-07-07 09:44:23 [-][3053][h6oplript4vf01093oit0ceuje][info][yii\db\Command::execute] UPDATE `craft_content` SET `elementId`=3636, `siteId`=4, `title`='21', `field_customTitle`='1', `field_summary`='2', `field_seoMetaTitle`='18', `field_seoMetaDescription`='19', `field_seoKeywords`='20', `dateUpdated`='2021-07-07 08:44:23' WHERE `id`=1459
2021-07-07 09:44:23 [-][3053][h6oplript4vf01093oit0ceuje][profile begin][yii\db\Command::execute] UPDATE `craft_content` SET `elementId`=3636, `siteId`=4, `title`='21', `field_customTitle`='1', `field_summary`='2', `field_seoMetaTitle`='18', `field_seoMetaDescription`='19', `field_seoKeywords`='20', `dateUpdated`='2021-07-07 08:44:23' WHERE `id`=1459
2021-07-07 09:44:23 [-][3053][h6oplript4vf01093oit0ceuje][profile end][yii\db\Command::execute] UPDATE `craft_content` SET `elementId`=3636, `siteId`=4, `title`='21', `field_customTitle`='1', `field_summary`='2', `field_seoMetaTitle`='18', `field_seoMetaDescription`='19', `field_seoKeywords`='20', `dateUpdated`='2021-07-07 08:44:23' WHERE `id`=1459
2021-07-07 09:44:23 [-][3053][h6oplript4vf01093oit0ceuje][trace][yii\db\Transaction::rollBack] Roll back transaction
2021-07-07 09:44:23 [-][3053][h6oplript4vf01093oit0ceuje][error][yii\db\IntegrityException] PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '3636-4' for key 'craft_content_elementId_siteId_unq_idx' in /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/db/Command.php:1302
Stack trace:
#0 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute()
#1 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('UPDATE `craft_c...')
#2 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/helpers/Db.php(795): yii\db\Command->execute()
#3 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/services/Content.php(206): craft\helpers\Db::update('{{%content}}', Array, Array, Array, true, Object(craft\db\Connection))
#4 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/services/Elements.php(2504): craft\services\Content->saveContent(Object(craft\elements\Entry))
#5 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/services/Elements.php(758): craft\services\Elements->_saveElementInternal(Object(craft\elements\Entry), true, false, true)
#6 /Users/joshuamartin/Documents/Plugins/translated/src/controllers/OrdersController.php(219): craft\services\Elements->saveElement(Object(craft\elements\Entry), true, false)
#7 [internal function]: scaramangagency\translated\controllers\OrdersController->actionSyncOrder()
#8 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/base/Controller.php(181): yii\base\InlineAction->runWithParams(Array)
#10 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/web/Controller.php(190): yii\base\Controller->runAction('sync-order', Array)
#11 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/base/Module.php(534): craft\web\Controller->runAction('sync-order', Array)
#12 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/web/Application.php(278): yii\base\Module->runAction('translated/orde...', Array)
#13 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/web/Application.php(581): craft\web\Application->runAction('translated/orde...', Array)
#14 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/web/Application.php(257): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#15 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/base/Application.php(392): craft\web\Application->handleRequest(Object(craft\web\Request))
#16 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/www/index.php(21): yii\base\Application->run()
#17 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '3636-4' for key 'craft_content_elementId_siteId_unq_idx'
The SQL being executed was: UPDATE `craft_content` SET `elementId`=3636, `siteId`=4, `title`='21', `field_customTitle`='1', `field_summary`='2', `field_seoMetaTitle`='18', `field_seoMetaDescription`='19', `field_seoKeywords`='20', `dateUpdated`='2021-07-07 08:44:23' WHERE `id`=1459 in /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/db/Schema.php:678
Stack trace:
#0 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'UPDATE `craft_c...')
#1 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('UPDATE `craft_c...')
#2 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/helpers/Db.php(795): yii\db\Command->execute()
#3 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/services/Content.php(206): craft\helpers\Db::update('{{%content}}', Array, Array, Array, true, Object(craft\db\Connection))
#4 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/services/Elements.php(2504): craft\services\Content->saveContent(Object(craft\elements\Entry))
#5 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/services/Elements.php(758): craft\services\Elements->_saveElementInternal(Object(craft\elements\Entry), true, false, true)
#6 /Users/joshuamartin/Documents/Plugins/translated/src/controllers/OrdersController.php(219): craft\services\Elements->saveElement(Object(craft\elements\Entry), true, false)
#7 [internal function]: scaramangagency\translated\controllers\OrdersController->actionSyncOrder()
#8 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/base/Controller.php(181): yii\base\InlineAction->runWithParams(Array)
#10 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/web/Controller.php(190): yii\base\Controller->runAction('sync-order', Array)
#11 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/base/Module.php(534): craft\web\Controller->runAction('sync-order', Array)
#12 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/web/Application.php(278): yii\base\Module->runAction('translated/orde...', Array)
#13 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/web/Application.php(581): craft\web\Application->runAction('translated/orde...', Array)
#14 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/craftcms/cms/src/web/Application.php(257): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#15 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/vendor/yiisoft/yii2/base/Application.php(392): craft\web\Application->handleRequest(Object(craft\web\Request))
#16 /Users/joshuamartin/Documents/Websites/scaramanga-agency-reanimated/www/index.php(21): yii\base\Application->run()
#17 {main}
Additional Information:
Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry '3636-4' for key 'craft_content_elementId_siteId_unq_idx'
)

[brandonkelly]

Can you paste in the code for that OrdersController::actionSyncOrder() method?

[jsmrtn]

OrderController

public function actionSyncOrder()
{
    $data = Craft::$app->getRequest()->getBodyParam('order', []);
    $syncData = translated::$plugin->dataService->updateEntryFromTranslationCSV($data);

    if (!$syncData) {
        Craft::$app->getSession()->setError(Craft::t('app', 'Failed to get order'));
        return $this->redirect(Craft::$app->getRequest()->referrer);
    }

    $element = Entry::find()
        ->id($data['entryId'])
        ->one();

    $element->siteId = $data['siteId'];

    if (isset($syncData['title'])) {
        $element->title = $syncData['title'];
        unset($syncData['title']);
    }
    if (isset($syncData['slug'])) {
        $element->slug = $syncData['slug'];
        unset($syncData['slug']);
    }

    $element->setFieldValues($syncData);

    $success = Craft::$app->elements->saveElement($element, true, false);

    if (!$success) {
        Craft::$app->getSession()->setError(Craft::t('app', 'Failed to sync data to entry'));
        return $this->redirect(Craft::$app->getRequest()->referrer);
    } else {
        Craft::$app->getSession()->setNotice(Craft::t('app', 'Translated data synced to entry'));
        return $this->redirect(UrlHelper::cpUrl('translated/orders/view/' . $data['id']));
    }
}

DataService

public function updateEntryFromTranslationCSV($data)
{
    $order = translated::$plugin->orderService->getOrder($data['id']);

    if (!$order) {
        Craft::$app->getSession()->setError(Craft::t('app', 'Failed to get order'));
        return false;
    }

    $element = Craft::$app->getElements()->getElementById($data['entryId'], null);
    $originalElement = $this->getDataFromElement($element);

    $deliveryBlob = base64_decode($order['translatedContent']);

    $csvData = $deliveryBlob;
    $lines = explode(PHP_EOL, $csvData);
    $array = [];

    $i = 0;

    foreach ($lines as $line) {
        $i++;
        if ($i == 1) {
            continue;
        }

        $parsed = str_getcsv($line);
        $fieldHandle = explode('.', $parsed[0]);

        if (sizeof($fieldHandle) == 1) {
            $originalElement[$parsed[0]] = $parsed[2];
        } else {
            $this->setValueByDot($originalElement, $parsed[0], $parsed[2]);
        }
    }

    return $originalElement;
}

[brandonkelly]

$element->siteId = $data['siteId'];

You can’t just change an element’s site ID like that, as it’s already loaded with other IDs and potentially Matrix block IDs, etc., that are all site-specific. You’d need to fetch it in the proper site to begin with:

$element = Entry::find()
    ->id($data['entryId'])
    ->siteId($data['siteId'])
    ->one();

Guessing that’s your culprit.

[jsmrtn]

mea culpa. Thanks @brandonkelly, simple fix at least!

J