Ever tried to migrate a MySQL database with stored procedures or views? Ever ran into the error “The user specified as a definer does not exist”, or “Access denied; you need (at least one of) the SUPER privilege(s) for this operation”? Chances are that you are trying to migrate a MySQL database with stored procedures or views.
Luckily, the fix for this is fairly simple. All you have to do is change the definer of the stored procedure or view to the current user. Lucky for us, this can be done very easily with the sed -command:
sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' database.sql > database.new.sql
Special thanks go out to Jonathan Brinley for pointing it out in his article. My first attempts on replacing the definer where by find-and-replacing an exact match, but his regular expression solution made it a for more convenient snippet.
Visitors give this article an average rating of 5.0 out of 5.
How would you rate this article?
★ ★ ★ ★ ★