Product Import Issue - Cardinality violation: 1242 Subquery returns more than 1 row

vignesvaran commented 4 years ago

Environment and configuration

magento community version 2.3.3
current akeneo connector version - 100.3.4

Steps to reproduce

After several successful imports of products I have came across this issue

Expected result

Products should be imported successfully.
If there are any duplicate entries it should overwrite them in db.

Actual result

akeneo-import-error

akosglue commented 4 years ago

have you solved it anyhow?

vignesvaran commented 4 years ago

@akosglue Not Yet. I had flush db and Akeneo Connector tables. And then imports again.

simonrl commented 4 years ago

Hi,

any news on this? We're experiencing the same issue with options after update to 100.4.11. Interesting to note that it works fine in a single store environment, problems only occur on multistore.

-- Simon

thanninger commented 4 years ago

We need a fix as well, thanks in advance! Christian

Dnd-Gimix commented 4 years ago

Hello @vignesvaran @simonrl @thanninger , @akosglue ,

Thank you for your interest in Akeneo Connector for Magento 2.

Could you please provide more insights on your setup so we can understand why this error is happening:

Did you used the connector on an existing Magento 2 with already created products or a new one ?
What is your version of the connector ? (for those who didn't provided it)
Can you check in your akeneo_connector_entities table for any duplicates in the "entity_id" column on : import = product ?
Did you deleted products from Magento 2 ? Did you created products manually in Magento 2 ?
@simonrl said that it might be related to multistore environment : could you all tell us if you are on a Magento 2 instance with multiple stores or websites please ?

Many thanks.

Regards,

simonrl commented 4 years ago

Hi @Dnd-Gimix ,

multiple websites, currently 1 store view each; Magento-version 2.3.1. We created all products through Akeneo import and have been using various versions of this module (and its predecessor) since 2016. We don't change product or attribute data manually in the backend.

My case is for attribute options, not products, so maybe it's not the same problem.

In the meantime, we investigated a bit further, and your hint with "akeneo_connector_entities" actually was correct - there are duplicates (which is, basically, why the query fails). However, if we delete them, they get created with every new import.

I think another, earlier version of the module had an error and actually created some attribute options with identical names twice. We're seeing them in the backend. We're currently in the process of removing them & checking the import again. I will update with our findings. (That we're not seeing this issue in a single-store, single-locale environment may signal that somewhere, somewhen, the check if an attribute option exists was done by using the store view label, and not the pim code or admin label.)

-- Simon

madleen commented 3 years ago

Hello @simonrl , Have you been able to correct your problem? If so, could you give us details of your actions? I have the exact same problem. Magento : 2.3.5 Akeneo : 3.1.18 Akeneo Connector : 100.4.11

And hello @Dnd-Gimix , how is it possible to unblock the situation?

Thank you in advance for your return, Madleen

simonrl commented 3 years ago

Hi @madleen

we went with the way I pointed out in my earlier comment:

We're currently in the process of removing them & checking the import again.

Basically we went through backend & database, checked duplicate attribute options, updated our products (so they always use the correct option) and removed the duplicates afterwards.

-- Simon

ksiamro commented 2 years ago

Hey, I have exactly the same problem with attribute option imports.

Cardinality violation: 1242 Subquery returns more than 1 row, query was:
  UPDATE `tmp_akeneo_connector_entities_option` t SET `_entity_id` = (
    SELECT `entity_id` FROM `akeneo_connector_entities` c
    WHERE CONCAT(t.`attribute`, "-", t.`code`) = c.`code`
    AND c.`import` = "option"
  )

Magento: 2.3.7-p3 Akeneo Connector: 101.8.2

Removing duplicates from akeneo_connector_entities and eav_attribute_option fixes the problem for a while and Cardinality violation: 1242 is no longer thrown for few next imports. However, after some time the issue happens again.

We are using multi-store ( one website / multiple store views ) and multi-locale. Attributes and Attribute options are not added manually in Magento. Just from Akeneo.

Have You or someone found the reason for the issue and possible solution? :)

simonrl commented 2 years ago

@ksiamro it's not enough to remove the duplicates from akeneo_connector_entities. See my comments https://github.com/akeneo/magento2-connector-community/issues/173#issuecomment-671422576 and https://github.com/akeneo/magento2-connector-community/issues/173#issuecomment-698846461 above:

I think another, earlier version of the module had an error and actually created some attribute options with identical names twice. We're seeing them in the backend. We're currently in the process of removing them & checking the import again.

ksiamro commented 2 years ago

@simonrl , thanks for the quick response :) Yea, when the problem happens, we are removing duplications also from eav_attribute_option (I added the info to the previous post). In general, that helps but just for a while. The issue with Cardinality violation: 1242 happens again from time to time for Attribute Option Imports anyway.

