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

[caesarcxiv]

Preconditions

1. Latest Magento2 pull from github as of 1/2/17
2. PHP 7, NGINX, mariadb

Steps to reproduce

1. run indexer:reindex catalog_category_product

Expected result

1. index is rebuilt past 500 products per category resulting in products missing from site.

Actual result

1. products are not included in the catalog_category_product_index resulting in products not appearing on site.

Query in error:
INSERT INTO catalog_category_product_index (category_id, product_id, position, is_parent, store_id, visibility) SELECT cc.entity_id AS category_id, ccp.product_id, ccp.position, 1 AS is_parent, 1 AS store_id, IFNULL(cpvs.value, cpvd.value) AS visibility FROM catalog_category_entity AS cc
INNER JOIN catalog_category_product AS ccp ON ccp.category_id = cc.entity_id
INNER JOIN catalog_product_website AS cpw ON cpw.product_id = ccp.product_id
INNER JOIN catalog_product_entity AS cpe ON ccp.product_id = cpe.entity_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 = 94
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 = 96
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 WHERE (cc.path LIKE '1/2/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (cc.entity_id > '1051') ORDER BY cc.entity_id ASC
LIMIT 500 ON DUPLICATE KEY UPDATE category_id = VALUES(category_id), product_id = VALUES(product_id), position = VALUES(position), is_parent = VALUES(is_parent), store_id = VALUES(store_id), visibility = VALUES(visibility)

Query that successfully rebuilds full index (dropped limit and changed cc.entity_id > 0):
INSERT INTO catalog_category_product_index (category_id, product_id, position, is_parent, store_id, visibility) SELECT cc.entity_id AS category_id, ccp.product_id, ccp.position, 1 AS is_parent, 1 AS store_id, IFNULL(cpvs.value, cpvd.value) AS visibility FROM catalog_category_entity AS cc
INNER JOIN catalog_category_product AS ccp ON ccp.category_id = cc.entity_id
INNER JOIN catalog_product_website AS cpw ON cpw.product_id = ccp.product_id
INNER JOIN catalog_product_entity AS cpe ON ccp.product_id = cpe.entity_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 = 94
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 = 96
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 WHERE (cc.path LIKE '1/2/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (cc.entity_id > '0') ORDER BY cc.entity_id ASC
ON DUPLICATE KEY UPDATE category_id = VALUES(category_id), product_id = VALUES(product_id), position = VALUES(position), is_parent = VALUES(is_parent), store_id = VALUES(store_id), visibility = VALUES(visibility)

[pravalitera]

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.

_MagentoCatalog\Model\IndexerCategory\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

[dnadle]

I can confirm this is a defect in 2.1.3. After indexing the table catalog_category_product_index is limited to a maximum of 500 rows per category_id, except for the root category.

For example, if on my database I execute the command
select category_id, count(*) from catalog_category_product_index group by category_id order by count(*) desc limit 10; I get:

|category_id|count(*)|
|-----------|--------|
|2|9793|
|213|500|
|258|500|
|228|500|
|220|500|
|231|500|
|229|495|
|268|411|
|224|366|
|218|362|

[siment]

I can confirm this bug.

Will describe the problem as it appears when indexing.

The problem is that \Magento\Framework\DB\Query\Generator::generate uses the "FROM" part of the $select argument to determine which table it is going to construct the limit queries from. The "FROM" table is catalog_category_entity.

The $rangeField argument, which has value entity_id, then determines that the incremental selects should be done by incrementing value of entity_id on catalog_category_entity for each subsequent query.

While looping through the limited select queries, the method \Magento\Framework\DB\Query\BatchIterator::calculateBatchSize sets current increment of catalog_category_entity.entity_id in the following way:

    /**
     * Calculate batch size for select.
     *
     * @param Select $select
     * @return int
     */
    private function calculateBatchSize(Select $select)
    {
        $wrapperSelect = $this->connection->select();
        $wrapperSelect->from(
            $select,
            [
                new \Zend_Db_Expr('MAX(' . $this->rangeFieldAlias . ') as max'),
                new \Zend_Db_Expr('COUNT(*) as cnt')
            ]
        );
        $row = $this->connection->fetchRow($wrapperSelect);
        $this->minValue = $row['max'];
        return intval($row['cnt']);

Notice the $this->minValue = $row['max'];. This determines the minimal value of catalog_category_entity.entity_id for the next query. The problem is that there might be many more results than the 500 rows that the select query (\Magento\Framework\DB\Query\BatchIterator::initSelectObject) will return. The iterator is not aware of this and increments catalog_category_entity.entity_id by 1 the next time it is run. Thus missing out on all the results exceeding 500 from the previous query.

[dnadle]

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

[siment]

@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.

[dnadle]

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

[sjovanig]

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.

[siment]

Seems to be a duplicate of #7968

[xmav]

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

[shahankitb997]

Guys. To override follow below post

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

[PieterCappelle]

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

[hostep]

Also confirmed on Magento CE 2.1.4
Temporary fix from @pravalitera seems to work over here.

It looks like this is being fixed in MAGETWO-62616, can someone confirm if this is correct?

@maksek: This is high priority bug and should be released in the next bugfix release (2.1.7 I believe?).

Thanks!

[SinisterGlitch]

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

[pravalitera]

@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 ;)

[hubaig]

Hi,

Can anyone create a noob friendly steps? Step by Step? I have this problem. I understand it. But not very familiar with Magento modules.

Regards,
Hussain Baig

[bangerkuwranger]

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

[Jakhotiya]

I would like to work on it

[franciskim]

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

[franciskim]

For now, @bangerkuwranger's module seems to be working a treat 👍

[pravalitera]

It will be fixed in Magento 3.

2017-05-07 2:32 GMT+02:00 Francis Kim [e-mail]:

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
.

[psapoznik]

This is full of bugs .... buaaaa

[petterkj]

Confirmed 2.1.6. Thanks for the workaround.. The amount of workarounds needed for M2 is tipping to a dangerous edge now.

[flancer64]

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.

[dnadle]

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;
    }    
}

