From 3a05847abb0e7a05c3d1997293fe2d332730ed7a Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 8 Jan 2007 22:01:06 +0100 Subject: 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: 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: 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: 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. --- mysql-test/r/rpl_sp.result | 261 ++++++++++++++++++++++++++------------------- mysql-test/t/rpl_sp.test | 50 +++++++-- sql/sp_head.cc | 2 + 3 files changed, 194 insertions(+), 119 deletions(-) diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result index a0464e97e28..0fe1b4b908e 100644 --- a/mysql-test/r/rpl_sp.result +++ b/mysql-test/r/rpl_sp.result @@ -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; diff --git a/mysql-test/t/rpl_sp.test b/mysql-test/t/rpl_sp.test index 373b895a7c1..fc17c5615ef 100644 --- a/mysql-test/t/rpl_sp.test +++ b/mysql-test/t/rpl_sp.test @@ -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 " 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; - - diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 6cb6514a46f..962a56a6dcf 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -1450,6 +1450,8 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, { 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++) -- cgit v1.2.1