You do likely need log_bin_trust_function_creators
= 1 on RDS but that isn't the issue, here.
You can specify a DEFINER
value other than your own account only if you have the SUPER
privilege.
— http://dev.mysql.com/doc/refman/5.6/en/stored-programs-security.html
When a stored program (proc, function, event, or trigger) is running, everything it does has the permissions of the user who defined it, or of the user explicitly stated with a DEFINER
declaration. This allows, among other things, for stored programs to permit other users to do things to data they don't directly have permission to manipulate, as long as they have permission to use the stored program itself.
It would be a serious vulnerability, then, if a non-SUPER
user could create a procedure with an arbitrary definer, because the user could escalate his or her privileges at will.
This is also true of views, of course, when the definer security context is used, as in the example you posted.
One of the biggest complaints I have with RDS is that you can't have SUPER
... and now it can be one of yours, too :) because that fact is the cause of the problem you are having.
Of course, if I were running a managed MySQL service, I wouldn't give anybody SUPER
, either, so their security model makes sense, even if it is sometimes unwieldy.
If all of your objects have the same definer, a workaround would be to restore the dump using that account instead of the one you're using now, but that seems unlikely.
Deleting just the line with the DEFINER
declaration should make the dumpfile work in cases where it appears on a line by itself, or you could use sed or perl to modify the file... an idea that I already know you're not fond of, but it really is a nice thing about MySQL that such hackery is quite legitimate, and not really all that far afield from the kinds of things I have to do as a DBA even in a non-RDS envirnoment.
perl -pe 's/\sDEFINER=`[^`]+`@`[^`]+`//' < oldfile.sql > newfile.sql
...possibly not the answer you hoped for, but you could run that against your dumpfile and should end up with a slightly more usable file.
I understand it can't or shouldn't be done. Am I right?
I would say you are incorrect, in spite of the fact that what you are saying seems consistent with what's documented.
A plain reading of the documentation notwithstanding, you can do this indefinitely, on an ongoing basis, as long as you don't expect Amazon to provide support for your non-standard configuration... which, to me, seems reasonable. Almost certainly, using native replication like this is a better alternative than any other mechanism.
As of this writing, I have had servers configured like this for over a year with no issues.
Going beyond this anecdotal evidence, confirmation of my position can actually be found in an official RDS Webinar, in a conversation that begins at around 56:45:
"You can keep it in a replicated state indefinitely...
"...as long as you take the responsibility to maintain the replication..."
"We are not preventing you from doing ongoing replication if that's what you want."
Note that, as always with MySQL replication, your slave's configured @@SERVER_ID
must be different than the master's, and different than any other RDS or non-RDS replica connecting to the same master.
Best Answer
You can also modify
query_cache_size
andmax_allowed_packet
parameters via the AWS Management console. Use this AWS documentation as your guide, link