We rolled out a new release of our server yesterday, and ran into an unusual problem. We still don't know the root cause of it (probably a MySQL bug), but we do know what the symptoms and workarrounds are.
The symptom is the following error message:
execute command denied to user ''@'' for routine 'mydatabase.myproc'
The interesting part of this error message is the user with no name and no host. We are logged in as an application user. A search for this user reveals that it does indeed exist, although it was not present on the development box.
mysql> select count(1) from mysql.user where user='' and host='';
| count(1) |
| 1 |
1 row in set (0.03 sec)
We use replication to separate writes from reads. This user appears on both the write and read servers, but this error only occurs on reads.
Investigating further, we tried to learn more about this mystery user. We entered the following command:
mysql> show grants for ''@'';
PLEASE DO NOT EXECUTE THIS COMMAND. This brought the server down. We didn't believe that it was the cause, so we tried again on another server. After restarting both servers, we continued our investigation.
We have no idea where this user came from or what might depend upon it. We also have no idea why this user is used to authenticate this proc instead of the app user. This being the production server, we didn't do anything as drastic as dropping the user without proper research. So we focused on this procedure in particular.
Here's my solution
The fix that we eventually found is two-fold. First, identify the user by password in the grant clause after creating the procedure. Second, explicitly drop and recreate the procedure on each of the read servers. Replication of the create script had some part in causing the problem. So our script ended up looking like this:
DROP PROCEDURE IF EXISTS myproc
CREATE PROCEDURE myproc()
GRANT EXECUTE ON PROCEDURE myproc TO appuser IDENTIFIED BY 'apppassword';
Run this script on the write server, wait for it to replicate to the read servers, then run this script on each read server individually. I don't know why, but it works.