ID #1152

Problems with CREATE/ALTER 'stored program' when server binary log is enabled.

You may enocunter the situation where creation/alteration of a 'stored program' (Procedure/Function/Trigger/Event) fails to create even if user has appropirate (like CREATE ROUTINE or ALTER ROUTINE for instance) privilege.

The reason is that those 'basic privileges' are not always enough when binary logging is enabled in the server. Binary logging is used for replication and the restrictions are implemented in the server to ensure that no unwanted change of data takes place on the replicating slave.

MySQL explains:
Server 5.0:  http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html
Server 5.1   http://dev.mysql.com/doc/refman/5.1/en/stored-procedure-logging.html
Server 6.0   http://dev.mysql.com/doc/refman/6.0/en/stored-procedure-logging.html


So this is NOT a SQLyog issue or bug! (and it is not a server bug either!)
 

If either of the errors
 
ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_routine_creators variable)

ERROR 1418 (HY000): 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 1417 (HY000): A routine failed and has neither NO SQL nor
READS SQL DATA in its declaration and binary logging is enabled; if
non-transactional tables were updated, the binary log will miss their
changes

..occur when creating or (for the last message) executing a 'stored program' and when connected to a server with binary logging enabled you may need to do either of those for a 'stored program' to create (and execute) successfully:

* grant SUPER privilege to the user creating the 'stored program'
* explicitly declare the stored program with one or more of the keywords DETERMINISTIC, NO SQL, or READS SQL DATA
* set the server variable log_bin_trust_function_creators = 1 in server configuration.


But before doing so you should understand the reasons for these restrictions in detail.  They were implemented to protect your data!  And finally you should be aware that details depend on the exact server version (and likely we have not seen the last change yet).

Tags: -

Related entries:

You can comment this FAQ