summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/mysql-test-run.sh3
-rw-r--r--mysql-test/r/blackhole.result8
-rw-r--r--mysql-test/r/rpl_sp.result235
-rw-r--r--mysql-test/t/rpl_sp-master.opt1
-rw-r--r--mysql-test/t/rpl_sp-slave.opt1
-rw-r--r--mysql-test/t/rpl_sp.test233
-rw-r--r--mysql-test/valgrind.supp40
-rw-r--r--sql/item_func.cc15
-rw-r--r--sql/log_event.cc62
-rw-r--r--sql/log_event.h5
-rw-r--r--sql/mysql_priv.h2
-rw-r--r--sql/mysqld.cc14
-rw-r--r--sql/set_var.cc5
-rw-r--r--sql/share/errmsg.txt6
-rw-r--r--sql/slave.cc26
-rw-r--r--sql/sp.cc42
-rw-r--r--sql/sql_acl.cc10
-rw-r--r--sql/sql_parse.cc69
18 files changed, 733 insertions, 44 deletions
diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh
index fb0b6c5c2a7..1a648f98b1b 100644
--- a/mysql-test/mysql-test-run.sh
+++ b/mysql-test/mysql-test-run.sh
@@ -1187,6 +1187,7 @@ start_master()
--language=$LANGUAGE \
--innodb_data_file_path=ibdata1:128M:autoextend \
--open-files-limit=1024 \
+ --log-bin-trust-routine-creators \
$MASTER_40_ARGS \
$SMALL_SERVER \
$EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT \
@@ -1207,6 +1208,7 @@ start_master()
--tmpdir=$MYSQL_TMP_DIR \
--language=$LANGUAGE \
--innodb_data_file_path=ibdata1:128M:autoextend \
+ --log-bin-trust-routine-creators \
$MASTER_40_ARGS \
$SMALL_SERVER \
$EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT \
@@ -1339,6 +1341,7 @@ start_slave()
--report-port=$slave_port \
--master-retry-count=10 \
-O slave_net_timeout=10 \
+ --log-bin-trust-routine-creators \
$SMALL_SERVER \
$EXTRA_SLAVE_OPT $EXTRA_SLAVE_MYSQLD_OPT"
CUR_MYERR=$slave_err
diff --git a/mysql-test/r/blackhole.result b/mysql-test/r/blackhole.result
index 66752b11655..38e548490fe 100644
--- a/mysql-test/r/blackhole.result
+++ b/mysql-test/r/blackhole.result
@@ -105,8 +105,8 @@ a
select * from t3;
a
show binlog events;
-Log_name Pos Event_type Server_id Orig_log_pos Info
-master-bin.000001 # Start 1 # Server ver: VERSION, Binlog ver: 3
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Format_desc 1 # Server ver: VERSION, Binlog ver: 4
master-bin.000001 # Query 1 # use `test`; drop table t1,t2
master-bin.000001 # Query 1 # use `test`; create table t1 (a int) engine=blackhole
master-bin.000001 # Query 1 # use `test`; delete from t1 where a=10
@@ -115,8 +115,8 @@ master-bin.000001 # Query 1 # use `test`; insert into t1 values(1)
master-bin.000001 # Query 1 # use `test`; insert ignore into t1 values(1)
master-bin.000001 # Query 1 # use `test`; replace into t1 values(100)
master-bin.000001 # Query 1 # use `test`; create table t2 (a varchar(200)) engine=blackhole
-master-bin.000001 # Create_file 1 # db=test;table=t2;file_id=1;block_len=581
-master-bin.000001 # Exec_load 1 # ;file_id=1
+master-bin.000001 # Begin_load_query 1 # ;file_id=1;block_len=581
+master-bin.000001 # Execute_load_query 1 # use `test`; load data infile '../../std_data/words.dat' into table t2 ;file_id=1
master-bin.000001 # Query 1 # use `test`; alter table t1 add b int
master-bin.000001 # Query 1 # use `test`; alter table t1 drop b
master-bin.000001 # Query 1 # use `test`; create table t3 like t1
diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result
new file mode 100644
index 00000000000..be93e51e34b
--- /dev/null
+++ b/mysql-test/r/rpl_sp.result
@@ -0,0 +1,235 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+create database if not exists mysqltest1;
+use mysqltest1;
+create table t1 (a varchar(100));
+use mysqltest1;
+drop procedure if exists foo;
+drop procedure if exists foo2;
+drop procedure if exists foo3;
+drop procedure if exists foo4;
+drop procedure if exists bar;
+drop function if exists fn1;
+create procedure foo()
+begin
+declare b int;
+set b = 8;
+insert into t1 values (b);
+insert into t1 values (unix_timestamp());
+end|
+ERROR HY000: This routine is declared to be non-deterministic and to modify data and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)
+show binlog events from 98|
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query 1 # create database if not exists mysqltest1
+master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a varchar(100))
+create procedure foo() deterministic
+begin
+declare b int;
+set b = 8;
+insert into t1 values (b);
+insert into t1 values (unix_timestamp());
+end|
+select * from mysql.proc where name='foo' 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
+mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL YES DEFINER begin
+declare b int;
+set b = 8;
+insert into t1 values (b);
+insert into t1 values (unix_timestamp());
+end root@localhost # #
+select * from mysql.proc where name='foo' 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
+mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL YES DEFINER begin
+declare b int;
+set b = 8;
+insert into t1 values (b);
+insert into t1 values (unix_timestamp());
+end @ # #
+set timestamp=1000000000;
+call foo();
+show binlog events from 308;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo() deterministic
+begin
+declare b int;
+set b = 8;
+insert into t1 values (b);
+insert into t1 values (unix_timestamp());
+end
+master-bin.000001 # Query 1 # use `mysqltest1`; call foo()
+select * from t1;
+a
+8
+1000000000
+select * from t1;
+a
+8
+1000000000
+delete from t1;
+create procedure foo2()
+not deterministic
+reads sql data
+select * from mysqltest1.t1;
+call foo2();
+a
+show binlog events from 605;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1
+master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo2()
+not deterministic
+reads sql data
+select * from mysqltest1.t1
+alter procedure foo2 contains sql;
+ERROR HY000: This routine is declared to be non-deterministic and to modify data and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)
+drop table t1;
+create table t1 (a int);
+create table t2 like t1;
+create procedure foo3()
+deterministic
+insert into t1 values (15);
+grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
+grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
+grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;
+create procedure foo4()
+deterministic
+insert into t1 values (10);
+ERROR HY000: You do not have SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)
+set global log_bin_trust_routine_creators=1;
+create procedure foo4()
+deterministic
+begin
+insert into t2 values(3);
+insert into t1 values (5);
+end|
+call foo4();
+ERROR 42000: INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
+show warnings;
+Level Code Message
+Warning 1417 A routine failed and is declared to modify data and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes
+call foo3();
+show warnings;
+Level Code Message
+call foo4();
+ERROR 42000: INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
+show warnings;
+Level Code Message
+Warning 1417 A routine failed and is declared to modify data and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes
+alter procedure foo4 sql security invoker;
+call foo4();
+show warnings;
+Level Code Message
+show binlog events from 841;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1
+master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int)
+master-bin.000001 # Query 1 # use `mysqltest1`; create table t2 like t1
+master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo3()
+deterministic
+insert into t1 values (15)
+master-bin.000001 # Query 1 # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
+master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
+master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
+master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4()
+deterministic
+begin
+insert into t2 values(3);
+insert into t1 values (5);
+end
+master-bin.000001 # Query 1 # use `mysqltest1`; call foo3()
+master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo4 sql security invoker
+master-bin.000001 # Query 1 # use `mysqltest1`; call foo4()
+select * from t1;
+a
+15
+5
+select * from t2;
+a
+3
+3
+3
+select * from t1;
+a
+15
+5
+select * from t2;
+a
+3
+select if(compte<>3,"this is broken but documented","this unexpectedly works?") from (select count(*) as compte from t2) as aggreg;
+if(compte<>3,"this is broken but documented","this unexpectedly works?")
+this is broken but documented
+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
+mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES INVOKER begin
+insert into t2 values(3);
+insert into t1 values (5);
+end @ # #
+drop procedure foo4;
+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
+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
+drop procedure foo;
+drop procedure foo2;
+drop procedure foo3;
+create function fn1(x int)
+returns int
+deterministic
+begin
+insert into t1 values (x);
+return x+2;
+end|
+delete t1,t2 from t1,t2;
+select fn1(20);
+fn1(20)
+22
+insert into t2 values(fn1(21));
+select * from t1;
+a
+21
+20
+select * from t2;
+a
+23
+select * from t1;
+a
+21
+select if(compte<>1,"this is broken but documented","this unexpectedly works?") from (select count(*) as compte from t1 where a=20) as aggreg;
+if(compte<>1,"this is broken but documented","this unexpectedly works?")
+this is broken but documented
+select * from t2;
+a
+23
+drop function fn1;
+create function fn1()
+returns int
+deterministic
+begin
+return unix_timestamp();
+end|
+delete from t1;
+set timestamp=1000000000;
+insert into t1 values(fn1());
+select * from mysql.proc where 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
+mysqltest1 fn1 FUNCTION fn1 SQL CONTAINS_SQL YES DEFINER int(11) begin
+return unix_timestamp();
+end root@localhost # #
+select * from t1;
+a
+1000000000
+use mysqltest1;
+select * from t1;
+a
+1000000000
+select * from mysql.proc where 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
+mysqltest1 fn1 FUNCTION fn1 SQL CONTAINS_SQL YES DEFINER int(11) begin
+return unix_timestamp();
+end @ # #
+drop function fn1;
+drop database mysqltest1;
+drop user "zedjzlcsjhd"@127.0.0.1;
diff --git a/mysql-test/t/rpl_sp-master.opt b/mysql-test/t/rpl_sp-master.opt
new file mode 100644
index 00000000000..709a224fd92
--- /dev/null
+++ b/mysql-test/t/rpl_sp-master.opt
@@ -0,0 +1 @@
+--log_bin_trust_routine_creators=0
diff --git a/mysql-test/t/rpl_sp-slave.opt b/mysql-test/t/rpl_sp-slave.opt
new file mode 100644
index 00000000000..709a224fd92
--- /dev/null
+++ b/mysql-test/t/rpl_sp-slave.opt
@@ -0,0 +1 @@
+--log_bin_trust_routine_creators=0
diff --git a/mysql-test/t/rpl_sp.test b/mysql-test/t/rpl_sp.test
new file mode 100644
index 00000000000..c87585a138c
--- /dev/null
+++ b/mysql-test/t/rpl_sp.test
@@ -0,0 +1,233 @@
+# Test of replication of stored procedures (WL#2146 for MySQL 5.0)
+
+source include/master-slave.inc;
+
+# First let's test replication of current_user() (that's a related thing)
+# we need a db != test, where we don't have automatic grants
+create database if not exists mysqltest1;
+use mysqltest1;
+create table t1 (a varchar(100));
+sync_slave_with_master;
+use mysqltest1;
+
+# ********************** PART 1 : STORED PROCEDURES ***************
+
+# Does the same proc as on master get inserted into mysql.proc ?
+# (same definer, same properties...)
+
+connection master;
+# cleanup
+--disable_warnings
+drop procedure if exists foo;
+drop procedure if exists foo2;
+drop procedure if exists foo3;
+drop procedure if exists foo4;
+drop procedure if exists bar;
+drop function if exists fn1;
+--enable_warnings
+
+delimiter |;
+--error 1418; # not deterministic
+create procedure foo()
+begin
+ declare b int;
+ set b = 8;
+ insert into t1 values (b);
+ insert into t1 values (unix_timestamp());
+end|
+
+--replace_column 2 # 5 #
+show binlog events from 98| # check that not there
+
+create procedure foo() deterministic
+begin
+ declare b int;
+ set b = 8;
+ insert into t1 values (b);
+ insert into t1 values (unix_timestamp());
+end|
+delimiter ;|
+
+# we replace columns having times
+# (even with fixed timestamp displayed time may changed based on TZ)
+--replace_result localhost.localdomain localhost 127.0.0.1 localhost
+--replace_column 13 # 14 #
+select * from mysql.proc where name='foo' and db='mysqltest1';
+sync_slave_with_master;
+--replace_result localhost.localdomain localhost 127.0.0.1 localhost
+--replace_column 13 # 14 #
+select * from mysql.proc where name='foo' and db='mysqltest1';
+
+# Now when we call it, does the CALL() get into binlog,
+# or the substatements?
+connection master;
+# see if timestamp used in SP on slave is same as on master
+set timestamp=1000000000;
+call foo();
+--replace_column 2 # 5 #
+show binlog events from 308;
+select * from t1;
+sync_slave_with_master;
+select * from t1;
+
+# Now a SP which is supposed to not update tables (CALL should not be
+# binlogged) as it's "read sql data", so should not give error even if
+# non-deterministic.
+
+connection master;
+delete from t1;
+create procedure foo2()
+ not deterministic
+ reads sql data
+ select * from mysqltest1.t1;
+call foo2();
+# verify CALL is not in binlog
+--replace_column 2 # 5 #
+show binlog events from 605;
+
+--error 1418;
+alter procedure foo2 contains sql;
+
+# SP with definer's right
+
+drop table t1;
+create table t1 (a int);
+create table t2 like t1;
+
+create procedure foo3()
+ deterministic
+ insert into t1 values (15);
+
+# let's create a non-privileged user
+grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
+grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
+grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;
+
+connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,);
+connection con1;
+
+--error 1419; # only full-global-privs user can create a routine
+create procedure foo4()
+ deterministic
+ insert into t1 values (10);
+
+connection master;
+set global log_bin_trust_routine_creators=1;
+connection con1;
+
+delimiter |;
+create procedure foo4()
+ deterministic
+ begin
+ insert into t2 values(3);
+ insert into t1 values (5);
+ end|
+
+delimiter ;|
+
+--replace_result localhost.localdomain localhost 127.0.0.1 localhost
+--error 1142;
+call foo4(); # invoker has no INSERT grant on table => failure
+show warnings;
+
+connection master;
+call foo3(); # success (definer == root)
+show warnings;
+
+--replace_result localhost.localdomain localhost 127.0.0.1 localhost
+--error 1142;
+call foo4(); # definer's rights => failure
+show warnings;
+
+# we test replication of ALTER PROCEDURE
+alter procedure foo4 sql security invoker;
+call foo4(); # invoker's rights => success
+show warnings;
+
+# Check that only successful CALLs are in binlog
+--replace_column 2 # 5 #
+show binlog events from 841;
+
+# Note that half-failed CALLs are not in binlog, which is a known
+# bug. If we compare t2 on master and slave we see they differ:
+
+select * from t1;
+select * from t2;
+sync_slave_with_master;
+select * from t1;
+select * from t2;
+select if(compte<>3,"this is broken but documented","this unexpectedly works?") from (select count(*) as compte from t2) as aggreg;
+
+# Test of DROP PROCEDURE
+
+--replace_result localhost.localdomain localhost 127.0.0.1 localhost
+--replace_column 13 # 14 #
+select * from mysql.proc where name="foo4" and db='mysqltest1';
+connection master;
+drop procedure foo4;
+select * from mysql.proc where name="foo4" and db='mysqltest1';
+sync_slave_with_master;
+select * from mysql.proc where name="foo4" and db='mysqltest1';
+
+# ********************** PART 2 : FUNCTIONS ***************
+
+connection master;
+drop procedure foo;
+drop procedure foo2;
+drop procedure foo3;
+
+delimiter |;
+create function fn1(x int)
+ returns int
+ deterministic
+begin
+ insert into t1 values (x);
+ return x+2;
+end|
+
+delimiter ;|
+delete t1,t2 from t1,t2;
+select fn1(20);
+insert into t2 values(fn1(21));
+select * from t1;
+select * from t2;
+sync_slave_with_master;
+select * from t1;
+select if(compte<>1,"this is broken but documented","this unexpectedly works?") from (select count(*) as compte from t1 where a=20) as aggreg;
+select * from t2;
+
+connection master;
+delimiter |;
+
+drop function fn1;
+
+create function fn1()
+ returns int
+ deterministic
+begin
+ return unix_timestamp();
+end|
+delimiter ;|
+delete from t1;
+set timestamp=1000000000;
+insert into t1 values(fn1());
+
+--replace_result localhost.localdomain localhost 127.0.0.1 localhost
+--replace_column 13 # 14 #
+select * from mysql.proc where db='mysqltest1';
+select * from t1;
+
+sync_slave_with_master;
+use mysqltest1;
+select * from t1;
+--replace_result localhost.localdomain localhost 127.0.0.1 localhost
+--replace_column 13 # 14 #
+select * from mysql.proc where db='mysqltest1';
+
+
+# Clean up
+connection master;
+drop function fn1;
+drop database mysqltest1;
+drop user "zedjzlcsjhd"@127.0.0.1;
+sync_slave_with_master;
diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp
index d8a13ca9dfd..26f3477140b 100644
--- a/mysql-test/valgrind.supp
+++ b/mysql-test/valgrind.supp
@@ -25,6 +25,24 @@
}
{
+ pthread allocate_dtv memory loss second
+ Memcheck:Leak
+ fun:calloc
+ fun:allocate_dtv
+ fun:_dl_allocate_tls
+ fun:pthread_create*
+}
+
+{
+ pthread allocate_dtv memory loss second
+ Memcheck:Leak
+ fun:calloc
+ fun:allocate_dtv
+ fun:_dl_allocate_tls
+ fun:pthread_create*
+}
+
+{
pthread memalign memory loss
Memcheck:Leak
fun:memalign
@@ -34,6 +52,28 @@
}
{
+ pthread strstr uninit
+ Memcheck:Cond
+ fun:strstr
+ obj:/lib/tls/libpthread.so.*
+ obj:/lib/tls/libpthread.so.*
+ fun:call_init
+ fun:_dl_init
+ obj:/lib/ld-*.so
+}
+
+{
+ pthread strstr uninit
+ Memcheck:Cond
+ fun:strstr
+ obj:/lib/tls/libpthread.so.*
+ obj:/lib/tls/libpthread.so.*
+ fun:call_init
+ fun:_dl_init
+ obj:/lib/ld-*.so
+}
+
+{
pthread errno
Memcheck:Leak
fun:calloc
diff --git a/sql/item_func.cc b/sql/item_func.cc
index b0fe0a78844..691e34b85a6 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -4671,9 +4671,24 @@ Item_func_sp::execute(Item **itp)
DBUG_RETURN(-1);
}
#endif
+ /*
+ Like for SPs, we don't binlog the substatements. If the statement which
+ called this function is an update statement, it will be binlogged; but if
+ it's not (e.g. SELECT myfunc()) it won't be binlogged (documented known
+ problem).
+ */
+ tmp_disable_binlog(thd); /* don't binlog the substatements */
res= m_sp->execute_function(thd, args, arg_count, itp);
+ reenable_binlog(thd);
+ if (res && mysql_bin_log.is_open() &&
+ (m_sp->m_chistics->daccess == SP_CONTAINS_SQL ||
+ m_sp->m_chistics->daccess == SP_MODIFIES_SQL_DATA))
+ push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_FAILED_ROUTINE_BREAK_BINLOG,
+ ER(ER_FAILED_ROUTINE_BREAK_BINLOG));
+
#ifndef NO_EMBEDDED_ACCESS_CHECKS
sp_restore_security_context(thd, m_sp, &save_ctx);
#endif
diff --git a/sql/log_event.cc b/sql/log_event.cc
index 86d31a9c2e8..42134d2b990 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -955,6 +955,18 @@ void Query_log_event::pack_info(Protocol *protocol)
#ifndef MYSQL_CLIENT
+/* Utility function for the next method */
+static void write_str_with_code_and_len(char **dst, const char *src,
+ int len, uint code)
+{
+ DBUG_ASSERT(src);
+ *((*dst)++)= code;
+ *((*dst)++)= (uchar) len;
+ bmove(*dst, src, len);
+ (*dst)+= len;
+}
+
+
/*
Query_log_event::write()
@@ -1039,12 +1051,10 @@ bool Query_log_event::write(IO_CACHE* file)
int8store(start, (ulonglong)sql_mode);
start+= 8;
}
- if (catalog_len) // i.e. "catalog inited" (false for 4.0 events)
+ if (catalog_len) // i.e. this var is inited (false for 4.0 events)
{
- *start++= Q_CATALOG_NZ_CODE;
- *start++= (uchar) catalog_len;
- bmove(start, catalog, catalog_len);
- start+= catalog_len;
+ write_str_with_code_and_len((char **)(&start),
+ catalog, catalog_len, Q_CATALOG_NZ_CODE);
/*
In 5.0.x where x<4 masters we used to store the end zero here. This was
a waste of one byte so we don't do it in x>=4 masters. We change code to
@@ -1176,6 +1186,25 @@ Query_log_event::Query_log_event(THD* thd_arg, const char* query_arg,
#endif /* MYSQL_CLIENT */
+/* 2 utility functions for the next method */
+
+static void get_str_len_and_pointer(const char **dst, const char **src, uint *len)
+{
+ if ((*len= **src))
+ *dst= *src + 1; // Will be copied later
+ (*src)+= *len+1;
+}
+
+
+static void copy_str_and_move(char **dst, const char **src, uint len)
+{
+ memcpy(*dst, *src, len);
+ *src= *dst;
+ (*dst)+= len;
+ *(*dst)++= 0;
+}
+
+
/*
Query_log_event::Query_log_event()
This is used by the SQL slave thread to prepare the event before execution.
@@ -1264,9 +1293,7 @@ Query_log_event::Query_log_event(const char* buf, uint event_len,
break;
}
case Q_CATALOG_NZ_CODE:
- if ((catalog_len= *pos))
- catalog= (char*) pos+1; // Will be copied later
- pos+= catalog_len+1;
+ get_str_len_and_pointer(&catalog, (const char **)(&pos), &catalog_len);
break;
case Q_AUTO_INCREMENT:
auto_increment_increment= uint2korr(pos);
@@ -1282,9 +1309,7 @@ Query_log_event::Query_log_event(const char* buf, uint event_len,
}
case Q_TIME_ZONE_CODE:
{
- if ((time_zone_len= *pos))
- time_zone_str= (char *)(pos+1);
- pos+= time_zone_len+1;
+ get_str_len_and_pointer(&time_zone_str, (const char **)(&pos), &time_zone_len);
break;
}
case Q_CATALOG_CODE: /* for 5.0.x where 0<=x<=3 masters */
@@ -1308,12 +1333,7 @@ Query_log_event::Query_log_event(const char* buf, uint event_len,
if (catalog_len) // If catalog is given
{
if (likely(catalog_nz)) // true except if event comes from 5.0.0|1|2|3.
- {
- memcpy(start, catalog, catalog_len);
- catalog= start;
- start+= catalog_len;
- *start++= 0;
- }
+ copy_str_and_move(&start, &catalog, catalog_len);
else
{
memcpy(start, catalog, catalog_len+1); // copy end 0
@@ -1322,12 +1342,8 @@ Query_log_event::Query_log_event(const char* buf, uint event_len,
}
}
if (time_zone_len)
- {
- memcpy(start, time_zone_str, time_zone_len);
- time_zone_str= start;
- start+= time_zone_len;
- *start++= 0;
- }
+ copy_str_and_move(&start, &time_zone_str, time_zone_len);
+
/* A 2nd variable part; this is common to all versions */
memcpy((char*) start, end, data_len); // Copy db and query
start[data_len]= '\0'; // End query with \0 (For safetly)
diff --git a/sql/log_event.h b/sql/log_event.h
index 2985fcabb50..f8989c4994a 100644
--- a/sql/log_event.h
+++ b/sql/log_event.h
@@ -234,13 +234,12 @@ struct sql_ex_info
/* these are codes, not offsets; not more than 256 values (1 byte). */
#define Q_FLAGS2_CODE 0
#define Q_SQL_MODE_CODE 1
-#ifndef TO_BE_DELETED
/*
Q_CATALOG_CODE is catalog with end zero stored; it is used only by MySQL
- 5.0.x where 0<=x<=3.
+ 5.0.x where 0<=x<=3. We have to keep it to be able to replicate these
+ old masters.
*/
#define Q_CATALOG_CODE 2
-#endif
#define Q_AUTO_INCREMENT 3
#define Q_CHARSET_CODE 4
#define Q_TIME_ZONE_CODE 5
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 06c946114eb..82acd3936f0 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1088,7 +1088,7 @@ extern my_bool opt_readonly, lower_case_file_system;
extern my_bool opt_enable_named_pipe, opt_sync_frm, opt_allow_suspicious_udfs;
extern my_bool opt_secure_auth;
extern my_bool sp_automatic_privileges;
-extern my_bool opt_old_style_user_limits;
+extern my_bool opt_old_style_user_limits, trust_routine_creators;
extern uint opt_crash_binlog_innodb;
extern char *shared_memory_base_name, *mysqld_unix_port;
extern bool opt_enable_shared_memory;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 3bfa498e521..16f53101056 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -303,7 +303,7 @@ my_bool opt_log_queries_not_using_indexes= 0;
my_bool lower_case_file_system= 0;
my_bool opt_large_pages= 0;
uint opt_large_page_size= 0;
-my_bool opt_old_style_user_limits= 0;
+my_bool opt_old_style_user_limits= 0, trust_routine_creators= 0;
/*
True if there is at least one per-hour limit for some user, so we should
check them before each query (and possibly reset counters when hour is
@@ -4170,6 +4170,7 @@ enum options_mysqld
OPT_INNODB_FAST_SHUTDOWN,
OPT_INNODB_FILE_PER_TABLE, OPT_CRASH_BINLOG_INNODB,
OPT_INNODB_LOCKS_UNSAFE_FOR_BINLOG,
+ OPT_LOG_BIN_TRUST_ROUTINE_CREATORS,
OPT_SAFE_SHOW_DB, OPT_INNODB_SAFE_BINLOG,
OPT_INNODB, OPT_ISAM,
OPT_ENGINE_CONDITION_PUSHDOWN,
@@ -4590,6 +4591,17 @@ Disable with --skip-innodb-doublewrite.", (gptr*) &innobase_use_doublewrite,
"File that holds the names for last binary log files.",
(gptr*) &opt_binlog_index_name, (gptr*) &opt_binlog_index_name, 0, GET_STR,
REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+ /*
+ This option starts with "log-bin" to emphasize that it is specific of
+ binary logging. Hopefully in 5.1 nobody will need it anymore, when we have
+ row-level binlog.
+ */
+ {"log-bin-trust-routine-creators", OPT_LOG_BIN_TRUST_ROUTINE_CREATORS,
+ "If equal to 0 (the default), then when --log-bin is used, creation of "
+ "a routine is allowed only to users having the SUPER privilege and only"
+ "if this routine may not break binary logging",
+ (gptr*) &trust_routine_creators, (gptr*) &trust_routine_creators, 0,
+ GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
{"log-error", OPT_ERROR_LOG_FILE, "Error log file.",
(gptr*) &log_error_file_ptr, (gptr*) &log_error_file_ptr, 0, GET_STR,
OPT_ARG, 0, 0, 0, 0, 0, 0},
diff --git a/sql/set_var.cc b/sql/set_var.cc
index 0e6e36f63a2..99549654e11 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -203,6 +203,9 @@ sys_var_key_cache_long sys_key_cache_age_threshold("key_cache_age_threshold",
param_age_threshold));
sys_var_bool_ptr sys_local_infile("local_infile",
&opt_local_infile);
+sys_var_bool_ptr
+sys_trust_routine_creators("log_bin_trust_routine_creators",
+ &trust_routine_creators);
sys_var_thd_ulong sys_log_warnings("log_warnings", &SV::log_warnings);
sys_var_thd_ulong sys_long_query_time("long_query_time",
&SV::long_query_time);
@@ -703,6 +706,7 @@ sys_var *sys_variables[]=
&sys_innodb_thread_sleep_delay,
&sys_innodb_thread_concurrency,
#endif
+ &sys_trust_routine_creators,
&sys_engine_condition_pushdown,
#ifdef HAVE_NDBCLUSTER_DB
&sys_ndb_autoincrement_prefetch_sz,
@@ -842,6 +846,7 @@ struct show_var_st init_vars[]= {
#endif
{"log", (char*) &opt_log, SHOW_BOOL},
{"log_bin", (char*) &opt_bin_log, SHOW_BOOL},
+ {sys_trust_routine_creators.name,(char*) &sys_trust_routine_creators, SHOW_SYS},
{"log_error", (char*) log_error_file, SHOW_CHAR},
#ifdef HAVE_REPLICATION
{"log_slave_updates", (char*) &opt_log_slave_updates, SHOW_MY_BOOL},
diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt
index 050bbe86948..388f47bf96f 100644
--- a/sql/share/errmsg.txt
+++ b/sql/share/errmsg.txt
@@ -5346,3 +5346,9 @@ ER_SP_NO_RETSET_IN_FUNC 0A000
eng "Not allowed to return a result set from a function"
ER_CANT_CREATE_GEOMETRY_OBJECT 22003
eng "Cannot get geometry object from data you send to the GEOMETRY field"
+ER_FAILED_ROUTINE_BREAK_BINLOG
+ eng "A routine failed and is declared to modify data and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes"
+ER_BINLOG_UNSAFE_ROUTINE
+ eng "This routine is declared to be non-deterministic and to modify data and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)"
+ER_BINLOG_CREATE_ROUTINE_NEED_SUPER
+ eng "You do not have SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)"
diff --git a/sql/slave.cc b/sql/slave.cc
index ebf87660a0e..70803c88c3a 100644
--- a/sql/slave.cc
+++ b/sql/slave.cc
@@ -852,7 +852,7 @@ static TABLE_RULE_ENT* find_wild(DYNAMIC_ARRAY *a, const char* key, int len)
SYNOPSIS
tables_ok()
- thd thread (SQL slave thread normally)
+ thd thread (SQL slave thread normally). Mustn't be null.
tables list of tables to check
NOTES
@@ -885,6 +885,23 @@ bool tables_ok(THD* thd, TABLE_LIST* tables)
bool some_tables_updating= 0;
DBUG_ENTER("tables_ok");
+ /*
+ In routine, can't reliably pick and choose substatements, so always
+ replicate.
+ We can't reliably know if one substatement should be executed or not:
+ consider the case of this substatement: a SELECT on a non-replicated
+ constant table; if we don't execute it maybe it was going to fill a
+ variable which was going to be used by the next substatement to update
+ a replicated table? If we execute it maybe the constant non-replicated
+ table does not exist (and so we'll fail) while there was no need to
+ execute this as this SELECT does not influence replicated tables in the
+ rest of the routine? In other words: users are used to replicate-*-table
+ specifying how to handle updates to tables, these options don't say
+ anything about reads to tables; we can't guess.
+ */
+ if (thd->spcont)
+ DBUG_RETURN(1);
+
for (; tables; tables= tables->next_global)
{
char hash_key[2*NAME_LEN+2];
@@ -2791,12 +2808,17 @@ static int init_slave_thread(THD* thd, SLAVE_THD_TYPE thd_type)
DBUG_ENTER("init_slave_thread");
thd->system_thread = (thd_type == SLAVE_THD_SQL) ?
SYSTEM_THREAD_SLAVE_SQL : SYSTEM_THREAD_SLAVE_IO;
+ /*
+ The two next lines are needed for replication of SP (CREATE PROCEDURE
+ needs a valid user to store in mysql.proc).
+ */
+ thd->priv_user= (char *) "";
+ thd->priv_host[0]= '\0';
thd->host_or_ip= "";
thd->client_capabilities = 0;
my_net_init(&thd->net, 0);
thd->net.read_timeout = slave_net_timeout;
thd->master_access= ~0;
- thd->priv_user = 0;
thd->slave_thread = 1;
set_slave_thread_options(thd);
/*
diff --git a/sql/sp.cc b/sql/sp.cc
index 1956f32f2c6..81513cb3198 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -58,6 +58,9 @@ enum
bool mysql_proc_table_exists= 1;
+/* Tells what SP_DEFAULT_ACCESS should be mapped to */
+#define SP_DEFAULT_ACCESS_MAPPING SP_CONTAINS_SQL
+
/* *opened=true means we opened ourselves */
static int
db_find_routine_aux(THD *thd, int type, sp_name *name,
@@ -189,7 +192,7 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
chistics.daccess= SP_MODIFIES_SQL_DATA;
break;
default:
- chistics.daccess= SP_CONTAINS_SQL;
+ chistics.daccess= SP_DEFAULT_ACCESS_MAPPING;
}
if ((ptr= get_field(thd->mem_root,
@@ -425,9 +428,46 @@ db_create_routine(THD *thd, int type, sp_head *sp)
store(sp->m_chistics->comment.str, sp->m_chistics->comment.length,
system_charset_info);
+ if (!trust_routine_creators && mysql_bin_log.is_open())
+ {
+ if (!sp->m_chistics->detistic)
+ {
+ /*
+ Note that for a _function_ this test is not enough; one could use
+ a non-deterministic read-only function in an update statement.
+ */
+ enum enum_sp_data_access access=
+ (sp->m_chistics->daccess == SP_DEFAULT_ACCESS) ?
+ SP_DEFAULT_ACCESS_MAPPING : sp->m_chistics->daccess;
+ if (access == SP_CONTAINS_SQL ||
+ access == SP_MODIFIES_SQL_DATA)
+ {
+ my_message(ER_BINLOG_UNSAFE_ROUTINE,
+ ER(ER_BINLOG_UNSAFE_ROUTINE), MYF(0));
+ ret= SP_INTERNAL_ERROR;
+ goto done;
+ }
+ }
+ if (!(thd->master_access & SUPER_ACL))
+ {
+ my_message(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER,
+ ER(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER), MYF(0));
+ ret= SP_INTERNAL_ERROR;
+ goto done;
+ }
+ }
+
ret= SP_OK;
if (table->file->write_row(table->record[0]))
ret= SP_WRITE_ROW_FAILED;
+ else if (mysql_bin_log.is_open())
+ {
+ thd->clear_error();
+ /* Such a statement can always go directly to binlog, no trans cache */
+ Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+ mysql_bin_log.write(&qinfo);
+ }
+
}
done:
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index df19c6e55fd..58cbf1996bd 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -1515,7 +1515,7 @@ static bool update_user_table(THD *thd, const char *host, const char *user,
*/
tables.updating= 1;
/* Thanks to bzero, tables.next==0 */
- if (!tables_ok(0, &tables))
+ if (!tables_ok(thd, &tables))
DBUG_RETURN(0);
}
#endif
@@ -2699,7 +2699,7 @@ bool mysql_table_grant(THD *thd, TABLE_LIST *table_list,
account in tests.
*/
tables[0].updating= tables[1].updating= tables[2].updating= 1;
- if (!tables_ok(0, tables))
+ if (!tables_ok(thd, tables))
DBUG_RETURN(FALSE);
}
#endif
@@ -2904,7 +2904,7 @@ bool mysql_procedure_grant(THD *thd, TABLE_LIST *table_list,
account in tests.
*/
tables[0].updating= tables[1].updating= 1;
- if (!tables_ok(0, tables))
+ if (!tables_ok(thd, tables))
DBUG_RETURN(FALSE);
}
#endif
@@ -3035,7 +3035,7 @@ bool mysql_grant(THD *thd, const char *db, List <LEX_USER> &list,
account in tests.
*/
tables[0].updating= tables[1].updating= 1;
- if (!tables_ok(0, tables))
+ if (!tables_ok(thd, tables))
DBUG_RETURN(FALSE);
}
#endif
@@ -4245,7 +4245,7 @@ int open_grant_tables(THD *thd, TABLE_LIST *tables)
*/
tables[0].updating=tables[1].updating=tables[2].updating=
tables[3].updating=tables[4].updating=1;
- if (!tables_ok(0, tables))
+ if (!tables_ok(thd, tables))
DBUG_RETURN(1);
tables[0].updating=tables[1].updating=tables[2].updating=
tables[3].updating=tables[4].updating=0;;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 22febd50035..7b465a0c086 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4097,7 +4097,43 @@ unsent_create_error:
thd->variables.select_limit= HA_POS_ERROR;
thd->row_count_func= 0;
+ tmp_disable_binlog(thd); /* don't binlog the substatements */
res= sp->execute_procedure(thd, &lex->value_list);
+ reenable_binlog(thd);
+
+ /*
+ We write CALL to binlog; on the opposite we didn't write the
+ substatements. That choice is necessary because the substatements
+ may use local vars.
+ Binlogging should happen when all tables are locked. They are locked
+ just above, and unlocked by close_thread_tables(). All tables which
+ are to be updated are locked like with a table-level write lock, and
+ this also applies to InnoDB (I tested - note that it reduces
+ InnoDB's concurrency as we don't use row-level locks). So binlogging
+ below is safe.
+ Note the limitation: if the SP returned an error, but still did some
+ updates, we do NOT binlog it. This is because otherwise "permission
+ denied", "table does not exist" etc would stop the slave quite
+ often. There is no easy way to know if the SP updated something
+ (even no_trans_update is not suitable, as it may be a transactional
+ autocommit update which happened, and no_trans_update covers only
+ INSERT/UPDATE/LOAD).
+ */
+ if (mysql_bin_log.is_open() &&
+ (sp->m_chistics->daccess == SP_CONTAINS_SQL ||
+ sp->m_chistics->daccess == SP_MODIFIES_SQL_DATA))
+ {
+ if (res)
+ push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_FAILED_ROUTINE_BREAK_BINLOG,
+ ER(ER_FAILED_ROUTINE_BREAK_BINLOG));
+ else
+ {
+ thd->clear_error();
+ Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+ mysql_bin_log.write(&qinfo);
+ }
+ }
/*
If warnings have been cleared, we have to clear total_warn_count
@@ -4153,14 +4189,32 @@ unsent_create_error:
sp->m_name.str, 0))
goto error;
memcpy(&lex->sp_chistics, &chistics, sizeof(lex->sp_chistics));
- if (lex->sql_command == SQLCOM_ALTER_PROCEDURE)
- result= sp_update_procedure(thd, lex->spname, &lex->sp_chistics);
- else
- result= sp_update_function(thd, lex->spname, &lex->sp_chistics);
+ if (!trust_routine_creators && mysql_bin_log.is_open() &&
+ !sp->m_chistics->detistic &&
+ (chistics.daccess == SP_CONTAINS_SQL ||
+ chistics.daccess == SP_MODIFIES_SQL_DATA))
+ {
+ my_message(ER_BINLOG_UNSAFE_ROUTINE,
+ ER(ER_BINLOG_UNSAFE_ROUTINE), MYF(0));
+ result= SP_INTERNAL_ERROR;
+ }
+ else
+ {
+ if (lex->sql_command == SQLCOM_ALTER_PROCEDURE)
+ result= sp_update_procedure(thd, lex->spname, &lex->sp_chistics);
+ else
+ result= sp_update_function(thd, lex->spname, &lex->sp_chistics);
+ }
}
switch (result)
{
case SP_OK:
+ if (mysql_bin_log.is_open())
+ {
+ thd->clear_error();
+ Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+ mysql_bin_log.write(&qinfo);
+ }
send_ok(thd);
break;
case SP_KEY_NOT_FOUND:
@@ -4237,6 +4291,12 @@ unsent_create_error:
switch (result)
{
case SP_OK:
+ if (mysql_bin_log.is_open())
+ {
+ thd->clear_error();
+ Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+ mysql_bin_log.write(&qinfo);
+ }
send_ok(thd);
break;
case SP_KEY_NOT_FOUND:
@@ -4495,6 +4555,7 @@ unsent_create_error:
break;
}
thd->proc_info="query end";
+ /* Two binlog-related cleanups: */
if (thd->one_shot_set)
{
/*
View Lorry Controller Status