Bug #19725 Calls to SF in other database are not replicated correctly in some cases
Submitted: 11 May 2006 13:20 Modified: 23 Jan 2007 20:21
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.22-bk OS:Linux (Linux)
Assigned to: Guilhem Bichot CPU Architecture:Any

[11 May 2006 13:20] Dmitry Lenev
Description:
Calls to stored functions which reside in database which is different from current database and which are done from statements that don't usually go to binlog (like SET or SELECT) are not replicated correctly.

See "How to repeat" section for more info.

How to repeat:
# This is contents of .test file for MySQL test suite
# Be sure that file is called rpl_something.test otherwise slave won't be started.
source include/master-slave.inc;

# Prepare playground
--disable_warnings
drop database if exists mysqltest;
drop function if exists f1;
drop table if exists t1;
--enable_warnings
create table t1 (i int);
delimiter |;
create function f1 () returns int
begin
  insert into t1 values (1);
  return 0;
end|
delimiter ;|
create database mysqltest;
# Sync slave and master
save_master_pos;
connection slave;
sync_with_master;

# Let us test how we replicate function call for routine
# which resides in other DB
connection master;
use mysqltest;
set @a:= test.f1();

# This will show that binlog is wrong
# (f1() is assumed to be in wrong database)
show binlog events;

# We can't do this because slave won't be able to find f1()
# in mysqltest database.
# save_master_pos;
# connection slave;
# sync_with_master;

Suggested fix:
We should store fully-qualified names of functions in binary log in this case.
[11 May 2006 15:14] Valeriy Kravchuk
Verified on Linux (manually :):

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (i int);
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql>  create function f1 () returns int deterministic
    -> begin
    ->   insert into t1 values (1);
    ->   return 0;
    -> end//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> create database mysqltest;
Query OK, 1 row affected (0.01 sec)

mysql> use mysqltest;
Database changed
mysql> set @a := test.f1();
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysqlbinlog var/suse-bin.000001

...

#060511 13:35:25 server id 1  end_log_pos 349   Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1147343725;
create table t1 (i int);
# at 349
#060511 13:36:36 server id 1  end_log_pos 537   Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1147343796;
CREATE DEFINER=`root`@`localhost` function f1 () returns int deterministic
begin
  insert into t1 values (1);
  return 0;
end;
# at 537
#060511 13:36:47 server id 1  end_log_pos 630   Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1147343807;
create database mysqltest;
# at 630
#060511 13:37:00 server id 1  end_log_pos 719   Query   thread_id=3     exec_time=0     error_code=0
use mysqltest;
SET TIMESTAMP=1147343820;
SET @@session.time_zone='SYSTEM';
SELECT `f1`();
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

It is a bug.
[8 Jan 2007 14:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17731

ChangeSet@1.2350, 2007-01-08 16:56:30+01:00, guilhem@gbichot3.local +3 -0
  Fix for BUG#19725 "Calls to SF in other database are not replicated
  correctly in some cases".
  In short, calls to a stored function located in another database
  than the default database, may fail to replicate if the call was made
  by SET, SELECT, or DO.
  Longer: when a stored function is called from a statement which does not go
  to binlog ("SET @a=somedb.myfunc()", "SELECT somedb.myfunc()",
  "DO somedb.myfunc()"), this crafted statement is binlogged:
  "SELECT myfunc();" (accompanied with a mention of the default database
  if there is one). So, if "somedb" is not the default database,
  the slave would fail to find myfunc(). The fix is to specify the
  function's database name in the crafted binlogged statement, like this:
  "SELECT somedb.myfunc();". Test added in rpl_sp.test.
[8 Jan 2007 19:29] Chuck Bell
Patch reviewed on 5.0 on SUSE 32-bit. It looks fine.
[8 Jan 2007 20:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17744

ChangeSet@1.2350, 2007-01-08 22:01:06+01:00, guilhem@gbichot3.local +3 -0
  Fix for BUG#19725 "Calls to SF in other database are not replicated
  correctly in some cases".
  In short, calls to a stored function located in another database
  than the default database, may fail to replicate if the call was made
  by SET, SELECT, or DO.
  Longer: when a stored function is called from a statement which does not go
  to binlog ("SET @a=somedb.myfunc()", "SELECT somedb.myfunc()",
  "DO somedb.myfunc()"), this crafted statement is binlogged:
  "SELECT myfunc();" (accompanied with a mention of the default database
  if there is one). So, if "somedb" is not the default database,
  the slave would fail to find myfunc(). The fix is to specify the
  function's database name in the crafted binlogged statement, like this:
  "SELECT somedb.myfunc();". Test added in rpl_sp.test.
[8 Jan 2007 21:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17751

ChangeSet@1.2371, 2007-01-08 23:52:21+01:00, guilhem@gbichot3.local +3 -0
  Manual merge of the fix for BUG#19725 "Calls to SF in other database are not replicated
  correctly in some cases", from 5.0.
  In short, calls to a stored function located in another database
  than the default database, may fail to replicate if the call was made
  by SET, SELECT, or DO.
  sp_head.cc automerged, only the test and test's result had to be hand-merged.
[8 Jan 2007 22:00] Guilhem Bichot
fix pushed to 5.0-rpl and 5.1-new-rpl.
Documentation:
  In short, calls to a stored function located in another database
  than the default database, may fail to replicate if the call was made
  by SET, SELECT, or DO.
  Longer: when a stored function is called from a statement which does not go
  to binlog ("SET @a=somedb.myfunc()", "SELECT somedb.myfunc()",
  "DO somedb.myfunc()"), this crafted statement is binlogged:
  "SELECT myfunc();" (accompanied with a mention of the default database
  if there is one). So, if "somedb" is not the default database,
  the slave would fail to find myfunc(). The fix is to specify the
  function's database name in the crafted binlogged statement, like this:
  "SELECT somedb.myfunc();". Test added in rpl_sp.test.
[18 Jan 2007 15:27] Lars Thalmann
Pushed into 5.0.36, 5.1.15
[23 Jan 2007 20:21] Paul Dubois
Noted in 5.0.36, 5.1.15 changelogs.

For SET, SELECT, and DO statements that invoked a stored function from
a database other than the default database, the function invocation
could fail to be replicated.