Magento 2.2.5 Enterprise Edition Price Reindexing On Checkout fails in case of backorder

Question

I have been facing issue on checkout page in case of Back Order product. The exception I am getting "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '42438-0-1' for key 'PRIMARY', query was: INSERT INTO catalog_product_index_price_temp SELECT catalog_product_index_price_final_temp.entity_id, catalog_product_index_price_final_temp.customer_group_id, catalog_product_index_price_final_temp.website_id, catalog_product_index_price_final_temp.tax_class_id, catalog_product_index_price_final_temp.orig_price AS price, catalog_product_index_price_final_temp.price AS final_price, catalog_product_index_price_final_temp.min_price, catalog_product_index_price_final_temp.max_price, catalog_product_index_price_final_temp.tier_price FROM catalog_product_index_price_final_temp"

Followings are my findings :

• Price re-indexing happens to be initiated when "sales_model_service_quote_submit_success" is fired which calls Magento\CatalogInventory\Observer\ReindexQuoteInventoryObserver.
• If you see line number 77, this where it starts price reindxing.
• Also, I am not able to find the existance of key mentioned in exceptioned.

Help me. This is bit of urgent.

Answer 1

Found the root cause of this issue. Since this question is unanswered yet, let me explain what I found and what workaround I came up with hoping this may help someone else. Our business requirement made us to customize products in such a way so that an admin user should be able to add bundle and configurable in group products. We had handled the frontend scenarios accordingly and everything was working fine until this issue. I will just give a short description about was happening :

• In table catalog_product_index_price_temp, columns (entity_id,customer_group_id) is candidate key which means combination of these columns should always be unique. Now, the id that i have mentioned in the issue is 42438-0-1 which means 42438 for product id, 0 for customer group id and 1 for website id.
• We do have Bundle and configurable in the Grouped products so we were getting duplicate product ids for the associated products of bundle and configurable product (children of bundle and configurable products are considered as children for Group product too).

Workaround

Updated line $query = $select->insertFromSelect($this->getIdxTable()); to $query = $select->insertFromSelect($this->getIdxTable(), [], true) in following files

1. \Magento\Bundle\Model\ResourceModel\Indexer\Price::_movePriceDataToIndexTable($entityIds = null)
2. \Magento\ConfigurableProduct\Model\ResourceModel\Product\Indexer\Price\Configurable::_movePriceDataToIndexTable($entityIds = null)

Changes done are nothing but insert on duplicate constraint for index table. So, if we have duplicate values for index table then it will just update the existing one.

Note

I know this is not the right way to do this but this worked like a charm for me and there was no other that I could think of at that moment. Any suggestions on doing this the right way are welcome. Until then it is still up and running.

Also,this was in EEv2.2.5 now _movePriceDataToIndexTable($entityIds = null) has been moved to Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\DefaultPrice in latest versions.