How can I resolve ERROR 1227 when importing data into an Amazon RDS MySQL instance with automated backups enabled?

Last updated: 2020-06-24

I imported the logical backup taken using mysqldump to an Amazon Relational Database Service (Amazon RDS) for MySQL instance with 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."

Short description

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.

Resolution

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 RDS DB instance if the default parameter group is currently associated with the RDS DB instance. After you associate a new parameter group to your RDS DB instance, you must reboot your RDS DB instance.

If you have a custom parameter group attached to your RDS DB instance, then:

  1. Open the Amazon RDS console, and choose Parameter groups from the navigation pane.
  2. Choose the custom parameter group name that is associated to your RDS DB instance.
  3. Enter log_bin_trust_function_creators in the Filter parameters field, and then choose Edit Parameters.
  4. Change the Values for log_bin_trust_function_creators to 1.
  5. Choose Save changes.
    Note: Because this parameter is dynamic and the customer parameter group is already associated to your RDS DB instance, 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:

  1. Open the Amazon RDS console, and then choose Parameter groups from the navigation pane.
  2. Choose Create a parameter group, and select the Parameter group family that matches your RDS DB instance.
  3. Enter a Group name and Description, and then choose Create.
  4. Choose the new parameter group name, and enter log_bin_trust_function_creators in the Filter parameters field.
  5. 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 RDS DB instance that is experiencing the error.
  6. Choose Save changes.
  7. Choose Databases from the navigation pane.
  8. Select your RDS DB instance, and choose Modify.
  9. Under Database options, choose the new parameter group that you created.
  10. Choose Continue.
  11. Choose Apply immediately or Apply during the next scheduled maintenance window.
  12. Choose Modify DB Instance.
  13. Manually reboot your RDS 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 RDS DB instance. For more information, see Working with DB parameter groups.

If you have enabled binary logging or automated backups and you receive this error when attempting to import the dump into an Amazon RDS for 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?


Did this article help you?

Anything we could improve?


Need more help?