inventory_stock_1 doest exist on Magento 2.3

Question

When I go to product pages and checkout the exception shows:

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'inventory_stock_1' doesn't exist, query was: INSERT INTO `search_tmp_5c4f24124efa61_76233970` SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT  `search_index`.`entity_id`, (((0) + (0)) * 1) AS `score` FROM `catalog_product_index_eav` AS `search_index`
 INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = search_index.entity_id
 INNER JOIN `inventory_stock_1` AS `stock_index` ON stock_index.sku = product.sku
 INNER JOIN `catalog_category_product_index_store1` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (`search_index`.`attribute_id` = 102 AND `search_index`.`value` in ('2', '4') AND `search_index`.`store_id` = '1') AND (category_ids_index.category_id = 394)) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
 LIMIT 10000
Exception #1 (PDOException): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'inventory_stock_1' doesn't exist

Does anyone meet that problem on Magento 2.3?

Answer 1

I got the solution for this, you need to create a view table inventory_stock_1 for your catalog in database.

Here is the command you need to run in your DB SQL. Make sure to update 3 things in below query dbusername, localhost, dbname.

CREATE ALGORITHM=UNDEFINED DEFINER=`dbusername`@`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 (`dbname`.`cataloginventory_stock_status` `legacy_stock_status` join `dbname`.`catalog_product_entity` `product` on((`legacy_stock_status`.`product_id` = `product`.`entity_id`)));

Answer 2

You need elevated permissions to use the CREATE ALGORITHM bit

This can and will stop an import in it's tracks.

Instead use this statement to create the view

CREATE
OR REPLACE
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` )));

Answer 3

Find data patches name from patch_list table

Magento\InventoryCatalog\Setup\Patch\Schema\CreateLegacyStockStatusView

Delete or rename it.

run upgrade command

php bin/magento s:upg

This issue may be raised when you export or import the database incorrect way.

you can found this query at here.

vendor\magento\module-inventory-catalog\Setup\Patch\Schema\CreateLegacyStockStatusView.php

Thank you to my senior to help me to resolve this issue.

Thanks again!

Answer 4

I have same bug. Seems becouse not imported full SQL of DB.

Seems the reason in Magento2 backup tool (have been imported Magento backup to new Database).

How to fix.

Open dump SQL file - Lookking on row with - DROP VIEW IF EXISTS inventory_stock_1;

Before words CREATE ALGORITHM symbols for breaking like \n

But MySQL not understands this symbols correcty, and returned Error.

Just remove breaking symbols, and save dump SQL.

And try upload.

Answer 5

Don't import the database directly.

Open it in your favorite editor then search by inventory_stock_1.

Define username before @localhost

Now import database.

This time inventory_stock_1 table will be there.

Answer 6

look on

\nCREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `inventory_stock_1`

and remove \n

Answer 7

If you're on Magento 2.4.x

Download inventory_stock_1 from database, open it with an editor and

replace

CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`localhost` SQL SECURITY DEFINER VIEW

with

CREATE OR REPLACE VIEW

• Save the file.sql
• drop the view from the database
• import the saved .sql file
• reindex

Answer 8

i am having the same error but when i add the above query "CREATE OR REPLACE 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 )));"

i get the following error #1932 - Table 'magento2.cataloginventory_stock_status' doesn't exist in engine

Answer 9

The view is not created if it exists because of the "typo", resulting in a follow-up error.

The problem with the "newline" character in de SQL-code is caused by the Magento tool to create a database backup (bin/magento setup:backup).

If you create the backup file using mysqldmp you won't have this problem, but in that case, the SQL-code tries to create a TABLE rather than a VIEW. This then also results in an error on the inventory_stock_1 view.

In both cases you need to correct the lines in the SQL-file.

Answer 10

This works for me - I had 6 stores in magento , so I go to admin -> inventory->stocks and created 6 new entries for 6 stores. After that I reindex data and it works.