Magento 2 inventory_stock_1 FIXED database view missing

Error salable quantity in Magento is showing null, 0.

Elasticsearch Smile/Elasticsuite plugin also throws an error while running reindex.

Catalog Search index process unknown error:
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘magento_database.inventory_stock_1’ doesn’t exist, query was: SELECT product.entity_id AS product_id, stock_index.is_salable AS stock_status, stock_index.quantity AS qty FROM catalog_product_entity AS product

Even if products are enabled and marked as “In Stock”, the product won’t be able ordered. Since Magento 2.3 this new introduced parameter is not to be found in your database tables because it’s a view.

Why is this happening?

This issue is often seen after migrating a database to a different host. From Localhost to Staging server for example. Since the export script for a MySQL dump file generally excludes the View if done through PhpMyAdmin, this table is discarded from the creation script. So you need to run the following SQL query to have it created again on the migrated server:

CREATE ALGORITHM=UNDEFINED DEFINER=`{root}`@`localhost` SQL SECURITY INVOKER VIEW `inventory_stock_1` AS SELECT DISTINCT `legacy_stock_status`.`product_id` AS `product_id` , `legacy_stock_status`.`website_id` AS `website_id` , `legacy_stock_status`.`stock_id` AS `stock_id` , `legacy_stock_status`.`qty` AS `quantity` , `legacy_stock_status`.`stock_status` AS `is_salable` , `product`.`sku` AS `sku`
FROM (
`cataloginventory_stock_status` `legacy_stock_status`
JOIN `catalog_product_entity` `product` ON ( `legacy_stock_status`.`product_id` = `product`.`entity_id` )
)

Log back into Magento 2 > Products > Catalog, you should now see the Salable Qty values automatically generated corresponding to the Quantity attribute value.

NEXCESS doesn’t allow running query using CREATE ALGORITHM. So the workaround is using CREATE VIEW

CREATE VIEW `inventory_stock_1` AS SELECT DISTINCT `legacy_stock_status`.`product_id` AS `product_id` , `legacy_stock_status`.`website_id` AS `website_id` , `legacy_stock_status`.`stock_id` AS `stock_id` , `legacy_stock_status`.`qty` AS `quantity` , `legacy_stock_status`.`stock_status` AS `is_salable` , `product`.`sku` AS `sku`
FROM (
`cataloginventory_stock_status` `legacy_stock_status`
JOIN `catalog_product_entity` `product` ON ( `legacy_stock_status`.`product_id` = `product`.`entity_id` )
)