Question 41722 in PHP Question posted in
Official manual can be found here.

Shopware 6 Sync API: Integrity constraint violation: 1062 Duplicate entry while UPSERT – PHP

Hello Shopware community 🙂

I try to synchronize product data from a PIM to Shopware 6. I use the Sync API to send bulk product data that should upsert products. Here is a sample request to upsert a single product:

array:1 [
  "80-write-product-be5bd23c62944f3588533ab8dba39826" => array:3 [
    "entity" => "product"
    "action" => "upsert"
    "payload" => array:1 [
      0 => array:12 [
        "id" => "be5bd23c62944f3588533ab8dba39826"
        "name" => "Product name"
        "productNumber" => "SKU0001"
        "stock" => 0
        "price" => array:1 [
          0 => array:4 [
            "currencyId" => "b7d2554b0ce847cd82f3ac9bd1c0dfca"
            "gross" => 31.5
            "net" => 29.24791086351
            "linked" => false
          ]
        ]
        "taxId" => "9d3607650a7047c1977e89b9f9fd8d7f"
        "description" => ""
        "customFields" => array:1 [
          "product_group" => null
        ]
        "manufacturerId" => "0df8d5d59ecb403ab7f4c157886eba1a"
        "visibilities" => array:1 [
          0 => array:3 [
            "id" => "be5bd23c62944f3588533ab8dba39826"
            "salesChannelId" => "343eafc74bf84a059f5151705737ac70"
            "visibility" => 30
          ]
        ]
        "cover" => array:3 [
          "id" => "4c9243a7c28afa41e3af9205815f57ee"
          "productId" => "be5bd23c62944f3588533ab8dba39826"
          "mediaId" => "105a864606da427788044766121c73d3"
        ]
        "media" => array:1 [
          0 => array:3 [
            "id" => "36f03ed24c57b7777e45ff4a68d0def1"
            "productId" => "be5bd23c62944f3588533ab8dba39826"
            "mediaId" => "105a864606da427788044766121c73d3"
          ]
        ]
      ]
    ]
  ]
]

After successfully addig around 200 products in Shopware I get the following error message for every product I try to sync.

{"success":false,"data":{"80-write-product-be5bd23c62944f3588533ab8dba39826":{"result":[{"entities":[],"errors":[{"code":"0","status":"500","title":"Internal Server Error","detail":"An exception occurred while executing u0027INSERT INTO `product` (`id`, `version_id`, `parent_version_id`, `product_manufacturer_id`, `product_manufacturer_version_id`, `tax_id`, `product_media_id`, `product_media_version_id`, `cms_page_version_id`, `price`, `product_number`, `stock`, `restock_time`, `active`, `is_closeout`, `purchase_steps`, `min_purchase`, `shipping_free`, `created_at`) VALUES (u0027ufffd[ufffdu003CbufffdO5ufffdS:ufffdu06e3ufffdu0026u0027,u0027u000fufffdu001cufffdufffdjKu00beKufffdufffdu,4%u0027,u0027u000fufffdu001cufffdufffdjKu00beKufffdufffdu,4%u0027,u0027\rufffdufffdu055eufffd@:ufffdufffdWufffdnufffd\Zu0027,u0027u000fufffdu001cufffdufffdjKu00beKufffdufffdu,4%u0027,u0027ufffd6u0007e\npGufffdufffd~ufffdufffdufffdufffdufffdx7Fu0027,u0027LufffdCufffdu008aufffdAu3bd2u0005ufffd_Wufffdu0027,u0027u000fufffdu001cufffdufffdjKu00beKufffdufffdu,4%u0027,u0027u000fufffdu001cufffdufffdjKu00beKufffdufffdu,4%u0027,u0027{\u0022cb7d2554b0ce847cd82f3ac9bd1c0dfca\u0022:{\u0022currencyId\u0022:\u0022b7d2554b0ce847cd82f3ac9bd1c0dfca\u0022,\u0022gross\u0022:31.5,\u0022net\u0022:29.247910863509752,\u0022linked\u0022:false}}u0027,u0027C901 0100 04u0027,u00270u0027,NULL,u00271u0027,u00270u0027,u00271u0027,u00271u0027,u00270u0027,u00272022-11-11 22:38:59.752u0027);u0027:nnSQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry u0027\xBE[\xD2u003Cb\x94O5\x88S:\xB8\xDB\xA3\x98u0026-\x0F\xA9\x1C\xE3\xE9jK\u0027 for key u0027PRIMARYu0027"}]}],"extensions":[]}},"deleted":[],"notFound":[],"extensions":[]}
  1. I try to upsert (insert or update) products. Shopware should only use an INSERT statement if the product doesn’t exists, or am I wrong?
  2. How can I make the ids in the error response readable?
  3. Is there an error in my request?

