CREATE ALGORITHM=UNDEFINED DEFINER

Question

I make a backup of some database from distance server, and I had an problem on my local server when I trying to import that backup. I get an error in this line:

CREATE ALGORITHM=UNDEFINED DEFINER=root@% SQL SECURITY DEFINER VIEW tematics_field AS select.....

Both server have a mysql 5.5.2x. And users are different in that two servers.

Answer 1

I only try to:

CREATE VIEW tematics_field AS select....

And all is work perfectly and import is well done.

Answer 2

MySql Error: #1227 – Access denied; you need (at least one of) the SUPER privilege(s) for this operation

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`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 `decg_catalog_product_entity` `product` ON(`legacy_stock_status`.`product_id` = `product`.`entity_id`)) ;

Fixed Solution:

The problem is you set definer as root, which is not your current running user, that’s why you need to SUPER privilege. you can create a user called root in RDS, and use root to run the command, or simply

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER

change to:

CREATE ALGORITHM=UNDEFINED DEFINER=CURRENT_USER SQL SECURITY INVOKER

read more about CURRENT_USER Final SQL query looks like

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 `decg_catalog_product_entity` `product` ON(`legacy_stock_status`.`product_id` = `product`.`entity_id`)) ;

Thank you.From: MazziTorch