Integrity constraint violation when importing existing products, categories, etc.
Forum rules
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
Integrity constraint violation when importing existing products, categories, etc.
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 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
Last edited by kdim95 on 12 Apr 2024, 17:30, edited 2 times in total.
Re: Integrity constraint violation when importing existing products, categories, etc.
Can you provide a minimal XML which reproduces the problem?
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
Re: Integrity constraint violation when importing existing products, categories, etc.
Hello,
I get Integrity constraint violation even when attempting to import the root catalog item:
Minimal XML:
Error:
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>
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]
Re: Integrity constraint violation when importing existing products, categories, etc.
"ref" and "catalog.code" must be the same case (lower case).kdim95 wrote: ↑04 Apr 2024, 15:02Code: 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>
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
Re: Integrity constraint violation when importing existing products, categories, etc.
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):
Here is an example with ref="" attributes included (categories update properly with no errors):
Best regards
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>
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>
Re: Integrity constraint violation when importing existing products, categories, etc.
Yes, for updates the "ref" parameter is required, for initial insertion it can be omitted.kdim95 wrote: ↑08 Apr 2024, 09:14 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/
We've updated the docs accordingly like you've suggested. Thanks!
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
Re: Integrity constraint violation when importing existing products, categories, etc.
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:
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
- The property value should update based on the passed property type and language
- Multiple rows for one property.type shouldn't be added
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>
Re: Integrity constraint violation when importing existing products, categories, etc.
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?
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
Re: Integrity constraint violation when importing existing products, categories, etc.
We only use 2023.10 LTS as 2023.04 isn't supported any more.
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star