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?
Solved! Go to 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.
Hello @PankajS_Magento
https://magento.stackexchange.com/questions/249286/error-with-integration-test?rq=1
check above link it will help you.
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.
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
@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.
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.
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
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`)));
I hate Magento