Bug #60142 Unsafe stored routines breaking binary logging between 5.0 master and 5.1 slave
Submitted: 16 Feb 2011 13:41 Modified: 16 Mar 2011 19:31
Reporter: Jonathan Levin Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1.52 OS:Linux (Redhat Enterprise 6.0)
Assigned to: CPU Architecture:Any

[16 Feb 2011 13:41] Jonathan Levin
Description:
I had tried to create a slave DB server using mysql 5.1.52 from a mysql master using 5.0.56. 
Apparently, a long time ago, someone had developed a function which had an UPDATE statement inside of it. Something like this:

CREATE FUNCTION `next_id`( sInput CHAR(15) ) RETURNS INT(11)
BEGIN
			SET @iVal = NULL;
			UPDATE test.test SET `last_id` = @iVal:= `last_id`+1 WHERE `name` = sInput;
			RETURN @iVal;
		END$$

After connecting the slave to the master and it had started replicating, I received this error: 

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its 
declaration and binary.

I then ran:
set global log_bin_trust_function_creators=1;

This allowed replication to continue from the master to this 5.1 slave.

However, then was a plan to add another slave directly to this 5.1 slave.
I had written to the my.cnf the settings:
log-bin
log-bin-index
relay-log
relay-log-index
log-slave-updates

After turning on global log_bin_trust_function_creators, the binary log stopped logging.
When I ran show master status, I could see that the numbers are not incrementing compared to when I did show slave status, where I could see them incrementing.

How to repeat:
Setup mysql 5.0.56 as master
Add funtion:

CREATE FUNCTION `next_id`( sInput CHAR(15) ) RETURNS INT(11)
BEGIN
			SET @iVal = NULL;
			UPDATE test.test SET `last_id` = @iVal:= `last_id`+1 WHERE `name` = sInput;
			RETURN @iVal;
END$$

Use function within an update/insert statement on master.

Setup mysql slave 5.1.52 but allow log-bin and log_slave_updates.
Configure replication.

Run on slave: 
set global log_bin_trust_function_creators=1; 

Suggested fix:
It appears that if I write log_bin_trust_function_creators to the my.cnf file that I get better results.

Current my.cnf file:

log-bin
log-bin-trust-function-creators
log-slave-updates
log-bin-index
relay-log-index
relay-log = relay-log.info
[16 Feb 2011 19:31] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Additionally version 5.1.52 is old. Please try with current version 5.1.55 and inform us if problem still exists.
[17 Mar 2011 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".