How can I resolve ERROR 1227 when enabling replication or automated backups on an Amazon RDS MySQL instance?
Last updated: 2019-04-23
I imported data to an Amazon Relational Database Service (Amazon RDS) for MySQL instance using mysqldump. But when I tried to enable replication or automated backups, I received an error similar to the following:
"Error: 1227 SQLSTATE: 42000 (ER_SPECIFIC_ACCESS_DENIED_ERROR) Access denied; you need (at least one of) the %s privilege(s) for this operation."
This error occurs when the database has the binary log enabled, and the mysqldump file contains an object (a trigger, view, function, or event). For more information, see the MySQL Documentation for The Binary Log.
If any create statements don't include the “NO SQL,” “READS SQL DATA,” or “DETERMINISTIC” keywords, then MySQL can't create those objects, and the import fails with error 1227.
To resolve error 1227, change the parameter group value of log_bin_trust_function_creators to 1.
To relax this condition and allow the import of all the objects, set the global log_bin_trust_function_creators system variables to 1 through the RDS custom parameter group. For more information, see Modifying Parameters in a DB Parameter Group.
Note: Because you can't change values in a default parameter group, you must associate a custom parameter group to your instance if the default parameter group is currently associated with the instance. After you associate a new parameter group to your instance, you must reboot your instance.
If you have a custom parameter group attached to your instance, then:
- Open the Amazon RDS console, and choose Parameter groups from the navigation pane.
- Choose the custom parameter group name that is associated to your instance.
- Enter log_bin_trust_function_creators in the Filter parameters field, and then choose Edit Parameters.
- Change the Values for log_bin_trust_function_creators to 1.
- Choose Save changes.
Note: Because this parameter is dynamic and the customer parameter group is already associated to your instance, this change is applied immediately. For more information, see Working with DB Parameter Groups.
If you have a default parameter group attached to your instance:
- Open the Amazon RDS console, and then choose Parameter groups from the navigation pane.
- Choose Create a parameter group, and select the Parameter group family that matches your instance.
- Enter a Group name and Description, and then choose Create.
- Choose the new parameter group name, and enter log_bin_trust_function_creators in the Filter parameters field.
- Choose Edit parameters, and change the Values for log_bin_trust_function_creators to 1.
Note: Be sure that the same parameter group is attached to the target instance that is experiencing the error.
- Choose Save changes.
- Choose Databases from the navigation pane.
- Select your DB instance, and choose Modify.
- Under Database options, choose the new parameter group that you created.
- Choose Continue.
- Choose Apply immediately or Apply during the next scheduled maintenance window.
- Choose Modify cluster.
- Manually reboot your DB instance so that your parameter group status is in-sync.
Note: To apply the changes for the new associated parameter group, you must manually reboot your DB instance. For more information, see Working with DB Parameter Groups.
If binary logging or automated backups are enabled and you receive this error when attempting to import the dump into an Amazon RDS MySQL instance, be sure that you have set the log_bin_trust_function_creators parameter. For more information, see How can I resolve definer errors when importing data to my Amazon RDS for MySQL instance using mysqldump?