Integrity constraint violation when importing existing products, categories, etc.

[kdim95]

Laravel framework version: 10.16.1
Aimeos Laravel version: 2023.04.*
PHP Version: 8.2.17
Environment: Linux

Hello,

I'm getting integrity constraint errors when importing XMLs for products, categories, etc.
The expected behavior is for the products, categegories, etc. to be updated if they already exist.
It even says so in the documentation here: https://aimeos.org/docs/2023.x/cronjobs ... ml-import/
"You can use the product import job controller for XML files to import/update the products."

For example, I get Integrity constraint violation: 1062 Duplicate entry when attempting to import a catalog item, which already exists in the database.

Can you help me out understanding if this is supposed to happen and why it's happening?

Best regards

[aimeos]

Can you provide a minimal XML which reproduces the problem?

[kdim95]

Hello,

I get Integrity constraint violation even when attempting to import the root catalog item:

Minimal XML:

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<catalog>
    <catalogitem ref="home">
        <catalog.code><![CDATA[Home]]></catalog.code>
        <catalog.label><![CDATA[Home]]></catalog.label>
        <catalog.status><![CDATA[1]]></catalog.status>
    </catalogitem>
</catalog>

Error:

Code: Select all

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Home-1.' for key 'unq_mscat_code_sid': 
        INSERT INTO "mshop_catalog" (
                "siteid", "label", "code", "status", "parentid", "level",
                "nleft", "nright", "config", "mtime", "ctime", "editor", "target"
        ) VALUES (
                '1.', ?, ?, ?, ?, ?, ?, ?, '', '1970-01-01 00:00:00', '1970-01-01 00:00:00', '', ''
        )
["Home","Home",1,0,0,2325,2326]

[aimeos]

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<catalog>
    <catalogitem ref="home">
        <catalog.code><![CDATA[Home]]></catalog.code>
        <catalog.label><![CDATA[Home]]></catalog.label>
        <catalog.status><![CDATA[1]]></catalog.status>
    </catalogitem>
</catalog>

"ref" and "catalog.code" must be the same case (lower case).

[kdim95]

Hello,

Fixing the capitalization worked for the root catalog item, but the error occurs for the children as well
Does this mean that the children also have to have ref="" parameter matching the catalog.code?
Adding this parameter to the child <catalogitem> tags seems to work, so I think the docs might be wrong.

I'm looking at the docs here and I see no ref="" parameter in the child items in the example:
https://aimeos.org/docs/2023.x/cronjobs ... ml-import/

Here is an example without ref="" attribute on subcategories (throws integrity constraint violation):

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<catalog>
    <catalogitem ref="home">
        <catalog.code><![CDATA[home]]></catalog.code>
        <catalog.label><![CDATA[Root category]]></catalog.label>
        <catalog.status><![CDATA[1]]></catalog.status>
    </catalogitem>
    <catalog>
        <catalogitem>
            <catalog.code><![CDATA[cat-1]]></catalog.code>
            <catalog.label><![CDATA[Category 1]]></catalog.label>
            <catalog.status><![CDATA[1]]></catalog.status>
        </catalogitem>
        <catalog>
            <catalogitem>
                <catalog.code><![CDATA[cat-2]]></catalog.code>
                <catalog.label><![CDATA[Subcategory 1]]></catalog.label>
                <catalog.status><![CDATA[1]]></catalog.status>
            </catalogitem>
            <catalogitem>
                <catalog.code><![CDATA[cat-3]]></catalog.code>
                <catalog.label><![CDATA[Subcategory 2]]></catalog.label>
                <catalog.status><![CDATA[1]]></catalog.status>
            </catalogitem>
        </catalog>
    </catalog>  
</catalog>

