Debian Codegraph @

mysql-test/suite/rpl/t/rpl_sp.test

raw
1 # row-based and statement have expected binlog difference in result files
2
3 # Test of replication of stored procedures (WL#2146 for MySQL 5.0)
4 # Modified by WL#2971.
5
6 source include/have_binlog_format_mixed.inc;
7 source include/master-slave.inc;
8
9 set local sql_mode='';
10 # we need a db != test, where we don't have automatic grants
11 --disable_warnings
12 drop database if exists mysqltest1;
13 --enable_warnings
14 create database mysqltest1;
15 use mysqltest1;
16 create table t1 (a varchar(100));
17 sync_slave_with_master;
18 use mysqltest1;
19
20 # ********************** PART 1 : STORED PROCEDURES ***************
21
22 # Does the same proc as on master get inserted into mysql.proc ?
23 # (same definer, same properties...)
24
25 connection master;
26
27 delimiter |;
28
29 # Stored procedures don't have the limitations that functions have
30 # regarding binlogging: it's ok to create a procedure as not
31 # deterministic and updating data, while it's not ok to create such a
32 # function. We test this.
33
34 create procedure foo()
35 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 delimiter ;|
42
43 # we replace columns having times
44 # (even with fixed timestamp displayed time may changed based on TZ)
45 --replace_result localhost.localdomain localhost 127.0.0.1 localhost
46 --replace_column 13 # 14 #
47 select * from mysql.proc where name='foo' and db='mysqltest1';
48 sync_slave_with_master;
49 # You will notice in the result that the definer does not match what
50 # it is on master, it is a known bug on which Alik is working
51 --replace_result localhost.localdomain localhost 127.0.0.1 localhost
52 --replace_column 13 # 14 #
53 select * from mysql.proc where name='foo' and db='mysqltest1';
54
55 connection master;
56 # see if timestamp used in SP on slave is same as on master
57 set timestamp=1000000000;
58 call foo();
59 select * from t1;
60 sync_slave_with_master;
61 select * from t1;
62
63 # Now a SP which is not updating tables
64
65 connection master;
66 delete from t1;
67 create procedure foo2()
68 select * from mysqltest1.t1;
69 call foo2();
70
71 # check that this is allowed (it's not for functions):
72 alter procedure foo2 contains sql;
73
74 # SP with definer's right
75
76 drop table t1;
77 create table t1 (a int);
78 create table t2 like t1;
79
80 create procedure foo3()
81 deterministic
82 insert into t1 values (15);
83
84 # let's create a non-privileged user
85 grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
86 grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
87 grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;
88
89 # ToDo: BUG#14931: There is a race between the last grant binlogging, and
90 # the binlogging in the new connection made below, causing sporadic test
91 # failures due to switched statement order in binlog. To fix this we do
92 # SELECT 1 in the first connection before starting the second, ensuring
93 # that binlogging is done in the expected order.
94 # Please remove this SELECT 1 when BUG#14931 is fixed.
95 SELECT 1;
96
97 connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,);
98 connection con1;
99
100 # this routine will fail in the second INSERT because of privileges
101 delimiter |;
102 create procedure foo4()
103 deterministic
104 begin
105 insert into t2 values(3);
106 insert into t1 values (5);
107 end|
108
109 delimiter ;|
110
111 # I add ,0 so that it does not print the error in the test output,
112 # because this error is hostname-dependent
113 --error 1142,0
114 call foo4(); # invoker has no INSERT grant on table t1 => failure
115
116 connection master;
117 call foo3(); # success (definer == root)
118 show warnings;
119
120 --error 1142,0
121 call foo4(); # definer's rights => failure
122
123 # we test replication of ALTER PROCEDURE
124 alter procedure foo4 sql security invoker;
125 call foo4(); # invoker's rights => success
126 show warnings;
127
128 # Note that half-failed procedure calls are ok with binlogging;
129 # if we compare t2 on master and slave we see they are identical:
130
131 select * from t1;
132 select * from t2;
133 sync_slave_with_master;
134 select * from t1;
135 select * from t2;
136
137 # Let's check another failing-in-the-middle procedure
138 connection master;
139 delete from t2;
140 alter table t2 add unique (a);
141
142 drop procedure foo4;
143 delimiter |;
144 create procedure foo4()
145 deterministic
146 begin
147 insert into t2 values(20),(20);
148 end|
149
150 delimiter ;|
151
152 --error ER_DUP_ENTRY
153 call foo4();
154 show warnings;
155
156 select * from t2;
157 sync_slave_with_master;
158 # check that this failed-in-the-middle replicated right:
159 select * from t2;
160
161 # Test of DROP PROCEDURE
162
163 --replace_result localhost.localdomain localhost 127.0.0.1 localhost
164 --replace_column 13 # 14 #
165 select * from mysql.proc where name="foo4" and db='mysqltest1';
166 connection master;
167 drop procedure foo4;
168 select * from mysql.proc where name="foo4" and db='mysqltest1';
169 sync_slave_with_master;
170 select * from mysql.proc where name="foo4" and db='mysqltest1';
171
172 # ********************** PART 2 : FUNCTIONS ***************
173
174 connection master;
175 drop procedure foo;
176 drop procedure foo2;
177 drop procedure foo3;
178
179 delimiter |;
180 # check that needs "deterministic"
181 --error 1418
182 create function fn1(x int)
183 returns int
184 begin
185 insert into t1 values (x);
186 return x+2;
187 end|
188 create function fn1(x int)
189 returns int
190 deterministic
191 begin
192 insert into t1 values (x);
193 return x+2;
194 end|
195
196 delimiter ;|
197 delete t1,t2 from t1,t2;
198 --disable_ps2_protocol
199 select fn1(20);
200 --enable_ps2_protocol
201 insert into t2 values(fn1(21));
202 --sorted_result
203 select * from t1;
204 select * from t2;
205 sync_slave_with_master;
206 --sorted_result
207 select * from t1;
208 select * from t2;
209
210 connection master;
211 delimiter |;
212
213 drop function fn1|
214
215 create function fn1()
216 returns int
217 no sql
218 begin
219 return unix_timestamp();
220 end|
221
222 delimiter ;|
223 # check that needs "deterministic"
224 --error 1418
225 alter function fn1 contains sql;
226
227 delete from t1;
228 set timestamp=1000000000;
229 insert into t1 values(fn1());
230
231 connection con1;
232
233 delimiter |;
234 --error 1419 # only full-global-privs user can create a function
235 create function fn2()
236 returns int
237 no sql
238 begin
239 return unix_timestamp();
240 end|
241 delimiter ;|
242 connection master;
243 set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
244 set global log_bin_trust_function_creators=0;
245 set global log_bin_trust_function_creators=1;
246 # slave needs it too otherwise will not execute what master allowed:
247 connection slave;
248 set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
249 set global log_bin_trust_function_creators=1;
250
251 connection con1;
252
253 delimiter |;
254 create function fn2()
255 returns int
256 no sql
257 begin
258 return unix_timestamp();
259 end|
260 delimiter ;|
261
262 connection master;
263
264 # Now a function which is supposed to not update tables
265 # as it's "reads sql data", so should not give error even if
266 # non-deterministic.
267
268 delimiter |;
269 create function fn3()
270 returns int
271 not deterministic
272 reads sql data
273 begin
274 return 0;
275 end|
276 delimiter ;|
277
278 select fn3();
279 --replace_result localhost.localdomain localhost 127.0.0.1 localhost
280 --replace_column 13 # 14 #
281 select * from mysql.proc where db='mysqltest1';
282 select * from t1;
283
284 sync_slave_with_master;
285 use mysqltest1;
286 select * from t1;
287 --replace_result localhost.localdomain localhost 127.0.0.1 localhost
288 --replace_column 13 # 14 #
289 select * from mysql.proc where db='mysqltest1';
290
291 # Let's check a failing-in-the-middle function
292 connection master;
293 delete from t2;
294 alter table t2 add unique (a);
295
296 drop function fn1;
297
298 delimiter |;
299 create function fn1(x int)
300 returns int
301 begin
302 insert into t2 values(x),(x);
303 return 10;
304 end|
305
306 delimiter ;|
307
308 do fn1(100);
309
310 --error ER_DUP_ENTRY
311 select fn1(20);
312
313 select * from t2;
314 sync_slave_with_master;
315
316 # check that this failed-in-the-middle replicated right:
317 select * from t2;
318
319 # ********************** PART 3 : TRIGGERS ***************
320
321 connection con1;
322 # now fails due to missing trigger grant (err 1142 i/o 1227) due to new
323 # check in sql_trigger.cc (v1.44) by anozdrin on 2006/02/01 --azundris
324 --error ER_TABLEACCESS_DENIED_ERROR
325 create trigger trg before insert on t1 for each row set new.a= 10;
326
327 connection master;
328 delete from t1;
329 # TODO: when triggers can contain an update, test that this update
330 # does not go into binlog.
331 # I'm not setting user vars in the trigger, because replication of user vars
332 # would take care of propagating the user var's value to slave, so even if
333 # the trigger was not executed on slave it would not be discovered.
334 create trigger trg before insert on t1 for each row set new.a= 10;
335 insert into t1 values (1);
336 select * from t1;
337 sync_slave_with_master;
338 select * from t1;
339
340 connection master;
341 delete from t1;
342 drop trigger trg;
343 insert into t1 values (1);
344 select * from t1;
345 sync_slave_with_master;
346 select * from t1;
347
348
349 # ********************** PART 4 : RELATED FIXED BUGS ***************
350
351
352 #
353 # Test for bug #13969 "Routines which are replicated from master can't be
354 # executed on slave".
355 #
356 connection master;
357 create procedure foo()
358 not deterministic
359 reads sql data
360 select * from t1;
361 sync_slave_with_master;
362 # This should not fail
363 call foo();
364 connection master;
365 drop procedure foo;
366 sync_slave_with_master;
367
368
369 # Clean up
370 connection master;
371 drop function fn1;
372 drop database mysqltest1;
373 drop user "zedjzlcsjhd"@127.0.0.1;
374 use test;
375 sync_slave_with_master;
376 use test;
377
378 #
379 # Bug#14077 "Failure to replicate a stored function with a cursor":
380 # verify that stored routines with cursors work on slave.
381 #
382 connection master;
383 --disable_warnings
384 drop function if exists f1;
385 --enable_warnings
386 delimiter |;
387 create function f1() returns int reads sql data
388 begin
389 declare var integer;
390 declare c cursor for select a from v1;
391 open c;
392 fetch c into var;
393 close c;
394 return var;
395 end|
396 delimiter ;|
397 create view v1 as select 1 as a;
398 create table t1 (a int);
399 insert into t1 (a) values (f1());
400 select * from t1;
401 drop view v1;
402 drop function f1;
403 sync_slave_with_master;
404 connection slave;
405 select * from t1;
406
407 #
408 # Bug#16621 "INSERTs in Stored Procedures causes data corruption in the Binary
409 # Log for 5.0.18"
410 #
411
412 # Prepare environment.
413
414 connection master;
415
416 --disable_warnings
417 DROP PROCEDURE IF EXISTS p1;
418 DROP TABLE IF EXISTS t1;
419 --enable_warnings
420
421 # Test case.
422
423 CREATE TABLE t1(col VARCHAR(10));
424
425 CREATE PROCEDURE p1(arg VARCHAR(10))
426 INSERT INTO t1 VALUES(arg);
427
428 CALL p1('test');
429
430 SELECT * FROM t1;
431
432 sync_slave_with_master;
433 SELECT * FROM t1;
434
435 # Cleanup
436 connection master;
437 DROP PROCEDURE p1;
438
439
440 #
441 # BUG#20438: CREATE statements for views, stored routines and triggers can be
442 # not replicable.
443 #
444
445 --echo
446 --echo ---> Test for BUG#20438
447
448 # Prepare environment.
449
450 --echo
451 --echo ---> Preparing environment...
452 --connection master
453
454 --disable_warnings
455 DROP PROCEDURE IF EXISTS p1;
456 DROP FUNCTION IF EXISTS f1;
457 --enable_warnings
458
459 --echo
460 --echo ---> Synchronizing slave with master...
461
462 --sync_slave_with_master
463
464 --connection master
465
466 # Test.
467
468 --echo
469 --echo ---> Creating procedure...
470
471 /*!50003 CREATE PROCEDURE p1() SET @a = 1 */;
472
473 /*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */;
474
475 --echo
476 --echo ---> Checking on master...
477
478 SHOW CREATE PROCEDURE p1;
479 SHOW CREATE FUNCTION f1;
480
481 --echo
482 --echo ---> Synchronizing slave with master...
483
484 --sync_slave_with_master
485
486 --echo
487 --echo ---> Checking on slave...
488
489 SHOW CREATE PROCEDURE p1;
490 SHOW CREATE FUNCTION f1;
491
492 # Cleanup.
493
494 --connection master
495
496 --echo
497 --echo ---> Cleaning up...
498
499 DROP PROCEDURE p1;
500 DROP FUNCTION f1;
501
502 --sync_slave_with_master
503 --connection master
504
505
506 # cleanup
507 connection master;
508 drop table t1;
509 sync_slave_with_master;
510
511 #
512 # Bug22043: MySQL don't add "USE <DATABASE>" before "DROP PROCEDURE IF EXISTS"
513 #
514
515 connection master;
516 --disable_warnings
517 drop database if exists mysqltest;
518 drop database if exists mysqltest2;
519 --enable_warnings
520 create database mysqltest;
521 create database mysqltest2;
522 use mysqltest2;
523 create table t ( t integer );
524 create procedure mysqltest.test() begin end;
525 insert into t values ( 1 );
526 --error ER_BAD_DB_ERROR
527 create procedure `\\`.test() begin end;
528
529 #
530 # BUG#19725: Calls to stored function in other database are not
531 # replicated correctly in some cases
532 #
533
534 connection master;
535 delimiter |;
536 create function f1 () returns int
537 begin
538 insert into t values (1);
539 return 0;
540 end|
541 delimiter ;|
542 sync_slave_with_master;
543 # Let us test if we don't forget to binlog the function's database
544 connection master;
545 use mysqltest;
546 set @a:= mysqltest2.f1();
547 sync_slave_with_master;
548 connection master;
549
550 # Final inspection which verifies how all statements of this test file
551 # were written to the binary log.
552 --source include/show_binlog_events.inc
553
554
555 # Restore log_bin_trust_function_creators to its original value.
556 # This is a cleanup for all parts above where we tested stored
557 # functions and triggers.
558 connection slave;
559 set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
560 connection master;
561 set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
562
563 # Clean up
564 drop database mysqltest;
565 drop database mysqltest2;
566 sync_slave_with_master;
567
568 #
569 # Bug#36570: Parse error of CREATE PROCEDURE stmt with comments on slave
570 #
571 connection master;
572 use test;
573 delimiter |;
574
575 /*!50001 create procedure `mysqltestbug36570_p1`() */
576 begin
577 select 1;
578 end|
579
580 use mysql|
581 create procedure test.` mysqltestbug36570_p2`(/*!50001 a int*/)`label`:
582 begin
583 select a;
584 end|
585
586 /*!50001 create function test.mysqltestbug36570_f1() */
587 returns int
588 /*!50001 deterministic */
589 begin
590 return 3;
591 end|
592 use test|
593
594 delimiter ;|
595
596 --replace_column 5 t 6 t
597 show procedure status like '%mysqltestbug36570%';
598 show create procedure ` mysqltestbug36570_p2`;
599
600 sync_slave_with_master;
601 connection slave;
602
603 --replace_column 5 t 6 t
604 show procedure status like '%mysqltestbug36570%';
605 show create procedure ` mysqltestbug36570_p2`;
606 call ` mysqltestbug36570_p2`(42);
607
608 --replace_column 5 t 6 t
609 show function status like '%mysqltestbug36570%';
610
611 connection master;
612 flush logs;
613 let $MYSQLD_DATADIR= `select @@datadir`;
614 --replace_regex /$MYSQL_TEST_DIR/MYSQL_TEST_DIR/ /TIMESTAMP=[0-9]*/TIMESTAMP=t/
615 --exec $MYSQL_BINLOG --short-form $MYSQLD_DATADIR/master-bin.000001
616 use test;
617 drop procedure mysqltestbug36570_p1;
618 drop procedure ` mysqltestbug36570_p2`;
619 drop function mysqltestbug36570_f1;
620 --echo End of 5.0 tests
621 --echo # End of 5.1 tests
622 --echo #
623 --echo # Test Bug#30977 Concurrent statement using stored
624 --echo # function and DROP FUNCTION breaks SBR.
625 --echo #
626 --echo # Demonstrate that stored function DDL can not go through,
627 --echo # or, worse yet, make its way into the binary log, while
628 --echo # the stored function is in use.
629 --echo # For that, try to insert a result of a stored function
630 --echo # into a table. Block the insert in the beginning, waiting
631 --echo # on a table lock. While insert is blocked, attempt to
632 --echo # drop the routine. Verify that this attempt
633 --echo # blocks and waits for INSERT to complete. Commit and
634 --echo # reap the chain of events. Master and slave must contain
635 --echo # identical data. Statements in the binrary log must be
636 --echo # consistent with data in the table.
637 --echo #
638 connection default;
639 --disable_warnings
640 drop table if exists t1, t2;
641 drop function if exists t1;
642 --enable_warnings
643 create table t1 (a int);
644 create table t2 (a int) as select 1 as a;
645 create function f1() returns int deterministic return (select max(a) from t2);
646 lock table t2 write;
647 connection master;
648 --echo # Sending 'insert into t1 (a) values (f1())'...
649 --send insert into t1 (a) values (f1())
650 connection master1;
651 --echo # Waitng for 'insert into t1 ...' to get blocked on table lock...
652 let $wait_condition=select count(*)=1 from information_schema.processlist
653 where state='Waiting for table metadata lock' and
654 info='insert into t1 (a) values (f1())';
655 --source include/wait_condition.inc
656 --echo # Sending 'drop function f1'. It will wait till insert finishes.
657 --send drop function f1;
658 connection default;
659 --echo # Check that 'drop function f1' gets blocked.
660 let $wait_condition=select count(*)=1 from information_schema.processlist
661 where state='Waiting for stored function metadata lock' and info='drop function f1';
662 --source include/wait_condition.inc
663 --echo # Now let's let 'insert' go through...
664 unlock tables;
665 connection master;
666 --echo # Reaping 'insert into t1 (a) values (f1())'...
667 --reap
668 connection master1;
669 --echo # Reaping 'drop function f1'
670 --reap
671 connection master;
672 select * from t1;
673 sync_slave_with_master;
674 connection slave;
675 select * from t1;
676 # Cleanup
677 connection master;
678 drop table t1, t2;
679 --error ER_SP_DOES_NOT_EXIST
680 drop function f1;
681
682
683 --echo #
684 --echo # Bug #11918 Can't use a declared variable in LIMIT clause
685 --echo #
686 --source include/rpl_reset.inc
687
688 create table t1 (c1 int);
689 insert into t1 (c1) values
690 (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
691
692 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
693
694 create procedure p1(p1 integer)
695 delete from t1 limit p1;
696
697 set @save_binlog_format=@@session.binlog_format;
698 set @@session.binlog_format=STATEMENT;
699
700 --disable_warnings
701 call p1(NULL);
702 call p1(0);
703 call p1(1);
704 call p1(2);
705 call p1(3);
706 --enable_warnings
707
708 select * from t1;
709 sync_slave_with_master;
710 connection slave;
711 select * from t1;
712 connection master;
713 --disable_warnings
714 call p1(-1);
715 --enable_warnings
716 select * from t1;
717 sync_slave_with_master;
718 connection slave;
719 select * from t1;
720 connection master;
721
722 --echo # Cleanup
723 set @@session.binlog_format=@save_binlog_format;
724 drop table t1;
725 drop procedure p1;
726
727 --echo # End of 5.5 tests.
728
729
730 # Cleanup
731 sync_slave_with_master;
732 --source include/rpl_end.inc