[flancer64]

Thanks, @dnadle

my solution allows to leave isRangingNeeded() as is (true) and use query batch iterator correctly on large catalogs.

[inelukipg]

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

[onepack]

@siment
Fixed it with your solution.
This needs to be addressed by Magento ASAP as it limits a webshop in the products it can display in a category.
A small workaround is not using Anchor categories but this means limiting customers in finding the products they want.

[dimple-vasoya]

Hello,

I have upgraded my magento version from 2.1.0 to 2.1.7
Earlier the reindex time for Product Categories was 00:03:08 and now it has increased to 01:12:00
Can anyone help me understand the reason behind this?

[tommybds]

@dimple-ambab
It's because now it reindex more than 500 products at a time and the bug is corrected ?

[flancer64]

1h 12 min vs. 3 min?! @dimple-ambab , email me to "[email protected]" if your problem is still actual.

[itg-ddanielson]

Still and issue in EE 2.1.7

[Silarn]

@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.

[hostep]

@Silarn: be aware that Magento is linking MAGETWO-63601 to this issue, I have no idea if one of both solves this problem, or if you need both...

We currently still use @pravalitera's solution for now and it seems to get us around the problem (but it's definitely not the "right" solution).

[Silarn]

@hostep Thanks. Looks like that fix was applied on top of MAGETWO-62616.

[Silarn]

I'm testing my modular fix now and will update when I have a standalone version available.

[Silarn]

You should be able to install using composer package silarn/magento2-catalog-indexer-fix.

For now I just have dev-master available, but as soon as I can confirm this standalone version works I'll push a v1.0.0 tag.

I'm using identical code in a magento 2 module, so it should work - I just haven't tested yet pulling this in via composer.

[Silarn]

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.

[andidhouse]

It is unbelievable that a function that blocks an ecommerce system is not working right and not fixed properly since month (maybe over a year).

We are on 2.1.7 - massive indexing problems resulting in products just missing from our catalog. I think the problem is that the magento team build a "monster" and now realizes that they can not handle it properly. Nearly every fix they implement causes massive problems on other parts.

[rosandrest]

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.

[AlexanderHuyghebaert]

Issue still is in 2.1.7, workaround fixes issue

[deckside]

Using 2.18, I seem to have a fix working.
Problem:
If you run
magento indexer:reindex
Lots of products disappear

Fix:
Run the following
magento indexer:reindex
magento indexer:reindex catalog_category_product
magento cache:clean
magento cache:clean full_page
Then they all come back

[johnny-longneck]

Well. That's pretty straight forward. Not.

Is there a non-manual way? This needs to be working automatically with cron.

[deckside]

Yes you can add the above to your cron.
Below is my cron file. The directory structure is based on our instance coming from Bitnami.
You will need to change the directory structure to suit your instance.
If anyone has any suggestions please let us know

*/5 * * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/bin/magento cron:run
*/5 * * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/update/cron.php
*/5 * * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/bin/magento setup:cron:run
0 0 * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/bin/magento indexer:reindex
0 0 * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/bin/magento indexer:reindex catalog_category_product
0 0 * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/bin/magento cache:clean full_page

[kervin]

The issue exists in 2.1.8 as well..

[deckside]

If you run indexer:reindex catalog_category_product after magento indexer:reindex it seems to work.

php /bin/magento indexer:reindex
php /bin/magento indexer:reindex catalog_category_product

or add to CRON

[kervin]

I have large around 60,000 products on 16 stores, and indexing is failing at time.

I've added the following in 2.1.8, and it seems to be better. I am still testing.

