| blob | 8d0d6a8bf8629b3985c299824c8e8a46ff1f3712 |
1 include/master-slave.inc
2 [connection master]
3 drop database if exists mysqltest1;
4 create database mysqltest1;
5 use mysqltest1;
6 create table t1 (a varchar(100));
7 use mysqltest1;
8 create procedure foo()
9 begin
10 declare b int;
11 set b = 8;
12 insert into t1 values (b);
13 insert into t1 values (unix_timestamp());
14 end|
15 select * from mysql.proc where name='foo' and db='mysqltest1';
16 db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
17 mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin
18 declare b int;
19 set b = 8;
20 insert into t1 values (b);
21 insert into t1 values (unix_timestamp());
22 end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
23 declare b int;
24 set b = 8;
25 insert into t1 values (b);
26 insert into t1 values (unix_timestamp());
27 end
28 select * from mysql.proc where name='foo' and db='mysqltest1';
29 db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
30 mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin
31 declare b int;
32 set b = 8;
33 insert into t1 values (b);
34 insert into t1 values (unix_timestamp());
35 end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
36 declare b int;
37 set b = 8;
38 insert into t1 values (b);
39 insert into t1 values (unix_timestamp());
40 end
41 set timestamp=1000000000;
42 call foo();
43 select * from t1;
44 a
45 8
46 1000000000
47 select * from t1;
48 a
49 8
50 1000000000
51 delete from t1;
52 create procedure foo2()
53 select * from mysqltest1.t1;
54 call foo2();
55 a
56 alter procedure foo2 contains sql;
57 drop table t1;
58 create table t1 (a int);
59 create table t2 like t1;
60 create procedure foo3()
61 deterministic
62 insert into t1 values (15);
63 grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
64 grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
65 grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;
66 SELECT 1;
67 1
68 1
69 create procedure foo4()
70 deterministic
71 begin
72 insert into t2 values(3);
73 insert into t1 values (5);
74 end|
75 call foo4();
76 Got one of the listed errors
77 call foo3();
78 show warnings;
79 Level Code Message
80 call foo4();
81 Got one of the listed errors
82 alter procedure foo4 sql security invoker;
83 call foo4();
84 show warnings;
85 Level Code Message
86 select * from t1;
87 a
88 15
89 5
90 select * from t2;
91 a
92 3
93 3
94 3
95 select * from t1;
96 a
97 15
98 5
99 select * from t2;
100 a
101 3
102 3
103 3
104 delete from t2;
105 alter table t2 add unique (a);
106 drop procedure foo4;
107 create procedure foo4()
108 deterministic
109 begin
110 insert into t2 values(20),(20);
111 end|
112 call foo4();
113 ERROR 23000: Duplicate entry '20' for key 'a'
114 show warnings;
115 Level Code Message
116 Error 1062 Duplicate entry '20' for key 'a'
117 select * from t2;
118 a
119 20
120 select * from t2;
121 a
122 20
123 select * from mysql.proc where name="foo4" and db='mysqltest1';
124 db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
125 mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES DEFINER begin
126 insert into t2 values(20),(20);
127 end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
128 insert into t2 values(20),(20);
129 end
130 drop procedure foo4;
131 select * from mysql.proc where name="foo4" and db='mysqltest1';
132 db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
133 select * from mysql.proc where name="foo4" and db='mysqltest1';
134 db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
135 drop procedure foo;
136 drop procedure foo2;
137 drop procedure foo3;
138 create function fn1(x int)
139 returns int
140 begin
141 insert into t1 values (x);
142 return x+2;
143 end|
144 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)
145 create function fn1(x int)
146 returns int
147 deterministic
148 begin
149 insert into t1 values (x);
150 return x+2;
151 end|
152 delete t1,t2 from t1,t2;
153 select fn1(20);
154 fn1(20)
155 22
156 insert into t2 values(fn1(21));
157 select * from t1;
158 a
159 21
160 20
161 select * from t2;
162 a
163 23
164 select * from t1;
165 a
166 21
167 20
168 select * from t2;
169 a
170 23
171 drop function fn1;
172 create function fn1()
173 returns int
174 no sql
175 begin
176 return unix_timestamp();
177 end|
178 alter function fn1 contains sql;
179 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)
180 delete from t1;
181 set timestamp=1000000000;
182 insert into t1 values(fn1());
183 create function fn2()
184 returns int
185 no sql
186 begin
187 return unix_timestamp();
188 end|
189 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_function_creators variable)
190 set @old_log_bin_trust_routine_creators= @@global.log_bin_trust_routine_creators;
191 set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
192 set global log_bin_trust_routine_creators=1;
193 Warnings:
194 Warning 1287 '@@log_bin_trust_routine_creators' is deprecated and will be removed in a future release. Please use '@@log_bin_trust_function_creators' instead
195 set global log_bin_trust_function_creators=0;
196 set global log_bin_trust_function_creators=1;
197 set @old_log_bin_trust_routine_creators= @@global.log_bin_trust_routine_creators;
198 set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
199 set global log_bin_trust_function_creators=1;
200 create function fn2()
201 returns int
202 no sql
203 begin
204 return unix_timestamp();
205 end|
206 create function fn3()
207 returns int
208 not deterministic
209 reads sql data
210 begin
211 return 0;
212 end|
213 select fn3();
214 fn3()
215 0
216 select * from mysql.proc where db='mysqltest1';
217 db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
218 mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin
219 return unix_timestamp();
220 end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
221 return unix_timestamp();
222 end
223 mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin
224 return unix_timestamp();
225 end zedjzlcsjhd@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
226 return unix_timestamp();
227 end
228 mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin
229 return 0;
230 end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
231 return 0;
232 end
233 select * from t1;
234 a
235 1000000000
236 use mysqltest1;
237 select * from t1;
238 a
239 1000000000
240 select * from mysql.proc where db='mysqltest1';
241 db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
242 mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin
243 return unix_timestamp();
244 end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
245 return unix_timestamp();
246 end
247 mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin
248 return unix_timestamp();
249 end zedjzlcsjhd@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
250 return unix_timestamp();
251 end
252 mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin
253 return 0;
254 end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
255 return 0;
256 end
257 delete from t2;
258 alter table t2 add unique (a);
259 drop function fn1;
260 create function fn1(x int)
261 returns int
262 begin
263 insert into t2 values(x),(x);
264 return 10;
265 end|
266 do fn1(100);
267 Warnings:
268 Error 1062 Duplicate entry '100' for key 'a'
269 select fn1(20);
270 ERROR 23000: Duplicate entry '20' for key 'a'
271 select * from t2;
272 a
273 20
274 100
275 select * from t2;
276 a
277 20
278 100
279 create trigger trg before insert on t1 for each row set new.a= 10;
280 ERROR 42000: TRIGGER command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
281 delete from t1;
282 create trigger trg before insert on t1 for each row set new.a= 10;
283 insert into t1 values (1);
284 select * from t1;
285 a
286 10
287 select * from t1;
288 a
289 10
290 delete from t1;
291 drop trigger trg;
292 insert into t1 values (1);
293 select * from t1;
294 a
295 1
296 select * from t1;
297 a
298 1
299 create procedure foo()
300 not deterministic
301 reads sql data
302 select * from t1;
303 call foo();
304 a
305 1
306 drop procedure foo;
307 drop function fn1;
308 drop database mysqltest1;
309 drop user "zedjzlcsjhd"@127.0.0.1;
310 use test;
311 use test;
312 drop function if exists f1;
313 create function f1() returns int reads sql data
314 begin
315 declare var integer;
316 declare c cursor for select a from v1;
317 open c;
318 fetch c into var;
319 close c;
320 return var;
321 end|
322 create view v1 as select 1 as a;
323 create table t1 (a int);
324 insert into t1 (a) values (f1());
325 select * from t1;
326 a
327 1
328 drop view v1;
329 drop function f1;
330 select * from t1;
331 a
332 1
333 DROP PROCEDURE IF EXISTS p1;
334 DROP TABLE IF EXISTS t1;
335 CREATE TABLE t1(col VARCHAR(10));
336 CREATE PROCEDURE p1(arg VARCHAR(10))
337 INSERT INTO t1 VALUES(arg);
338 CALL p1('test');
339 SELECT * FROM t1;
340 col
341 test
342 SELECT * FROM t1;
343 col
344 test
345 DROP PROCEDURE p1;
347 ---> Test for BUG#20438
349 ---> Preparing environment...
350 ---> connection: master
351 DROP PROCEDURE IF EXISTS p1;
352 DROP FUNCTION IF EXISTS f1;
354 ---> Synchronizing slave with master...
356 ---> connection: master
358 ---> Creating procedure...
359 /*!50003 CREATE PROCEDURE p1() SET @a = 1 */;
360 /*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */;
362 ---> Checking on master...
363 SHOW CREATE PROCEDURE p1;
364 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
365 p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
366 SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci
367 SHOW CREATE FUNCTION f1;
368 Function sql_mode Create Function character_set_client collation_connection Database Collation
369 f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
370 RETURN 0 latin1 latin1_swedish_ci latin1_swedish_ci
372 ---> Synchronizing slave with master...
373 ---> connection: master
375 ---> Checking on slave...
376 SHOW CREATE PROCEDURE p1;
377 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
378 p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
379 SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci
380 SHOW CREATE FUNCTION f1;
381 Function sql_mode Create Function character_set_client collation_connection Database Collation
382 f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
383 RETURN 0 latin1 latin1_swedish_ci latin1_swedish_ci
385 ---> connection: master
387 ---> Cleaning up...
388 DROP PROCEDURE p1;
389 DROP FUNCTION f1;
390 drop table t1;
391 drop database if exists mysqltest;
392 drop database if exists mysqltest2;
393 create database mysqltest;
394 create database mysqltest2;
395 use mysqltest2;
396 create table t ( t integer );
397 create procedure mysqltest.test() begin end;
398 insert into t values ( 1 );
399 create procedure `\\`.test() begin end;
400 ERROR 42000: Unknown database '\\'
401 create function f1 () returns int
402 begin
403 insert into t values (1);
404 return 0;
405 end|
406 use mysqltest;
407 set @a:= mysqltest2.f1();
408 show binlog events from <binlog_start>;
409 Log_name Pos Event_type Server_id End_log_pos Info
410 master-bin.000001 # Query # # drop database if exists mysqltest1
411 master-bin.000001 # Query # # create database mysqltest1
412 master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a varchar(100))
413 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`()
414 begin
415 declare b int;
416 set b = 8;
417 insert into t1 values (b);
418 insert into t1 values (unix_timestamp());
419 end
420 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
421 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (unix_timestamp())
422 master-bin.000001 # Query # # use `mysqltest1`; delete from t1
423 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `foo2`()
424 select * from mysqltest1.t1
425 master-bin.000001 # Query # # use `mysqltest1`; alter procedure foo2 contains sql
426 master-bin.000001 # Query # # use `mysqltest1`; drop table t1
427 master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a int)
428 master-bin.000001 # Query # # use `mysqltest1`; create table t2 like t1
429 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `foo3`()
430 DETERMINISTIC
431 insert into t1 values (15)
432 master-bin.000001 # Query # # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
433 master-bin.000001 # Query # # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
434 master-bin.000001 # Query # # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
435 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` PROCEDURE `foo4`()
436 DETERMINISTIC
437 begin
438 insert into t2 values(3);
439 insert into t1 values (5);
440 end
441 master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(3)
442 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (15)
443 master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(3)
444 master-bin.000001 # Query # # use `mysqltest1`; alter procedure foo4 sql security invoker
445 master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(3)
446 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (5)
447 master-bin.000001 # Query # # use `mysqltest1`; delete from t2
448 master-bin.000001 # Query # # use `mysqltest1`; alter table t2 add unique (a)
449 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo4
450 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `foo4`()
451 DETERMINISTIC
452 begin
453 insert into t2 values(20),(20);
454 end
455 master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(20),(20)
456 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo4
457 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo
458 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo2
459 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo3
460 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
461 DETERMINISTIC
462 begin
463 insert into t1 values (x);
464 return x+2;
465 end
466 master-bin.000001 # Query # # use `mysqltest1`; delete t1,t2 from t1,t2
467 master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
468 master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(fn1(21))
469 master-bin.000001 # Query # # use `mysqltest1`; drop function fn1
470 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`() RETURNS int(11)
471 NO SQL
472 begin
473 return unix_timestamp();
474 end
475 master-bin.000001 # Query # # use `mysqltest1`; delete from t1
476 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values(fn1())
477 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` FUNCTION `fn2`() RETURNS int(11)
478 NO SQL
479 begin
480 return unix_timestamp();
481 end
482 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS int(11)
483 READS SQL DATA
484 begin
485 return 0;
486 end
487 master-bin.000001 # Query # # use `mysqltest1`; delete from t2
488 master-bin.000001 # Query # # use `mysqltest1`; alter table t2 add unique (a)
489 master-bin.000001 # Query # # use `mysqltest1`; drop function fn1
490 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
491 begin
492 insert into t2 values(x),(x);
493 return 10;
494 end
495 master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(100)
496 master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
497 master-bin.000001 # Query # # use `mysqltest1`; delete from t1
498 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10
499 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (1)
500 master-bin.000001 # Query # # use `mysqltest1`; delete from t1
501 master-bin.000001 # Query # # use `mysqltest1`; drop trigger trg
502 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (1)
503 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`()
504 READS SQL DATA
505 select * from t1
506 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo
507 master-bin.000001 # Query # # use `mysqltest1`; drop function fn1
508 master-bin.000001 # Query # # drop database mysqltest1
509 master-bin.000001 # Query # # drop user "zedjzlcsjhd"@127.0.0.1
510 master-bin.000001 # Query # # use `test`; drop function if exists f1
511 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
512 READS SQL DATA
513 begin
514 declare var integer;
515 declare c cursor for select a from v1;
516 open c;
517 fetch c into var;
518 close c;
519 return var;
520 end
521 master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 as a
522 master-bin.000001 # Query # # use `test`; create table t1 (a int)
523 master-bin.000001 # Query # # use `test`; insert into t1 (a) values (f1())
524 master-bin.000001 # Query # # use `test`; drop view v1
525 master-bin.000001 # Query # # use `test`; drop function f1
526 master-bin.000001 # Query # # use `test`; DROP PROCEDURE IF EXISTS p1
527 master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
528 master-bin.000001 # Query # # use `test`; CREATE TABLE t1(col VARCHAR(10))
529 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(arg VARCHAR(10))
530 INSERT INTO t1 VALUES(arg)
531 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test' COLLATE 'latin1_swedish_ci'))
532 master-bin.000001 # Query # # use `test`; DROP PROCEDURE p1
533 master-bin.000001 # Query # # use `test`; DROP PROCEDURE IF EXISTS p1
534 master-bin.000001 # Query # # use `test`; DROP FUNCTION IF EXISTS f1
535 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
536 SET @a = 1
537 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
538 RETURN 0
539 master-bin.000001 # Query # # use `test`; DROP PROCEDURE p1
540 master-bin.000001 # Query # # use `test`; DROP FUNCTION f1
541 master-bin.000001 # Query # # use `test`; drop table t1
542 master-bin.000001 # Query # # drop database if exists mysqltest
543 master-bin.000001 # Query # # drop database if exists mysqltest2
544 master-bin.000001 # Query # # create database mysqltest
545 master-bin.000001 # Query # # create database mysqltest2
546 master-bin.000001 # Query # # use `mysqltest2`; create table t ( t integer )
547 master-bin.000001 # Query # # use `mysqltest2`; CREATE DEFINER=`root`@`localhost` PROCEDURE `mysqltest`.`test`()
548 begin end
549 master-bin.000001 # Query # # use `mysqltest2`; insert into t values ( 1 )
550 master-bin.000001 # Query # # use `mysqltest2`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
551 begin
552 insert into t values (1);
553 return 0;
554 end
555 master-bin.000001 # Query # # use `mysqltest`; SELECT `mysqltest2`.`f1`()
556 set @@global.log_bin_trust_routine_creators= @old_log_bin_trust_routine_creators;
557 Warnings:
558 Warning 1287 '@@log_bin_trust_routine_creators' is deprecated and will be removed in a future release. Please use '@@log_bin_trust_function_creators' instead
559 set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
560 set @@global.log_bin_trust_routine_creators= @old_log_bin_trust_routine_creators;
561 Warnings:
562 Warning 1287 '@@log_bin_trust_routine_creators' is deprecated and will be removed in a future release. Please use '@@log_bin_trust_function_creators' instead
563 set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
564 drop database mysqltest;
565 drop database mysqltest2;
566 use test;
567 /*!50001 create procedure `mysqltestbug36570_p1`() */
568 begin
569 select 1;
570 end|
571 use mysql|
572 create procedure test.` mysqltestbug36570_p2`(/*!50001 a int*/)`label`:
573 begin
574 select a;
575 end|
576 /*!50001 create function test.mysqltestbug36570_f1() */
577 returns int
578 /*!50001 deterministic */
579 begin
580 return 3;
581 end|
582 use test|
583 show procedure status like '%mysqltestbug36570%';
584 Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
585 test mysqltestbug36570_p2 PROCEDURE root@localhost t t DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
586 test mysqltestbug36570_p1 PROCEDURE root@localhost t t DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
587 show create procedure ` mysqltestbug36570_p2`;
588 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
589 mysqltestbug36570_p2 CREATE DEFINER=`root`@`localhost` PROCEDURE ` mysqltestbug36570_p2`( a int)
590 `label`:
591 begin
592 select a;
593 end latin1 latin1_swedish_ci latin1_swedish_ci
594 show procedure status like '%mysqltestbug36570%';
595 Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
596 test mysqltestbug36570_p2 PROCEDURE root@localhost t t DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
597 test mysqltestbug36570_p1 PROCEDURE root@localhost t t DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
598 show create procedure ` mysqltestbug36570_p2`;
599 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
600 mysqltestbug36570_p2 CREATE DEFINER=`root`@`localhost` PROCEDURE ` mysqltestbug36570_p2`( a int)
601 `label`:
602 begin
603 select a;
604 end latin1 latin1_swedish_ci latin1_swedish_ci
605 call ` mysqltestbug36570_p2`(42);
606 a
607 42
608 show function status like '%mysqltestbug36570%';
609 Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
610 test mysqltestbug36570_f1 FUNCTION root@localhost t t DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
611 flush logs;
612 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
613 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
614 DELIMITER /*!*/;
615 ROLLBACK/*!*/;
616 SET TIMESTAMP=t/*!*/;
617 SET @@session.pseudo_thread_id=999999999/*!*/;
618 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
619 SET @@session.sql_mode=0/*!*/;
620 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
621 /*!\C latin1 *//*!*/;
622 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
623 SET @@session.lc_time_names=0/*!*/;
624 SET @@session.collation_database=DEFAULT/*!*/;
625 drop database if exists mysqltest1
626 /*!*/;
627 SET TIMESTAMP=t/*!*/;
628 create database mysqltest1
629 /*!*/;
630 use `mysqltest1`/*!*/;
631 SET TIMESTAMP=t/*!*/;
632 create table t1 (a varchar(100))
633 /*!*/;
634 SET TIMESTAMP=t/*!*/;
635 CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`()
636 begin
637 declare b int;
638 set b = 8;
639 insert into t1 values (b);
640 insert into t1 values (unix_timestamp());
641 end
642 /*!*/;
643 SET TIMESTAMP=t/*!*/;
644 insert into t1 values ( NAME_CONST('b',8))
645 /*!*/;
646 SET TIMESTAMP=t/*!*/;
647 insert into t1 values (unix_timestamp())
648 /*!*/;
649 SET TIMESTAMP=t/*!*/;
650 delete from t1
651 /*!*/;
652 SET TIMESTAMP=t/*!*/;
653 CREATE DEFINER=`root`@`localhost` PROCEDURE `foo2`()
654 select * from mysqltest1.t1
655 /*!*/;
656 SET TIMESTAMP=t/*!*/;
657 alter procedure foo2 contains sql
658 /*!*/;
659 SET TIMESTAMP=t/*!*/;
660 drop table t1
661 /*!*/;
662 SET TIMESTAMP=t/*!*/;
663 create table t1 (a int)
664 /*!*/;
665 SET TIMESTAMP=t/*!*/;
666 create table t2 like t1
667 /*!*/;
668 SET TIMESTAMP=t/*!*/;
669 CREATE DEFINER=`root`@`localhost` PROCEDURE `foo3`()
670 DETERMINISTIC
671 insert into t1 values (15)
672 /*!*/;
673 SET TIMESTAMP=t/*!*/;
674 grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
675 /*!*/;
676 SET TIMESTAMP=t/*!*/;
677 grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
678 /*!*/;
679 SET TIMESTAMP=t/*!*/;
680 grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
681 /*!*/;
682 SET TIMESTAMP=t/*!*/;
683 CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` PROCEDURE `foo4`()
684 DETERMINISTIC
685 begin
686 insert into t2 values(3);
687 insert into t1 values (5);
688 end
689 /*!*/;
690 SET TIMESTAMP=t/*!*/;
691 insert into t2 values(3)
692 /*!*/;
693 SET TIMESTAMP=t/*!*/;
694 insert into t1 values (15)
695 /*!*/;
696 SET TIMESTAMP=t/*!*/;
697 insert into t2 values(3)
698 /*!*/;
699 SET TIMESTAMP=t/*!*/;
700 alter procedure foo4 sql security invoker
701 /*!*/;
702 SET TIMESTAMP=t/*!*/;
703 insert into t2 values(3)
704 /*!*/;
705 SET TIMESTAMP=t/*!*/;
706 insert into t1 values (5)
707 /*!*/;
708 SET TIMESTAMP=t/*!*/;
709 delete from t2
710 /*!*/;
711 SET TIMESTAMP=t/*!*/;
712 alter table t2 add unique (a)
713 /*!*/;
714 SET TIMESTAMP=t/*!*/;
715 drop procedure foo4
716 /*!*/;
717 SET TIMESTAMP=t/*!*/;
718 CREATE DEFINER=`root`@`localhost` PROCEDURE `foo4`()
719 DETERMINISTIC
720 begin
721 insert into t2 values(20),(20);
722 end
723 /*!*/;
724 SET TIMESTAMP=t/*!*/;
725 insert into t2 values(20),(20)
726 /*!*/;
727 SET TIMESTAMP=t/*!*/;
728 drop procedure foo4
729 /*!*/;
730 SET TIMESTAMP=t/*!*/;
731 drop procedure foo
732 /*!*/;
733 SET TIMESTAMP=t/*!*/;
734 drop procedure foo2
735 /*!*/;
736 SET TIMESTAMP=t/*!*/;
737 drop procedure foo3
738 /*!*/;
739 SET TIMESTAMP=t/*!*/;
740 CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
741 DETERMINISTIC
742 begin
743 insert into t1 values (x);
744 return x+2;
745 end
746 /*!*/;
747 SET TIMESTAMP=t/*!*/;
748 delete t1,t2 from t1,t2
749 /*!*/;
750 SET TIMESTAMP=t/*!*/;
751 SELECT `mysqltest1`.`fn1`(20)
752 /*!*/;
753 SET TIMESTAMP=t/*!*/;
754 insert into t2 values(fn1(21))
755 /*!*/;
756 SET TIMESTAMP=t/*!*/;
757 drop function fn1
758 /*!*/;
759 SET TIMESTAMP=t/*!*/;
760 CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`() RETURNS int(11)
761 NO SQL
762 begin
763 return unix_timestamp();
764 end
765 /*!*/;
766 SET TIMESTAMP=t/*!*/;
767 delete from t1
768 /*!*/;
769 SET TIMESTAMP=t/*!*/;
770 insert into t1 values(fn1())
771 /*!*/;
772 SET TIMESTAMP=t/*!*/;
773 CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` FUNCTION `fn2`() RETURNS int(11)
774 NO SQL
775 begin
776 return unix_timestamp();
777 end
778 /*!*/;
779 SET TIMESTAMP=t/*!*/;
780 CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS int(11)
781 READS SQL DATA
782 begin
783 return 0;
784 end
785 /*!*/;
786 SET TIMESTAMP=t/*!*/;
787 delete from t2
788 /*!*/;
789 SET TIMESTAMP=t/*!*/;
790 alter table t2 add unique (a)
791 /*!*/;
792 SET TIMESTAMP=t/*!*/;
793 drop function fn1
794 /*!*/;
795 SET TIMESTAMP=t/*!*/;
796 CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
797 begin
798 insert into t2 values(x),(x);
799 return 10;
800 end
801 /*!*/;
802 SET TIMESTAMP=t/*!*/;
803 SELECT `mysqltest1`.`fn1`(100)
804 /*!*/;
805 SET TIMESTAMP=t/*!*/;
806 SELECT `mysqltest1`.`fn1`(20)
807 /*!*/;
808 SET TIMESTAMP=t/*!*/;
809 delete from t1
810 /*!*/;
811 SET TIMESTAMP=t/*!*/;
812 CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10
813 /*!*/;
814 SET TIMESTAMP=t/*!*/;
815 insert into t1 values (1)
816 /*!*/;
817 SET TIMESTAMP=t/*!*/;
818 delete from t1
819 /*!*/;
820 SET TIMESTAMP=t/*!*/;
821 drop trigger trg
822 /*!*/;
823 SET TIMESTAMP=t/*!*/;
824 insert into t1 values (1)
825 /*!*/;
826 SET TIMESTAMP=t/*!*/;
827 CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`()
828 READS SQL DATA
829 select * from t1
830 /*!*/;
831 SET TIMESTAMP=t/*!*/;
832 drop procedure foo
833 /*!*/;
834 SET TIMESTAMP=t/*!*/;
835 drop function fn1
836 /*!*/;
837 SET TIMESTAMP=t/*!*/;
838 drop database mysqltest1
839 /*!*/;
840 SET TIMESTAMP=t/*!*/;
841 drop user "zedjzlcsjhd"@127.0.0.1
842 /*!*/;
843 use `test`/*!*/;
844 SET TIMESTAMP=t/*!*/;
845 drop function if exists f1
846 /*!*/;
847 SET TIMESTAMP=t/*!*/;
848 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
849 READS SQL DATA
850 begin
851 declare var integer;
852 declare c cursor for select a from v1;
853 open c;
854 fetch c into var;
855 close c;
856 return var;
857 end
858 /*!*/;
859 SET TIMESTAMP=t/*!*/;
860 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 as a
861 /*!*/;
862 SET TIMESTAMP=t/*!*/;
863 create table t1 (a int)
864 /*!*/;
865 SET TIMESTAMP=t/*!*/;
866 insert into t1 (a) values (f1())
867 /*!*/;
868 SET TIMESTAMP=t/*!*/;
869 drop view v1
870 /*!*/;
871 SET TIMESTAMP=t/*!*/;
872 drop function f1
873 /*!*/;
874 SET TIMESTAMP=t/*!*/;
875 DROP PROCEDURE IF EXISTS p1
876 /*!*/;
877 SET TIMESTAMP=t/*!*/;
878 DROP TABLE IF EXISTS t1
879 /*!*/;
880 SET TIMESTAMP=t/*!*/;
881 CREATE TABLE t1(col VARCHAR(10))
882 /*!*/;
883 SET TIMESTAMP=t/*!*/;
884 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(arg VARCHAR(10))
885 INSERT INTO t1 VALUES(arg)
886 /*!*/;
887 SET TIMESTAMP=t/*!*/;
888 INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test' COLLATE 'latin1_swedish_ci'))
889 /*!*/;
890 SET TIMESTAMP=t/*!*/;
891 DROP PROCEDURE p1
892 /*!*/;
893 SET TIMESTAMP=t/*!*/;
894 DROP PROCEDURE IF EXISTS p1
895 /*!*/;
896 SET TIMESTAMP=t/*!*/;
897 DROP FUNCTION IF EXISTS f1
898 /*!*/;
899 SET TIMESTAMP=t/*!*/;
900 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
901 SET @a = 1
902 /*!*/;
903 SET TIMESTAMP=t/*!*/;
904 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
905 RETURN 0
906 /*!*/;
907 SET TIMESTAMP=t/*!*/;
908 DROP PROCEDURE p1
909 /*!*/;
910 SET TIMESTAMP=t/*!*/;
911 DROP FUNCTION f1
912 /*!*/;
913 SET TIMESTAMP=t/*!*/;
914 drop table t1
915 /*!*/;
916 SET TIMESTAMP=t/*!*/;
917 drop database if exists mysqltest
918 /*!*/;
919 SET TIMESTAMP=t/*!*/;
920 drop database if exists mysqltest2
921 /*!*/;
922 SET TIMESTAMP=t/*!*/;
923 create database mysqltest
924 /*!*/;
925 SET TIMESTAMP=t/*!*/;
926 create database mysqltest2
927 /*!*/;
928 use `mysqltest2`/*!*/;
929 SET TIMESTAMP=t/*!*/;
930 create table t ( t integer )
931 /*!*/;
932 SET TIMESTAMP=t/*!*/;
933 CREATE DEFINER=`root`@`localhost` PROCEDURE `mysqltest`.`test`()
934 begin end
935 /*!*/;
936 SET TIMESTAMP=t/*!*/;
937 insert into t values ( 1 )
938 /*!*/;
939 SET TIMESTAMP=t/*!*/;
940 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
941 begin
942 insert into t values (1);
943 return 0;
944 end
945 /*!*/;
946 use `mysqltest`/*!*/;
947 SET TIMESTAMP=t/*!*/;
948 SELECT `mysqltest2`.`f1`()
949 /*!*/;
950 SET TIMESTAMP=t/*!*/;
951 drop database mysqltest
952 /*!*/;
953 SET TIMESTAMP=t/*!*/;
954 drop database mysqltest2
955 /*!*/;
956 use `test`/*!*/;
957 SET TIMESTAMP=t/*!*/;
958 CREATE DEFINER=`root`@`localhost` PROCEDURE `mysqltestbug36570_p1`()
959 begin
960 select 1;
961 end
962 /*!*/;
963 use `mysql`/*!*/;
964 SET TIMESTAMP=t/*!*/;
965 CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.` mysqltestbug36570_p2`( a int)
966 `label`:
967 begin
968 select a;
969 end
970 /*!*/;
971 SET TIMESTAMP=t/*!*/;
972 CREATE DEFINER=`root`@`localhost` FUNCTION `test`.`mysqltestbug36570_f1`() RETURNS int(11)
973 DETERMINISTIC
974 begin
975 return 3;
976 end
977 /*!*/;
978 DELIMITER ;
979 # End of log file
980 ROLLBACK /* added by mysqlbinlog */;
981 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
982 use test;
983 drop procedure mysqltestbug36570_p1;
984 drop procedure ` mysqltestbug36570_p2`;
985 drop function mysqltestbug36570_f1;
986 End of 5.0 tests
987 End of 5.1 tests
988 include/rpl_end.inc