2

problem description

i have a problem on a magento 2.4.5-p1 project. the problem: sometimes, the indexer has a problem and is hanging. i can run "php bin/mangeto indexer:reindex". then, everything is fine and working. but i want to solve the problem without reindex the index. if the index hangs, some products are missing in frontend and sometimes a'm not able to run the reindex command. last time, the problem is triggered on weekend, no one was not working in the magento admin. the triggering action is unknown. if i knew the triggering action, finding the problem would probably be much easier. after the problem is there, the indexer is not working anymore and some products are hidden in the frontend until i reindex the blocking index.

in the cron_Schedule db table is an error:

  • job_code: indexer_update_all_views
  • status: error
  • message: 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``) You can see: there are six fields/columns. But the table have 9 fields. entity_id, customer_group_id, website_id are missing.

how the query is generated

i checked the code of these files and think, that they are responsible for the wrong sql-query:

vendor/magento/module-catalog/Model/Indexer/Product/Price.php:68 // $this->_productPriceIndexerRows->execute()
vendor/magento/module-catalog/Model/Indexer/Product/Price/Action/Rows.php:116 // $this->_reindexRows()
vendor/magento/module-catalog/Model/Indexer/Product/Price/AbstractAction.php:407 // $this->_insertFromTable()
vendor/magento/module-catalog/Model/Indexer/Product/Price/AbstractAction.php:344 // $this->getConnection()->describeTable()
vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:1713 describeTable
vendor/magento/module-catalog/Model/Indexer/Product/Price/AbstractAction.php:349 // $this->getConnection()->insertFromSelect()
vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:3560 insertFromSelect

On checking these files, i was sure, that there only way how magento get the colums/fields is in the Mysql.php file on line 1713. And then, it store the value in the cache. I believe, that the SQL Command "DESCRIBE" return the right value every time. My first idea was, that something with the cache is wrong:

checked, that the frontend and the cli use the same cache

First, i checked, that the cli and the store use the same way to cache things. because sometimes cli/bash use other php modules than the frontend. In our case, cli was not using OPCache. But now, cli and the frontend are equal and the indexer problem is still there. (https://ayg.ro/magento-cron-twolevel-cache-issue-pdoexception-sqlstate-42s22-and-sqlstate-42000/)

checked redis cache

Then, i checked the content of the redis cache while the the index hangs (we use redis as caching solution). but in the redis cache, everything was fine: {"entity_id":{"SCHEMA_NAME":null,"TABLE_NAME":"catalog_product_index_price","COLUMN_NAME":"entity_id","COLUMN_POSITION":1,"DATA_TYPE":"int","DEFAULT":null,"NULLABLE":false,"LENGTH":null,"SCALE":null,"PRECISION":null,"UNSIGNED":true,"PRIMARY":true,"PRIMARY_POSITION":1,"IDENTITY":false},"customer_group_id":{"SCHEMA_NAME":null,"TABLE_NAME":"catalog_product_index_price","COLUMN_NAME":"customer_group_id","COLUMN_POSITION":2,"DATA_TYPE":"int","DEFAULT":null,"NULLABLE":false,"LENGTH":null,"SCALE":null,"PRECISION":null,"UNSIGNED":true,"PRIMARY":true,"PRIMARY_POSITION":2,"IDENTITY":false},"website_id":{"SCHEMA_NAME":null,"TABLE_NAME":"catalog_product_index_price","COLUMN_NAME":"website_id","COLUMN_POSITION":3,"DATA_TYPE":"smallint","DEFAULT":null,"NULLABLE":false,"LENGTH":null,"SCALE":null,"PRECISION":null,"UNSIGNED":true,"PRIMARY":true,"PRIMARY_POSITION":3,"IDENTITY":false},"tax_class_id":{"SCHEMA_NAME":null,"TABLE_NAME":"catalog_product_index_price","COLUMN_NAME":"tax_class_id","COLUMN_POSITION":4,"DATA_TYPE":"smallint","DEFAULT":"0","NULLABLE":true,"LENGTH":null,"SCALE":null,"PRECISION":null,"UNSIGNED":true,"PRIMARY":false,"PRIMARY_POSITION":null,"IDENTITY":false},"price":{"SCHEMA_NAME":null,"TABLE_NAME":"catalog_product_index_price","COLUMN_NAME":"price","COLUMN_POSITION":5,"DATA_TYPE":"decimal","DEFAULT":null,"NULLABLE":true,"LENGTH":null,"SCALE":"6","PRECISION":"20","UNSIGNED":null,"PRIMARY":false,"PRIMARY_POSITION":null,"IDENTITY":false},"final_price":{"SCHEMA_NAME":null,"TABLE_NAME":"catalog_product_index_price","COLUMN_NAME":"final_price","COLUMN_POSITION":6,"DATA_TYPE":"decimal","DEFAULT":null,"NULLABLE":true,"LENGTH":null,"SCALE":"6","PRECISION":"20","UNSIGNED":null,"PRIMARY":false,"PRIMARY_POSITION":null,"IDENTITY":false},"min_price":{"SCHEMA_NAME":null,"TABLE_NAME":"catalog_product_index_price","COLUMN_NAME":"min_price","COLUMN_POSITION":7,"DATA_TYPE":"decimal","DEFAULT":null,"NULLABLE":true,"LENGTH":null,"SCALE":"6","PRECISION":"20","UNSIGNED":null,"PRIMARY":false,"PRIMARY_POSITION":null,"IDENTITY":false},"max_price":{"SCHEMA_NAME":null,"TABLE_NAME":"catalog_product_index_price","COLUMN_NAME":"max_price","COLUMN_POSITION":8,"DATA_TYPE":"decimal","DEFAULT":null,"NULLABLE":true,"LENGTH":null,"SCALE":"6","PRECISION":"20","UNSIGNED":null,"PRIMARY":false,"PRIMARY_POSITION":null,"IDENTITY":false},"tier_price":{"SCHEMA_NAME":null,"TABLE_NAME":"catalog_product_index_price","COLUMN_NAME":"tier_price","COLUMN_POSITION":9,"DATA_TYPE":"decimal","DEFAULT":null,"NULLABLE":true,"LENGTH":null,"SCALE":"6","PRECISION":"20","UNSIGNED":null,"PRIMARY":false,"PRIMARY_POSITION":null,"IDENTITY":false}}

did anyone have an idea, what can trigger my index problem?

some information about our setup:

  • LiteSpeed Web Server
  • DB: MySQL 8
  • PHP: 7.4
  • Elasticsearch 7
  • Redis as Session Cache
  • Second Redis as Frontend Cache
  • magento 2.4.5-p1

enter image description here enter image description here

1 Answer 1

0

I found the problem. It was a third party gift card module. The paid gift card creates a new product type and i can create gift card products.

I checked only the code if the indexer is a DimensionIndexer. But in the gift card case, the indexer was an old indexer. I added a condition to continue the loop on line 413, if the indexer was this gift card indexer. Then, everything was working fine. I was able to reproduce the problem. On changing the Price in the admin area, the indexer was hanging. After my hotfix, the indexer was working as expected. the problem is still there at the moment. but now i know the problem and i will contact the developers of the module. they have to fix this problem.

if someone have a related problem with the indexer on a live system, i can recommend to make a whole copy of the system. Run php bin/magento indexer:reindex on the live system to hotfix the problem there. Then, start to disable modules one by one on the copied system. If after the command php bin/magento cron:run the output of php bin/magento indexer:status is better (less in backlog), maybe the latest disabled module was the problem. Or use xdebug in cli to find the problem without disabling modules. I used xdebug in cli on a local copy of the broken system.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.