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

Symptoms

On the import of the function or trigger to MySQL database, the following error arises: “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)”.

Cause

The error arises when you try to import the function or trigger containing dangerous statements that make changes in the database. The error arises only if the binary logging option, which is required for the replication, is turned on for the MySQL server.

Solutions

There are several solutions for the issue:

• You need to specify the SUPER privileges for the user running the import into the database along with the CREATE ROUTINE, ALTER ROUTINE, CREATE TRIGGER, ALTER TRIGGER, CREATE FUNCTION and ALTER FUNCTION privileges applied;
• If you want to allow all the users on your MySQL server that has the CREATE ROUTINE privilege to have the possibility to create such functions, you can specify the log_bin_trust_function_creators option by two ways:
  • by specifying it on the server start, like: --log-bin-trust-function-creators=1
  • by setting it to 1 through the SET GLOBAL statement, like:

 mysql> SET GLOBAL log_bin_trust_function_creators = 1;

• If you are not planning to use your MySQL server for the replication consider turning the binary logging off by removing the option --log-bin from the command options for the mysqld utility starting the MySQL server.

SQLWays Product Page - Migration to MySQL Free Evaluation