Debian Codegraph @

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