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: | |
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
[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.