Magento 2.3: Inventory_stock_1 doesn't Exist On Magento 2.3

[PankajS_Magento]

After migrating to production server from localhost I am facing this issue in magento 2.3:

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

any solution?

[Sunil Patel]

Hello @PankajS_Magento 

https://magento.stackexchange.com/questions/249286/error-with-integration-test?rq=1

check above link it will help you.

[PankajS_Magento · Accepted solution]

I got the solution for this, For create a view of inventory_stock_1 table I did some changes in the database.

Here is the command I run in DB SQL. If you are using same 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`)));

Cheers, Hope it will work for everyone.

[amjad_freihat1]

Hi, 

when i do this on my cloud hosting i see this error: #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

can you help me if you can please 

[SrLucasM]

---

@amjad_freihat1 wrote:

Hi, 

when i do this on my cloud hosting i see this error: #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

can you help me if you can please 

---

You have to use the following:

mysqldump -h host --user=user --single-transaction database  | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | gzip > /tmp/database_no-definer_stg.sql.gz

sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' being the key part, otherwise you'll see a permission error.

[epeets]

For those of you searching for a way to do this on cloud hosts like Cloudways and still running into "Access denied; you need (at least one of) the SUPER privilege(s) for this operation" like me. If neither @SrLucasM  or @PankajS_Magento solutions are working. The solutions are correct but you will need to modify the DEFINER like so:

CREATE ALGORITHM=UNDEFINED DEFINER=CURRENT_USER 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`)));

You can also achieve the same thing by removing the DEFINER=`dbusername@`localhost` from the code altogether and running the query while logged into the DB via your control panel or wherever you accessed it from.

[leopoldo69]

Hello
can you explain to me the steps please?
I want to import my database into a new database at different host.
which database to modify?
which command to execute and what changes to make?
Thank you

[SteveBerrill]

I ran like this inside (use) the database, 

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

[dharake86]

I hate Magento