From: Date: November 17 2006 9:30pm Subject: bk commit into 5.1 tree (tnurnberg:1.2372) BUG#16456 List-Archive: http://lists.mysql.com/commits/15523 X-Bug: 16456 Message-Id: Below is the list of changes that have just been committed into a local 5.1 repository of tnurnberg. When tnurnberg 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, 2006-11-17 21:30:28+01:00, tnurnberg@stripped +26 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Fix tests for new behaviour: an error is thrown if a NON DETERMINISTIC stored function (SF) is called during statement-based replication (SBR). mysql-test/r/func_time.result@stripped, 2006-11-17 21:28:42+01:00, tnurnberg@stripped +2 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/r/gis.result@stripped, 2006-11-17 21:28:42+01:00, tnurnberg@stripped +2 -1 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Mark function as DETERMINISTIC so it can be called with no error while doing statement-based replication (SBR). mysql-test/r/grant2.result@stripped, 2006-11-17 21:28:42+01:00, tnurnberg@stripped +4 -2 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/r/innodb_notembedded.result@stripped, 2006-11-17 21:28:42+01:00, tnurnberg@stripped +2 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/r/ps.result@stripped, 2006-11-17 21:28:42+01:00, tnurnberg@stripped +2 -2 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Mark function as DETERMINISTIC so it can be called with no error while doing statement-based replication (SBR). mysql-test/r/query_cache.result@stripped, 2006-11-17 21:28:43+01:00, tnurnberg@stripped +2 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/r/query_cache_notembedded.result@stripped, 2006-11-17 21:28:43+01:00, tnurnberg@stripped +2 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/r/rpl_sf.result@stripped, 2006-11-17 21:30:21+01:00, tnurnberg@stripped +23 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Test for stored function (SF) calls in different replication modes. NON DETERMINISTIC SFs are not allowed while doing statement-based replication (SBR). mysql-test/r/rpl_sf.result@stripped, 2006-11-17 21:30:21+01:00, tnurnberg@stripped +0 -0 mysql-test/r/rpl_sp.result@stripped, 2006-11-17 21:28:43+01:00, tnurnberg@stripped +0 -18 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Move test for SF-calls in different replication modes to its own file, rpl_sf. mysql-test/r/rpl_sp_effects.result@stripped, 2006-11-17 21:28:43+01:00, tnurnberg@stripped +2 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/r/sp.result@stripped, 2006-11-17 21:28:43+01:00, tnurnberg@stripped +1 -17 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Mark function as DETERMINISTIC so it can be called with no error while doing statement-based replication (SBR). mysql-test/r/timezone2.result@stripped, 2006-11-17 21:28:43+01:00, tnurnberg@stripped +2 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/t/func_time.test@stripped, 2006-11-17 21:28:43+01:00, tnurnberg@stripped +4 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/t/gis.test@stripped, 2006-11-17 21:28:43+01:00, tnurnberg@stripped +1 -1 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Mark function as DETERMINISTIC so it can be called with no error while doing statement-based replication (SBR). mysql-test/t/grant2.test@stripped, 2006-11-17 21:28:43+01:00, tnurnberg@stripped +6 -3 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/t/innodb_notembedded.test@stripped, 2006-11-17 21:29:30+01:00, tnurnberg@stripped +2 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/t/ps.test@stripped, 2006-11-17 21:29:30+01:00, tnurnberg@stripped +2 -2 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/t/query_cache.test@stripped, 2006-11-17 21:29:30+01:00, tnurnberg@stripped +2 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/t/query_cache_notembedded.test@stripped, 2006-11-17 21:29:30+01:00, tnurnberg@stripped +3 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/t/rpl_sf.test@stripped, 2006-11-17 21:30:21+01:00, tnurnberg@stripped +68 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Test for stored function (SF) calls in different replication modes. NON DETERMINISTIC SFs are not allowed while doing statement-based replication (SBR). mysql-test/t/rpl_sf.test@stripped, 2006-11-17 21:30:21+01:00, tnurnberg@stripped +0 -0 mysql-test/t/rpl_sp.test@stripped, 2006-11-17 21:29:30+01:00, tnurnberg@stripped +1 -29 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/t/rpl_sp_effects.test@stripped, 2006-11-17 21:30:21+01:00, tnurnberg@stripped +4 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). mysql-test/t/sp.test@stripped, 2006-11-17 21:30:21+01:00, tnurnberg@stripped +1 -1 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Mark function as DETERMINISTIC so it can be called with no error while doing statement-based replication (SBR). mysql-test/t/timezone2.test@stripped, 2006-11-17 21:30:21+01:00, tnurnberg@stripped +4 -0 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Modify test that contains non-deterministic functions so it can still be called with no error while doing statement-based replication (SBR). sql/item_func.cc@stripped, 2006-11-17 21:30:21+01:00, tnurnberg@stripped +1 -1 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR If 'log_bin_trust_function_creators' variable is set, don't throw an error on calling a non-deterministc function in statement-based replication (SBR). sql/sql_parse.cc@stripped, 2006-11-17 21:30:21+01:00, tnurnberg@stripped +0 -12 Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR Do not throw an error on calling a NON DETERMINISTIC stored procedure (SP) while doing statement-based replication (SBR), as the routine body is executed statement-by-statement. # 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: tnurnberg # Host: salvation.intern.azundris.com # Root: /home/tnurnberg/work/mysql-5.1-maint-16456 --- 1.333/sql/item_func.cc 2006-11-15 18:29:20 +01:00 +++ 1.334/sql/item_func.cc 2006-11-17 21:30:21 +01:00 @@ -5003,7 +5003,7 @@ Item_func_sp::execute_impl(THD *thd, Fie Throw an error if a non-deterministic function is called while statement-based replication (SBR) is active. */ - if (!m_sp->m_chistics->detistic && + if (!m_sp->m_chistics->detistic && !trust_function_creators && (mysql_bin_log.is_open() && thd->variables.binlog_format == BINLOG_FORMAT_STMT)) { --- 1.595/sql/sql_parse.cc 2006-11-15 15:46:43 +01:00 +++ 1.596/sql/sql_parse.cc 2006-11-17 21:30:21 +01:00 @@ -4576,18 +4576,6 @@ end_with_restore_list: goto error; } - /* - Throw an error if a non-deterministic procedure is called while - statement-based replication (SBR) is active. - */ - if (!sp->m_chistics->detistic && - (mysql_bin_log.is_open() && - thd->variables.binlog_format == BINLOG_FORMAT_STMT)) - { - my_error(ER_BINLOG_ROW_RBR_TO_SBR, MYF(0)); - goto error; - } - my_bool nsok= thd->net.no_send_ok; thd->net.no_send_ok= TRUE; if (sp->m_flags & sp_head::MULTI_RESULTS) --- 1.17/mysql-test/r/timezone2.result 2006-11-01 15:50:21 +01:00 +++ 1.18/mysql-test/r/timezone2.result 2006-11-17 21:28:43 +01:00 @@ -269,6 +269,7 @@ select * from t1; convert_tz(NULL, NULL, NULL) NULL drop table t1; +SET GLOBAL log_bin_trust_function_creators = 1; create table t1 (ldt datetime, udt datetime); create function f1(i datetime) returns datetime return convert_tz(i, 'UTC', 'Europe/Moscow'); @@ -283,3 +284,4 @@ ldt ldt2 2006-04-19 16:30:00 2006-04-19 16:30:00 drop table t1; drop function f1; +SET GLOBAL log_bin_trust_function_creators = 0; --- 1.12/mysql-test/t/timezone2.test 2006-11-01 15:50:21 +01:00 +++ 1.13/mysql-test/t/timezone2.test 2006-11-17 21:30:21 +01:00 @@ -228,6 +228,8 @@ drop table t1; # Test for bug #11081 "Using a CONVERT_TZ function in a stored function # or trigger fails". # +SET GLOBAL log_bin_trust_function_creators = 1; + create table t1 (ldt datetime, udt datetime); create function f1(i datetime) returns datetime return convert_tz(i, 'UTC', 'Europe/Moscow'); @@ -240,5 +242,7 @@ select * from t1; select ldt, f1(udt) as ldt2 from t1; drop table t1; drop function f1; + +SET GLOBAL log_bin_trust_function_creators = 0; # End of 5.0 tests --- 1.24/mysql-test/r/rpl_sp.result 2006-11-15 16:08:43 +01:00 +++ 1.25/mysql-test/r/rpl_sp.result 2006-11-17 21:28:43 +01:00 @@ -124,9 +124,6 @@ select * from mysql.proc where name="foo db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment select * from mysql.proc where name="foo4" and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment -set binlog_format=STATEMENT; -call foo(); -ERROR HY000: Slave running with --log-slave-updates must use row-based binary logging to be able to replicate row-based binary log events drop procedure foo; drop procedure foo2; drop procedure foo3; @@ -255,15 +252,6 @@ select * from t2; a 20 100 -set binlog_format=STATEMENT; -create function fn16456() -returns int -begin -return unix_timestamp(); -end| -select fn16456(); -ERROR HY000: Slave running with --log-slave-updates must use row-based binary logging to be able to replicate row-based binary log events -drop function fn16456; create trigger trg before insert on t1 for each row set new.a= 10; ERROR 42000: TRIGGER command denied to user 'zedjzlcsjhd'@'localhost' for table 't1' delete from t1; @@ -376,12 +364,6 @@ 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`; CREATE DEFINER=`root`@`localhost` function fn16456() -returns int -begin -return unix_timestamp(); -end -master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn16456 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) --- 1.21/mysql-test/t/rpl_sp.test 2006-11-15 15:46:43 +01:00 +++ 1.22/mysql-test/t/rpl_sp.test 2006-11-17 21:29:30 +01:00 @@ -173,15 +173,6 @@ select * from mysql.proc where name="foo sync_slave_with_master; select * from mysql.proc where name="foo4" and db='mysqltest1'; -# fail if non-deterministic SP is called in SBR, bug#16456 -let $oblf=`select @@SESSION.BINLOG_FORMAT`; -set binlog_format=STATEMENT; ---error ER_BINLOG_ROW_RBR_TO_SBR -call foo(); ---disable_query_log -eval set binlog_format=$oblf; ---enable_query_log - # ********************** PART 2 : FUNCTIONS *************** connection master; @@ -326,25 +317,6 @@ sync_slave_with_master; # check that this failed-in-the-middle replicated right: select * from t2; -# fail if non-deterministic SF is called in SBR, bug#16456 -connection master; -let $oblf=`select @@SESSION.BINLOG_FORMAT`; -set binlog_format=STATEMENT; -delimiter |; -create function fn16456() - returns int -begin - return unix_timestamp(); -end| -delimiter ;| ---error ER_BINLOG_ROW_RBR_TO_SBR -select fn16456(); ---disable_query_log -eval set binlog_format=$oblf; ---enable_query_log -drop function fn16456; - - # ********************** PART 3 : TRIGGERS *************** connection con1; @@ -555,5 +527,5 @@ set global log_bin_trust_function_creato connection master; set global log_bin_trust_function_creators=0; --echo End of 5.0 tests ---echo End of 5.1 tests +--echo End of 5.1 tests --- 1.8/mysql-test/r/query_cache_notembedded.result 2006-10-04 16:33:23 +02:00 +++ 1.9/mysql-test/r/query_cache_notembedded.result 2006-11-17 21:28:43 +01:00 @@ -314,6 +314,7 @@ drop procedure f2; drop procedure f3; drop procedure f4; drop table t1; +SET GLOBAL log_bin_trust_function_creators = 1; reset query cache; drop function if exists f1; create table t1 (id int); @@ -345,3 +346,4 @@ id drop table t1; drop function f1; set GLOBAL query_cache_size=0; +SET GLOBAL log_bin_trust_function_creators = 0; --- 1.5/mysql-test/t/query_cache_notembedded.test 2006-03-21 13:10:08 +01:00 +++ 1.6/mysql-test/t/query_cache_notembedded.test 2006-11-17 21:29:30 +01:00 @@ -183,6 +183,8 @@ drop table t1; # # bug#14767: INSERT in SF + concurrent SELECT with query cache # +SET GLOBAL log_bin_trust_function_creators = 1; + reset query cache; --disable_warnings drop function if exists f1; @@ -222,3 +224,4 @@ disconnect con2; connection default; set GLOBAL query_cache_size=0; +SET GLOBAL log_bin_trust_function_creators = 0; --- 1.87/mysql-test/r/func_time.result 2006-11-15 01:40:04 +01:00 +++ 1.88/mysql-test/r/func_time.result 2006-11-17 21:28:42 +01:00 @@ -1057,6 +1057,7 @@ Note 1003 select timestampdiff(WEEK,_lat select time_format('100:00:00', '%H %k %h %I %l'); time_format('100:00:00', '%H %k %h %I %l') 100 100 04 04 4 +SET GLOBAL log_bin_trust_function_creators = 1; create table t1 (a timestamp default '2005-05-05 01:01:01', b timestamp default '2005-05-05 01:01:01'); drop function if exists t_slow_sysdate; @@ -1080,6 +1081,7 @@ a != b drop trigger t_before; drop function t_slow_sysdate; drop table t1; +SET GLOBAL log_bin_trust_function_creators = 0; create table t1 (a datetime, i int, b datetime); insert into t1 select sysdate(), sleep(1), sysdate() from dual; select a != b from t1; --- 1.71/mysql-test/t/func_time.test 2006-11-09 00:38:27 +01:00 +++ 1.72/mysql-test/t/func_time.test 2006-11-17 21:28:43 +01:00 @@ -580,6 +580,8 @@ select time_format('100:00:00', '%H %k % # Bug #12562: Make SYSDATE behave like it does in Oracle: always the current # time, regardless of magic to make NOW() always the same for the # entirety of a statement. +SET GLOBAL log_bin_trust_function_creators = 1; + create table t1 (a timestamp default '2005-05-05 01:01:01', b timestamp default '2005-05-05 01:01:01'); delimiter //; @@ -608,6 +610,8 @@ select a != b from t1; drop trigger t_before; drop function t_slow_sysdate; drop table t1; + +SET GLOBAL log_bin_trust_function_creators = 0; create table t1 (a datetime, i int, b datetime); insert into t1 select sysdate(), sleep(1), sysdate() from dual; --- 1.82/mysql-test/r/query_cache.result 2006-10-06 16:29:04 +02:00 +++ 1.83/mysql-test/r/query_cache.result 2006-11-17 21:28:43 +01:00 @@ -1077,10 +1077,12 @@ create procedure `p1`() begin select a, f1() from t1; end// +SET GLOBAL log_bin_trust_function_creators = 1; call p1()// a f1() 1 2 2 2 +SET GLOBAL log_bin_trust_function_creators = 0; drop procedure p1// drop function f1// drop table t1// --- 1.63/mysql-test/t/query_cache.test 2006-10-04 16:33:24 +02:00 +++ 1.64/mysql-test/t/query_cache.test 2006-11-17 21:29:30 +01:00 @@ -788,7 +788,9 @@ create procedure `p1`() begin select a, f1() from t1; end// +SET GLOBAL log_bin_trust_function_creators = 1; call p1()// +SET GLOBAL log_bin_trust_function_creators = 0; drop procedure p1// drop function f1// --- 1.3/mysql-test/r/innodb_notembedded.result 2006-10-04 13:09:34 +02:00 +++ 1.4/mysql-test/r/innodb_notembedded.result 2006-11-17 21:28:42 +01:00 @@ -1,4 +1,5 @@ drop table if exists t1; +SET GLOBAL log_bin_trust_function_creators = 1; create table t1 (col1 integer primary key, col2 integer) engine=innodb; insert t1 values (1,100); create function f1 () returns integer begin @@ -18,3 +19,4 @@ rollback; rollback; drop table t1; drop function f1; +SET GLOBAL log_bin_trust_function_creators = 0; --- 1.1/mysql-test/t/innodb_notembedded.test 2006-02-24 17:34:10 +01:00 +++ 1.2/mysql-test/t/innodb_notembedded.test 2006-11-17 21:29:30 +01:00 @@ -13,6 +13,7 @@ connect (b,localhost,root,,); # BUG#11238 - in prelocking mode SELECT .. FOR UPDATE is changed to # non-blocking SELECT # +SET GLOBAL log_bin_trust_function_creators = 1; create table t1 (col1 integer primary key, col2 integer) engine=innodb; insert t1 values (1,100); delimiter |; @@ -38,3 +39,4 @@ drop table t1; drop function f1; disconnect a; disconnect b; +SET GLOBAL log_bin_trust_function_creators = 0; --- 1.233/mysql-test/r/sp.result 2006-11-14 19:10:21 +01:00 +++ 1.234/mysql-test/r/sp.result 2006-11-17 21:28:43 +01:00 @@ -243,7 +243,7 @@ set x = (select sum(t.i) from test.t2 t) insert into test.t1 values (id, x); end| drop procedure if exists sub3| -create function sub3(i int) returns int +create function sub3(i int) returns int deterministic return i+1| call sub1("sub1a", (select 7))| call sub1("sub1b", (select max(i) from t2))| @@ -4802,22 +4802,6 @@ i 0 drop table t3| drop procedure bug16887| -create table t3 (f1 int, f2 varchar(3), primary key(f1)) engine=innodb| -insert into t3 values (1,'aaa'),(2,'bbb'),(3,'ccc')| -CREATE FUNCTION bug13575 ( p1 integer ) -returns varchar(3) -BEGIN -DECLARE v1 VARCHAR(10) DEFAULT null; -SELECT f2 INTO v1 FROM t3 WHERE f1 = p1; -RETURN v1; -END| -select distinct f1, bug13575(f1) from t3 order by f1| -f1 bug13575(f1) -1 aaa -2 bbb -3 ccc -drop function bug13575; -drop table t3| drop procedure if exists bug16474_1| drop procedure if exists bug16474_2| delete from t1| --- 1.205/mysql-test/t/sp.test 2006-11-14 18:54:00 +01:00 +++ 1.206/mysql-test/t/sp.test 2006-11-17 21:30:21 +01:00 @@ -358,7 +358,7 @@ end| --disable_warnings drop procedure if exists sub3| --enable_warnings -create function sub3(i int) returns int +create function sub3(i int) returns int deterministic return i+1| call sub1("sub1a", (select 7))| --- 1.38/mysql-test/r/gis.result 2006-09-25 09:30:19 +02:00 +++ 1.39/mysql-test/r/gis.result 2006-11-17 21:28:42 +01:00 @@ -679,10 +679,11 @@ insert into t1 values (null,null); ERROR 23000: Column 's1' cannot be null drop table t1; drop procedure if exists fn3; -create function fn3 () returns point return GeomFromText("point(1 1)"); +create function fn3 () returns point deterministic return GeomFromText("point(1 1)"); show create function fn3; Function sql_mode Create Function fn3 CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS point + DETERMINISTIC return GeomFromText("point(1 1)") select astext(fn3()); astext(fn3()) --- 1.30/mysql-test/t/gis.test 2006-08-17 17:16:17 +02:00 +++ 1.31/mysql-test/t/gis.test 2006-11-17 21:28:43 +01:00 @@ -393,7 +393,7 @@ drop table t1; --disable_warnings drop procedure if exists fn3; --enable_warnings -create function fn3 () returns point return GeomFromText("point(1 1)"); +create function fn3 () returns point deterministic return GeomFromText("point(1 1)"); show create function fn3; select astext(fn3()); drop function fn3; --- 1.33/mysql-test/r/grant2.result 2006-10-04 16:33:22 +02:00 +++ 1.34/mysql-test/r/grant2.result 2006-11-17 21:28:42 +01:00 @@ -365,13 +365,14 @@ insert into mysql.user select * from t1; drop table t1, t2; drop database TESTDB; flush privileges; +SET GLOBAL log_bin_trust_function_creators = 1; grant all privileges on test.* to `a@`@localhost; grant execute on * to `a@`@localhost; create table t2 (s1 int); insert into t2 values (1); drop function if exists f2; -create function f2 () returns int begin declare v int; select s1 from t2 -into v; return v; end// +create function f2 () returns int +begin declare v int; select s1 from t2 into v; return v; end// select f2(); f2() 1 @@ -379,3 +380,4 @@ drop function f2; drop table t2; REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost; drop user `a@`@localhost; +SET GLOBAL log_bin_trust_function_creators = 0; --- 1.39/mysql-test/t/grant2.test 2006-08-15 00:15:55 +02:00 +++ 1.40/mysql-test/t/grant2.test 2006-11-17 21:28:43 +01:00 @@ -484,6 +484,8 @@ flush privileges; # BUG#13310 incorrect user parsing by SP # +SET GLOBAL log_bin_trust_function_creators = 1; + grant all privileges on test.* to `a@`@localhost; grant execute on * to `a@`@localhost; connect (bug13310,localhost,'a@',,test); @@ -494,11 +496,10 @@ insert into t2 values (1); drop function if exists f2; --enable_warnings delimiter //; -create function f2 () returns int begin declare v int; select s1 from t2 -into v; return v; end// +create function f2 () returns int +begin declare v int; select s1 from t2 into v; return v; end// delimiter ;// select f2(); - drop function f2; drop table t2; disconnect bug13310; @@ -506,3 +507,5 @@ disconnect bug13310; connection default; REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost; drop user `a@`@localhost; + +SET GLOBAL log_bin_trust_function_creators = 0; --- 1.87/mysql-test/r/ps.result 2006-11-07 14:13:25 +01:00 +++ 1.88/mysql-test/r/ps.result 2006-11-17 21:28:42 +01:00 @@ -1500,9 +1500,9 @@ create procedure proc_1() reset query ca call proc_1(); call proc_1(); call proc_1(); -create function func_1() returns int begin reset query cache; return 1; end| +create function func_1() returns int deterministic begin reset query cache; return 1; end| ERROR 0A000: RESET is not allowed in stored function or trigger -create function func_1() returns int begin call proc_1(); return 1; end| +create function func_1() returns int deterministic begin call proc_1(); return 1; end| select func_1(), func_1(), func_1() from dual; ERROR 0A000: RESET is not allowed in stored function or trigger drop function func_1; --- 1.5/mysql-test/r/rpl_sp_effects.result 2006-06-01 11:53:08 +02:00 +++ 1.6/mysql-test/r/rpl_sp_effects.result 2006-11-17 21:28:43 +01:00 @@ -10,6 +10,7 @@ drop function if exists f1; drop table if exists t1,t2; drop view if exists v1; create table t1 (a int); +SET GLOBAL log_bin_trust_function_creators = 1; create procedure p1() begin declare spv int default 0; @@ -234,3 +235,4 @@ drop table t1; drop function f1; drop function f2; drop procedure p1; +SET GLOBAL log_bin_trust_function_creators = 0; --- 1.86/mysql-test/t/ps.test 2006-11-07 14:13:25 +01:00 +++ 1.87/mysql-test/t/ps.test 2006-11-17 21:29:30 +01:00 @@ -1559,8 +1559,8 @@ call proc_1(); call proc_1(); delimiter |; --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG -create function func_1() returns int begin reset query cache; return 1; end| -create function func_1() returns int begin call proc_1(); return 1; end| +create function func_1() returns int deterministic begin reset query cache; return 1; end| +create function func_1() returns int deterministic begin call proc_1(); return 1; end| delimiter ;| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG select func_1(), func_1(), func_1() from dual; --- 1.7/mysql-test/t/rpl_sp_effects.test 2006-06-01 11:53:10 +02:00 +++ 1.8/mysql-test/t/rpl_sp_effects.test 2006-11-17 21:30:21 +01:00 @@ -20,6 +20,8 @@ drop view if exists v1; --enable_warnings create table t1 (a int); +SET GLOBAL log_bin_trust_function_creators = 1; + # 1. Test simple variables use. delimiter //; create procedure p1() @@ -205,3 +207,5 @@ drop function f1; drop function f2; drop procedure p1; sync_slave_with_master; + +SET GLOBAL log_bin_trust_function_creators = 0; --- New file --- +++ mysql-test/r/rpl_sf.result 06/11/17 21:30:21 set global log_bin_trust_function_creators=0; set binlog_format=STATEMENT; create function fn16456() returns int begin return unix_timestamp(); end| ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) set global log_bin_trust_function_creators=1; create function fn16456() returns int begin return unix_timestamp(); end| set global log_bin_trust_function_creators=0; set binlog_format=ROW; select fn16456(); fn16456() timestamp set binlog_format=STATEMENT; select fn16456(); ERROR HY000: Slave running with --log-slave-updates must use row-based binary logging to be able to replicate row-based binary log events drop function fn16456; --- New file --- +++ mysql-test/t/rpl_sf.test 06/11/17 21:30:21 # Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR # save status let $oblf=`select @@SESSION.BINLOG_FORMAT`; let $otfc=`select @@log_bin_trust_function_creators`; set global log_bin_trust_function_creators=0; # fail *on definition* set binlog_format=STATEMENT; delimiter |; --error ER_BINLOG_UNSAFE_ROUTINE create function fn16456() returns int begin return unix_timestamp(); end| delimiter ;| # force in definition, so we can see whether we fail on call set global log_bin_trust_function_creators=1; delimiter |; create function fn16456() returns int begin return unix_timestamp(); end| delimiter ;| set global log_bin_trust_function_creators=0; # allow funcall in RBR set binlog_format=ROW; --replace_column 1 timestamp select fn16456(); # fail funcall in SBR set binlog_format=STATEMENT; --error ER_BINLOG_ROW_RBR_TO_SBR select fn16456(); # restore status drop function fn16456; --disable_query_log eval set binlog_format=$oblf; eval set global log_bin_trust_function_creators=$otfc; --enable_query_log