How can I resolve ERROR 1227 when importing data into an Amazon RDS MySQL DB instance that has automated backups enabled?
Last updated: 2020-11-20
I imported the logical backup taken using mysqldump to an Amazon Relational Database Service (Amazon RDS) MySQL DB instance that has automated backups enabled. 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 a stored 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 Amazon RDS custom DB parameter group. For more information, see Modifying parameters in a DB parameter group.
Note: You can't change values in a default parameter group. Instead, associate a custom parameter group to your DB instance if the default parameter group is currently associated with the DB instance. After you associate a new parameter group to your RDS DB instance, you must reboot your DB instance.
If you have a custom parameter group attached to your DB 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 DB 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 DB instance, Amazon RDS applies this change immediately. For more information, see Working with DB parameter groups.
If you have a default parameter group attached to your RDS DB 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 DB 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 you have attached the same parameter group to the target DB 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 DB Instance.
- 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 you enabled binary logging or automated backups and you receive this error when attempting to import the dump into an Amazon RDS MySQL DB 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 DB instance using mysqldump?