Resolved: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Recetly we hosted a PHP based website to our one domain, this website had predefined mySQL database which we had to import using phpmyadmin panel to our database. When we tried to import the database, we got an error as,

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

when we looked at the .sql file of the database, where the error was reported, it had the code as below,

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

but since we were using different database username, we tried to change it to cpanel username or actual database username but it didnt worked.

Solution :

So the final solution which worked was to remove the DEFINER syntax, i.e. we removed “DEFINER=`root`@`localhost`” and it them attempted an import, and it worked without any issues.

So, we changed from

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

to

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW

Follow Lynxbee Posts with Google News

Leave a Comment