magento2: Running indexer:reindex catalog_category_product fails due to limit 500

It will be fixed in Magento 3.

2017-05-07 2:32 GMT+02:00 Francis Kim notifications@github.com:

For now, @bangerkuwranger https://github.com/bangerkuwranger’s module seems to be working a treat 👍

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/magento/magento2/issues/8018#issuecomment-299674401, or mute the thread https://github.com/notifications/unsubscribe-auth/APqjaNFVqWxl6YFZfyCt5a2xt3hZtY2bks5r3RELgaJpZM4LZL6M .

I concur with @hostep

Confirmed problem in CE 2.1.5. None of the product counts are accurate and missing products in catalogus while they appear correct in the backend.

We are losing an client because of this and we are already looking for alternative eCommerce products

@dnadle I did the same, but I would like for the bug to be fixed. Without query limiting this will quickly become a problem on large catalogs. Here is my patch for anyone interested:

--- Model/Indexer/Category/Product/AbstractAction.orig.php	2017-01-17 23:51:36.000000000 +0100
+++ Model/Indexer/Category/Product/AbstractAction.php	2017-01-18 00:17:50.000000000 +0100
@@ -299,7 +299,11 @@
      */
     protected function isRangingNeeded()
     {
-        return true;
+        /*
+         * PATCH for select query limiting bug
+         * See https://github.com/magento/magento2/issues/8018
+         */
+        return false;
     }
 
     /**

EDIT: Actually, I patched and did not override. isRangingNeeded() is not present in Magento\Catalog\Model\Indexer\Category\Product\Action\Full and there are no obvious entry points for using plugins anywhere near the problem code.

I confirm that bug on 2.1.3

In brief: the splitted SQLs for reindex are very wrong, but the original request is good.

In deep: My categories are not deeper than 4. All have “is_anchor” = 1

Root Category (is anchor) – Category 1 (is anchor) ---- Sub-Category 1 (is anchor) ------- Sub-Sub-Category 1 (is anchor)

I have about 60000 products in “Category 1” , with the anchor function.

On my project, i coded a module that can do research by categories. And some of the skus were not found after that. And i found that is because “catalog_category_product” is incomplete. A lot of product are not in.

Magento\Catalog\Model\Indexer\Category\Product\Action\Full

 public function execute()
    {
        $this->clearTmpData();

        $this->reindex();

        $this->publishData();
        $this->removeUnnecessaryData();

        return $this;
    }

$this->reindex() is in Magento\Catalog\Model\Indexer\Category\Product

   protected function reindex()
    {
        foreach ($this->storeManager->getStores() as $store) {
            if ($this->getPathFromCategoryId($store->getRootCategoryId())) {
                $this->reindexRootCategory($store);
                $this->reindexAnchorCategories($store);
                $this->reindexNonAnchorCategories($store);
            }
        }
    }

I dig through reindexAnchorCategories :

 protected function reindexAnchorCategories(\Magento\Store\Model\Store $store)
    {
        $selects = $this->prepareSelectsByRange($this->getAnchorCategoriesSelect($store), 'entity_id');

        foreach ($selects as $select) {
            $this->connection->query(
                $this->connection->insertFromSelect(
                    $select,
                    $this->getMainTmpTable(),
                    ['category_id', 'product_id', 'position', 'is_parent', 'store_id', 'visibility'],
                    \Magento\Framework\DB\Adapter\AdapterInterface::INSERT_ON_DUPLICATE
                )
            );
        }
    }

Here is the Original SQL, BEFORE, splitted by range:

SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, ccp.position + 10000 AS `position`, 0 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
 INNER JOIN `test_category` AS `cc2` ON cc2.parent_id = cc.entity_id AND cc.entity_id NOT IN (1)
 INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child_id
 INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
 INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
 INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = cpe.entity_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 97
 LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = cpe.entity_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
 INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = cpe. entity_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 99
 LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.entity_id = cpe.entity_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1
 INNER JOIN `catalog_category_entity_int` AS `ccad` ON ccad.entity_id = cc.entity_id AND ccad.store_id = 0 AND ccad.attribute_id = 54
 LEFT JOIN `catalog_category_entity_int` AS `ccas` ON ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad.attribute_id AND ccas.store_id = 1 WHERE (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (IFNULL(ccas.value, ccad.value) = 1)
 
 

It gives me 116048 entry.

It is splitted in 13 requests. The last is:

SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, ccp.position + 10000 AS `position`, 0 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
 INNER JOIN `temp_catalog_category_tree_index_61ca549e` AS `cc2` ON cc2.parent_id = cc.entity_id AND cc.entity_id NOT IN (1)
 INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child_id
 INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
 INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
 INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = cpe.entity_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 97
 LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = cpe.entity_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
 INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = cpe. entity_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 99
 LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.entity_id = cpe.entity_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1
 INNER JOIN `catalog_category_entity_int` AS `ccad` ON ccad.entity_id = cc.entity_id AND ccad.store_id = 0 AND ccad.attribute_id = 54
 LEFT JOIN `catalog_category_entity_int` AS `ccas` ON ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad.attribute_id AND ccas.store_id = 1 WHERE (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (IFNULL(ccas.value, ccad.value) = 1) AND (`cc`.`entity_id` > '1259') ORDER BY `cc`.`entity_id` ASC LIMIT 500

The 13 only have 6500 entries on total.

The splitting is incorrect. So just for testing purpose, i changed the function in AbstractAction

protected function isRangingNeeded()
{
//    return true;
	return false;
}

It corrected the reindex (and was way much faster).

So i putted it in Full.php

 protected function isRangingNeeded()
    {
        return false;
    }

But i know it’s not the proper correction. The split is there to handle way much more products and categories than i have.

I do not have the time right know to go deeper in the debug, maybe in few weeks

@magento-engcom-team @okorshenko Can you please confirm that not fixing the issue in a stable release 2.1.x is the official statement from Magento core team. Does it mean that once 2.2 version is released, version 2.0 and 2.1 will not be supported any more?

I can confirm this bug in Magento CE 2.1.3.

Changing isRangingNeeded() to return value to false in Magento/Catalog/Model/Indexer/Category/Product/AbstractAction.php fixes it.

This is an issue in 2.2.1, One of my clients has over 5K products and products not being on the website means they lose a lot of money. Im glad i ran into this thread but we wont update the shop until 2.3 because of the mayor changes it is going to have its not worth putting in the time to check the 2.2.3 compability. Bug patches are used in every kind of software. I have over 10 ‘fix’ modules in place to keep their website running. Hoping this will be addressed soon @magento-engcom-team

Everyday I discover something new using Magento this is another one I came across on 2.1.7. Is there an ending to bugs !

@itg-ddanielson IMHO the problem is not with the software. The software is very (perhaps overly) complex, but it is a large enterprise piece of software so is to be expected. It’s mostly well structured and coded, and for the most part does an excellent job. However, when an issue is reported, it should stay open until it’s committed or released in a useable format to fix what it’s reported . Closing dozens/hundreds of issues with ‘fixed’, when it’s only fixed in a development branch that people may not see for months to years, is misleading and smacks of an academic exercise in trying to keep issue counts down, or trying to push people towards a paid-for or development alternative. Having a policy where critical fixes are only committed to an active branch when enough people go through the pain of hitting the bug and complain loudly enough, and even then not handled through the original github workflow, is just plain stupid. Stupid, stupid, stupid. I hope this improves post 2.2.

I really, really hope that Magento 2.2 changes this workflow system to something that actually fixes bugs in patch releases. If anyone involved in the engineering of this product think this is a reasonable and sane workflow currently, you’re smacked out of your head. I hit problem after problem in 2.1.x, including severe regressions in 2.1.8, and almost every single issue has been fixed for months and months in dev branch, and any relevant issues closed. Absolutely insane.

@magento-engcom-team excuse me, but you are saying, after 9 monthes, to everybody on 2.0 and 2.1 “it’s your problem”…

I know “we will just have to update”, but… really ? So why the magento team is still maintaining 2.0.x with security updates ? Because some people can’t update 2.0.x to 2.1.x ? So you are saying to us “migration to 2.2.x will be easy” ?

Yup, after switching to M2 and wading through bug after bug, this one was the last straw and I am now moving over to a new platform.

Issue exists on 2.1.6 and the documented workarounds for previous versions do not seem to work for 2.1.6

The fixes from MAGETWO-63601 and MAGETWO-62616 appear to be present in the 2.1.8 release.

(Despite no mention in the release notes that I could see.)

So these workarounds may no longer be necessary.

For the lazy (or backwards lazy, like myself…) Composer-installable workaround module, ready for install in CE or EE v2.1.3 and later… Sorry for the long-ass name

Completely agree with all of these comments. As an enterprise user I would expect a much greater level of support. We have implemented a workaround for this issue. We ran into another bug: run your catalog product indexer while a page is uncached. visit the page while the indexer is running and if you hit it at the right time… boom! … the page is cached with “sorry no products found” . We are working on a workaround on this too (to not cache pages with no results) but magento states this is intended behavior… what??? Issue is that while the index is running there catalog returns results. They are working on a “stable index” but gave no eta due to the major refactor. Don’t get me wrong I see the benefits in mage 2 but core issues like this that have no eta to a stable fix have me questioning why it was ever released as “stable”

Yes, @pravalitera, setting “false” into “isRangingNeeded()” will fix the error but it will not split all 60K products into batches in the indexing process. Does this affect to performance or does not - I don’t know. I have no so much products in my catalog (about ~3K only). My solution should add batches to the indexing (as Magento team planned originally). Does this better than just switch ranging off? I don’t know. It’s just a different solution.

I have published new version (0.2.0) of “range supported” fix for Magento 2.1.8.

Thanks for all.

Thank you @flancer64! Your module solved a big issue in our shop.

We are using Magento 2.1.6

https://github.com/flancer32/mage2_fix_pr9621

If you’re having trouble working around this bug, just add one line to your module’s di.xml:

   <preference for="Magento\Catalog\Model\Indexer\Category\Product\Action\Full" type="<My>\<Module>\Model\Indexer\Category\Product\Action\Full" />

Then add the override class to your module:

<?php
namespace <My>\<Module>\Model\Indexer\Category\Product\Action;

/*
 * Need to override this because ranging is limiting the original indexer to 500 rows per category
 *
 */

