Fix Command Denied to user on MySQL Functions (solved)

I was having trouble getting a function I built a long time ago on a different IP address to work this morning. I keep trying to execute a function called digits which simply strips out all characters that aren’t digits. It’s good for mashing down phone numbers and dates if you have “dirty data”. I was getting the following error.

execute command denied to user ‘user’@’x.x.x.x’ for routine ‘SimpleSignups.digits’

I’m connecting directly to the remote server out on the internet with NaviCat for Linux but same thing was happening when I was connecting via the MySQL CLI client.

I tried granting all on the database to my current user name & IP address but was still getting the error. Finally via NaviCat I opened the function to see if there was anything in there and found this right at the top. The IP address was from a location that I haven’t been at in 6 years!

CREATE DEFINER=user@X.X.X.X

I changed to my current IP address, reran the query and it worked this time.

I have root privileges to this database so keep this in mind. Privileges may vary.