inventory_stock_1 doest exist on Magento 2.3

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

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

Method 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!

Method 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.

enter image description here

Method 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.

Method 6

look on

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

and remove \n

Method 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

Method 8

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.

Method 9

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply