• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Maurisource Web Agency

Maurisource Web Agency

Magento Architect and Development

  • Services
  • Magento ® 2 Integrations
  • Magento ® Articles
  • Contact

Magento 2 inventory_stock_1 FIXED database view missing

September 1, 2020 by Manesh Sonah Leave a Comment

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` )
)

Filed Under: Magento 2 Tagged With: magento 2, magento 2.3 inventory

About Manesh Sonah

When I'm not looking for solutions in the eCommerce industry: I enjoy traveling, photography, water-sports and gaming.

Primary Sidebar

Pick a certified team that got your back while your eCommerce requirements grow.

Who am I ?

When I'm not looking for solutions in the eCommerce industry: I enjoy traveling, photography, water-sports and gaming. About me...

Locally made in Canada

Agence Web - Magento

MONTREAL / QUEBEC / MTL

  • Services
  • Magento ® 2 Integrations
  • Magento ® Articles
  • Contact