Magento 2.1 re-indexing issue

Question

I am using Magento 2.1

I have an issue that after i re-index products get hidden. I do have a work around but this is not good. Let me explain how I reproduce error then what i do to fix.

1. Upload via emagic 7000 products
2. I notice as the products are been loaded that the front end is looking good
3. After upload products automatically indexed (or i run index command)
4. Products are hidden from front end ( i.e 1 page should have 1,500 products but now only shows 500 another page should have 6,000 products but now only shows 500 and a page showing all records with 7,500 now only shows 500)

I can fix this by doing the following 1. run this sql query to disable all products UPDATE catalog_product_entity_int SET value =0 WHERE attribute_id =94

2. run re-index command php bin/magento indexer:reindex

3. run sql query to enable all products UPDATE catalog_product_entity_int SET value =1 WHERE attribute_id =94

4. run re-index command php bin/magento indexer:reindex

5. run this command (twice) php bin/magento cron:run php bin/magento cron:run

then all products display again. but if i run re-index again I have to repeat steps i to 5 to get working again which is not very good.

I also notice that the below 2 commands cause the issue, all other index commands run seperatly cause no problems php bin/magento indexer:reindex catalog_category_product php bin/magento indexer:reindex catalog_product_category

How can I fix this permanently? any ideas

Answer 1

I resolved this issue after completely clearing all products (truncating MYSQL) I did the following two steps

1. Followed the details in the below link to truncate all tables that to delete all data ready for re-import. Obviously get a back up of your data to re-import first

2. Checked mysql and truncated all tables in mysql beginning with 'catalog_' EXCEPT catalog_product_link_type

3.logeed in as ssh and ran the following commands cd public_html php -f bin/magento indexer:reindex

Delete the static view files directory and compile directory

rm -rf var/di mkdir var/di rm -rf var/generation mkdir var/generation cd pub/static rm -rf _cache rm -rf deployed_version.txt rm -rf frontend rm -rf _requirejs rm -rf adminhtml cd .. cd .. php -f bin/magento setup:upgrade php bin/magento setup:di:compile php bin/magento setup:static-content:deploy php -f bin/magento cache:clean php -f bin/magento cache:flush php -f bin/magento indexer:reindex

4. Then I removed all product related images (again back up for re-import) rm -rf /pub/media/catalog/product mkdir /pub/media/catalog/product

That worked for me, just started cleared the database and all files ready for re-import