Exception during product export

[Hocdoc]

When we export our 160,000 products with Akeneo 1.5.5/MySQL we sometimes get this exception during the job execution:

INSERT INTO pim_catalog_completeness ( locale_id, channel_id, product_id, ratio, missing_count, required_count ) SELECT l.id AS locale_id, c.id AS channel_id, p.id AS product_id, ( COUNT(distinct v.id) / ( SELECT count(*) FROM pim_catalog_attribute_requirement r LEFT JOIN pim_catalog_attribute_locale al ON al.attribute_id = r.attribute_id WHERE family_id = p.family_id AND channel_id = c.id AND r.required = true AND (al.locale_id = l.id OR al.locale_id IS NULL) ) * 100 ) AS ratio, ( ( SELECT count(*) FROM pim_catalog_attribute_requirement r LEFT JOIN pim_catalog_attribute_locale al ON al.attribute_id = r.attribute_id WHERE family_id = p.family_id AND channel_id = c.id AND r.required = true AND (al.locale_id = l.id OR al.locale_id IS NULL) ) - COUNT(distinct v.id) ) AS missing_count, ( SELECT count(*) FROM pim_catalog_attribute_requirement r LEFT JOIN pim_catalog_attribute_locale al ON al.attribute_id = r.attribute_id WHERE family_id = p.family_id AND channel_id = c.id AND r.required = true AND (al.locale_id = l.id OR al.locale_id IS NULL) ) AS required_count FROM missing_completeness m JOIN pim_catalog_channel c ON c.id = m.channel_id JOIN pim_catalog_locale l ON l.id = m.locale_id JOIN pim_catalog_product p ON p.id = m.product_id JOIN pim_catalog_attribute_requirement r ON r.family_id = p.family_id AND r.channel_id = c.id JOIN pim_catalog_product_value v ON v.attribute_id = r.attribute_id AND (v.scope_code = c.code OR v.scope_code IS NULL) AND (v.locale_code = l.code OR v.locale_code IS NULL) AND v.entity_id = p.id LEFT JOIN pim_catalog_metric _rel_1 ON _rel_1.id = v.metric_id LEFT JOIN pim_catalog_product_value_option _rel_7 ON _rel_7.value_id = v.id LEFT JOIN complete_price AS complete_price ON complete_price.value_id = v.id AND complete_price.channel_id = c.id AND complete_price.locale_id = l.id LEFT JOIN pim_catalog_attribute_locale al ON al.attribute_id = v.attribute_id WHERE ( v.value_string IS NOT NULL OR v.value_integer IS NOT NULL OR v.value_decimal IS NOT NULL OR v.value_boolean IS NOT NULL OR v.value_text IS NOT NULL OR v.value_date IS NOT NULL OR v.value_datetime IS NOT NULL OR _rel_1.data IS NOT NULL OR v.option_id IS NOT NULL OR v.media_id IS NOT NULL OR _rel_7.option_id IS NOT NULL OR complete_price.value_id IS NOT NULL ) AND (al.locale_id = l.id OR al.locale_id IS NULL) AND r.required = true GROUP BY p.id, c.id, l.id' with params [1]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-1-161126' for key 'searchunique_idx'

Wow, that's really a big SQL statement :-o. You have to scroll to the right to see the error message: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-1-161126' for key 'searchunique_idx'.

Anything we can do?

[LaureBrosseau]

Hello @Hocdoc,

This error can happen when you have 2 completeness calculations executed at the same time on the same product. For instance, if you run a product export while a completeness command is also being launched, it creates a conflict and such issues happen.

I'm not sure how you or we can prevent that.

Best regards,

Laure

[Hocdoc]

Thanks, this is a good explanation. There were some mass edits in the same time, maybe they have triggered the completeness calculation.

The error may be prevented when using an INSERT IGNORE instead of the INSERT SQL command (found in CompletenessGenerator.php)? Should I write a PR?

[LaureBrosseau]

Hello @Hocdoc,

Thanks for your feedback, we'll be very pleased if you could :) Our core team will review it and let you know if it's accepted or not. Thanks a lot for contributing :)

Kind regards,

Laure

[LaureBrosseau]

Hi @Hocdoc, thanks again for contributing! Your fix has been merged in the next coming version. Can we close this issue?

Kind regards,

Laure