3

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 ?

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

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

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

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

  • 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. – Joe Fletcher Jul 28 at 23:38
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.

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