Recently I’ve been facing issues with reindex process, some indexes are shown with status Reindex Required and even if I achieve to reindex them they continue with the same status.
After some days indexes got stuck and reindex never ends.
So I decided to take a backup from my production server and investigate deeper but when I run mysqldump I get and error because catalog_category_product_index_store10_replica table is always busy.
And if I query the amount of records on this table many times just with few seconds one after each other I got different results.
select count(*) from catalog_category_product_index_store10_replica;
Example
- Second 1 -> 1288
- Second 2 -> 722
- Second 4 -> 0
It seems like there is a process stuck or something is being executed over and over, do you have any idea about this?