thecfguy

A Unique Developer

AWS RDS (MySQL) error you *might* want to use the less safe log_bin_trust_function_creators variable) for function creation.

Recently moving one of our client to AWS Cloud for project built on RAILO and MYSQL. Maninly two issue face during MYSQL migration. 

  1. Default timezone for MySql is UTC and there is not way to change per RDS instance.
  2. Error occured while creating function with message "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)"

For first error there are lots of conversation why AWS do not provide ability to change timezone since most of the project are do not required locale and programmer store local timezone date instead of UTC to avoid conversion of date whenever retrieved it from database but at the time of writing there is no option to change timezone. Only solution I found that is instead of using MySql now() function use RAILO in built NOW() function to pass into query. Since mysql do not store timestamp in datetime field it will store as it is and you can easily work on it.

 

For second error, fortunately there is a solution with help of parameter group. There will be default parameter group setup by AWS whenever RDS instance generated which can not be edited but you can create your own and change parameter 'log_bin_trust_function_creators' value to 1. 

Once you added new parameter group go to instance tab and apply to your mysql instance by right clicking on instance >> Modify. Choose new parameter group in dropdown and save changes. Once saved you will see on console displaying (Applying...) and after sometime it will be change to "reboot required". You need to restart your instance and you are done.