who is “Definer” in terms of MySQL
Today I was playing with our database settings and trying to grant/ cut permissions of users from the development DB and production DB, changing passwords and so on.
Using our primitive hosting’s control panel I wanted to remove the main production DB user from Development DB, which I ended up loosing the user completely.
I created a new user/pass and in few seconds I changed the configurations. Evertyhing looked fine till I got a phone call from by boss and I found that something is missing and not working properly.
The problem was on one of our scripts and the error was :
The user specified as a definer : (’no_such_user’@'no_such_host’) is invalid or unregistered
And thank to our primitive control panel, I was not able to recreate the user as it contains “_” [underscore] in the username.
All the settings were fine and I was wondering what’s causing the problem. At first rush I thought that it could be a cache, as I had requested recently to have xcache on our server.
But it wasn’t the case. The error was much more stupid than even one can imagine. That specific script uses a stored procedure to insert / fetch data to/from MySQL. The user who had created the sp was the one who was deleted. And that was the problem. The term “Definer” in terms of MySQL is the one who creates the stored procedure and for the stored procedure to be executed that user must exists.
A quick solution for that was dropping the old stored procedure and re-create it while logged in to MySQL with the new user.
Voila, it solved our issue. Not the best solution but at least I figured out what “Definer” means and it must exists when a sp is being called.
