Running indexer:reindex catalog_category_product fails due to limit 500

[magnetic5355]

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.

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

[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 Magento\Catalog\Model\Indexer\Category\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...

[sergiojovanig]

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