MySQL: Cannot create or drop Stored Procedures when binary logs active

Written by - 0 comments

Published on - Listed in Database Linux MySQL


When someone wants to create a Stored Program (like Stored Routine, Stored Procedure, Trigger) in MySQL 5.1 and the MySQL server is running with binary logs active, one of the following errors will most likely appear:

ERROR 1418 (HY000) at line 3: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

ERROR 1419 (HY000) at line 3: 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)

The second error (1419) is actually quite confusing about the SUPER privileges. This error is also shown when the user was granted ALL privileges (GRANT ALL also includes SUPER privileges).

As it is described in the official documentation (19.7 Binary Logging of Stored Programs) the creation should actually work...
But after further research there seems to be a bug open (Bug #39489 Cannot create or drop triggers without SUPER privilege) which discusses this issue when binary logging is active. This bug has been open since September 2008 and is as of today marked as "In Progress".

There seems to be two solutions if one wants to create or drop Stored Programs:

1) Disable binary logging
2) Activate the log_bin_trust variables

The latter one allows to continue binary logging and can be activated like this:

Runtime activation:

mysql> show global variables WHERE Variable_name LIKE 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| log_bin_trust_routine_creators  | OFF   |
+---------------------------------+-------+
3 rows in set (0.00 sec)


mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL log_bin_trust_routine_creators = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec


mysql> show global variables WHERE Variable_name LIKE 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | ON    |
| log_bin_trust_routine_creators  | ON    |
+---------------------------------+-------+
3 rows in set (0.00 sec)

In case of a MySQL restart, this option needs to be set in my.cnf:

log-bin-trust-function-creators = true
log-bin-trust-routine-creators = true

After this, the Stored Program was successfully created in the DB.



Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder