magento2: catalog_product_price index getting stuck

Hello, I’m having this problem updating product inventory directly using Magento\CatalogInventory\Api\StockRegistryInterface::updateStockItemBySku(). In Magento\CatalogInventory\Model\ResourceModel\Stock\Item::_afterSave(), there’s a check to see if the data changed for any of the fields [‘is_in_stock’,‘use_config_manage_stock’,‘manage_stock’], and if so it runs the function reindexRow() from the priceIndexProcessor. I’ve been having trouble finding someone discussing this problem until I stumbled across your post here, and instead I’ve been somewhat convinced that the error lies in some of our custom modules against the recent 2.4.5 update and spending most of my time reviewing them. But, all paths appear to lead back to the Magento_CatalogInventory module.

I believe this problem was introduced by the commit https://github.com/magento/magento2/commit/263c17fd05152ff06b2ce2f32611094d371ba665, where the db_schema.xml was modified, and an “id” column was added to the catalog_product_index_price_tmp table (and the PK set against just that column), which makes the schema for that table different than catalog_product_index_price. Through the Magento\Catalog\Model\Indexer\Product\Price\AbstractAction::reindexRow() a temporary table is created based on the table catalog_product_index_price_tmp, named catalog_product_index_price_temp, and that table is then used in an INSERT INTO tbl SELECT * FROM othertbl query. Since the schemas now differ, you get this quite colossal problem.

We don’t seem to be seeing this error running a full reindex as that code path eventually leads to the function Magento\Catalog\Model\Indexer\Product\Price\AbstractAction::_insertFromTable(), which determines the columns to use for the insert directly from the target table (catalog_product_index_price), instead of just using the mighty *, and the adjustment to the schema for catalog_product_index_price_tmp does not matter there. I’m really not sure why you’re seeing this problem where you are, and now I’m scouring my logs to see if I’m getting this error outside the case where I’m adjusting inventory quantities directly, and I don’t seem to be - but I still think this boils down to the same problem.

I’ve been looking to put together a composer patch to get the Magento\Catalog\Model\Indexer\Product\Price\AbstractAction::_syncData() function to use the _insertFromTable() instead, but this is a bit deeper into the M2 code than I’m generally comfortable, and hoping to get a bit of discussion confirming the problem and suggested solution. My best guess is that throughout those “optimizations”, this particular product-specific reindexing was simply overlooked.

I believe this problem was introduced by the commit https://github.com/magento/magento2/commit/263c17fd05152ff06b2ce2f32611094d371ba665, where the db_schema.xml was modified, and an “id” column was added to the catalog_product_index_price_tmp table (and the PK set against just that column), which makes the schema for that table different than catalog_product_index_price. Through the Magento\Catalog\Model\Indexer\Product\Price\AbstractAction::reindexRow() a temporary table is created based on the table catalog_product_index_price_tmp, named catalog_product_index_price_temp, and that table is then used in an INSERT INTO tbl SELECT * FROM othertbl query. Since the schemas now differ, you get this quite colossal problem.

This caused a similar problem for my company (running Magento 2.4.6-p3). The new id primary key on the temporary table slowed down the inventory indexer to the point where it could never complete. We reverted the db_schema.xml so that the 3 previous primary keys were restored then rebuilt the codebase (you need to drop the catalog_product_index_price_tmp table on the first run). Then the inventory indexer ran fine, usually completing in approximatley 5 minutes.

Any update on the target version for this? Not fixed in 2.4.6-p1. Though I suppose it doesn’t matter as the custom indexer model workaround is easy enough.

Edit: To be explicit, my workaround for this was adding this in my custom product type <type> tag:

<indexerModel instance="Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\SimpleProductPrice" />

Hello @vseager,

We have found a way to reproduce the issue. We have tried it in Magento 2.4.5-p1 and 2.4-develop branch and the issue is reproducible for us. We have made the below module in order to reproduce it: Adobe.zip

1. Put the above module in app\code folder.
2. Run bin/magento setup:upgrade command
3. Create a new product of the new Product type created by the above module.
4. Indexer should be on “Update by Schedule”
5. Run the below command to trigger the error: n98-magerun2 sys:cron:run indexer_update_all_views The error is reproducible:

Internal JIRA has been created to address this issue:

https://jira.corp.adobe.com/browse/ACP2E-1401

The team has started working on it and a hotfix will share soon with the community.

Thanks

Hello,

As I can see this issue got fixed in the scope of the internal Jira ticket ACP2E-1401 by the internal team Related commits: https://github.com/magento/magento2/search?q=ACP2E-1401&type=commits

Based on the Jira ticket, the target version is 2.4.6.

Thanks

