Bug #11424 Stored procedures are not working when binary logging is enabled
Submitted: 18 Jun 2005 7:29 Modified: 13 Jul 2005 17:52
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.0.7 OS:Linux (Linux i86)
Assigned to: Paul DuBois CPU Architecture:Any

[18 Jun 2005 7:29] [ name withheld ] 
Description:

I've installed mysql 5.0.7 recently for testing stored procedure, view etc. I've configured the mysql server in binary logging mode. when i've executed a simple stored procedure it threw error.

After that i've disabled binary logging mode in my.cnf file and restarted the server.
Now stored procedures are working...

How to repeat:
Enable the log-bin option in my.cnf
restart the server.
try to create any simple stored procedure 

Error 1418 (HY0000) This routine is declared to be non-deterministic and to modify data and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) 

will be appeared
[13 Jul 2005 17:44] Patrick Leary 
you must add log_bin_trust_routine_creators to your ini (Win) or cnf (other platforms) file. This will allow anyone to create functions or stored procedures even if they don't have SUPER privilege. So, think about the security concequence before you turn on that variable.
[13 Jul 2005 17:52] Paul DuBois 
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Stored routine use with respect to binary logging now
is documented here:

http://dev.mysql.com/doc/mysql/en/stored-procedure-logging.html