5

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

Anyone meet that problem on magento 2.3 ?

2
  • check your schema , it might be a problem with SQL SECURITY INVOKER
    – fmsthird
    Feb 1 '19 at 12:45
  • any solution? Facing similar issue after migrating to new server. Feb 5 '19 at 13:05
14

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`)));
1
  • my issues similar your reply. It was caused by username mysql incorrect between local and host. Thank.
    – the light
    Feb 11 '19 at 13:57
19

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` )));
1
  • This one is the perfect solution as defining a name can cause a permission issue.
    – Arunendra
    Mar 8 at 12:29
1

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

1
  • Exactly my case. I exported via Magento Backup tool. Got syntax error when importing that backup sql file. There was a \n which caused syntax error. Deleting and moving CREATE ALGORITHM... to new line worked. Jul 28 '19 at 23:38
1

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!

1
  • This is probably the cleanest solution i've seen for this problem! Sep 29 at 8:12
0

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.

0

look on

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

and remove \n

0
0

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
-1

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.

-2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.