catalog_product_price index getting stuck

[vseager]

Preconditions and environment

• Magento 2.4.5-p1
• Large catalog (~8,000 products)
• All indexes set to "UPDATE BY SCHEDULE"

Steps to reproduce

Let indexes run on cron

Expected result

Indexes run successfully

Actual result

• Indexes get stuck at catalog_product_price.
• Product disappear from categories

Logs show the following errors:

[2022-10-13T09:59:05.759670+00:00] main.ERROR: Cron Job indexer_update_all_views has an error: SQLSTATE[21S01]: Insert value list does not match column list:
1136 Column count doesn't match value count at row 1, query was: INSERT INTO `catalog_product_index_price` SELECT `ip_tmp`.* FROM `catalog_product_index_price_temp` AS `ip_tmp` ON DUPLICATE KEY UPDATE `tax_class_id` = VALUES(`tax_class_id`), `price` = VALUES(`price`), `final_price` = VALUES(`final_price`), `min_price` = VALUES(`min_price`), `max_price` = VALUES(`max_price`), `tier_price` = VALUES(`tier_price`). Statistics: {"sum":0,"count":1,"realmem":0,"emalloc":0,"realmem_start":256376832,"emalloc_start":237979584} [] []
[2022-10-13T09:59:05.760119+00:00] main.CRITICAL: PDOException: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1 in /vendor/magento/framework/DB/Statement/Pdo/Mysql.php:90
Stack trace:
#0 /vendor/magento/framework/DB/Statement/Pdo/Mysql.php(90): PDOStatement->execute()
#1 /vendor/magento/framework/DB/Statement/Pdo/Mysql.php(106): Magento\Framework\DB\Statement\Pdo\Mysql->Magento\Framework\DB\Statement\Pdo\{closure}()
#2 /vendor/magento/framework/DB/Statement/Pdo/Mysql.php(91): Magento\Framework\DB\Statement\Pdo\Mysql->tryExecute(Object(Closure))
#3 /vendor/magento/zendframework1/library/Zend/Db/Statement.php(313): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#4 /vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#5 /vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(247): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#6 /vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(564): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO`ca...', Array)
#7 /vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(634): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('INSERT INTO `ca...', Array)
#8 /vendor/magento/module-catalog/Model/Indexer/Product/Price/AbstractAction.php(193): Magento\Framework\DB\Adapter\Pdo\Mysql->query('INSERT INTO `ca...')
#9 /vendor/magento/module-catalog/Model/Indexer/Product/Price/AbstractAction.php(417): Magento\Catalog\Model\Indexer\Product\Price\AbstractAction->_syncData(Array)
#10 /vendor/magento/module-catalog/Model/Indexer/Product/Price/Action/Rows.php(126): Magento\Catalog\Model\Indexer\Product\Price\AbstractAction->_reindexRows(Array)
#11 /vendor/magento/module-catalog/Model/Indexer/Product/Price.php(68): Magento\Catalog\Model\Indexer\Product\Price\Action\Rows->execute(Array)
#12 /vendor/magento/framework/Interception/Interceptor.php(58): Magento\Catalog\Model\Indexer\Product\Price->execute(Array)
#13 /vendor/magento/framework/Interception/Interceptor.php(138): Magento\Catalog\Model\Indexer\Product\Price\Interceptor->___callParent('execute', Array)

Next Zend_Db_Statement_Exception: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1, query was: INSERT INTO `catalog_product_index_price` SELECT `ip_tmp`.* FROM `catalog_product_index_price_temp` AS `ip_tmp` ON DUPLICATE KEY UPDATE `tax_class_id` = VALUES(`tax_class_id`), `price` = VALUES(`price`), `final_price` = VALUES(`final_price`), `min_price` = VALUES(`min_price`), `max_price` = VALUES(`max_price`), `tier_price` = VALUES(`tier_price`) in /vendor/magento/framework/DB/Statement/Pdo/Mysql.php:109
Stack trace:
#0 /vendor/magento/framework/DB/Statement/Pdo/Mysql.php(91): Magento\Framework\DB\Statement\Pdo\Mysql->tryExecute(Object(Closure))
#1 /vendor/magento/zendframework1/library/Zend/Db/Statement.php(313): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#2 /vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(247): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#4 /vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(564): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO`ca...', Array)
#5 /vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(634): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('INSERT INTO `ca...', Array)
#6 /vendor/magento/module-catalog/Model/Indexer/Product/Price/AbstractAction.php(193): Magento\Framework\DB\Adapter\Pdo\Mysql->query('INSERT INTO `ca...')
#7 /vendor/magento/module-catalog/Model/Indexer/Product/Price/AbstractAction.php(417): Magento\Catalog\Model\Indexer\Product\Price\AbstractAction->_syncData(Array)
#8 /vendor/magento/module-catalog/Model/Indexer/Product/Price/Action/Rows.php(126): Magento\Catalog\Model\Indexer\Product\Price\AbstractAction->_reindexRows(Array)
#9 /vendor/magento/module-catalog/Model/Indexer/Product/Price.php(68): Magento\Catalog\Model\Indexer\Product\Price\Action\Rows->execute(Array)
#10 /vendor/magento/framework/Interception/Interceptor.php(58): Magento\Catalog\Model\Indexer\Product\Price->execute(Array)
#11 /vendor/magento/framework/Interception/Interceptor.php(138): Magento\Catalog\Model\Indexer\Product\Price\Interceptor->___callParent('execute', Array)

Changing catalog_product_price index to "Update on Save" seems to work as a temporary workaround.

Additional information

No response

Release note

No response

Triage and priority

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

[m2-assistant]

Hi @vseager. Thank you for your report.
To speed up processing of this issue, make sure that you provided the following information:

• Summary of the issue
• Information on your environment
• Steps to reproduce
• Expected and actual results

Make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, Add a comment to the issue:

@magento give me 2.4-develop instance - upcoming 2.4.x release

For more details, review the Magento Contributor Assistant documentation.

Add a comment to assign the issue: @magento I am working on this

To learn more about issue processing workflow, refer to the Code Contributions.

---

• Join Magento Community Engineering Slack and ask your questions in #github channel.

⚠️ According to the Magento Contribution requirements, all issues must go through the Community Contributions Triage process. Community Contributions Triage is a public meeting.

🕙 You can find the schedule on the Magento Community Calendar page.

📞 The triage of issues happens in the queue order. If you want to speed up the delivery of your contribution, join the Community Contributions Triage session to discuss the appropriate ticket.

✏️ Feel free to post questions/proposals/feedback related to the Community Contributions Triage process to the corresponding Slack Channel

[jaminion]

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 263c17f, 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.

[m2-assistant]

Hi @engcom-November. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 4. Verify that the issue is reproducible on 2.4-develop branch

  Details

  - Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 5. Add label Issue: Confirmed once verification is complete.

• 6. Make sure that automatic system confirms that report has been added to the backlog.

[engcom-November]

Hi @vseager ,

Thank you for reporting and collaboration. Verified the issue on Magento 2.4-develop instance but the issue is not reproducible with below steps performed:
Steps performed:

1. Setup 20k products in Magento 2.4-develop instance
2. Ran php bin/magento indexer:reindex
   catalog_product_price index took 06min to reindex. There is an ongoing issue confirmed here for the same. Kindly move on this issue if you are facing similar issue.
3. Also checked with Indexer set with "UPDATE BY SCHEDULE" but no errors were observed.
   Kindly recheck the issue on Magento 2.4-develop instance as it is the upcoming 2.4.x release and provide missing steps if any if you are still facing any issues.
   Thank you.

[alek-s-andr]

We have faced the same error, Magento 2.4.5-p1, in the cron_schedule table many error rows having messages:
Cron Execution ErrorSQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1, query was: INSERT INTO `catalog_product_index_price` SELECT `ip_tmp`.* FROM `catalog_product_index_price_temp` AS `ip_tmp` ON DUPLICATE KEY UPDATE `tax_class_id` = VALUES(`tax_class_id`), `price` = VALUES(`price`), `final_price` = VALUES(`final_price`), `min_price` = VALUES(`min_price`), `max_price` = VALUES(`max_price`), `tier_price` = VALUES(`tier_price`)

[jaminion]

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.

[jaminion]

Hey @vseager and @alek-s-andr, is there any chance that you have custom product types defined, possibly with third party modules? I have been able to determine that the disappearing products and logged query exceptions are limited to my custom product types. I did not declare an "indexerModel" for them, and it looks like the default indexerModel that gets used for any product type which does not have one declared is Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\DefaultPrice, however that indexer model does not inherit from Magento\Framework\Indexer\DimensionalIndexerInterface and results in using the AbstractAction::_syncData() method which became broken with the above referenced commit. @engcom-November, this would also explain why you're not reproducing the situation with generic data.

[jaminion]

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!

[alek-s-andr]

is there any chance that you have custom product types defined, possibly with third party modules?

Hi @jaminion, yes we have custom product type from third party module.

[jaminion]

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!