Here is an example with ref="" attributes included (categories update properly with no errors):

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<catalog>
    <catalogitem ref="home">
        <catalog.code><![CDATA[home]]></catalog.code>
        <catalog.label><![CDATA[Root category]]></catalog.label>
        <catalog.status><![CDATA[1]]></catalog.status>
    </catalogitem>
    <catalog>
        <catalogitem ref="cat-1">
            <catalog.code><![CDATA[cat-1]]></catalog.code>
            <catalog.label><![CDATA[Category 1]]></catalog.label>
            <catalog.status><![CDATA[1]]></catalog.status>
        </catalogitem>
        <catalog>
            <catalogitem ref="cat-2">
                <catalog.code><![CDATA[cat-2]]></catalog.code>
                <catalog.label><![CDATA[Subcategory 1]]></catalog.label>
                <catalog.status><![CDATA[1]]></catalog.status>
            </catalogitem>
            <catalogitem ref="cat-3">
                <catalog.code><![CDATA[cat-3]]></catalog.code>
                <catalog.label><![CDATA[Subcategory 2]]></catalog.label>
                <catalog.status><![CDATA[1]]></catalog.status>
            </catalogitem>
        </catalog>
    </catalog>  
</catalog>

Best regards

[aimeos]

Fixing the capitalization worked for the root catalog item, but the error occurs for the children as well
Does this mean that the children also have to have ref="" parameter matching the catalog.code?
Adding this parameter to the child <catalogitem> tags seems to work, so I think the docs might be wrong.

I'm looking at the docs here and I see no ref="" parameter in the child items in the example:
https://aimeos.org/docs/2023.x/cronjobs ... ml-import/

Yes, for updates the "ref" parameter is required, for initial insertion it can be omitted.
We've updated the docs accordingly like you've suggested. Thanks!

[kdim95]

Hello,

Thank you, this makes it clear about the category items.

I am experiencing more integrity constraint violations when importing existing products.
This time it seems to be coming from the product properties under the <property> tag.

This is the error I get:
Integrity constraint violation: 1062 Duplicate entry '19-product/property-default--1.' for key 'unq_msproli_pid_dm_ty_rid_sid'

Here is how to reproduce the error:

• Import the example XML
• Change the package-weight property value from 50.00 to 60.00
• Import the changed example XML again

Expected behavior:

• The property value should update based on the passed property type and language
• Multiple rows for one property.type shouldn't be added

Here is a Minimal XML taken from the documentation:

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>

<products>
  <!-- List of product items with unique code as "ref" -->
  <productitem ref="TEST-ARTICLE">

    <!-- Required, possible values: "default" (article), "bundle", "select", "voucher" -->
    <product.type><![CDATA[default]]></product.type>

    <!-- Required, unique SKU of the product -->
    <product.code><![CDATA[TEST-ARTICLE]]></product.code>

    <!-- Required, label of the product, used if no language specific name is avialable -->
    <product.label><![CDATA[Test article]]></product.label>

    <!-- Optional, possible values: 1 = enabled (default), 0 = disabled, -1 = review, -2 =archived -->
    <product.status><![CDATA[1]]></product.status>

    <!-- Optional, JSON encoded (multi-dimensional) key/value pairs with arbitrary settings -->
    <product.config><![CDATA[{"key": "value"}]]></product.config>

    <!-- Optional, start ISO date/time value (YYYY-MM-DDTHH:mm:ss) the product is available from -->
    <product.datestart><![CDATA[2000-01-01T00:00:00]]></product.datestart>

    <!-- Optional, end ISO date/time value (YYYY-MM-DDTHH:mm:ss) the product is available to -->
    <product.dateend><![CDATA[2000-01-01T23:59:59]]></product.dateend>

    <!-- Optional, list of product property items -->
    <property>

        <!-- Optional, arbitrary product properties -->
        <propertyitem>

            <!-- Required, any available product property type like "package-height", "package-length", "package-width", "package-weight" -->
            <product.property.type><![CDATA[package-weight]]></product.property.type>

            <!-- Required, two letter ISO language code or empty for no language -->
            <product.property.languageid><![CDATA[]]></product.property.languageid>

            <!-- Required, value of the product property -->
            <product.property.value><![CDATA[50.00]]></product.property.value>

        </propertyitem>
        <!-- more property items -->

    </property>
    
  </productitem>
</products>

[aimeos]

We can't reproduce the problem with the product property update with your file. According to the error message, the property was tried to be imported as referenced item in mshop_product_list. Did you mixed up <lists> with <property> tags?

[kdim95]

Did you mixed up <lists> with <property> tags?

I'm not sure. How can I checked if I "mixed up <lists> with <property> tags" ?
Are you running the import on the same Aimeos version as mine?

[aimeos]

We only use 2023.10 LTS as 2023.04 isn't supported any more.