class Full extends \Magento\Catalog\Model\Indexer\Category\Product\Action\Full
{
    protected function isRangingNeeded()
    {
        return false;
    }    
}

I have created a PR with fix but unit tests are failed for the fix:

There were 3 failures:
1) Magento\Framework\Test\Unit\DB\Query\BatchIteratorTest::testCurrent
Magento\Framework\DB\Select::where('correlationName.rangeField > ?', 0, null) was not expected to be called more than once.
/home/travis/build/magento/magento2/lib/internal/Magento/Framework/DB/Query/BatchIterator.php:207
/home/travis/build/magento/magento2/lib/internal/Magento/Framework/DB/Query/BatchIterator.php:170
/home/travis/build/magento/magento2/lib/internal/Magento/Framework/DB/Query/BatchIterator.php:97
/home/travis/build/magento/magento2/lib/internal/Magento/Framework/Test/Unit/DB/Query/BatchIteratorTest.php:96
2) Magento\Framework\Test\Unit\DB\Query\BatchIteratorTest::testIterations
Failed asserting that actual size 1 matches expected size 3.
/home/travis/build/magento/magento2/lib/internal/Magento/Framework/Test/Unit/DB/Query/BatchIteratorTest.php:161
3) Magento\Framework\Test\Unit\DB\Query\BatchIteratorTest::testNext
Expectation failed for method name is equal to <string:where> when invoked at sequence index 3
Parameter 1 for invocation Magento\Framework\DB\Select::where('correlationName.rangeField > ?', 0, null) does not match expected value.
Failed asserting that 0 matches expected 25.
/home/travis/build/magento/magento2/lib/internal/Magento/Framework/DB/Query/BatchIterator.php:207
/home/travis/build/magento/magento2/lib/internal/Magento/Framework/DB/Query/BatchIterator.php:170
/home/travis/build/magento/magento2/lib/internal/Magento/Framework/DB/Query/BatchIterator.php:97
/home/travis/build/magento/magento2/lib/internal/Magento/Framework/DB/Query/BatchIterator.php:109
/home/travis/build/magento/magento2/lib/internal/Magento/Framework/Test/Unit/DB/Query/BatchIteratorTest.php:193