I have two changes that I’m using on a site now to resolve this problem. The first one is a composer patch against magento/module-catalog, and just adjusts that _syncData() function to target specific columns instead of using the “SELECT *”. I don’t believe it’s much of a performance knock as the DDL information that gets queried is pretty heavily cached. This requires cweagans/composer-patches. Contents I am using are:

--- module-catalog/Model/Indexer/Product/Price/AbstractAction.php	2022-09-12 15:47:34.000000000 -0500
+++ module-catalog-b/Model/Indexer/Product/Price/AbstractAction.php	2022-11-15 10:46:26.324397600 -0600
@@ -176,8 +176,11 @@ abstract class AbstractAction
     {
         // for backward compatibility split data from old idx table on dimension tables
         foreach ($this->dimensionCollectionFactory->create() as $dimensions) {
+            $destinationTable = $this->tableMaintainer->getMainTableByDimensions($dimensions);
+            $columns = array_keys($this->getConnection()->describeTable($destinationTable));
             $insertSelect = $this->getConnection()->select()->from(
-                ['ip_tmp' => $this->_defaultIndexerResource->getIdxTable()]
+                ['ip_tmp' => $this->_defaultIndexerResource->getIdxTable()],
+                $columns
             );
 
             foreach ($dimensions as $dimension) {
@@ -189,7 +192,7 @@ abstract class AbstractAction
                 }
             }
 
-            $query = $insertSelect->insertFromSelect($this->tableMaintainer->getMainTableByDimensions($dimensions));
+            $query = $insertSelect->insertFromSelect($destinationTable);
             $this->getConnection()->query($query);
         }
         return $this;

Next, for my modules defining custom product types, I’m adding a definition for an <indexerModel> inside the <type> declaration in the etc/product_types.xml file, looks like <indexerModel instance="Client\Project\Model\ResourceModel\ProductIndexerPrice\ProjectPrice" />. This requires creating that ProjectPrice class, extending Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\SimpleProductPrice and overriding the __construct() enough to adjust the default value of the $productType argument to set it to the machine type of your custom product type. If you don’t do that, it will fail to generate any records for those products in the price index because part of the query it generates filters to only the product type that is specified for that index. I have not found a way to declare that argument for the original SimpleProductPrice class so that it only affects products of that type - I don’t know that it’s possible. This appears to be the easy way around it.

Hopefully this helps somebody - and if anyone sees anything troubling with these adjustments, by all means please let me know!

Hi @pmonosolo , Verified the issue again on Magento 2.4-develop instance with the mentioned steps by you but still no luck. Steps performed:

1. Magento instance with 10k products.
2. Added new custom product type
3. enabled cron
4. Indexers set on “Update by schedule”
5. Created 10 catalog price rules with different price combinations
6. Created 5 cart price rules
7. imported 1000 products again using command: bin/magento setup:perf:generate-fixtures /var/www/html/magento2/setup/performance-toolkit/profiles/ce/small.xml
8. Wait till all the indexers are re indexed (With Cron)
9. Cache clean No errors observed in cron_schedule table.

Hey @pmonosolo, I’d suggest taking a look at that #36370 mentioned above by hostep - that’s generally the same thing at least when I reviewed it earlier, they’re trying to solve the same problem and that is code that I’d expect to actually get committed in the future. It would be useful for them to get feedback on that as well - it should be pretty easy to pull those changes as a patch. Either way hopefully you have success, good luck!

Hi @alek-s-andr , you might peek at the “etc/product_types.xml” file in that third party module, see if there is a declaration for an <indexerModel> inside the <type> declaration for the product type. It would probably be useful to communicate with the vendor for that module and reference this github issue to see if they can confirm the problem and provide a fix. Even if an indexerModel is defined, if it does not implement Magento\Framework\Indexer\DimensionalIndexerInterface or inherit from a class that does, it will likely end up going through that function which appears to be causing problems. Good luck!

Hello @engcom-November, I’d suggest that the conditions to reproduce this issue include the adjustment of some products to change their is_in_stock/manage_stock/etc values. The full php bin/magento indexer:reindex runs through a different set of functions, and the specific calls that get to the erroring code require that you’re only reindexing specific products at a time, rather than all of them.

If you have your indexes (at least price index) configured to update on save, you can simply take a product that is not managed by inventory (default not managed), go into the product settings and change the configuration to manage inventory, leave the qty at zero so it will be calculated to out of stock, save it, and now it’s no longer in categories. Do a full reindex (php bin/magento indexer:reindex) afterwards and it will fix it and the product will show up again.

The important thing is that you do not see exceptions on the frontend or when running the CLI, because they are caught inside the indexer and written to logs. It does not bubble up from there, and there’s no indication in calling code that there was any problem whatsoever, you have to be looking through the logs to see that anything went wrong or see the symptoms from the frontend.