I was recently working with a sidekick thing where I was using MySQL as my backend database.
This was accessed from the application with hostname - localhost
and username dev
.
I had created one view and was using it in the thing I was working on. It was originally scripted like this:
CREATE
DEFINER = 'dev'@'localhost'
VIEW somedatabase.somerandomviewname
AS
SELECT
`sd`.`somerandomcolumnone` as `ColOne`,
`sd`.`somerandomcolumntwo` as `ColTwo`
FROM `someshitytable` `sd`
WHERE sd.blahblah IS NOT NULL;
One day I realized that my workstation is going crazy over few tools I use everyday. I could have repaired or fixed or reinstalled them but then I choosed to do a full cleanup and start fresh (for no particular reason).
So I used MySQL dump backup feature from maintanance and created backup scripts for that database, which also contains the creation script for this view obviously.
Once my machine was ready again, I restored the database from the MySQL dump script. This time when I setup MySQL database I created another user called localdev
(for no particular reason).
So I do not have the dev
user in new MySQL setup anymore. Tried to see if the application is still running as expected or not.
Changed connection string and on run, I started getting following error:
The user specified as a definer (dev@localhost) does not exit - MySQL error 1449
I am still getting used to MySQL and experienced this situation first time. Wasted 20 minutes trying to figure out why I was getting this exception.
I retrived the script that was used to restore this view and observed that following line of SQL code:
DEFINER = 'dev'@'localhost'
expected that following user should be there and should have approriate rights.
Once I alter the view to use the new hostname and username, the application stared working as expected.
ALTER
DEFINER = 'localdev'@'localhost'
VIEW somedatabase.somerandomviewname
AS
SELECT
`sd`.`somerandomcolumnone` as `ColOne`,
`sd`.`somerandomcolumntwo` as `ColTwo`
FROM `someshitytable` `sd`
WHERE sd.blahblah IS NOT NULL;
You can skip or remove the DEFINER
clause all to gather to avoid this issue. You can also recreate that user as before to make it work. Make sure you make those modification in your restore script before restoring using those scripts.
I hope that would help someone.
Happy coding !!