there are lot of recommendations over the Internet on how lớn enable SUPER privileges in case if someone hit the following error:

"ERROR 1419 (HY000): You tự not have the SUPER Privilege and Binary Logging is Enabled"

But I wasn't be able lớn find WHY MySQL disables these privileges when binary logging option is on.

Are there some issues with replication if I use e.g. triggers which modify DB or something else? Whether it's safe and, if no, what kind of issues and under which circumstances I can hit if I will return SUPER privileges back? I think there should be some rationale behind this restriction but don't understand which one.

Does anybody have an answer on this?

Thank you.

asked May 31, 2019 at 6:22

1

Here is some detailed explaination I had found in documentation. Hopefully this could help you lớn understand.

The CREATE FUNCTION and INSERT statements are written lớn the binary log, sánh the slave will execute them. Because the slave SQL thread has full privileges, it will execute the dangerous statement. Thus, the function invocation has different effects on the master and slave and is not replication-safe.

To guard against this danger for servers that have binary logging enabled, stored function creators must have the SUPER privilege, in addition lớn the usual CREATE ROUTINE privilege that is required. Similarly, lớn use ALTER FUNCTION, you must have the SUPER privilege in addition lớn the ALTER ROUTINE privilege. Without the SUPER privilege, an error will occur:

ERROR 1419 (HY000): You tự not have the SUPER privilege and
binary logging is enabled (you *might* want lớn use the less safe
log_bin_trust_function_creators variable)

If you tự not want lớn require function creators lớn have the SUPER privilege (for example, if all users with the CREATE ROUTINE privilege on your system are experienced application developers), set the global log_bin_trust_function_creators system variable lớn 1. You can also mix this variable by using the --log-bin-trust-function-creators=1 option when starting the server. If binary logging is not enabled, log_bin_trust_function_creators does not apply. SUPER is not required for function creation unless, as described previously, the DEFINER value in the function definition requires it.

Source: https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html

Ivar

6,73012 gold badges56 silver badges67 bronze badges

answered May 31, 2019 at 6:45

4

Hi if anybody came here lớn find a solution and if you are using Linux

  1. systemctl stop mysqld
  2. Add log_bin_trust_function_creators = 1 lớn my.cnf under /etc
  3. systemctl start mysqld

Dharman♦

33.1k27 gold badges99 silver badges146 bronze badges

answered Sep 29, 2021 at 7:57

1

Is you are using AWS RDS, such as MariaDB.

Set a Parameter Groups a name and mô tả tìm kiếm, using those Familly (MariaDB)

And mix the Parameter Value "log_bin_trust_function_creators" lớn 1.

Save and Reboot the instance RDS.

answered Aug 12, 2023 at 1:47

3

Building on Bawantha's answer and Adam Ježek's comment, a clean way lớn fix the problem is lớn create a tệp tin named 60-trust-function-creators.cnf with

# Fix 'ERROR 1419 (HY000): You tự not have the SUPER privilege and binary logging is enabled'

[mysqld]
log_bin_trust_function_creators = 1

Depending on your system, this tệp tin would go under /etc/mysql/mysql.conf.d/, /etc/mysql/mariadb.conf.d/, or something similar.

The tệp tin doesn't have lớn be that specific name. It can be XX-whatever-your-want.cnf, where 50 < XX < 100.

answered May 26, 2023 at 14:03