Shopware sync API fails with "Duplicate entry..."

Question

We have a running Shopware 6.4.7 and our ERP system is importing products into it.

One of the API requests looks like this:

URL: https://www.example.com/api/_action/sync

additional header:
    single-operation -> 1
    indexing-behavior -> use-queue-indexing

{
    "write-product-CONF-242732": {
        "entity": "product",
        "action": "upsert",
        "payload": [...]
}

The payload contains

"configuratorSettings": [
    {
        "optionId": "cbdaf832e70950ee5b9135fa6535e9a8"
    },
    {
        "optionId": "fa6549ff184c43be95d5db190d63ad8f"
    },
    {
        "optionId": "61f4ff3cdc394b489ff4ba90627bf43a"
    }
],

And the request fails with a horrible error message (due to multiple encodings)

{"errors":[{"code":"0","status":"500","title":"Internal Server Error","detail":"An exception occurred while executing \u0027INSERT INTO product_configurator_setting (id, version_id, product_id, product_version_id, property_group_option_id, created_at) VALUES (\u0027|F\ufffd\ufffdb\\u0022I\u02bd\ufffdS\u0002\ufffd(\ufffd;\u0027,\u0027\u000f\ufffd\u001c\ufffd\ufffdjK\u00beK\ufffd\ufffdu,4%\u0027,\u0027\u0228Z2\u003C_C\u0583\ufffd\ufffd\ufffd\u0001\ufffdQ\ufffd\u0027,\u0027\u000f\ufffd\u001c\ufffd\ufffdjK\u00beK\ufffd\ufffdu,4%\u0027,\u0027\ufffd\ufffd2\ufffd\tP\ufffd[\ufffd5\ufffde5\ufffd\u0027,\u00272022-04-13 11:01:23.653\u0027);\u0027:\n\nSQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry \u0027\xC8\xA8Z2\u003C_C\xD6\x83\xE4\xF4\xC8\x01\x94Q\xDF-\x0F\xA9\x1C\xE3\\u0027 for key \u0027uniq.product_configurator_setting.prod_id.vers_id.prop_group_id\u0027"}]}

Let me translate it for you: Duplicate entry on the key uniq.product_configurator_setting.prod_id.vers_id.prop_group_id

So from my point of view should Shopware recognise, that the entry in product_configurator_setting already exist and either UPDATE it or even better just skip it, because no update is needed.

So the important question is:

• Is this intended behaviour?
• Is this a core bug?
• Does our ERP system somehow need to track, what is already imported to avoid this?

I'm a little puzzled and think, this is somewhat broken. I didn't dig down yet, why this happens, because hopefully someone already did :-)

Answer 1

The described behaviour of recognising the entity and updating it will in this case only find the given product and update that with the given payload, in your case new configuration options. That is intended behavior I guess.

You can either:

• Track the options in you ERP and only add new options
• Request the existing configuration and make the diff in your ERP
• Delete all entries for that product before the request. I guess you can do that with the same request by adding delete actions.

Currently at my phone and don’t have more in depth examples at hand. Hope this helps already.

Answer 2

Just in case someone is finding this. Our current solution is a core hack (which most likely only works on MY(!)sql):

Index: vendor/shopware/core/Framework/DataAbstractionLayer/Dbal/EntityWriteGateway.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vendor/shopware/core/Framework/DataAbstractionLayer/Dbal/EntityWriteGateway.php b/vendor/shopware/core/Framework/DataAbstractionLayer/Dbal/EntityWriteGateway.php
--- a/vendor/shopware/core/Framework/DataAbstractionLayer/Dbal/EntityWriteGateway.php   
+++ b/vendor/shopware/core/Framework/DataAbstractionLayer/Dbal/EntityWriteGateway.php   (date 1658315344735)
@@ -168,7 +168,7 @@
 
         $previous = null;
         $mappings = new MultiInsertQueryQueue($this->connection, $this->batchSize, false, true);
-        $inserts = new MultiInsertQueryQueue($this->connection, $this->batchSize);
+        $inserts = new MultiInsertQueryQueue($this->connection, $this->batchSize, false, true);
 
         $executeInserts = function () use ($mappings, $inserts): void {
             $mappings->execute();

Answer 3

I agree with Fabian since it is obviously Shopware's responsibility to decide on whether it should perform an update or an insert.

So why a Duplicate entry on the key (caused by an insert) whilst Shopware should indeed have been smart enough to rather perform an update?

Answer 4

The solution Fabian suggested works fine for inserts as it transforms the statement from INSERT INTO to INSERT IGNORE INTO which won't stop inserting further rows.

But the next error that pops up is a

`REPLACE INTO` related Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails...

which is caused by the fourth parameter passed to MultiInsertQueryQueue being set to true