Debian Codegraph @

mysql-test/suite/rpl/r/rpl_sp.result

raw
1 include/master-slave.inc
2 [connection master]
3 set local sql_mode='';
4 drop database if exists mysqltest1;
5 create database mysqltest1;
6 use mysqltest1;
7 create table t1 (a varchar(100));
8 connection slave;
9 use mysqltest1;
10 connection master;
11 create procedure foo()
12 begin
13 declare b int;
14 set b = 8;
15 insert into t1 values (b);
16 insert into t1 values (unix_timestamp());
17 end|
18 select * from mysql.proc where name='foo' and db='mysqltest1';
19 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 aggregate
20 mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin
21 declare b int;
22 set b = 8;
23 insert into t1 values (b);
24 insert into t1 values (unix_timestamp());
25 end root@localhost # # latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci begin
26 declare b int;
27 set b = 8;
28 insert into t1 values (b);
29 insert into t1 values (unix_timestamp());
30 end NONE
31 connection slave;
32 select * from mysql.proc where name='foo' and db='mysqltest1';
33 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 aggregate
34 mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin
35 declare b int;
36 set b = 8;
37 insert into t1 values (b);
38 insert into t1 values (unix_timestamp());
39 end root@localhost # # latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci begin
40 declare b int;
41 set b = 8;
42 insert into t1 values (b);
43 insert into t1 values (unix_timestamp());
44 end NONE
45 connection master;
46 set timestamp=1000000000;
47 call foo();
48 select * from t1;
49 a
50 8
51 1000000000
52 connection slave;
53 select * from t1;
54 a
55 8
56 1000000000
57 connection master;
58 delete from t1;
59 create procedure foo2()
60 select * from mysqltest1.t1;
61 call foo2();
62 a
63 alter procedure foo2 contains sql;
64 drop table t1;
65 create table t1 (a int);
66 create table t2 like t1;
67 create procedure foo3()
68 deterministic
69 insert into t1 values (15);
70 grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
71 grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
72 grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;
73 SELECT 1;
74 1
75 1
76 connect con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,;
77 connection con1;
78 create procedure foo4()
79 deterministic
80 begin
81 insert into t2 values(3);
82 insert into t1 values (5);
83 end|
84 call foo4();
85 Got one of the listed errors
86 connection master;
87 call foo3();
88 show warnings;
89 Level Code Message
90 call foo4();
91 Got one of the listed errors
92 alter procedure foo4 sql security invoker;
93 call foo4();
94 show warnings;
95 Level Code Message
96 select * from t1;
97 a
98 15
99 5
100 select * from t2;
101 a
102 3
103 3
104 3
105 connection slave;
106 select * from t1;
107 a
108 15
109 5
110 select * from t2;
111 a
112 3
113 3
114 3
115 connection master;
116 delete from t2;
117 alter table t2 add unique (a);
118 drop procedure foo4;
119 create procedure foo4()
120 deterministic
121 begin
122 insert into t2 values(20),(20);
123 end|
124 call foo4();
125 ERROR 23000: Duplicate entry '20' for key 'a'
126 show warnings;
127 Level Code Message
128 Error 1062 Duplicate entry '20' for key 'a'
129 Warning 1196 Some non-transactional changed tables couldn't be rolled back
130 Note 4094 At line 4 in mysqltest1.foo4
131 select * from t2;
132 a
133 20
134 connection slave;
135 select * from t2;
136 a
137 20
138 select * from mysql.proc where name="foo4" and db='mysqltest1';
139 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 aggregate
140 mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES DEFINER begin
141 insert into t2 values(20),(20);
142 end root@localhost # # latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci begin
143 insert into t2 values(20),(20);
144 end NONE
145 connection master;
146 drop procedure foo4;
147 select * from mysql.proc where name="foo4" and db='mysqltest1';
148 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 aggregate
149 connection slave;
150 select * from mysql.proc where name="foo4" and db='mysqltest1';
151 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 aggregate
152 connection master;
153 drop procedure foo;
154 drop procedure foo2;
155 drop procedure foo3;
156 create function fn1(x int)
157 returns int
158 begin
159 insert into t1 values (x);
160 return x+2;
161 end|
162 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)
163 create function fn1(x int)
164 returns int
165 deterministic
166 begin
167 insert into t1 values (x);
168 return x+2;
169 end|
170 delete t1,t2 from t1,t2;
171 select fn1(20);
172 fn1(20)
173 22
174 insert into t2 values(fn1(21));
175 select * from t1;
176 a
177 20
178 21
179 select * from t2;
180 a
181 23
182 connection slave;
183 select * from t1;
184 a
185 20
186 21
187 select * from t2;
188 a
189 23
190 connection master;
191 drop function fn1|
192 create function fn1()
193 returns int
194 no sql
195 begin
196 return unix_timestamp();
197 end|
198 alter function fn1 contains sql;
199 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)
200 delete from t1;
201 set timestamp=1000000000;
202 insert into t1 values(fn1());
203 connection con1;
204 create function fn2()
205 returns int
206 no sql
207 begin
208 return unix_timestamp();
209 end|
210 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)
211 connection master;
212 set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
213 set global log_bin_trust_function_creators=0;
214 set global log_bin_trust_function_creators=1;
215 connection slave;
216 set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
217 set global log_bin_trust_function_creators=1;
218 connection con1;
219 create function fn2()
220 returns int
221 no sql
222 begin
223 return unix_timestamp();
224 end|
225 connection master;
226 create function fn3()
227 returns int
228 not deterministic
229 reads sql data
230 begin
231 return 0;
232 end|
233 select fn3();
234 fn3()
235 0
236 select * from mysql.proc where db='mysqltest1';
237 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 aggregate
238 mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin
239 return unix_timestamp();
240 end root@localhost # # latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci begin
241 return unix_timestamp();
242 end NONE
243 mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin
244 return unix_timestamp();
245 end zedjzlcsjhd@localhost # # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci begin
246 return unix_timestamp();
247 end NONE
248 mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin
249 return 0;
250 end root@localhost # # latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci begin
251 return 0;
252 end NONE
253 select * from t1;
254 a
255 1000000000
256 connection slave;
257 use mysqltest1;
258 select * from t1;
259 a
260 1000000000
261 select * from mysql.proc where db='mysqltest1';
262 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 aggregate
263 mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin
264 return unix_timestamp();
265 end root@localhost # # latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci begin
266 return unix_timestamp();
267 end NONE
268 mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin
269 return unix_timestamp();
270 end zedjzlcsjhd@localhost # # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci begin
271 return unix_timestamp();
272 end NONE
273 mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin
274 return 0;
275 end root@localhost # # latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci begin
276 return 0;
277 end NONE
278 connection master;
279 delete from t2;
280 alter table t2 add unique (a);
281 Warnings:
282 Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release
283 drop function fn1;
284 create function fn1(x int)
285 returns int
286 begin
287 insert into t2 values(x),(x);
288 return 10;
289 end|
290 do fn1(100);
291 Warnings:
292 Error 1062 Duplicate entry '100' for key 'a'
293 Note 4094 At line 3 in mysqltest1.fn1
294 Warning 1196 Some non-transactional changed tables couldn't be rolled back
295 select fn1(20);
296 ERROR 23000: Duplicate entry '20' for key 'a'
297 select * from t2;
298 a
299 20
300 100
301 connection slave;
302 select * from t2;
303 a
304 20
305 100
306 connection con1;
307 create trigger trg before insert on t1 for each row set new.a= 10;
308 ERROR 42000: TRIGGER command denied to user 'zedjzlcsjhd'@'localhost' for table `mysqltest1`.`t1`
309 connection master;
310 delete from t1;
311 create trigger trg before insert on t1 for each row set new.a= 10;
312 insert into t1 values (1);
313 select * from t1;
314 a
315 10
316 connection slave;
317 select * from t1;
318 a
319 10
320 connection master;
321 delete from t1;
322 drop trigger trg;
323 insert into t1 values (1);
324 select * from t1;
325 a
326 1
327 connection slave;
328 select * from t1;
329 a
330 1
331 connection master;
332 create procedure foo()
333 not deterministic
334 reads sql data
335 select * from t1;
336 connection slave;
337 call foo();
338 a
339 1
340 connection master;
341 drop procedure foo;
342 connection slave;
343 connection master;
344 drop function fn1;
345 drop database mysqltest1;
346 drop user "zedjzlcsjhd"@127.0.0.1;
347 use test;
348 connection slave;
349 use test;
350 connection master;
351 drop function if exists f1;
352 create function f1() returns int reads sql data
353 begin
354 declare var integer;
355 declare c cursor for select a from v1;
356 open c;
357 fetch c into var;
358 close c;
359 return var;
360 end|
361 create view v1 as select 1 as a;
362 create table t1 (a int);
363 insert into t1 (a) values (f1());
364 select * from t1;
365 a
366 1
367 drop view v1;
368 drop function f1;
369 connection slave;
370 connection slave;
371 select * from t1;
372 a
373 1
374 connection master;
375 DROP PROCEDURE IF EXISTS p1;
376 DROP TABLE IF EXISTS t1;
377 CREATE TABLE t1(col VARCHAR(10));
378 CREATE PROCEDURE p1(arg VARCHAR(10))
379 INSERT INTO t1 VALUES(arg);
380 CALL p1('test');
381 SELECT * FROM t1;
382 col
383 test
384 connection slave;
385 SELECT * FROM t1;
386 col
387 test
388 connection master;
389 DROP PROCEDURE p1;
390
391 ---> Test for BUG#20438
392
393 ---> Preparing environment...
394 connection master;
395 DROP PROCEDURE IF EXISTS p1;
396 DROP FUNCTION IF EXISTS f1;
397
398 ---> Synchronizing slave with master...
399 connection slave;
400 connection master;
401
402 ---> Creating procedure...
403 /*!50003 CREATE PROCEDURE p1() SET @a = 1 */;
404 /*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */;
405
406 ---> Checking on master...
407 SHOW CREATE PROCEDURE p1;
408 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
409 p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
410 SET @a = 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
411 SHOW CREATE FUNCTION f1;
412 Function sql_mode Create Function character_set_client collation_connection Database Collation
413 f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
414 RETURN 0 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
415
416 ---> Synchronizing slave with master...
417 connection slave;
418
419 ---> Checking on slave...
420 SHOW CREATE PROCEDURE p1;
421 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
422 p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
423 SET @a = 1 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
424 SHOW CREATE FUNCTION f1;
425 Function sql_mode Create Function character_set_client collation_connection Database Collation
426 f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
427 RETURN 0 latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
428 connection master;
429
430 ---> Cleaning up...
431 DROP PROCEDURE p1;
432 DROP FUNCTION f1;
433 connection slave;
434 connection master;
435 connection master;
436 drop table t1;
437 connection slave;
438 connection master;
439 drop database if exists mysqltest;
440 drop database if exists mysqltest2;
441 create database mysqltest;
442 create database mysqltest2;
443 use mysqltest2;
444 create table t ( t integer );
445 create procedure mysqltest.test() begin end;
446 insert into t values ( 1 );
447 create procedure `\\`.test() begin end;
448 ERROR 42000: Unknown database '\\'
449 connection master;
450 create function f1 () returns int
451 begin
452 insert into t values (1);
453 return 0;
454 end|
455 connection slave;
456 connection master;
457 use mysqltest;
458 set @a:= mysqltest2.f1();
459 connection slave;
460 connection master;
461 include/show_binlog_events.inc
462 Log_name Pos Event_type Server_id End_log_pos Info
463 master-bin.000001 # Gtid # # GTID #-#-#
464 master-bin.000001 # Query # # drop database if exists mysqltest1
465 master-bin.000001 # Gtid # # GTID #-#-#
466 master-bin.000001 # Query # # create database mysqltest1
467 master-bin.000001 # Gtid # # GTID #-#-#
468 master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a varchar(100))
469 master-bin.000001 # Gtid # # GTID #-#-#
470 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`()
471 begin
472 declare b int;
473 set b = 8;
474 insert into t1 values (b);
475 insert into t1 values (unix_timestamp());
476 end
477 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
478 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
479 master-bin.000001 # Query # # COMMIT
480 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
481 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (unix_timestamp())
482 master-bin.000001 # Query # # COMMIT
483 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
484 master-bin.000001 # Query # # use `mysqltest1`; delete from t1
485 master-bin.000001 # Query # # COMMIT
486 master-bin.000001 # Gtid # # GTID #-#-#
487 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `foo2`()
488 select * from mysqltest1.t1
489 master-bin.000001 # Gtid # # GTID #-#-#
490 master-bin.000001 # Query # # use `mysqltest1`; alter procedure foo2 contains sql
491 master-bin.000001 # Gtid # # GTID #-#-#
492 master-bin.000001 # Query # # use `mysqltest1`; DROP TABLE `t1` /* generated by server */
493 master-bin.000001 # Gtid # # GTID #-#-#
494 master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a int)
495 master-bin.000001 # Gtid # # GTID #-#-#
496 master-bin.000001 # Query # # use `mysqltest1`; create table t2 like t1
497 master-bin.000001 # Gtid # # GTID #-#-#
498 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `foo3`()
499 DETERMINISTIC
500 insert into t1 values (15)
501 master-bin.000001 # Gtid # # GTID #-#-#
502 master-bin.000001 # Query # # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
503 master-bin.000001 # Gtid # # GTID #-#-#
504 master-bin.000001 # Query # # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
505 master-bin.000001 # Gtid # # GTID #-#-#
506 master-bin.000001 # Query # # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
507 master-bin.000001 # Gtid # # GTID #-#-#
508 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` PROCEDURE `foo4`()
509 DETERMINISTIC
510 begin
511 insert into t2 values(3);
512 insert into t1 values (5);
513 end
514 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
515 master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(3)
516 master-bin.000001 # Query # # COMMIT
517 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
518 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (15)
519 master-bin.000001 # Query # # COMMIT
520 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
521 master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(3)
522 master-bin.000001 # Query # # COMMIT
523 master-bin.000001 # Gtid # # GTID #-#-#
524 master-bin.000001 # Query # # use `mysqltest1`; alter procedure foo4 sql security invoker
525 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
526 master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(3)
527 master-bin.000001 # Query # # COMMIT
528 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
529 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (5)
530 master-bin.000001 # Query # # COMMIT
531 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
532 master-bin.000001 # Query # # use `mysqltest1`; delete from t2
533 master-bin.000001 # Query # # COMMIT
534 master-bin.000001 # Gtid # # GTID #-#-#
535 master-bin.000001 # Query # # use `mysqltest1`; alter table t2 add unique (a)
536 master-bin.000001 # Gtid # # GTID #-#-#
537 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo4
538 master-bin.000001 # Gtid # # GTID #-#-#
539 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `foo4`()
540 DETERMINISTIC
541 begin
542 insert into t2 values(20),(20);
543 end
544 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
545 master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(20),(20)
546 master-bin.000001 # Query # # COMMIT
547 master-bin.000001 # Gtid # # GTID #-#-#
548 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo4
549 master-bin.000001 # Gtid # # GTID #-#-#
550 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo
551 master-bin.000001 # Gtid # # GTID #-#-#
552 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo2
553 master-bin.000001 # Gtid # # GTID #-#-#
554 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo3
555 master-bin.000001 # Gtid # # GTID #-#-#
556 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
557 DETERMINISTIC
558 begin
559 insert into t1 values (x);
560 return x+2;
561 end
562 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
563 master-bin.000001 # Query # # use `mysqltest1`; delete t1,t2 from t1,t2
564 master-bin.000001 # Query # # COMMIT
565 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
566 master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
567 master-bin.000001 # Query # # COMMIT
568 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
569 master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(fn1(21))
570 master-bin.000001 # Query # # COMMIT
571 master-bin.000001 # Gtid # # GTID #-#-#
572 master-bin.000001 # Query # # use `mysqltest1`; drop function fn1
573 master-bin.000001 # Gtid # # GTID #-#-#
574 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`() RETURNS int(11)
575 NO SQL
576 begin
577 return unix_timestamp();
578 end
579 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
580 master-bin.000001 # Query # # use `mysqltest1`; delete from t1
581 master-bin.000001 # Query # # COMMIT
582 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
583 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values(fn1())
584 master-bin.000001 # Query # # COMMIT
585 master-bin.000001 # Gtid # # GTID #-#-#
586 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` FUNCTION `fn2`() RETURNS int(11)
587 NO SQL
588 begin
589 return unix_timestamp();
590 end
591 master-bin.000001 # Gtid # # GTID #-#-#
592 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS int(11)
593 READS SQL DATA
594 begin
595 return 0;
596 end
597 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
598 master-bin.000001 # Query # # use `mysqltest1`; delete from t2
599 master-bin.000001 # Query # # COMMIT
600 master-bin.000001 # Gtid # # GTID #-#-#
601 master-bin.000001 # Query # # use `mysqltest1`; alter table t2 add unique (a)
602 master-bin.000001 # Gtid # # GTID #-#-#
603 master-bin.000001 # Query # # use `mysqltest1`; drop function fn1
604 master-bin.000001 # Gtid # # GTID #-#-#
605 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
606 begin
607 insert into t2 values(x),(x);
608 return 10;
609 end
610 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
611 master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(100)
612 master-bin.000001 # Query # # COMMIT
613 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
614 master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
615 master-bin.000001 # Query # # COMMIT
616 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
617 master-bin.000001 # Query # # use `mysqltest1`; delete from t1
618 master-bin.000001 # Query # # COMMIT
619 master-bin.000001 # Gtid # # GTID #-#-#
620 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10
621 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
622 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (1)
623 master-bin.000001 # Query # # COMMIT
624 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
625 master-bin.000001 # Query # # use `mysqltest1`; delete from t1
626 master-bin.000001 # Query # # COMMIT
627 master-bin.000001 # Gtid # # GTID #-#-#
628 master-bin.000001 # Query # # use `mysqltest1`; drop trigger trg
629 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
630 master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (1)
631 master-bin.000001 # Query # # COMMIT
632 master-bin.000001 # Gtid # # GTID #-#-#
633 master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`()
634 READS SQL DATA
635 select * from t1
636 master-bin.000001 # Gtid # # GTID #-#-#
637 master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo
638 master-bin.000001 # Gtid # # GTID #-#-#
639 master-bin.000001 # Query # # use `mysqltest1`; drop function fn1
640 master-bin.000001 # Gtid # # GTID #-#-#
641 master-bin.000001 # Query # # drop database mysqltest1
642 master-bin.000001 # Gtid # # GTID #-#-#
643 master-bin.000001 # Query # # drop user "zedjzlcsjhd"@127.0.0.1
644 master-bin.000001 # Gtid # # GTID #-#-#
645 master-bin.000001 # Query # # use `test`; drop function if exists f1
646 master-bin.000001 # Gtid # # GTID #-#-#
647 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
648 READS SQL DATA
649 begin
650 declare var integer;
651 declare c cursor for select a from v1;
652 open c;
653 fetch c into var;
654 close c;
655 return var;
656 end
657 master-bin.000001 # Gtid # # GTID #-#-#
658 master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 as a
659 master-bin.000001 # Gtid # # GTID #-#-#
660 master-bin.000001 # Query # # use `test`; create table t1 (a int)
661 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
662 master-bin.000001 # Query # # use `test`; insert into t1 (a) values (f1())
663 master-bin.000001 # Query # # COMMIT
664 master-bin.000001 # Gtid # # GTID #-#-#
665 master-bin.000001 # Query # # use `test`; drop view v1
666 master-bin.000001 # Gtid # # GTID #-#-#
667 master-bin.000001 # Query # # use `test`; drop function f1
668 master-bin.000001 # Gtid # # GTID #-#-#
669 master-bin.000001 # Query # # use `test`; DROP PROCEDURE IF EXISTS p1
670 master-bin.000001 # Gtid # # GTID #-#-#
671 master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */
672 master-bin.000001 # Gtid # # GTID #-#-#
673 master-bin.000001 # Query # # use `test`; CREATE TABLE t1(col VARCHAR(10))
674 master-bin.000001 # Gtid # # GTID #-#-#
675 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(arg VARCHAR(10))
676 INSERT INTO t1 VALUES(arg)
677 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
678 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_utf8mb4'test' COLLATE 'utf8mb4_uca1400_ai_ci'))
679 master-bin.000001 # Query # # COMMIT
680 master-bin.000001 # Gtid # # GTID #-#-#
681 master-bin.000001 # Query # # use `test`; DROP PROCEDURE p1
682 master-bin.000001 # Gtid # # GTID #-#-#
683 master-bin.000001 # Query # # use `test`; DROP PROCEDURE IF EXISTS p1
684 master-bin.000001 # Gtid # # GTID #-#-#
685 master-bin.000001 # Query # # use `test`; DROP FUNCTION IF EXISTS f1
686 master-bin.000001 # Gtid # # GTID #-#-#
687 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
688 SET @a = 1
689 master-bin.000001 # Gtid # # GTID #-#-#
690 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
691 RETURN 0
692 master-bin.000001 # Gtid # # GTID #-#-#
693 master-bin.000001 # Query # # use `test`; DROP PROCEDURE p1
694 master-bin.000001 # Gtid # # GTID #-#-#
695 master-bin.000001 # Query # # use `test`; DROP FUNCTION f1
696 master-bin.000001 # Gtid # # GTID #-#-#
697 master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
698 master-bin.000001 # Gtid # # GTID #-#-#
699 master-bin.000001 # Query # # drop database if exists mysqltest
700 master-bin.000001 # Gtid # # GTID #-#-#
701 master-bin.000001 # Query # # drop database if exists mysqltest2
702 master-bin.000001 # Gtid # # GTID #-#-#
703 master-bin.000001 # Query # # create database mysqltest
704 master-bin.000001 # Gtid # # GTID #-#-#
705 master-bin.000001 # Query # # create database mysqltest2
706 master-bin.000001 # Gtid # # GTID #-#-#
707 master-bin.000001 # Query # # use `mysqltest2`; create table t ( t integer )
708 master-bin.000001 # Gtid # # GTID #-#-#
709 master-bin.000001 # Query # # use `mysqltest2`; CREATE DEFINER=`root`@`localhost` PROCEDURE `mysqltest`.`test`()
710 begin end
711 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
712 master-bin.000001 # Query # # use `mysqltest2`; insert into t values ( 1 )
713 master-bin.000001 # Query # # COMMIT
714 master-bin.000001 # Gtid # # GTID #-#-#
715 master-bin.000001 # Query # # use `mysqltest2`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
716 begin
717 insert into t values (1);
718 return 0;
719 end
720 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
721 master-bin.000001 # Query # # use `mysqltest`; SELECT `mysqltest2`.`f1`()
722 master-bin.000001 # Query # # COMMIT
723 connection slave;
724 set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
725 connection master;
726 set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
727 drop database mysqltest;
728 drop database mysqltest2;
729 connection slave;
730 connection master;
731 use test;
732 /*!50001 create procedure `mysqltestbug36570_p1`() */
733 begin
734 select 1;
735 end|
736 use mysql|
737 create procedure test.` mysqltestbug36570_p2`(/*!50001 a int*/)`label`:
738 begin
739 select a;
740 end|
741 /*!50001 create function test.mysqltestbug36570_f1() */
742 returns int
743 /*!50001 deterministic */
744 begin
745 return 3;
746 end|
747 use test|
748 show procedure status like '%mysqltestbug36570%';
749 Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
750 test mysqltestbug36570_p2 PROCEDURE root@localhost t t DEFINER latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
751 test mysqltestbug36570_p1 PROCEDURE root@localhost t t DEFINER latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
752 show create procedure ` mysqltestbug36570_p2`;
753 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
754 mysqltestbug36570_p2 CREATE DEFINER=`root`@`localhost` PROCEDURE ` mysqltestbug36570_p2`( a int)
755 `label`:
756 begin
757 select a;
758 end latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
759 connection slave;
760 connection slave;
761 show procedure status like '%mysqltestbug36570%';
762 Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
763 test mysqltestbug36570_p2 PROCEDURE root@localhost t t DEFINER latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
764 test mysqltestbug36570_p1 PROCEDURE root@localhost t t DEFINER latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
765 show create procedure ` mysqltestbug36570_p2`;
766 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
767 mysqltestbug36570_p2 CREATE DEFINER=`root`@`localhost` PROCEDURE ` mysqltestbug36570_p2`( a int)
768 `label`:
769 begin
770 select a;
771 end latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
772 call ` mysqltestbug36570_p2`(42);
773 a
774 42
775 show function status like '%mysqltestbug36570%';
776 Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
777 test mysqltestbug36570_f1 FUNCTION root@localhost t t DEFINER latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
778 connection master;
779 flush logs;
780 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
781 /*!40019 SET @@session.max_delayed_threads=0*/;
782 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
783 DELIMITER /*!*/;
784 ROLLBACK/*!*/;
785 SET TIMESTAMP=t/*!*/;
786 SET @@session.pseudo_thread_id=999999999/*!*/;
787 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=1, @@session.system_versioning_insert_history=0/*!*/;
788 SET @@session.sql_mode=0/*!*/;
789 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
790 /*!\C latin1 *//*!*/;
791 SET @@session.character_set_client=latin1,@@session.collation_connection=8,@@session.collation_server=2304/*!*/;
792 SET @@session.lc_time_names=0/*!*/;
793 SET @@session.collation_database=DEFAULT/*!*/;
794 drop database if exists mysqltest1
795 /*!*/;
796 SET TIMESTAMP=t/*!*/;
797 create database mysqltest1
798 /*!*/;
799 use `mysqltest1`/*!*/;
800 SET TIMESTAMP=t/*!*/;
801 create table t1 (a varchar(100))
802 /*!*/;
803 SET TIMESTAMP=t/*!*/;
804 CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`()
805 begin
806 declare b int;
807 set b = 8;
808 insert into t1 values (b);
809 insert into t1 values (unix_timestamp());
810 end
811 /*!*/;
812 START TRANSACTION
813 /*!*/;
814 SET TIMESTAMP=t/*!*/;
815 insert into t1 values ( NAME_CONST('b',8))
816 /*!*/;
817 SET TIMESTAMP=t/*!*/;
818 COMMIT
819 /*!*/;
820 START TRANSACTION
821 /*!*/;
822 SET TIMESTAMP=t/*!*/;
823 insert into t1 values (unix_timestamp())
824 /*!*/;
825 SET TIMESTAMP=t/*!*/;
826 COMMIT
827 /*!*/;
828 START TRANSACTION
829 /*!*/;
830 SET TIMESTAMP=t/*!*/;
831 delete from t1
832 /*!*/;
833 SET TIMESTAMP=t/*!*/;
834 COMMIT
835 /*!*/;
836 SET TIMESTAMP=t/*!*/;
837 CREATE DEFINER=`root`@`localhost` PROCEDURE `foo2`()
838 select * from mysqltest1.t1
839 /*!*/;
840 SET TIMESTAMP=t/*!*/;
841 alter procedure foo2 contains sql
842 /*!*/;
843 SET TIMESTAMP=t/*!*/;
844 DROP TABLE `t1` /* generated by server */
845 /*!*/;
846 SET TIMESTAMP=t/*!*/;
847 create table t1 (a int)
848 /*!*/;
849 SET TIMESTAMP=t/*!*/;
850 create table t2 like t1
851 /*!*/;
852 SET TIMESTAMP=t/*!*/;
853 CREATE DEFINER=`root`@`localhost` PROCEDURE `foo3`()
854 DETERMINISTIC
855 insert into t1 values (15)
856 /*!*/;
857 SET TIMESTAMP=t/*!*/;
858 grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
859 /*!*/;
860 SET TIMESTAMP=t/*!*/;
861 grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
862 /*!*/;
863 SET TIMESTAMP=t/*!*/;
864 grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
865 /*!*/;
866 SET TIMESTAMP=t/*!*/;
867 SET @@session.sql_mode=1411383296/*!*/;
868 CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` PROCEDURE `foo4`()
869 DETERMINISTIC
870 begin
871 insert into t2 values(3);
872 insert into t1 values (5);
873 end
874 /*!*/;
875 START TRANSACTION
876 /*!*/;
877 SET TIMESTAMP=t/*!*/;
878 insert into t2 values(3)
879 /*!*/;
880 SET TIMESTAMP=t/*!*/;
881 COMMIT
882 /*!*/;
883 START TRANSACTION
884 /*!*/;
885 SET TIMESTAMP=t/*!*/;
886 SET @@session.sql_mode=0/*!*/;
887 insert into t1 values (15)
888 /*!*/;
889 SET TIMESTAMP=t/*!*/;
890 COMMIT
891 /*!*/;
892 START TRANSACTION
893 /*!*/;
894 SET TIMESTAMP=t/*!*/;
895 SET @@session.sql_mode=1411383296/*!*/;
896 insert into t2 values(3)
897 /*!*/;
898 SET TIMESTAMP=t/*!*/;
899 COMMIT
900 /*!*/;
901 SET TIMESTAMP=t/*!*/;
902 SET @@session.sql_mode=0/*!*/;
903 alter procedure foo4 sql security invoker
904 /*!*/;
905 START TRANSACTION
906 /*!*/;
907 SET TIMESTAMP=t/*!*/;
908 SET @@session.sql_mode=1411383296/*!*/;
909 insert into t2 values(3)
910 /*!*/;
911 SET TIMESTAMP=t/*!*/;
912 COMMIT
913 /*!*/;
914 START TRANSACTION
915 /*!*/;
916 SET TIMESTAMP=t/*!*/;
917 insert into t1 values (5)
918 /*!*/;
919 SET TIMESTAMP=t/*!*/;
920 COMMIT
921 /*!*/;
922 START TRANSACTION
923 /*!*/;
924 SET TIMESTAMP=t/*!*/;
925 SET @@session.sql_mode=0/*!*/;
926 delete from t2
927 /*!*/;
928 SET TIMESTAMP=t/*!*/;
929 COMMIT
930 /*!*/;
931 SET TIMESTAMP=t/*!*/;
932 alter table t2 add unique (a)
933 /*!*/;
934 SET TIMESTAMP=t/*!*/;
935 drop procedure foo4
936 /*!*/;
937 SET TIMESTAMP=t/*!*/;
938 CREATE DEFINER=`root`@`localhost` PROCEDURE `foo4`()
939 DETERMINISTIC
940 begin
941 insert into t2 values(20),(20);
942 end
943 /*!*/;
944 START TRANSACTION
945 /*!*/;
946 SET TIMESTAMP=t/*!*/;
947 insert into t2 values(20),(20)
948 /*!*/;
949 SET TIMESTAMP=t/*!*/;
950 COMMIT
951 /*!*/;
952 SET TIMESTAMP=t/*!*/;
953 drop procedure foo4
954 /*!*/;
955 SET TIMESTAMP=t/*!*/;
956 drop procedure foo
957 /*!*/;
958 SET TIMESTAMP=t/*!*/;
959 drop procedure foo2
960 /*!*/;
961 SET TIMESTAMP=t/*!*/;
962 drop procedure foo3
963 /*!*/;
964 SET TIMESTAMP=t/*!*/;
965 CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
966 DETERMINISTIC
967 begin
968 insert into t1 values (x);
969 return x+2;
970 end
971 /*!*/;
972 START TRANSACTION
973 /*!*/;
974 SET TIMESTAMP=t/*!*/;
975 delete t1,t2 from t1,t2
976 /*!*/;
977 SET TIMESTAMP=t/*!*/;
978 COMMIT
979 /*!*/;
980 START TRANSACTION
981 /*!*/;
982 SET TIMESTAMP=t/*!*/;
983 SELECT `mysqltest1`.`fn1`(20)
984 /*!*/;
985 SET TIMESTAMP=t/*!*/;
986 COMMIT
987 /*!*/;
988 START TRANSACTION
989 /*!*/;
990 SET TIMESTAMP=t/*!*/;
991 insert into t2 values(fn1(21))
992 /*!*/;
993 SET TIMESTAMP=t/*!*/;
994 COMMIT
995 /*!*/;
996 SET TIMESTAMP=t/*!*/;
997 drop function fn1
998 /*!*/;
999 SET TIMESTAMP=t/*!*/;
1000 CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`() RETURNS int(11)
1001 NO SQL
1002 begin
1003 return unix_timestamp();
1004 end
1005 /*!*/;
1006 START TRANSACTION
1007 /*!*/;
1008 SET TIMESTAMP=t/*!*/;
1009 delete from t1
1010 /*!*/;
1011 SET TIMESTAMP=t/*!*/;
1012 COMMIT
1013 /*!*/;
1014 START TRANSACTION
1015 /*!*/;
1016 SET TIMESTAMP=t/*!*/;
1017 insert into t1 values(fn1())
1018 /*!*/;
1019 SET TIMESTAMP=t/*!*/;
1020 COMMIT
1021 /*!*/;
1022 SET TIMESTAMP=t/*!*/;
1023 SET @@session.sql_mode=1411383296/*!*/;
1024 CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` FUNCTION `fn2`() RETURNS int(11)
1025 NO SQL
1026 begin
1027 return unix_timestamp();
1028 end
1029 /*!*/;
1030 SET TIMESTAMP=t/*!*/;
1031 SET @@session.sql_mode=0/*!*/;
1032 CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS int(11)
1033 READS SQL DATA
1034 begin
1035 return 0;
1036 end
1037 /*!*/;
1038 START TRANSACTION
1039 /*!*/;
1040 SET TIMESTAMP=t/*!*/;
1041 delete from t2
1042 /*!*/;
1043 SET TIMESTAMP=t/*!*/;
1044 COMMIT
1045 /*!*/;
1046 SET TIMESTAMP=t/*!*/;
1047 alter table t2 add unique (a)
1048 /*!*/;
1049 SET TIMESTAMP=t/*!*/;
1050 drop function fn1
1051 /*!*/;
1052 SET TIMESTAMP=t/*!*/;
1053 CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
1054 begin
1055 insert into t2 values(x),(x);
1056 return 10;
1057 end
1058 /*!*/;
1059 START TRANSACTION
1060 /*!*/;
1061 SET TIMESTAMP=t/*!*/;
1062 SELECT `mysqltest1`.`fn1`(100)
1063 /*!*/;
1064 SET TIMESTAMP=t/*!*/;
1065 COMMIT
1066 /*!*/;
1067 START TRANSACTION
1068 /*!*/;
1069 SET TIMESTAMP=t/*!*/;
1070 SELECT `mysqltest1`.`fn1`(20)
1071 /*!*/;
1072 SET TIMESTAMP=t/*!*/;
1073 COMMIT
1074 /*!*/;
1075 START TRANSACTION
1076 /*!*/;
1077 SET TIMESTAMP=t/*!*/;
1078 delete from t1
1079 /*!*/;
1080 SET TIMESTAMP=t/*!*/;
1081 COMMIT
1082 /*!*/;
1083 SET TIMESTAMP=t/*!*/;
1084 CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10
1085 /*!*/;
1086 START TRANSACTION
1087 /*!*/;
1088 SET TIMESTAMP=t/*!*/;
1089 insert into t1 values (1)
1090 /*!*/;
1091 SET TIMESTAMP=t/*!*/;
1092 COMMIT
1093 /*!*/;
1094 START TRANSACTION
1095 /*!*/;
1096 SET TIMESTAMP=t/*!*/;
1097 delete from t1
1098 /*!*/;
1099 SET TIMESTAMP=t/*!*/;
1100 COMMIT
1101 /*!*/;
1102 SET TIMESTAMP=t/*!*/;
1103 drop trigger trg
1104 /*!*/;
1105 START TRANSACTION
1106 /*!*/;
1107 SET TIMESTAMP=t/*!*/;
1108 insert into t1 values (1)
1109 /*!*/;
1110 SET TIMESTAMP=t/*!*/;
1111 COMMIT
1112 /*!*/;
1113 SET TIMESTAMP=t/*!*/;
1114 CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`()
1115 READS SQL DATA
1116 select * from t1
1117 /*!*/;
1118 SET TIMESTAMP=t/*!*/;
1119 drop procedure foo
1120 /*!*/;
1121 SET TIMESTAMP=t/*!*/;
1122 drop function fn1
1123 /*!*/;
1124 SET TIMESTAMP=t/*!*/;
1125 drop database mysqltest1
1126 /*!*/;
1127 SET TIMESTAMP=t/*!*/;
1128 drop user "zedjzlcsjhd"@127.0.0.1
1129 /*!*/;
1130 use `test`/*!*/;
1131 SET TIMESTAMP=t/*!*/;
1132 drop function if exists f1
1133 /*!*/;
1134 SET TIMESTAMP=t/*!*/;
1135 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
1136 READS SQL DATA
1137 begin
1138 declare var integer;
1139 declare c cursor for select a from v1;
1140 open c;
1141 fetch c into var;
1142 close c;
1143 return var;
1144 end
1145 /*!*/;
1146 SET TIMESTAMP=t/*!*/;
1147 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 as a
1148 /*!*/;
1149 SET TIMESTAMP=t/*!*/;
1150 create table t1 (a int)
1151 /*!*/;
1152 START TRANSACTION
1153 /*!*/;
1154 SET TIMESTAMP=t/*!*/;
1155 insert into t1 (a) values (f1())
1156 /*!*/;
1157 SET TIMESTAMP=t/*!*/;
1158 COMMIT
1159 /*!*/;
1160 SET TIMESTAMP=t/*!*/;
1161 drop view v1
1162 /*!*/;
1163 SET TIMESTAMP=t/*!*/;
1164 drop function f1
1165 /*!*/;
1166 SET TIMESTAMP=t/*!*/;
1167 DROP PROCEDURE IF EXISTS p1
1168 /*!*/;
1169 SET TIMESTAMP=t/*!*/;
1170 DROP TABLE IF EXISTS `t1` /* generated by server */
1171 /*!*/;
1172 SET TIMESTAMP=t/*!*/;
1173 CREATE TABLE t1(col VARCHAR(10))
1174 /*!*/;
1175 SET TIMESTAMP=t/*!*/;
1176 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(arg VARCHAR(10))
1177 INSERT INTO t1 VALUES(arg)
1178 /*!*/;
1179 START TRANSACTION
1180 /*!*/;
1181 SET TIMESTAMP=t/*!*/;
1182 INSERT INTO t1 VALUES( NAME_CONST('arg',_utf8mb4'test' COLLATE 'utf8mb4_uca1400_ai_ci'))
1183 /*!*/;
1184 SET TIMESTAMP=t/*!*/;
1185 COMMIT
1186 /*!*/;
1187 SET TIMESTAMP=t/*!*/;
1188 DROP PROCEDURE p1
1189 /*!*/;
1190 SET TIMESTAMP=t/*!*/;
1191 DROP PROCEDURE IF EXISTS p1
1192 /*!*/;
1193 SET TIMESTAMP=t/*!*/;
1194 DROP FUNCTION IF EXISTS f1
1195 /*!*/;
1196 SET TIMESTAMP=t/*!*/;
1197 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
1198 SET @a = 1
1199 /*!*/;
1200 SET TIMESTAMP=t/*!*/;
1201 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
1202 RETURN 0
1203 /*!*/;
1204 SET TIMESTAMP=t/*!*/;
1205 DROP PROCEDURE p1
1206 /*!*/;
1207 SET TIMESTAMP=t/*!*/;
1208 DROP FUNCTION f1
1209 /*!*/;
1210 SET TIMESTAMP=t/*!*/;
1211 DROP TABLE `t1` /* generated by server */
1212 /*!*/;
1213 SET TIMESTAMP=t/*!*/;
1214 drop database if exists mysqltest
1215 /*!*/;
1216 SET TIMESTAMP=t/*!*/;
1217 drop database if exists mysqltest2
1218 /*!*/;
1219 SET TIMESTAMP=t/*!*/;
1220 create database mysqltest
1221 /*!*/;
1222 SET TIMESTAMP=t/*!*/;
1223 create database mysqltest2
1224 /*!*/;
1225 use `mysqltest2`/*!*/;
1226 SET TIMESTAMP=t/*!*/;
1227 create table t ( t integer )
1228 /*!*/;
1229 SET TIMESTAMP=t/*!*/;
1230 CREATE DEFINER=`root`@`localhost` PROCEDURE `mysqltest`.`test`()
1231 begin end
1232 /*!*/;
1233 START TRANSACTION
1234 /*!*/;
1235 SET TIMESTAMP=t/*!*/;
1236 insert into t values ( 1 )
1237 /*!*/;
1238 SET TIMESTAMP=t/*!*/;
1239 COMMIT
1240 /*!*/;
1241 SET TIMESTAMP=t/*!*/;
1242 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
1243 begin
1244 insert into t values (1);
1245 return 0;
1246 end
1247 /*!*/;
1248 START TRANSACTION
1249 /*!*/;
1250 use `mysqltest`/*!*/;
1251 SET TIMESTAMP=t/*!*/;
1252 SELECT `mysqltest2`.`f1`()
1253 /*!*/;
1254 SET TIMESTAMP=t/*!*/;
1255 COMMIT
1256 /*!*/;
1257 SET TIMESTAMP=t/*!*/;
1258 drop database mysqltest
1259 /*!*/;
1260 SET TIMESTAMP=t/*!*/;
1261 drop database mysqltest2
1262 /*!*/;
1263 use `test`/*!*/;
1264 SET TIMESTAMP=t/*!*/;
1265 CREATE DEFINER=`root`@`localhost` PROCEDURE `mysqltestbug36570_p1`()
1266 begin
1267 select 1;
1268 end
1269 /*!*/;
1270 use `mysql`/*!*/;
1271 SET TIMESTAMP=t/*!*/;
1272 CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.` mysqltestbug36570_p2`( a int)
1273 `label`:
1274 begin
1275 select a;
1276 end
1277 /*!*/;
1278 SET TIMESTAMP=t/*!*/;
1279 CREATE DEFINER=`root`@`localhost` FUNCTION `test`.`mysqltestbug36570_f1`() RETURNS int(11)
1280 DETERMINISTIC
1281 begin
1282 return 3;
1283 end
1284 /*!*/;
1285 DELIMITER ;
1286 # End of log file
1287 ROLLBACK /* added by mysqlbinlog */;
1288 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
1289 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
1290 use test;
1291 drop procedure mysqltestbug36570_p1;
1292 drop procedure ` mysqltestbug36570_p2`;
1293 drop function mysqltestbug36570_f1;
1294 End of 5.0 tests
1295 # End of 5.1 tests
1296 #
1297 # Test Bug#30977 Concurrent statement using stored
1298 # function and DROP FUNCTION breaks SBR.
1299 #
1300 # Demonstrate that stored function DDL can not go through,
1301 # or, worse yet, make its way into the binary log, while
1302 # the stored function is in use.
1303 # For that, try to insert a result of a stored function
1304 # into a table. Block the insert in the beginning, waiting
1305 # on a table lock. While insert is blocked, attempt to
1306 # drop the routine. Verify that this attempt
1307 # blocks and waits for INSERT to complete. Commit and
1308 # reap the chain of events. Master and slave must contain
1309 # identical data. Statements in the binrary log must be
1310 # consistent with data in the table.
1311 #
1312 connection default;
1313 drop table if exists t1, t2;
1314 drop function if exists t1;
1315 create table t1 (a int);
1316 create table t2 (a int) as select 1 as a;
1317 create function f1() returns int deterministic return (select max(a) from t2);
1318 lock table t2 write;
1319 connection master;
1320 # Sending 'insert into t1 (a) values (f1())'...
1321 insert into t1 (a) values (f1());
1322 connection master1;
1323 # Waitng for 'insert into t1 ...' to get blocked on table lock...
1324 # Sending 'drop function f1'. It will wait till insert finishes.
1325 drop function f1;;
1326 connection default;
1327 # Check that 'drop function f1' gets blocked.
1328 # Now let's let 'insert' go through...
1329 unlock tables;
1330 connection master;
1331 # Reaping 'insert into t1 (a) values (f1())'...
1332 connection master1;
1333 # Reaping 'drop function f1'
1334 connection master;
1335 select * from t1;
1336 a
1337 1
1338 connection slave;
1339 connection slave;
1340 select * from t1;
1341 a
1342 1
1343 connection master;
1344 drop table t1, t2;
1345 drop function f1;
1346 ERROR 42000: FUNCTION test.f1 does not exist
1347 #
1348 # Bug #11918 Can't use a declared variable in LIMIT clause
1349 #
1350 include/rpl_reset.inc
1351 create table t1 (c1 int);
1352 insert into t1 (c1) values
1353 (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
1354 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
1355 create procedure p1(p1 integer)
1356 delete from t1 limit p1;
1357 set @save_binlog_format=@@session.binlog_format;
1358 set @@session.binlog_format=STATEMENT;
1359 call p1(NULL);
1360 call p1(0);
1361 call p1(1);
1362 call p1(2);
1363 call p1(3);
1364 select * from t1;
1365 c1
1366 7
1367 8
1368 9
1369 10
1370 connection slave;
1371 connection slave;
1372 select * from t1;
1373 c1
1374 7
1375 8
1376 9
1377 10
1378 connection master;
1379 call p1(-1);
1380 select * from t1;
1381 c1
1382 connection slave;
1383 connection slave;
1384 select * from t1;
1385 c1
1386 connection master;
1387 # Cleanup
1388 set @@session.binlog_format=@save_binlog_format;
1389 drop table t1;
1390 drop procedure p1;
1391 # End of 5.5 tests.
1392 connection slave;
1393 include/rpl_end.inc