While creating a trigger, stored procedure getting ERROR 1419 errors:
Got ERROR 1419 (HY000): You do not have the SUPER Privilege and Binary Logging is Enabled “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)”.
In this situation MySQL is trying to protect database, because binary logging is enabled for replication.
Following options are available to fix it:
Option 1, turn on log_bin_trust_function_creators:
When binary logging is enable for replication, MySQL prevent function based data modification / treat functions
be deterministic and not allow data modification.
Following setting of log_bin_trust_function_creators will allow data modification using function, trigger, and stored procedure.
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Ref> https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html
Option 2, grant supper privileges:
Grant super privileges to user running CREATE ROUTINE, ALTER ROUTINE, CREATE TRIGGER, ALTER TRIGGER, CREATE FUNCTION and ALTER FUNCTION
mysql > UPDATE mysql.user SET Super_Priv='Y' WHERE user='user1' AND host='%'; OR
GRANT SUPER ON *.* TO user1@localhost
mysql > FLUSH PRIVILEGES;
Option 3, Start MySQL server with log-bin-trust-function-creators:
--log-bin-trust-function-creators=1
Option 4, tunr of binary logging when not using replication:
mysql> set SET sql_log_bin = OFF
Cluster Validation: select * from performance_schema.replication_group_members; All members should be online. select instance_name, mysql_server_uuid, addresses from mysql_innodb_cluster_metadata.instances; All instances should return same value for mysql_server_uuid SELECT @@GTID_EXECUTED; All nodes should return same value Frequently use commands: mysql> SET SQL_LOG_BIN = 0; mysql> stop group_replication; mysql> set global super_read_only=0; mysql> drop database mysql_innodb_cluster_metadata; mysql> RESET MASTER; mysql> RESET SLAVE ALL; JS > var cluster = dba.getCluster() JS > var cluster = dba.getCluster("<Cluster_name>") JS > var cluster = dba.createCluster('name') JS > cluster.removeInstance('root@<IP_Address>:<Port_No>',{force: true}) JS > cluster.addInstance('root@<IP add>,:<port>') JS > cluster.addInstance('root@ <IP add>,:<port> ') JS > dba.getC...
Comments
Post a Comment