I have created a standalone module with the same fix (flancer32/mage2_fix_pr9621). It is possible to check bundled re-indexing without falsing isRangingNeeded(). Please, let me know about any errors found.

Guys. To override follow below post

http://magento.stackexchange.com/questions/157797/magento-2-how-to-override-abstract-class-for-product-category-indexing-issue

@siment I would love for the bug to be fixed but I’ve learned not to hold my breath…

As @pravalitera suggested I subclassed Magento\Catalog\Model\Indexer\Category\Product\Action\Full and overrode isRangingNeeded(), returning false. This fixes the index.

@ccasciotti I think the isRanging() method is in \Magento\Catalog\Model\Indexer\Category\Product\AbstractAction. It may be that the underlying issue was fixed elsewhere. But how are we supposed to know what the fix is, because the commit isn’t referenced in this issue?

On EE 2.1.7 and updating isRangingNeeded() to false fixed the problem for us. +1 this is a major issue that needs to be resolved in 2.1.x.

@Silarn …apparently maintaining a stable ecommerce ecosystem is not Magento’s concern. Even patch updates have caused problems with third party modules and themes, regardless of whether or not they follow code conventions. Most long time Mage devs are facing whiplash because of Magento’s 180 in development process for 2.x. Yeah, it’s great that the software is evolving and modernizing quickly, but lack of backwards fixes and the issues introduced with each minor update have really hamstrung any argument for adoption, and put us in the awkward position of building workarounds for every issue that affects larger shops. Given these difficulties and Magento’s ‘just upgrade’ stance, I’m sure I’m not alone in wondering whether it’s economically feasible to keep developing for the platform at all. It’s not a great sign when every client is already shopping for something else or regressing to 1.x.

