What exactly this error is?

Question

Database i am using is MySQL 5.1. I specified, i believe, right connectionstring in my web.config. but when i run my web app. it throws this exception:-

MySql.Data.MySqlClient.MySqlException: The user specified as a definer ('root'@'%') does not exist

Any help. Why am i getting this?

Answer 1

The source code for the stored procedures that you have been loading probably contain "DEFINER=root@'%'" as part of the definition - looking a bit like this:

create definer='root'@'%' procedure sp_test() begin end;

The problem here is that you do not have an account on your system for 'root'@'%'. This can be easily demonstrated. From the MySQL command line:

show grants for 'root'@'%';

I expect that this will come back with an error message:

ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'

The fix is to alter the source of your stored procedures, or to create the missing account:

grant all on *.* to 'root'@'%' identified by 'password' with grant option;

It is not generally a good idea to have such a high-powered account accessible from anywhere, but that is another story.

Answer 2

I know this answer comes very late, but I found a solution that doesn't involve changing any rights at all.

Go into your Stored Procedures and delete the DEFINER clause. The server should write in a new DEFINER that matches the user information you're logged in with.

Worked for me...

Answer 3

Is this in a stored procedure? Then maybe this blog post helps (emphasis mine).

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.