I run a multi-store install of Magento 2.4.6, recently upgraded from 2.3.7-p4
2.3.7-p4 was running with no issues using MSI, products stock_status was being correctly indexed in cataloginventory_stock_status during the cronjobs and manual runs via CLI
I now have a curious issue, where the stock qty and status is correct for all the stock sources in inventory_source_item however the reindex process (specifically cataloginventory_stock) sets all items with backorder status of 1 or 2 as stock_status 1, but any items with an actual stock qty are set to stock_status 0.
I can use the following query to manually correct this, however the cronjob reverts it back to the wrong behaviour, resulting in a massive reduction in the number of products visible on the website(s)
INSERT INTO `cataloginventory_stock_status` (`product_id`, `website_id`, `stock_id`, `qty`, `stock_status`) SELECT * FROM ( SELECT _entity.entity_id, '0' AS 'website_id', '1' AS 'stock_id', SUM(_item.`quantity`) AS 'quantity', CASE WHEN SUM(_item.`status`) > 0 THEN '1' WHEN _stock.`backorders` > 0 THEN '1' ELSE 0 END AS 'stock_status' FROM `inventory_source_item` _item LEFT JOIN `catalog_product_entity` _entity ON _item.`sku` = _entity.`sku` LEFT JOIN `cataloginventory_stock_item` _stock ON _entity.`entity_id` = _stock.`product_id` WHERE _entity.`entity_id` IS NOT NULL AND _entity.`type_id` = 'simple' GROUP BY _item.`sku` ) AS _results ON DUPLICATE KEY UPDATE `qty` = _results.`quantity`, `stock_status` = _results.`stock_status`;
I've also tried the solution posted on Github: Reindex cataloginventory_stock does not update however this has made no difference either.
---Update---
I took a look at the indexer file itself in Magento\CatalogInventory\Model\ResourceModel\Stock\item.php and the stock behaviour is actually being read from the table cataloginventory_stock_item to determine whether to mark items as in, or out of stock.
I'm running MSI as per previous, and if I update an item to be out of stock by reducing the qty and stock status in the product, this table updates to reflect the new stock status, however adding qty and stock status to a product that's out of stock does not alter the in_stock_status value, unless that qty is added to the `Default Stock" source. As a temporary workaround that will also enable indexing to run, I'm executing the following cleanup statement after importing stock via feed or API
INSERT INTO `cataloginventory_stock_item` (`product_id`, `website_id`, `stock_id`, `is_in_stock`) SELECT * FROM ( SELECT _entity.entity_id, '0' AS 'website_id', '1' AS 'stock_id', CASE WHEN SUM(_item.`status`) > 0 THEN '1' WHEN _stock.`backorders` > 0 THEN '1' ELSE 0 END AS 'stock_status' FROM `inventory_source_item` _item LEFT JOIN `catalog_product_entity` _entity ON _item.`sku` = _entity.`sku` LEFT JOIN `cataloginventory_stock_item` _stock ON _entity.`entity_id` = _stock.`product_id` WHERE _entity.`entity_id` IS NOT NULL AND _entity.`type_id` = 'simple' GROUP BY _item.`sku` ) AS _results ON DUPLICATE KEY UPDATE `is_in_stock` = _results.`stock_status`;
