Magento 2 Error: Product disappeared on frontend, one store view only

Question

We are having a weird problem since some days after the shop running for months without trouble.

On one day (only thing we did were some backup-operations on the database which might have caused this),

• suddenly all product disappeared on frontend, showing "Wir können keine Produkte entsprechend dieser Auswahl finden", "we could not find any products for this selection".
• This happened on the default store view, german
• the other store views, french and english are not affected

When we open and save on product in backend, it sometimes shows up in front-end for some seconds. However, they disappear again with each indexer run.

Another point: After resignation yesterday evening, the products "just" have been back this morning. I don't know what indexer run made them disappear some time later.

Magento 2.1.13, managed server

Anyone has an idea or help for this issue -and probably broken database?

What we did until now:

• reindexed many times, cleared and flushed cache
• saved many products in top- and store view
• saved category in top- and store view
• deleted the flat tables in database, reindexed

No effect on all.

What we did not do until now is setup:upgrade and rebuild the production static files because we are experiencing a php-problem on the development site on the same server. We want to avoid that we could end up with a non-productive site after that due to the php problem.

Answer 1

As you have done a backup restore, check wether trigger were dumped or not, else check that triggers user is the same as you current magento database user.

mysql> show triggers

If you have one of these two cases, just recreate the triggers (with correct user), Reindex catalog and product will be shown again