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.

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

Please run the following command then it imports normally.

sed -i 's|DEFINER=[^*]*\*|\*|g' [PATH/]dbdump.sql
  • 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 '20 at 14:35

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

Try this command

sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' nameOfYourDump.sql > nameOfYourDumpClean.sql

It will fix the file and write it under nameOfYourDumpClean.sql

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.