I tried to insert or update products in Shopware 6 via the Sync API but I get a response with an unreadable error message.

Tags:

2

Answers


  1. 0
    1. I try to upsert (insert or update) products. Shopware should only use an INSERT statement if the product doesn’t exists, or am I wrong?

    You’re not wrong. Honestly hard to say how or why this could happen without having the entire payload at hand. You can set the fail-on-error header to false to skip the row if this is blocking you.

    1. How can I make the ids in the error response readable?

    You can’t. At the point of the sql execution the ids in the parameters have already been changed to binary data. What you see in the error message is just a garbled mess caused by printing binary data.

    1. Is there an error in my request?

    I don’t think so, looks to be alright. As already stated this shouldn’t happen with an upsert. Maybe try disabling fail on error as described and check the logs afterwards to see if this is the only instance of constraint violations. Then try reducing the payload by half each time until you found the record causing the constraint violation to investigate further.

    Login or Signup to reply.
  2. 0

    Hm, a bit tricky since you posted only one payload & not 2 payloads. If you delete all products from the system & start inserting I reckon the first product will succeed, the second will not. That would be one way to check that your query has no issues.

    Looking at the error it just says that a UNIQUE field of one of the tables is being re-used. In this case it’s most likely image->id and cover->id. You should omit these fields so that SW6 generates them randomly for you or you can generate them yourself on the fly.

    Here is a visual example of what I mean:

            "price" => array:1 [
          0 => array:4 [
            /* ID field omitted, so Shopware will generate one for you */
            "currencyId" => "b7d2554b0ce847cd82f3ac9bd1c0dfca"
            "gross" => 31.5
            "net" => 29.24791086351
            "linked" => false
          ]
        ]
        "cover" => array:3 [
          "id" => "4c9243a7c28afa41e3af9205815f57ee" /* must be unique per product */
          "productId" => "be5bd23c62944f3588533ab8dba39826"
          "mediaId" => "105a864606da427788044766121c73d3"
        ]
        "media" => array:1 [
          0 => array:3 [
            "id" => "36f03ed24c57b7777e45ff4a68d0def1" /* must be unique per product */
            "productId" => "be5bd23c62944f3588533ab8dba39826"
            "mediaId" => "105a864606da427788044766121c73d3"
          ]
        ]

    Suggestion 2:
    In worst case, you will have to install an API tool like Postman & play around with your queries. Just go to the Shopware 6 documentation on their API & try out different payloads. Just remove parts of your query like cover, then media, then prices, etc. Try to strip it down to get an understanding on what fails. Once you narrow it down, then you may either need to submit a bug report to Shopware. Or, if you get it working while playing about, to your PIM (whatever that is) software to adjust their queries.

    Suggestion 3:
    If you are running a dev environment, you can always check the "profile" of every API call. When you can an Error response, you can check the Headers for a link to a profile page. Just paste the link in your browser. There you can trace the Database query that is being made. Maybe it will tel you more.

    Suggestion 4:
    Just tell a PHP developer to xDebug the API call that fails. It will help them if you give them that Postman call I was talking about. They will also be able to confirm what’s wrong with the query, whether it’s a bug or whether some more data is needed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search