Category: MySQL

Categories
MySQL

MySQL error – “CREATE ALGORITHM=UNDEFINED DEFINER” solution

I found this problem these days when I had to deploy a mySQL database with some views.

To perform certain tasks, like importing certain SQL files or creating scheduled jobs (a problem which you can solve with with CRON jobs, if hosting is running under Apache, as I explained on another post ), you must have one user with USER privileges.

Unfortunately, if you use a shared deployment server like I do, it is not convenient that you have such a SUPER privileged user because it could lead to security problems.

For this reason, you could find an error like this when you try to deploy the database you used on development environment.

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

on the line which contains

CREATE ALGORITHM=UNDEFINED DEFINER

In case you find this problem, maybe you can solve this with the instructions I found on this Stack Overflow post.

According to that, you should change, for example:

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

to this:

CREATE ALGORITHM=UNDEFINED DEFINER=CURRENT_USER SQL SECURITY INVOKER

I hope it is useful for you like it was for me.