class Full extends \MagentoCatalog\Model\IndexerCategory\Product\Action\Full
{
protected function isRangingNeeded()
{
return false;
}
}

[mrjcgoodwin]

@kervin - Where should we apply this fix? We have a 2.1.8 site we can't index.

[flancer64]

Hi, guys.

Does this fix (https://github.com/flancer32/mage2_fix_pr9621) not work in 2.1.8?

[flancer64]

Uups, there are changes in 2.1.8 in \Magento\Framework\DB\Query\BatchIterator. I suppose - yes, the fix does not work in 2.1.8.

[pravalitera]

Please guys. Read the thread, stop posting for nothing, without adding any
news.
Everything needed to fix it is right here in front of your eyes.

Thk you.

2017-09-12 15:17 GMT+02:00 mrjcgoodwin [e-mail]:

@kervin https://github.com/kervin - Where should we apply this fix? We
have a 2.1.8 site we can't index.

—
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-328848914,
or mute the thread
https://github.com/notifications/unsubscribe-auth/APqjaFUOnFPmjmCTlpdeFN_ZtHg3l92gks5shoNkgaJpZM4LZL6M
.

[flancer64]

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.

[pravalitera]

It is better to do it like you did. But in fact, it depends a lot on your
mysql configuration: some server won't accept a so big sql request. I did
not test your fix.

I'm not saying this thread is not useful, it just that some of the posts
are too much "hey, how do i do it ?" and too much "maybe". After reading
alll the new post, I still don't know if 2.1.8 is fixed or not. We have to
be precise to be useful. It's an issue tracker, not stackoverflow ;)

And it's so incredible that this issue is still open. Seriously Magento
Team. Since 2 January ?! 501 products on a category and that's finished ?!
And you don't want to merge a fix, because it has to be "tested" ? I
understand that, but isn't it a bit funny to say to people that corrected a
awfull bug that is has to be tested ?

And there are so many bugs like this everywhere... I miss 1.x so much.

2017-09-12 16:08 GMT+02:00 Alex Gusev [e-mail]:

Yes, @pravalitera https://github.com/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.

—
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-328863479,
or mute the thread
https://github.com/notifications/unsubscribe-auth/APqjaAF5t9tKJoYz9KwDZDOu4Ey27Fwcks5shpBBgaJpZM4LZL6M
.

[andidhouse]

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

[deckside]

Further to my last comment.
Using CE2.18 I also use Igor Revenko's solution.

Added this fix https://magento.stackexchange.com/questions/88081/magento2-products-suddenly-disappeared-on-frontend

AND

Have the following in CRON

0 0 * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/bin/magento indexer:reindex
0 0 * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/bin/magento indexer:reindex catalog_category_product
0 0 * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/bin/magento cache:clean full_page

[flancer64]

I found that \Magento\Framework\DB\Query\BatchRangeIterator is used in 2.1.8 instead of bugged \Magento\Framework\DB\Query\BatchIterator in 2.1.7. I cannot reproduce failure with BatchRangeIterator in 2.1.8. So, my fix (flancer32/mage2_fix_pr9621) is not acceptable for 2.1.8.

[magento-engcom-team]

@caesarcxiv, thank you for your report.
The issue is already fixed in 2.2.0

[pravalitera]

@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" ?

[hostep]

I have to agree with @pravalitera, it was a regression bug introduced in 2.1.3, so it would make sense if it is also fixed on version 2.1.x and not only on 2.2.x

@magento-engcom-team: So is a backport to fix this in 2.1.x in the planning?

[siliconalchemy]

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.

[onepack]

@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.

[onepack]

@magento-engcom-team
And when will 2.2.0 be released?

[okorshenko]

in a few days. next week

[itg-ddanielson]

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"

[siliconalchemy]

@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.

[tmotyl]

@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?

[pravalitera]

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 [e-mail]:

@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
.

[Silarn]

FYI the upgrade to 2.2 is not simple if you have any third party modules / custom development.

[bangerkuwranger]

@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.

[Silarn]

Well, I can understand a major version having some compatibility issues. Most of the issues I've had have been due to the change in serialization. (and typically good third party devs who updated to use the new method but didn't covert the existing data...)

There really are some significant improvements. Indexing and compile times are vastly better. But there are a lot of small fixes that have absolutely no reason not to exist in 2.1. And due to its broken nature and the fact that 2.2 is not necessarily a simple upgrade, the indexing improvements absolutely should be backported to 2.1 as well.

My team is making the upgrade a priority for this reason, but I seriously doubt all Magento users are going to have the expertise to upgrade quickly.

[kervin]

Ok. After the 10th critial bug fix... I am staying on 2.1.8 for now.

I am trying to port the bugfixes manually from 2.2.0 to 2.1.8

[karlsminton]