zepgram commented 2 years ago

Hello, when you upgrade to new version of Akeneo you must delete oldest attribute and option after an indexation failure. In my case I'm upgrading from version 100.2.6 to version 103.0.3.
Here is a basic code that Akeneo should integrate in his connector as DataPatch to resolve incompatibility issues between major versions:

$phpPath = $this->phpExecutableFinder->find() ?: 'php';
try {
    // we create doubloon by running this command: ignore output error, this command will fail in any case
    $this->shell->execute($phpPath . ' ' . BP . '/bin/magento akeneo_connector:import --code=attribute');
} catch (\Throwable $e) {
}
try {
    // we create doubloon by running this command: ignore output error, this command will fail in any case
    $this->shell->execute($phpPath . ' ' . BP . '/bin/magento akeneo_connector:import --code=option');
} catch (\Throwable $e) {
    // we create doubloon by running this command: ignore output error
}

// now that doubloon are created in akeneo_connector_entities table, we can delete oldest ones and keep only the newest (corresponding to latest version of the attribute/option in Akeneo)
$connection = $this->moduleDataSetup->getConnection();
$akeneoConnectorTable = $connection->getTableName('akeneo_connector_entities');

// delete doubloon and keep only newest values
$select = $connection->select()
    ->from(['a' => $akeneoConnectorTable])
    ->joinLeft(
        ['b' => $akeneoConnectorTable],
        ' b.created_at > a.created_at ' .
        ' AND (b.code = a.code AND b.import = a.import)',
        []
    )
    ->where('b.id IS NOT NULL');

$sql = $select->deleteFromSelect('a');
$connection->query($sql);

To detect doubloons, just run option or attribute import (or both), then you can execute this SQL request to detecte duplicate entities:

SELECT `a`.* FROM `akeneo_connector_entities` AS `a`
LEFT JOIN `akeneo_connector_entities` AS `b` ON  b.created_at > a.created_at
AND (b.code = a.code AND b.import = a.import) WHERE (b.id IS NOT NULL)

wmdkdkussin commented 1 year ago

Hello all,

And thanks to @zepgram who provided a hint for me to solve the issue - at least in my case.

SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row, query was: UPDATE `tmp_akeneo_connector_entities_option` t SET `_entity_id` = ( SELECT `entity_id` FROM `akeneo_connector_entities` c WHERE CONCAT(t.`attribute`, "-", t.`code`) = c.`code` AND c.`import` = "option" )

The initial exception points out the issue that in Magento 2 are already existing attributes which also exists in Akeneo but which "duplicated attribute options" for Akeneo. - In short: In Magento are same named attribute like in Akeneo with multiple option with the same label. For Magento 2 there isn't an issue because labels can be named the same because the unique id is option_id but Akeneo Connector maps by option label which will lead to duplicates.

To solve the above exception you have to delete the multiple label in Magento or BETTER delete the whole attribute and Akeneo Connector will re-create it.

nguyetnt commented 1 year ago

Is there any other suggestion for this issue? I tried the solution suggested but it doesn't help. The issue still happens on latest Magento 2.4.5 and Akeneo Connector version 103.0.8

akeneo / magento2-connector-community