It means they don’t care about developpers that, for some on them, support Magento for 8 years, and made the brand what it is today. Even the “communty manager” on Twitter does not answer question about 2.0 and 2.1

The release note of 2.2 is incredible. Really, 2.0 and 2.1 should never have never been sold like a “finished product”.

@magento-engcom-team https://github.com/magento-engcom-team Guys, really. You should not spit on people that made what you became… But anyway, i really hope 2.2 gonna succeed, because the trust in your product is lost in many. Many. Many companies.

2017-09-25 9:18 GMT+02:00 Tymoteusz Motylewski notifications@github.com:

@magento-engcom-team https://github.com/magento-engcom-team @okorshenko https://github.com/okorshenko Can you please confirm that not fixing the issue in a stable release 2.1.x is the official statement from Magento core team. Does it mean that once 2.2 version is released, version 2.0 and 2.1 will not be supported any more?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/magento/magento2/issues/8018#issuecomment-331796958, or mute the thread https://github.com/notifications/unsubscribe-auth/APqjaDYNVwikbvQ0LgsDrdKkatktk-Sfks5sl1PjgaJpZM4LZL6M .

in a few days. next week

@magento-engcom-team

The issue is already fixed in 2.2.0

Please do me a favor and edit your message… Using the word “already” is so out of place here.

I have upset clients due to this massive bug that has been around for (9+) months! This bug has been around for too long so that, without a fix or a workaround, a serious webshop would have gone bankrupt. Missing big parts of your collection online, price updates and promotions not working.

Clients don’t see Magento as the source of issues but they see us as the source of all evil. One client has lost trust in Magento as a webshop solution due to these bugs in Magento2 and I don’t blame them.

@magento-engcom-team acting now or letting this be for another 6-12 month?

@hostep was correct - the commits marked MAGETWO-62616 are indeed a fix for this issue.

As with many as-yet-unreleased fixes, it does appear to be present in version 2.2 - though that doesn’t help us running current versions.

I retroactively applied this fix locally and my category results are now accurate. To avoid relying on core modifications, I’ll work toward releasing a module version of this patch tomorrow. It involves several changes and additions to the core Magento Framework.

@SinisterGlitch Be glad that everything is correct in your backend: i have so many product that the javascript is always hanging, and i can’t open the product listing anymore. If only it was the only problem we were facing… But yeah, let’s sell Magento 2 to every customers and dig our own grave -_-

A good alternative : Magento 1.9 . Faster, easier to customize, a lot of help on internet 😉

I can confirm this bug is also in Magento CE 2.1.4.

@caesarcxiv Thanks for reporting this issue. We’ve created internal ticket MAGETWO-63601 to address this issue.