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
