MySQL super privilege error 1227

Question

I exported my SQL tables and views from my local machine. I tried to import it into my website's machine. But I get this error: #1227 - Access denied; you need the SUPER privilege for this operation

The error is given for this query:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 
VIEW `myview` AS select `my_table`.`id` AS `id`,`my_table`.`name` AS 
`name`,`my_table`.`surname` AS `surname` from `my_table` where 
((`my_table`.`name` = 'michael') and (`my_table`.`surname` = 'notyet'));

What should I select when I export my table and views ? To not to get this error?

score 6 · Accepted Answer · answered Mar 01 '13 at 17:59

6

Remove everything on the first line from the word ALGORITHM to SECURITY DEFINER, so that the query simply reads:

CREATE VIEW `myview` AS select ...

Trying to set the DEFINER on the view to root@localhost is a privileged operation.

answered Mar 01 '13 at 17:59

1

Is it possible to prevent writing ALGORITHM and DEFINER, while I'm exporting the database tables ? From phpMyadmin. – trante Mar 01 '13 at 18:33

MySQL super privilege error 1227

1 Answers1