Normally, I am able to export my site databases locally, then transfer up to my live/remote server. Unfortunately, an issue arose –
When transferring to the remote database, it kept giving me an error about not having SUPER privileges to create stored procedures and functions. I had never come across this error, but was able to narrow it down to this line:
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_do_something`() RETURNS int(11)
Turns out that the DEFINER clause is the problem. There were two problems with this:
- The local database and the remote database have different users and possibly different server names.
- I work with GoDaddy and their databases would not have it – in fact, their tech support was uncertain why.
So, I started to investigate MySQL Workbench for an option to omit the DEFINER clauses. Unfortunately, that option does not exist. Turns out that mysqldump, the executable MySQL Workbench calls to create the SQL file, does not offer a way to turn off the clause. So, automation is out.
Ended up going into a text editor and doing a search and replace for the clauses in question. In the end, the lines in question look a little more like this:
CREATE FUNCTION `fn_do_something`() RETURNS int(11)
This topic is all over the net. I came across a number of sites with RegEx/Perl solutions and even a request asking for mysqldump to include an option for omitting the DEFINER clause – although I doubt anything will happen since the request was from 2010:
- https://dbperf.wordpress.com/2010/04/12/removing-definer-from-mysql-dump/
- http://stackoverflow.com/questions/9446783/remove-definer-clause-from-mysql-dumps
- https://bugs.mysql.com/bug.php?id=57646
- http://stackoverflow.com/questions/2666286/is-definer-required-when-creating-a-stored-procedure
- http://stackoverflow.com/questions/16594860/mysql-create-definer