rpl_sp.result 9.27 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
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|
24
ERROR HY000: This routine 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_routine_creators variable)
25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
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
63 64
master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (unix_timestamp())
65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
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
80
show binlog events from 518;
81
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
82 83
master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (unix_timestamp())
84 85 86 87 88 89
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;
90
ERROR HY000: This routine 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_routine_creators variable)
91 92 93 94 95 96 97 98 99 100 101 102
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);
103
ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)
104 105 106 107 108 109 110 111
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();
112
Got one of the listed errors
113 114
show warnings;
Level	Code	Message
115
Error	1142	INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
116
Warning	1417	A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes
117 118 119 120
call foo3();
show warnings;
Level	Code	Message
call foo4();
121
Got one of the listed errors
122 123
show warnings;
Level	Code	Message
124
Error	1142	INSERT command denied to user 'zedjzlcsjhd'@'127.0.0.1' for table 't1'
125
Warning	1417	A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes
126 127 128 129
alter procedure foo4 sql security invoker;
call foo4();
show warnings;
Level	Code	Message
130
show binlog events from 990;
131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146
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
147 148 149
master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values(3)
master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (15)
master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values(3)
150
master-bin.000001	#	Query	1	#	use `mysqltest1`; alter procedure foo4 sql security invoker
151 152
master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values(3)
master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (5)
153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168
select * from t1;
a
15
5
select * from t2;
a
3
3
3
select * from t1;
a
15
5
select * from t2;
a
3
169 170
3
3
171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206
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
207
20
208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237
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	@	#	#		
238 239 240 241 242 243 244 245 246 247 248 249 250
create trigger trg before insert on t1 for each row set new.a= 10;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
flush logs;
delete from t1;
create trigger trg before insert on t1 for each row set new.a= 10;
insert into t1 values (1);
select * from t1;
a
10
select * from t1;
a
10
delete from t1;
251
drop trigger trg;
252 253 254 255 256 257 258 259 260 261
insert into t1 values (1);
select * from t1;
a
1
show binlog events in 'master-bin.000002' from 98;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000002	#	Query	1	#	use `mysqltest1`; delete from t1
master-bin.000002	#	Query	1	#	use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10
master-bin.000002	#	Query	1	#	use `mysqltest1`; insert into t1 values (1)
master-bin.000002	#	Query	1	#	use `mysqltest1`; delete from t1
262
master-bin.000002	#	Query	1	#	use `mysqltest1`; drop trigger trg
263 264 265 266
master-bin.000002	#	Query	1	#	use `mysqltest1`; insert into t1 values (1)
select * from t1;
a
1
267 268 269 270 271 272 273 274
create procedure foo()
not deterministic
reads sql data
select * from t1;
call foo();
a
1
drop procedure foo;
275 276 277
drop function fn1;
drop database mysqltest1;
drop user "zedjzlcsjhd"@127.0.0.1;