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

Written by Claudio Kuenzler - 0 comments

Published on January 7th 2013 - 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.