MySQL Bugs: #14914: SP: Uses of session variables in routines are not always replicated

Bug #14914	SP: Uses of session variables in routines are not always replicated
Submitted:	14 Nov 2005 8:33	Modified:	9 Mar 2007 14:52
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0-bk	OS:
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
Uses of session variables in routines are not always replicated.
Here is an example where it is not replicated:

drop table if exists t1;
create table t1(a int);

drop function if exists f1;
delimiter //
create function f1() returns int deterministic
begin
  return @a;
end//
delimiter;

set @a=500;
insert into t1 values(f1());

How to repeat:
Run the above provided test case and examine mysqlbinlog output.

Suggested fix:
We actually do have support for catching uses of session variables inside invoked routines. If the above test case is modifed so that the invoked function uses a table, e.g.
 
create function f1() returns int deterministic
begin
  insert into t2 values(@a);
  return 0;
end//

then use of @a variable will be caught and appropriate SET statement will be written into the binlog.

It *seems* that the problem is limited only to case when invoked stored routine doesn't modify non-temporary tables (or something). We need to figure that out and fix it.

Thank you for a problem report. Verified just as described on 5.0.17-BK (ChangeSet@1.1952.17.5, 2005-11-14 17:42:14+03:00, SergeyV@selena.).

mysqlbinlog gave the following after performing tghe described actions:

[openxs@Fedora 5.0]$ bin/mysqlbinlog var/binlog.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#051116 21:49:10 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.17-log created 051116 21:49:10 at startup
ROLLBACK;
# at 98
#051116 21:49:57 server id 1  end_log_pos 184   Query   thread_id=1     exec_time=0     error_code=0
use test;
SET TIMESTAMP=1132170597;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=0;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8;
drop table if exists t1;
# at 184
#051116 21:49:57 server id 1  end_log_pos 269   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1132170597;
create table t1(a int);
# at 269
#051116 21:50:16 server id 1  end_log_pos 401   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1132170616;
create function f1() returns int deterministic
begin
  return @a;
end;
# at 401
#051116 21:50:43 server id 1  end_log_pos 499   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1132170643;
SET @@session.time_zone='SYSTEM';
insert into t1 values(f1());
# at 499
#051116 21:50:55 server id 1  end_log_pos 518   Stop
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

So, setting of the @a value is not in the binlog.

See also BUG#20141

The patch for this bug is satisfied by the patch for BUG#20141. Please see BUG#20141 for more details.

fixed in 5.0.38,5.1.17-beta

"fixed" above meant pushed

A note has been added to thr 5.0.38 and 5.1.17 changelogs.