2

I've created a magento 2.3.2 that works locally on pc (using laragon, if it matters). Now I need to transfer site to working server, so I made a backup using phpmyadmin and tried to import .sql file into server.

I edited file to use correct database on server, as well as user that has privileges on server. User on local machine is root. User on server is an user in a shared environment.

CREATE ALGORITHM=UNDEFINED DEFINER=`SHARED_USERNAME_IN_SERVER`@`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 (`cataloginventory_stock_status` `legacy_stock_status`
JOIN `catalog_product_entity` `product` ON `legacy_stock_status`.`product_id` = `product`.`entity_id`)

Problem is I get:

MySQL: #1227 - “Access denied; you need (at least one of) the SUPER privilege(s) for this operation”

How can I solve this? Thanks.

  • either you need to create a definer SHARED_USERNAME_IN_SERVER in your PhpMyAdmin Or replace SHARED_USERNAME_IN_SERVER with root in database file and import. – Pawan Dec 16 '19 at 2:54
  • @Pawan I tried to use "root" instead of SHARED_USERNAME_IN_SERVER, however got same result. I don't have root access on server. – fran35 Dec 16 '19 at 19:46
  • if you do not have access to root, you can use database user – Pawan Dec 17 '19 at 11:27
7

Please run the following command then it imports normally.

sed -i 's|DEFINER=[^*]*\*|\*|g' [PATH/]dbdump.sql
| improve this answer | |
  • Hi, unfortunately it didn't help, got: sed: -e expression #1, char 18: unterminated `s' command – fran35 Dec 16 '19 at 19:49
  • perfect solution @chirag working for me... thank you – Vishal Baraiya May 16 at 14:35
2

Ok, got it working by changing definer to DEFINER=CURRENT_USER:

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 (`cataloginventory_stock_status` `legacy_stock_status` join `catalog_product_entity` `product` on((`legacy_stock_status`.`product_id` = `product`.`entity_id`)))
| improve this answer | |

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.