UPSTREAM FIX
magento2-38050: Related, upsell and cross-sell queries prevent the indexer from renaming tables
Community fix magento2-38050 merged into magento/magento2 on 2024-05-16, released in 2.4.8; applies cleanly to 27 releases from 2.4.6 to 2.4.7-p10.
Related, upsell and cross-sell queries prevent the indexer from renaming tables edited
- Pull request title
- Randomly getting flooded with queries from related / upsell / crosssell blocks and price indexing
- Pull request
- magento/magento2#38050
- Issues
- #36667 human
- Author
- @ioweb-gr
- Merged
- 2024-05-16
- Fixed in
- 2.4.8
- Reported on
- 2.4.1
- Categories
- Performance
- Components
- magento/module-catalog-inventory
Labels
- Area
- Framework, Performance
- Component
- —
- Priority
- P2
- Severity
- —
- Reported on (labels)
- 2.4.1
Issue
Title and steps come from the upstream issue and pull request.
Description
The website is down with a lot of queries in queue. Over 2k siilar to this
Steps to reproduce
I don't have the exact steps, other than the fact that reindexing the price rules takes too long. However in my case I see hundreds of queries like this
Which take too long to finish and drop our website
Which take too long to finish and drop our website
Expected result
The site is still working and queries are much faster and won't bring the site down.
Actual result
The website is down with a lot of queries in queue. Over 2k siilar to this
SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, `stock_status_index`.`is_salable`, `links`.`link_id`, `links`.`product_id` AS `_linked_to_product_id`, `link_attribute_position_int`.`value` AS `position` FROM `catalog_product_entity` AS `e` INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '2' INNER JOIN `catalog_category_product_index_store5` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=5 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=2 INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = e.entity_id INNER JOIN `inventory_stock_5` AS `stock_status_index` ON product.sku = stock_status_index.sku INNER JOIN `catalog_product_link` AS `links` ON links.linked_product_id = e.entity_id AND links.link_type_id = 4 LEFT JOIN `catalog_product_link_attribute_int` AS `link_attribute_position_int` ON link_attribute_position_int.link_id = links.link_id AND link_attribute_position_int.product_link_attribute_id = '3' INNER JOIN `catalog_product_entity` AS `product_entity_table` ON links.product_id = product_entity_table.entity_id WHERE (inventory_in_stock.is_salable = 1) AND (stock_status_index.is_salable = 1) AND (links.product_id in ('80468')) AND (`e`.`entity_id` != '80468') ORDER BY `position` ASCTaken from the upstream issue.
Code match per tag
Each tag was checked with git apply --check against that tag's files. A clean match means the change applies; it is not a test result. Tags that already contain the fix are marked.
| Line | Code match per tag | Tests |
|---|---|---|
| 2.4.6 | 2.4.6 clean 2.4.6-p1 clean 2.4.6-p2 clean 2.4.6-p3 clean 2.4.6-p4 clean 2.4.6-p5 clean 2.4.6-p6 clean 2.4.6-p7 clean 2.4.6-p8 clean 2.4.6-p9 clean 2.4.6-p10 clean 2.4.6-p11 clean 2.4.6-p12 clean 2.4.6-p13 clean 2.4.6-p14 clean 2.4.6-p15 clean | 2.4.6: no test data 2.4.6-p1: no test data 2.4.6-p2: no test data 2.4.6-p3: no test data 2.4.6-p4: no test data 2.4.6-p5: no test data 2.4.6-p6: no test data 2.4.6-p7: no test data 2.4.6-p8: no test data 2.4.6-p9: no test data 2.4.6-p10: no test data 2.4.6-p11: no test data 2.4.6-p12: no test data 2.4.6-p13: no test data 2.4.6-p14: no test data 2.4.6-p15: no test data |
| 2.4.7 | 2.4.7 clean 2.4.7-p1 clean 2.4.7-p2 clean 2.4.7-p3 clean 2.4.7-p4 clean 2.4.7-p5 clean 2.4.7-p6 clean 2.4.7-p7 clean 2.4.7-p8 clean 2.4.7-p9 clean 2.4.7-p10 clean | 2.4.7: no test data 2.4.7-p1: no test data 2.4.7-p2: no test data 2.4.7-p3: no test data 2.4.7-p4: no test data 2.4.7-p5: no test data 2.4.7-p6: no test data 2.4.7-p7: no test data 2.4.7-p8: no test data 2.4.7-p9: no test data 2.4.7-p10: fails before, passes afterunit: fails before, passes after |
| 2.4.8 | 2.4.8 conflictcontains the fix 2.4.8-p1 conflictcontains the fix 2.4.8-p2 conflictcontains the fix 2.4.8-p3 conflictcontains the fix 2.4.8-p4 conflictcontains the fix 2.4.8-p5 conflictcontains the fix | 2.4.8: no test data 2.4.8-p1: no test data 2.4.8-p2: no test data 2.4.8-p3: no test data 2.4.8-p4: no test data 2.4.8-p5: no test data |
| 2.4.9 | 2.4.9 conflictcontains the fix | 2.4.9: no test data |
Triage
Model @cf/cloudflare/clef. Probability this is a bug fix: 86.6%. Probability it is security relevant: 0.5%.
Show the model's answers and probabilities
| Question | Answer | Probabilities | Confidence |
|---|---|---|---|
| Change kind | bugfix | bugfix 89.8%, refactor 7.7%, feature 0.9%, tests_only 0.8%, dependency 0.5%, docs_only 0.4% | 77.4% |
| Area | catalog | catalog 82.5%, framework 8.6%, other 2.5% | 64.4% |
| Reported version | 2.4.1 | 2.4.1 68.0%, 2.4.1-p1 2.3%, 2.4.3-p2 0.9% | 45.8% |
| Scope | 0.64 of 2 | 1 50.7%, 0 42.8%, 2 6.5% | 16.6% |
| Risk | 0.82 of 2 | 0 49.1%, 2 30.7%, 1 20.2% | 6.5% |
| Worth backporting | 1.48 of 2 | 2 58.4%, 1 31.5%, 0 10.1% | 17.6% |
Download
For cweagans/composer-patches, choose a version below and download the bundle. Copy its magento2-38050/ folder into patches/composer/, merge composer.patches.json into composer.json, then run composer install. Test files are always removed; paths are relative to each package root, using the default -p1 level.
Bundle README (what the ZIP ships)
# magento2-38050 Community fix merged upstream into magento/magento2, adapted by magento.watch. This is not a patch published by Adobe. Pull request: https://github.com/magento/magento2/pull/38050 Issue: https://github.com/magento/magento2/issues/36667 Author: @ioweb-gr Source commit: ad5548ae382f8a01cac5dec9d1e5fbc3407285c0 Modifications: test files and documentation removed, paths rewritten relative to each Composer package. Licence: OSL-3.0 / AFL-3.0, as the original Magento Open Source code. Maintainer: Łukasz Bajsarowicz (@lbajsarowicz)
Licence: Magento Open Source code under OSL-3.0 and AFL-3.0. The bundle carries the original author, source commit and the list of modifications.
