Magento 2.1.5: Products Missing in Categories after Reindex

Question

I am running Magento 2.1.5 and facing a really strange issue. I have over 6,500 products uploaded to the store but some items are not showing up after performing a complete reindex. Of course, some items are out of stock but many of the products that are not showing up in their respective categories are the similar as others that are showing up just fine. These products are simple (not configurable) and belong to do the same attribute group.

Upon searching for the items in search the missing products show up. Upon saving the item the item appears again only to go missing again upon reindex.

After performing reindex one by one, I noticed the issue is affecting the following tables:

catalog_category_product
catalog_product_category

I even went ahead and added a row which made the product appear again on the front-end but after reindexing the row was deleted.

Does anyone have any clue as to what this issue could be? It's very, very strange but I'm hoping there is a simple solution.

Update 02/27/2017: I am still not able figure out a solution for this bug. If any Magento Expert can gain some insight into this and fix it he or she will be rewarded.

Answer 1

Seem that we need to increase the memory limit when running re-index a large of number database:

1) Reset db: open your PHP Admin or Mysql GUI tool

update indexer_state set status = 'invalid' where status != 'valid';

2) Re-index again:

php -dmemory_limit=2048M bin/magento indexer:reindex

Answer 2

I have had the same issue, as an answer I am posting a link with a solution that worked for me. Besides, I cannot comment yet.

Reindexing Magento 2 causes products to be missing on the frontend (not the backend) (Solved) - Magento Stackexchange

Answer 3

you can run the following;

php bin/magento indexer:reset
php bin/magento indexer:reindex
php bin/magento cache:flush

Which should revive any missing categories.

Answer 4

I'm using Magento2.1.4 and have same issue but solved on github.

STEP 1: Create a new module or use an existing custom module
STEP 2: Create a new Class that extends the Full Action (see below)
STEP 3: Edit the di.xml in the etc directory to include a preference node (see below)
STEP 4: Clear Cache and Reindex

FILE: {company}{module}\Model\Indexer\Product\Category\Action\Full.php

namespace {company}\{module}\Model\Indexer\Product\Category\Action;

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

FILE: {company}{module}\etc\di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <preference 
            for="Magento\Catalog\Model\Indexer\Category\Product\Action\Full" 
            type="{company}\{module}\Model\Indexer\Product\Category\Action\Full" />
</config>

Hope this helps.