I attempted the isRangingNeeded true fix and it didn't help. This was for 2.1.8

[btwq]

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.

[lano-vargas]

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

[ccasciotti]

@magento-engcom-team checking code of 2.2.2 version, it seems that \Magento\Catalog\Model\Indexer\Category\Product\Action\Full doesn't include an override for isRangingNeeded returning false, are you sure that the fix is still included in 2.2 branch?

[duffner]

@magento-engcom-team, I just reviewed 2.2.3 and it's not there either.

Is this complete or no?

[siliconalchemy]

@ccasciotti I think the isRanging() method is in \MagentoCatalog\Model\IndexerCategory\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?

[duffner]

@siliconalchemy, I don't believe this to be fixed as I'm experiencing this issue in Magento 2.2.3 Commerce Edition

[versdivers]

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

[Denda69]

@siment thank you, it worked for me with ~250000 product

[LiamKarlMitchell]

Possibly having this on Magento 2.2.0
Backend appears fine, but some products don't show on front end including a whole brand category...

Planning to use work around: https://github.com/bangerkuwranger/Magento-2.1.3-500-Product-Category-Limit-Bug-Workaround for now.

Edit: Can confirm work around fixed problem.

[schizek]

What is the status of this being corrected in the Magento core? @magento-engcom-team

[shansari25]

Im facing this issue in Magento 2.2.4 - products disappeared after reindex. Pls suggest a solution.

Magento is seriously a very bad tool, i made a big mistake, i have gone through several issues. And now this one and no patch and no solution anywhere.

Pls suggest a solution to solve this issue. The solution given above doesnt fit for Magento 2.2.4, pls suggest a solution for Magento 2.2.4 version. The folder structure doesn't match with 2.2.4 version.

@magento-engcom-team

@schizek
@LiamKarlMitchell
@versdivers
@Silarn @caesarcxiv @pravalitera @dnadle @siment @sjovanig

I request you all - pls suggest a solution - we are stuck and not able to move this through.

[shansari25]

Im facing this issue in Magento 2.2.4 - products disappeared after reindex. Pls suggest a solution.

Pls suggest a solution. The solution given above doesnt fit for Magento 2.2.4, pls suggest a solution for Magento 2.2.4 version. The folder structure shown above doesn't match with 2.2.4 version so not able to understand where I have to make changes. And I guess after making the code changes we need to compile as well - Isn't it.

I request you all - pls suggest a solution - we are stuck and not able to move this through.

@magento-engcom-team

@schizek
@LiamKarlMitchell
@versdivers
@Silarn @caesarcxiv @pravalitera @dnadle @siment @sjovanig

@siliconalchemy

[CompactCodeEU]

@shansari25 They have changed indexing in Magento 2.2.5. Not sure if that fixes it. We did not have any problems anymore after upgrading our clients to Magento 2.2.4. Since the functions changed within that directory i guess they changed this bug.
I used to set isRangingNeeded to false but now it seems that is default.
You can check that in vendor/magento/module-catalog/Model/Indexer/Product/Category/Action/Rows.php

[shansari25]

@CompactCodeEU
thank you for your response and I could see that isRangingNeeded is returning false.

But my issue is not resolved, how do I resolve it, the products are totally blank in the frontend and we are not able to find out what to do, looks like there is no other mention of this issue anywhere other than making changes in isRangingNeeded.

Pls suggest a solution or a way to solve this issue.

[CompactCodeEU]

Add me on skype. Rob.conings. This is not a conversation for here. It seems more like a stackexchange conversation or personal conversation

[kriswen]

@shansari25 @CompactCodeEU
Do you guys have a solution? I'm on 2.2.6 and the products disappear from category occasionally, and sometimes does show up on search results, sometimes does not. I'm not exactly else to check anymore.

• isRangingNeeded is set to false in Rows.php
• cron job is running
  -all reindex is set by schedule

However, in the indexer_state table. the updated time for the following 3 seems not updating when i run reindex command.

catalog_product_category 2019-05-30 16:05:21
catalogrule_product 2019-05-30 16:05:22
amasty_elastic_relevance_rule_product (3rd party extension) 2019-05-30 23:09:59

===================================================

catalogsearch_fulltext 2019-05-31 08:20:36
catalog_category_product 2019-05-31 08:20:36
amasty_elastic_popup_data 2019-05-31 08:20:36
amasty_elastic_relevance_rule_rule 2019-05-31 08:20:36
.....and everything else is updated at 2019-05-31 08:20:36

you can see the top 3 is updated at 5/30, but i did ran re-index commands on 5/31

please let me you if you have any clue.

[tuyennn]

The fix of this was not port to magento 2.1.x.
For anyone who want to take a look on how they fixed it on magento 2.2 2.3x
e4b02a3c03927ee8d2c6e051d261ebdde25a94c9