New products don't get Salable Quantity update until reindexed manually

Question

I have an issue with my Magento 2.3.2 store where a new product, after it is created, never gets Salable Quantity updated until we manually go in and reindex at the command line. It stays at Default Stock:0 (thus not sellable) no matter what changes we make to stock status / quantities, but then it is corrected when we index at the command line and clear the cache. This is a big inconvenience for us because the person making the products is not the same person who would log into the server to manually update the index.

Other facts:

• On a development box with a copy of the same database I do not have this issue (new products get Salable Quantity just fine).
• I can see that the indexer runs once a minute and has no hang-ups.
• Once bin/magento indexer:reindex is run manually, the Default Stock value properly updates.
• I do have a view inventory_stock_1 that seems properly created. In fact my issue seems to be that an entry into cataloginventory_stock_status never gets inserted on product creation or update.

I know this has been answered similarly in other places but mine seems to be different, specifically that it's only new products and that it does work it just needs to be done manually.

Any help would be appreciated.

Answer 1

I was able to resolve this. The issue is that the indexer that runs in cron only updates logged changes, not the whole index. Changes are in the *_cl tables matching each indexer. The table mview_state holds where the index is currently "at" and looks at the *_cl tables auto_index number to see what changes need to be indexed next. The issue was a database setting that caches table status information, including auto_index. If it's cached, the indexer doesn't see the latest changes and won't process them. This will cause recent changes to be ignored by the index and the _cl tables to fill up with unprocessed changes.

Run this command: show variables like 'information_schema_stats_expiry';

If that number is not 0 (mine was 86400) your index will fail to process the backlog in a timely manner.

Run as root: SET PERSIST information_schema_stats_expiry = 0;

Then restart MySQL.