Access to mysql.proc on MySQL PS


This problem is related to another issue that I am having, but basically I’m trying to find a way of updating the ‘definer’ field in mysql.proc as I’d like to make the definer the same value for all routines that have been created on a particular database.

Basically, due to a bug in mysqldump, backing up stored routines using mysqldump won’t work unless the definer of the routines is the same as the user doing the backup. However, I’ve created routines with different IP addresses (as a result of using different computers) and this is now causing a problem e.g. username@ is different to username@

To resolve this, I need to update the definer in mysql.proc to be the same user @ the same IP address and then run mysqldump under this same account (which should all be fairly simple):

[font=Courier]UPDATE mysql.proc
SET definer = 'username@’
WHERE db = ‘mydb’ AND definer LIKE ‘username@%’;[/font]

However, even though I’m on MySQL PS, I don’t seem to have access to update this table. Is this permission not allowed under MySQL PS?



Basically I have a similar problem. My ip address has changed which prevents me from altering or even viewing my stored routines.




Hi, I just experienced the same problem. May I know how did you get that resolved?


You could SSH to the server, drop all the current routines, and then re-create them via SSH (if you have them scripted). This would then mean that all your routines will have the same user/IP address.

(As a side note, since initially raising this problem, I now always create my routines via SSH and don’t use remote tools to do this.)