While transferring data from one RDS instance to another you may receive the following error.

Error: 1227 SQLSTATE: 42000 (ER_SPECIFIC_ACCESS_DENIED_ERROR) Access denied; you need (at least one of) the %s privilege(s) for this operation.

The following could solve the problem in these circumstances.

Change the log_bin_trust_function_creators value in the custom DB parameter group to 1.
This allows functions, procedures, and triggers on your DB instance.

Remove the DEFINER (Eg: DEFINER=`user`@`localhost) information from the functions, stored procedures, and triggers. When MySQL tries to create an object for a database user but the database user doesn’t exist in the destination database, definer errors are generated. Similar errors could appear if MySQL tries to establish a user for localhost, which is not supported by Amazon RDS. This is due to Amazon RDS’s lack of superuser permissions.

Certain statements, like “SET @@SESSION.SQL_LOG_BIN= 0;” that are typically found in MySQL dump files aren’t supported by RDS. Before running the dump file against the RDS instance, these lines in the file should be removed or commented on.

Hope this helps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s