magento EE v2,4.1 products disappear from category pages, or appearing on wrong category pages

Question

I have a magento EE v2.4.1 website. my products disappear from PLP (category pages) automatically sometimes or appear on wrong category pages. this issue seems to be due to indexing, because when we are using magento's scheduled indexing or update on save indexing, this issue happens after few hours or days. but whenever we run manual re-index from CLI, the issue resolved for all products. but again appears after few days by itself. And also the issue is happening with the new products which are being added after the version upgrade to EE 2.4.1, the issue not happening with the old products imported from old version. and this issue also happening even if products are in stock.

Does anybody have the idea why this happening, or anyone ever faced this type of issue ?

Answer 1

How to verify that this particular error is causing the error

Since the products disappear on their own we know that we need to look for an automated background process. The first thing you should do is browse the var/log/exception.log and look for a periodic error that contains a long SQL statement and ends similarly to this

You can refer to this article.