Magento2: Indexers never come back from working state to valid

[redboxmarcins]

Steps to reproduce

1. Install Magento 2.0.10
2. Simulate that indexer is working (update indexer_state status column via mysql) - you can also try to throw exception in any indexer to stop it and have it in working state
3. Run indexer from command line

Expected result

1. there should be a message that indexer is still running and if it is stalled it should be reverted back to valid

Actual result

1. Command line message says that indexer was ran successfully in 0 seconds
2. Indexer will never come back to valid state so it is not possible to run it again in future without running custom mysql

[choukalos]

Hi @redboxmarcins how big is your store? In terms of # of sku's, # of websites, # of customer groups? We've seen as those #'s grow that MySQL requires a higher tmp_table_size setting otherwise the indexers run out of memory and fail.

[redboxmarcins]

It is not a problem that indexer fails. It is a problem that it stays in running state forever.

[IlnitskiyArtem]

Hi, @redboxmarcins thanks for reporting this issue. I tried to reproduce it but it seems to work fine. Please, add more details to your description of the steps you followed when identifying this issue.

[veloraven]

According to contributor guide, tickets without response for two weeks should be closed.
If this issue still reproducible please feel free to create the new one: format new issue according to the Issue reporting guidelines: with steps to reproduce, actual result and expected result and specify Magento version.

[orlangur]

@IlnitskiyArtem which exact steps did you use trying to reproduce issue? Description is quite comprehensive and I didn't spot any logic introduced in mainline which could solve this problem.

[IlnitskiyArtem]

@orlangur at first, i tried to change indexer's status for valid manually in database, when they already were valid. Then tried to reset them and change the status for valid when they were invalidated. Also in both cases after changing indexers' status, in CLI and Mysql, i created store views to invalidate the indexers and every time checked their status in CLI, Admin Panel and Mysql. The next step was to earn some additional information, but as you see it failed. Can you point out my mistakes? Thanks in advance.

[hostep]

@IlnitskiyArtem: you aren't mentioning the 'working' status like @redboxmarcins was mentioning, did you test it with that status?

(I think we had a similar problem a couple of weeks ago, but I think it was with the 'mview_state' table instead of with the 'indexer_state' table. But not 100% sure anymore. Somehow updating of the materialized views had stalled and it was because some status got stuck on 'working' and never got out of it until we manually fixed the status.
This was using Magento CE 2.1.7 btw.)

Hope this helps in regards of trying to reproduce this issue.

[IlnitskiyArtem]

@hostep Thanks, it's really helped. My bad.
@redboxmarcins Internal ticket MAGETWO-71086, which tracks this GitHub issue, is in our issue backlog.

[hostep]

@IlnitskiyArtem: maybe you could re-open this issue while you're at it, tnx! :)

[orlangur]

@hostep looks like it was fixed in https://github.com/magento/magento2/issues/11166 recently.

[larsroettig]

Hi, I cannot so easy backport my Fix because 2.1.X allowed to use php5.6 and this Error handling requires php7.0 it is broken change for 2.1.X. To catch Engine Error only for one function is in php5.6 not so simple.

Magento 2.1.X:
https://github.com/magento/magento2/blob/13d38880e25347d3aec59494813d643fb6975e0d/composer.json#L11

Magento 2.2.X
https://github.com/magento/magento2/blob/4d2609343a950e9ee6b50cc4444469d50d82b139/composer.json#L11

[orlangur]

@larsroettig yeah, I don't think it's a big problem for anyone who faces it. There is no reason to stay on PHP5.6 having 2.1.x and when you have PHP7.0 just adopt your PR for a particular instance and be happy :)