AWS RDS Database Import problem – fixed

Often RDS users trying to import data face this error when they try to create triggers:

ERROR 1419 (HY000) at line 49: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

The reason for this is that the default DB parameter group doesn’t have the privileges set to create and insert triggers into the database. To get rid of this error, do the following:

  1. Obtain RDS CLI tools from AWS website here, and follow the installation steps.
  2. On your terminal (or command line) create a new parameter group (since we can’t directly modify the default parameter group)
    rds-create-db-parameter-group allow-triggers --db-parameter-group-family mysql5.5 --description "parameter group to allow triggers"
  3. Now modify the parameter group created just now
    rds-modify-db-parameter-group allow-triggers --parameters "name=log_bin_trust_function_creators, value=true, method=immediate"
  4. Apply the new group to the database
    rds-modify-db-instance --db-instance-identifier instance-name --db-parameter-group-name allow-triggers --apply-immediately
  5. Finally, you will need to restart your RDS instance.

 

After running the above you should be able to import your schema/data and create triggers without problem. In case you receive the following error while using mysqldump

ERROR 1227 (42000) at line 2492: Access denied; you need (at least one of) the SUPER privilege(s) for this operation.

then, the problem is with the definer statement in your trigger. Change it from

DEFINER=user_a@localhost

to something like

DEFINER=correctuser@ip

Leave a Reply