Category product index is wrong after switching to temptables for indexing.

[nithinsag]

Preconditions

Magento Version: 2.1.9

ElasticSuite Version: 2.3.10

Environment: production

Third party modules :

Steps to reproduce

We are currently using Magento 2.1.9 with elasticsuite 2.3. with data migrated from Magento 1.8.1 with index on save turned on. We have noticed that some of the newly created products are indexed into the wrong categories. We have double checked the category tables and Magento's index tables and found them to be correct. However, our newly created products still appear in the wrong categories.

Expected result

Products should only appear on their respective categories

Actual result

We are seeing products in the wrong categories in frontend and in the admin panel preview for the category.

[nithinsag]

We have reverted the use temp table option but the issue persists, I suspect this is unrelated to the temptables change.

[afoucret]

Do not see what you mean by : "switching to temptables for indexing".
Can you explain us ?

[nithinsag]

We tried to use the fix suggested the problem mentioned here. magento/magento2#10591

--- a/vendor/magento/module-catalog/Model/Indexer/Category/Product/Action/Full.php	2017-09-28 14:11:25.000000000 -0400
+++ b/vendor/magento/module-catalog/Model/Indexer/Category/Product/Action/Full.php	2017-09-28 14:11:59.000000000 -0400
@@ -12,7 +12,7 @@
*
* @var bool
*/
-    protected $useTempTable = false;
+    protected $useTempTable = true;

/**
* Refresh entities index

[afoucret]

Hi @Diadara ,

We won't support the patch suggested in magento/magento2#10591 until it will be an official one.

Once Magento code is reverted to the original, have you tried :

1. Clean all caches
2. Reindex all indices :

bin/magento indexer:reset && bin/magento indexer:reindex

3. Clean all caches again

BR,

[afoucret]

@Diadara,

Another idea on this one : are you sharing the same ES server between multiple environment (testing, staging and production).

If so take care to use a different index alias name between the environment in the ES config section. See the doc here : https://github.com/Smile-SA/elasticsuite/wiki/ModuleInstall#indices-settings

[nithinsag]

We are using separate servers for our staging environments and we have tried reverting the patch and reindexing and clearing caches without success. Will it be possible for someone from your team to have a look at the staging environment or check our db dump. The mysql catalog indexes are fine and don't have an issue, but the elastic cache indexes are still wrong.

[afoucret]

@Diadara,

Our team can have a look as part of our paid commercial support if you want a faster resolution of the issue.
You can join me by email (aurelien.foucret@smile.f)r and I will forward to our sales.

BR,

[ajabso]

@Diadara

This patch solve only the issue of empty categories when index category products is in progress.
Not a wrong reindexing result

[nithinsag]

The issue was not caused by the temptables patch as we initially thought. I did some digging around and discovered that we had a child virtual category as one of the leaf categories of the category in question which did not have a root category selected. This caused the query builder to work in an unexpected way to create an empty query that selects all products, and all the all the parent categories go filled with irrelevant results.

You could have a validation step to prevent activating virtual categories without a parent category or modify the query builder to not select everything in the catalog, if no root category is selected.

[afoucret]

The feature works as designed and as expected for me.

When creating a virtual category without root and no filter, the whole catalog is used.
PR #647 allow to display the root category by default in the form when no category have been chosen yet :

• You can not create VC without root category
• It will not change the behavior for existing catalog

[afoucret]

PR have been merged in branches 2.4.x and master.
Will be part of ElasticSuite 2.4.2 and later versions.