Below is the list of changes that have just been committed into a local
5.0 repository of guilhem. When guilhem does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-01-08 22:01:06+01:00, guilhem@stripped +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.
mysql-test/r/rpl_sp.result@stripped, 2007-01-08 22:01:04+01:00, guilhem@stripped +152 -109
Because I moved the SHOW BINLOG EVENTS down a bit, big portions of its
output move. Also, the function's database name appears in
SELECT statements.
mysql-test/t/rpl_sp.test@stripped, 2007-01-08 22:01:04+01:00, guilhem@stripped +40 -10
Adding test for BUG#19725.
Moving the SHOW BINLOG EVENTS down, it is run at the very end to
test everything.
sql/sp_head.cc@stripped, 2007-01-08 22:01:04+01:00, guilhem@stripped +2 -0
When binlogging a "SELECT myfunc()" (when a stored function is executed
inside a statement which does not go to the binlog (like a SET,
SELECT, DO), we need to write "SELECT db_of_myfunc().myfunc()",
because the function may be in a database which is not the default
database.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: guilhem
# Host: gbichot3.local
# Root: /home/mysql_src/mysql-5.0-rpl-19725
--- 1.21/mysql-test/r/rpl_sp.result 2007-01-08 22:01:11 +01:00
+++ 1.22/mysql-test/r/rpl_sp.result 2007-01-08 22:01:11 +01:00
@@ -269,6 +269,118 @@ insert into t1 values (1);
select * from t1;
a
1
+select * from t1;
+a
+1
+create procedure foo()
+not deterministic
+reads sql data
+select * from t1;
+call foo();
+a
+1
+drop procedure foo;
+drop function fn1;
+drop database mysqltest1;
+drop user "zedjzlcsjhd"@127.0.0.1;
+use test;
+use test;
+drop function if exists f1;
+create function f1() returns int reads sql data
+begin
+declare var integer;
+declare c cursor for select a from v1;
+open c;
+fetch c into var;
+close c;
+return var;
+end|
+create view v1 as select 1 as a;
+create table t1 (a int);
+insert into t1 (a) values (f1());
+select * from t1;
+a
+1
+drop view v1;
+drop function f1;
+select * from t1;
+a
+1
+DROP PROCEDURE IF EXISTS p1;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1(col VARCHAR(10));
+CREATE PROCEDURE p1(arg VARCHAR(10))
+INSERT INTO t1 VALUES(arg);
+CALL p1('test');
+SELECT * FROM t1;
+col
+test
+SELECT * FROM t1;
+col
+test
+DROP PROCEDURE p1;
+
+---> Test for BUG#20438
+
+---> Preparing environment...
+---> connection: master
+DROP PROCEDURE IF EXISTS p1;
+DROP FUNCTION IF EXISTS f1;
+
+---> Synchronizing slave with master...
+
+---> connection: master
+
+---> Creating procedure...
+/*!50003 CREATE PROCEDURE p1() SET @a = 1 */;
+/*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */;
+
+---> Checking on master...
+SHOW CREATE PROCEDURE p1;
+Procedure sql_mode Create Procedure
+p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+SET @a = 1
+SHOW CREATE FUNCTION f1;
+Function sql_mode Create Function
+f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
+RETURN 0
+
+---> Synchronizing slave with master...
+---> connection: master
+
+---> Checking on slave...
+SHOW CREATE PROCEDURE p1;
+Procedure sql_mode Create Procedure
+p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+SET @a = 1
+SHOW CREATE FUNCTION f1;
+Function sql_mode Create Function
+f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
+RETURN 0
+
+---> connection: master
+
+---> Cleaning up...
+DROP PROCEDURE p1;
+DROP FUNCTION f1;
+drop table t1;
+drop database if exists mysqltest;
+drop database if exists mysqltest2;
+create database mysqltest;
+create database mysqltest2;
+use mysqltest2;
+create table t ( t integer );
+create procedure mysqltest.test() begin end;
+insert into t values ( 1 );
+create procedure `\\`.test() begin end;
+ERROR 42000: Incorrect database name '\\'
+create function f1 () returns int
+begin
+insert into t values (1);
+return 0;
+end|
+use mysqltest;
+set @a:= mysqltest2.f1();
show binlog events in 'master-bin.000001' from 98;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query 1 # drop database if exists mysqltest1
@@ -329,7 +441,7 @@ insert into t1 values (x);
return x+2;
end
master-bin.000001 # Query 1 # use `mysqltest1`; delete t1,t2 from t1,t2
-master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `fn1`(20)
+master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(fn1(21))
master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1
master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1()
@@ -362,32 +474,23 @@ begin
insert into t2 values(x),(x);
return 10;
end
-master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `fn1`(100)
-master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `fn1`(20)
+master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(100)
+master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1
master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10
master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (1)
master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1
master-bin.000001 # Query 1 # use `mysqltest1`; drop trigger trg
master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (1)
-select * from t1;
-a
-1
-create procedure foo()
+master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo()
not deterministic
reads sql data
-select * from t1;
-call foo();
-a
-1
-drop procedure foo;
-drop function fn1;
-drop database mysqltest1;
-drop user "zedjzlcsjhd"@127.0.0.1;
-use test;
-use test;
-drop function if exists f1;
-create function f1() returns int reads sql data
+select * from t1
+master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo
+master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1
+master-bin.000001 # Query 1 # drop database mysqltest1
+master-bin.000001 # Query 1 # drop user "zedjzlcsjhd"@127.0.0.1
+master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` function f1() returns int reads sql data
begin
declare var integer;
declare c cursor for select a from v1;
@@ -395,97 +498,37 @@ open c;
fetch c into var;
close c;
return var;
-end|
-create view v1 as select 1 as a;
-create table t1 (a int);
-insert into t1 (a) values (f1());
-select * from t1;
-a
-1
-drop view v1;
-drop function f1;
-select * from t1;
-a
-1
-DROP PROCEDURE IF EXISTS p1;
-DROP TABLE IF EXISTS t1;
-CREATE TABLE t1(col VARCHAR(10));
-CREATE PROCEDURE p1(arg VARCHAR(10))
-INSERT INTO t1 VALUES(arg);
-CALL p1('test');
-SELECT * FROM t1;
-col
-test
-SELECT * FROM t1;
-col
-test
-DROP PROCEDURE p1;
-
----> Test for BUG#20438
-
----> Preparing environment...
----> connection: master
-DROP PROCEDURE IF EXISTS p1;
-DROP FUNCTION IF EXISTS f1;
-
----> Synchronizing slave with master...
-
----> connection: master
-
----> Creating procedure...
-/*!50003 CREATE PROCEDURE p1() SET @a = 1 */;
-/*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */;
-
----> Checking on master...
-SHOW CREATE PROCEDURE p1;
-Procedure sql_mode Create Procedure
-p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
-SET @a = 1
-SHOW CREATE FUNCTION f1;
-Function sql_mode Create Function
-f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
-RETURN 0
-
----> Synchronizing slave with master...
----> connection: master
-
----> Checking on slave...
-SHOW CREATE PROCEDURE p1;
-Procedure sql_mode Create Procedure
-p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
-SET @a = 1
-SHOW CREATE FUNCTION f1;
-Function sql_mode Create Function
-f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
-RETURN 0
-
----> connection: master
-
----> Cleaning up...
-DROP PROCEDURE p1;
-DROP FUNCTION f1;
-drop table t1;
+end
+master-bin.000001 # Query 1 # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 as a
+master-bin.000001 # Query 1 # use `test`; create table t1 (a int)
+master-bin.000001 # Query 1 # use `test`; insert into t1 (a) values (f1())
+master-bin.000001 # Query 1 # use `test`; drop view v1
+master-bin.000001 # Query 1 # use `test`; drop function f1
+master-bin.000001 # Query 1 # use `test`; DROP TABLE IF EXISTS t1
+master-bin.000001 # Query 1 # use `test`; CREATE TABLE t1(col VARCHAR(10))
+master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE p1(arg VARCHAR(10))
+INSERT INTO t1 VALUES(arg)
+master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test'))
+master-bin.000001 # Query 1 # use `test`; DROP PROCEDURE p1
+master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE p1() SET @a = 1
+master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION f1() RETURNS INT RETURN 0
+master-bin.000001 # Query 1 # use `test`; DROP PROCEDURE p1
+master-bin.000001 # Query 1 # use `test`; DROP FUNCTION f1
+master-bin.000001 # Query 1 # use `test`; drop table t1
+master-bin.000001 # Query 1 # drop database if exists mysqltest
+master-bin.000001 # Query 1 # drop database if exists mysqltest2
+master-bin.000001 # Query 1 # create database mysqltest
+master-bin.000001 # Query 1 # create database mysqltest2
+master-bin.000001 # Query 1 # use `mysqltest2`; create table t ( t integer )
+master-bin.000001 # Query 1 # use `mysqltest2`; CREATE DEFINER=`root`@`localhost` procedure mysqltest.test() begin end
+master-bin.000001 # Query 1 # use `mysqltest2`; insert into t values ( 1 )
+master-bin.000001 # Query 1 # use `mysqltest2`; CREATE DEFINER=`root`@`localhost` function f1 () returns int
+begin
+insert into t values (1);
+return 0;
+end
+master-bin.000001 # Query 1 # use `mysqltest`; SELECT `mysqltest2`.`f1`()
set global log_bin_trust_function_creators=0;
set global log_bin_trust_function_creators=0;
-reset master;
-drop database if exists mysqltest;
-drop database if exists mysqltest2;
-create database mysqltest;
-create database mysqltest2;
-use mysqltest2;
-create table t ( t integer );
-create procedure mysqltest.test() begin end;
-insert into t values ( 1 );
-show binlog events in 'master-bin.000001' from 98;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 98 Query 1 199 drop database if exists mysqltest
-master-bin.000001 199 Query 1 302 drop database if exists mysqltest2
-master-bin.000001 302 Query 1 395 create database mysqltest
-master-bin.000001 395 Query 1 490 create database mysqltest2
-master-bin.000001 490 Query 1 587 use `mysqltest2`; create table t ( t integer )
-master-bin.000001 587 Query 1 726 use `mysqltest2`; CREATE DEFINER=`root`@`localhost` procedure mysqltest.test() begin end
-master-bin.000001 726 Query 1 821 use `mysqltest2`; insert into t values ( 1 )
-create procedure `\\`.test() begin end;
-ERROR 42000: Incorrect database name '\\'
drop database mysqltest;
drop database mysqltest2;
--- 1.16/mysql-test/t/rpl_sp.test 2007-01-08 22:01:11 +01:00
+++ 1.17/mysql-test/t/rpl_sp.test 2007-01-08 22:01:11 +01:00
@@ -338,12 +338,13 @@ delete from t1;
drop trigger trg;
insert into t1 values (1);
select * from t1;
---replace_column 2 # 5 #
-show binlog events in 'master-bin.000001' from 98;
sync_slave_with_master;
select * from t1;
+# ********************** PART 4 : RELATED FIXED BUGS ***************
+
+
#
# Test for bug #13969 "Routines which are replicated from master can't be
# executed on slave".
@@ -520,15 +521,11 @@ connection master;
drop table t1;
sync_slave_with_master;
-# Restore log_bin_trust_function_creators to original value
-set global log_bin_trust_function_creators=0;
-connection master;
-set global log_bin_trust_function_creators=0;
#
# Bug22043: MySQL don't add "USE <DATABASE>" before "DROP PROCEDURE IF EXISTS"
#
+
connection master;
-reset master;
--disable_warnings
drop database if exists mysqltest;
drop database if exists mysqltest2;
@@ -539,11 +536,44 @@ use mysqltest2;
create table t ( t integer );
create procedure mysqltest.test() begin end;
insert into t values ( 1 );
-show binlog events in 'master-bin.000001' from 98;
--error ER_WRONG_DB_NAME
create procedure `\\`.test() begin end;
+
+#
+# BUG#19725: Calls to stored function in other database are not
+# replicated correctly in some cases
+#
+
+connection master;
+delimiter |;
+create function f1 () returns int
+begin
+ insert into t values (1);
+ return 0;
+end|
+delimiter ;|
+sync_slave_with_master;
+# Let us test if we don't forget to binlog the function's database
+connection master;
+use mysqltest;
+set @a:= mysqltest2.f1();
+sync_slave_with_master;
+connection master;
+
+
+# Final inspection which verifies how all statements of this test file
+# were written to the binary log.
+--replace_column 2 # 5 #
+show binlog events in 'master-bin.000001' from 98;
+
+
+# Restore log_bin_trust_function_creators to its original value.
+# This is a cleanup for all parts above where we tested stored
+# functions and triggers.
+set global log_bin_trust_function_creators=0;
+connection master;
+set global log_bin_trust_function_creators=0;
+
# Clean up
drop database mysqltest;
drop database mysqltest2;
-
-
--- 1.227/sql/sp_head.cc 2007-01-08 22:01:11 +01:00
+++ 1.228/sql/sp_head.cc 2007-01-08 22:01:11 +01:00
@@ -1450,6 +1450,8 @@ sp_head::execute_function(THD *thd, Item
{
binlog_buf.length(0);
binlog_buf.append(STRING_WITH_LEN("SELECT "));
+ append_identifier(thd, &binlog_buf, m_db.str, m_db.length);
+ binlog_buf.append('.');
append_identifier(thd, &binlog_buf, m_name.str, m_name.length);
binlog_buf.append('(');
for (arg_no= 0; arg_no < argcount; arg_no++)
| Thread |
|---|
| • bk commit into 5.0 tree (guilhem:1.2350) BUG#19725 | Guilhem Bichot | 8 Jan |
