php Shopware 6同步API:完整性约束冲突:1062 UPSERT时出现重复条目

6qqygrtg  于 2022-12-17  发布在  PHP
关注(0)|答案(2)|浏览(176)

您好,Shopware社区:-)
我尝试将PIM中的产品数据同步到Shopware 6。我使用Sync API发送批量产品数据,这些数据需要更新产品。下面是更新单个产品的示例请求:

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"
          ]
        ]
      ]
    ]
  ]
]

在成功地在Shopware中添加了大约200个产品后,我尝试同步的每个产品都会收到以下错误信息。

{"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 (\u0027\ufffd[\ufffd\u003Cb\ufffdO5\ufffdS:\ufffd\u06e3\ufffd\u0026\u0027,\u0027\u000f\ufffd\u001c\ufffd\ufffdjK\u00beK\ufffd\ufffdu,4%\u0027,\u0027\u000f\ufffd\u001c\ufffd\ufffdjK\u00beK\ufffd\ufffdu,4%\u0027,\u0027\\r\ufffd\ufffd\u055e\ufffd@:\ufffd\ufffdW\ufffdn\ufffd\\Z\u0027,\u0027\u000f\ufffd\u001c\ufffd\ufffdjK\u00beK\ufffd\ufffdu,4%\u0027,\u0027\ufffd6\u0007e\\npG\ufffd\ufffd~\ufffd\ufffd\ufffd\ufffd\ufffd\x7F\u0027,\u0027L\ufffdC\ufffd\u008a\ufffdA\u3bd2\u0005\ufffd_W\ufffd\u0027,\u0027\u000f\ufffd\u001c\ufffd\ufffdjK\u00beK\ufffd\ufffdu,4%\u0027,\u0027\u000f\ufffd\u001c\ufffd\ufffdjK\u00beK\ufffd\ufffdu,4%\u0027,\u0027{\\\u0022cb7d2554b0ce847cd82f3ac9bd1c0dfca\\\u0022:{\\\u0022currencyId\\\u0022:\\\u0022b7d2554b0ce847cd82f3ac9bd1c0dfca\\\u0022,\\\u0022gross\\\u0022:31.5,\\\u0022net\\\u0022:29.247910863509752,\\\u0022linked\\\u0022:false}}\u0027,\u0027C901 0100 04\u0027,\u00270\u0027,NULL,\u00271\u0027,\u00270\u0027,\u00271\u0027,\u00271\u0027,\u00270\u0027,\u00272022-11-11 22:38:59.752\u0027);\u0027:\n\nSQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry \u0027\\xBE[\\xD2\u003Cb\\x94O5\\x88S:\\xB8\\xDB\\xA3\\x98\u0026-\\x0F\\xA9\\x1C\\xE3\\xE9jK\\\u0027 for key \u0027PRIMARY\u0027"}]}],"extensions":[]}},"deleted":[],"notFound":[],"extensions":[]}

1.我试图upsert(插入或更新)产品。如果产品不存在,Shopware应该只使用INSERT语句,还是我错了?
1.我怎样才能使错误响应中的id可读?
1.我的请求有错误吗?
我尝试通过Sync API在Shopware 6中插入或更新产品,但得到的响应是一条不可读的错误消息。

shstlldc

shstlldc1#

1.我试图upsert(插入或更新)产品。如果产品不存在,Shopware应该只使用INSERT语句,还是我错了?
你没有错。老实说,如果没有完整的有效负载,很难说这是如何或为什么会发生的。如果这阻碍了你,你可以将fail-on-error头部设置为false来跳过这一行。
1.我怎样才能使错误响应中的id可读?
你不能。在sql执行的时候,参数中的id已经被修改成二进制数据了。你在错误消息中看到的只是打印二进制数据时产生的混乱。
1.我的请求有错误吗?
我不这么认为,看起来还不错。正如前面所述,这种情况不应该发生在upsert上。也许可以尝试禁用错误时失败,然后检查日志,看看这是否是唯一的约束冲突示例。然后尝试每次将负载减少一半,直到找到导致约束冲突的记录,以便进一步调查。

92dk7w1h

92dk7w1h2#

嗯,有点棘手,因为你只发布了一个有效负载,而不是2个有效负载。如果你从系统中删除所有产品,并开始插入我估计第一个产品将成功,第二个不会。这将是一种方法来检查您的查询没有问题。
看看这个错误,它只是说其中一个表的UNIQUE字段正在被重用,在这种情况下,很可能是image->idcover->id,您应该忽略这些字段,以便SW6为您随机生成它们,或者您可以自己动态生成它们。
下面是一个直观的例子来说明我的意思:

"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"
          ]
        ]

相关问题