"You do not have the SUPER privilege and binary logging is enabled"

[thbar]

Hello,

we migrated from a RDS-backed MySQL 5.5 to a newer generation RDS MySQL 5.6, and the first attempt to add an index using lhm gives us this error:

I, [2014-10-20T13:56:11.100569 #8934]  INFO -- : Starting LHM run on table=lhmn_pushes
I, [2014-10-20T13:56:11.102629 #8934]  INFO -- : Starting run of class=Lhm::Migrator
I, [2014-10-20T13:56:11.344552 #8934]  INFO -- : Starting run of class=Lhm::Entangler
E, [2014-10-20T13:56:11.350324 #8934] ERROR -- : Error in class=Lhm::Entangler, reverting. exception=ActiveRecord::StatementInvalid message=Mysql2::Error: 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): create trigger `lhmt_del_pushes`
after delete on `pushes` for each row
delete ignore from `lhmn_pushes` /* large hadron migration */
where `lhmn_pushes`.`id` = OLD.`id` /* large hadron migration */
rake aborted!
An error has occurred, all later migrations canceled:

Mysql2::Error: 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): drop trigger if exists `lhmt_del_pushes` /* large hadron migration */

After discussing this with my client's sysadmin, SUPER cannot be used on RDS apparently, and binary logging is always on for point in time recovery.

This is also the first time we use LHM to add an index (previously it was just for columns).

Is the error message reliable here?

Did anyone meet this issue and can it be solved?

thanks!

[thbar]

I finally understood: the latest gen of RDS MySQL has triggers disabled (while our first gen apparently had it enabled).

The fix seems to be to set log_bin_trust_function_creators to 1 indeed.

[arthurnn]

will close this one, as I can say it is a duplicate of #65

[mnpenner]

For others that are stumbling across this issue via google, run this:

mysql -u USERNAME -p
set global log_bin_trust_function_creators=1;

[nickdowse]

To follow up on @mnpenner's comment: When using RDS you have to create a new RDS parameter group (this can be done from the RDS dashboard on the web), set log_bin_trust_function_creators to 1 (as opposed to <engine-default>, save the parameter group.

Then modify your RDS instance to use this parameter group, save, and reboot your RDS instance and it should work. You can read more here: https://forums.aws.amazon.com/message.jspa?messageID=183618, and http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html.

[ka4a]

sed -ie "s/DEFINER=`a-z0-9A-Z*`@`a-z0-9A-Z*`/DEFINER=CURRENT_USER /g sqldump.sql

This help me with my backup file. succesful import.

[leobut]

If you are facing this issue and your database is on Google Cloud, follow this guide to set the log_bin_trust_function_creators flag of your database to true: https://cloud.google.com/sql/docs/mysql/flags

[Mohamed-Nusry]

So you have to add SET GLOBAL log_bin_trust_function_creators = 1;

[gioudi]

Command [SET GLOBAL log_bin_trust_function_creators = 1;] is not working for me: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

[oh-andres-zepeda]

If using AWS RDS, this error can be bypassed by following these instructions from amazon https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/

[rushiikesh]

I finally understood: the latest gen of RDS MySQL has triggers disabled (while our first gen apparently had it enabled).

The fix seems to be to set log_bin_trust_function_creators to 1 indeed.

really help me thanks alot