1
# row-based and statement have expected binlog difference in result files
3
# Test of replication of stored procedures (WL#2146 for MySQL 5.0)
6
source include/have_binlog_format_mixed.inc;
7
source include/master-slave.inc;
9
# we need a db != test, where we don't have automatic grants
11
drop database if exists mysqltest1;
13
create database mysqltest1;
15
create table t1 (a varchar(100));
16
sync_slave_with_master;
19
# ********************** PART 1 : STORED PROCEDURES ***************
21
# Does the same proc as on master get inserted into mysql.proc ?
22
# (same definer, same properties...)
28
# Stored procedures don't have the limitations that functions have
29
# regarding binlogging: it's ok to create a procedure as not
30
# deterministic and updating data, while it's not ok to create such a
31
# function. We test this.
33
create procedure foo()
37
insert into t1 values (b);
38
insert into t1 values (unix_timestamp());
42
# we replace columns having times
43
# (even with fixed timestamp displayed time may changed based on TZ)
44
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
45
--replace_column 13 # 14 #
46
select * from mysql.proc where name='foo' and db='mysqltest1';
47
sync_slave_with_master;
48
# You will notice in the result that the definer does not match what
49
# it is on master, it is a known bug on which Alik is working
50
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
51
--replace_column 13 # 14 #
52
select * from mysql.proc where name='foo' and db='mysqltest1';
55
# see if timestamp used in SP on slave is same as on master
56
set timestamp=1000000000;
59
sync_slave_with_master;
62
# Now a SP which is not updating tables
66
create procedure foo2()
67
select * from mysqltest1.t1;
70
# check that this is allowed (it's not for functions):
71
alter procedure foo2 contains sql;
73
# SP with definer's right
76
create table t1 (a int);
77
create table t2 like t1;
79
create procedure foo3()
81
insert into t1 values (15);
83
# let's create a non-privileged user
84
grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
85
grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
86
grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;
88
# ToDo: BUG#14931: There is a race between the last grant binlogging, and
89
# the binlogging in the new connection made below, causing sporadic test
90
# failures due to switched statement order in binlog. To fix this we do
91
# SELECT 1 in the first connection before starting the second, ensuring
92
# that binlogging is done in the expected order.
93
# Please remove this SELECT 1 when BUG#14931 is fixed.
96
connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,);
99
# this routine will fail in the second INSERT because of privileges
101
create procedure foo4()
104
insert into t2 values(3);
105
insert into t1 values (5);
110
# I add ,0 so that it does not print the error in the test output,
111
# because this error is hostname-dependent
113
call foo4(); # invoker has no INSERT grant on table t1 => failure
116
call foo3(); # success (definer == root)
120
call foo4(); # definer's rights => failure
122
# we test replication of ALTER PROCEDURE
123
alter procedure foo4 sql security invoker;
124
call foo4(); # invoker's rights => success
127
# Note that half-failed procedure calls are ok with binlogging;
128
# if we compare t2 on master and slave we see they are identical:
132
sync_slave_with_master;
136
# Let's check another failing-in-the-middle procedure
139
alter table t2 add unique (a);
143
create procedure foo4()
146
insert into t2 values(20),(20);
156
sync_slave_with_master;
157
# check that this failed-in-the-middle replicated right:
160
# Test of DROP PROCEDURE
162
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
163
--replace_column 13 # 14 #
164
select * from mysql.proc where name="foo4" and db='mysqltest1';
167
select * from mysql.proc where name="foo4" and db='mysqltest1';
168
sync_slave_with_master;
169
select * from mysql.proc where name="foo4" and db='mysqltest1';
171
# ********************** PART 2 : FUNCTIONS ***************
179
# check that needs "deterministic"
181
create function fn1(x int)
184
insert into t1 values (x);
187
create function fn1(x int)
191
insert into t1 values (x);
196
delete t1,t2 from t1,t2;
198
insert into t2 values(fn1(21));
201
sync_slave_with_master;
210
create function fn1()
214
return unix_timestamp();
218
# check that needs "deterministic"
220
alter function fn1 contains sql;
223
set timestamp=1000000000;
224
insert into t1 values(fn1());
229
--error 1419 # only full-global-privs user can create a function
230
create function fn2()
234
return unix_timestamp();
238
set global log_bin_trust_function_creators=0;
239
set global log_bin_trust_function_creators=1;
240
# slave needs it too otherwise will not execute what master allowed:
242
set global log_bin_trust_function_creators=1;
247
create function fn2()
251
return unix_timestamp();
257
# Now a function which is supposed to not update tables
258
# as it's "reads sql data", so should not give error even if
262
create function fn3()
272
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
273
--replace_column 13 # 14 #
274
select * from mysql.proc where db='mysqltest1';
277
sync_slave_with_master;
280
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
281
--replace_column 13 # 14 #
282
select * from mysql.proc where db='mysqltest1';
284
# Let's check a failing-in-the-middle function
287
alter table t2 add unique (a);
292
create function fn1(x int)
295
insert into t2 values(x),(x);
307
sync_slave_with_master;
309
# check that this failed-in-the-middle replicated right:
312
# ********************** PART 3 : TRIGGERS ***************
315
# now fails due to missing trigger grant (err 1142 i/o 1227) due to new
316
# check in sql_trigger.cc (v1.44) by anozdrin on 2006/02/01 --azundris
317
--error ER_TABLEACCESS_DENIED_ERROR
318
create trigger trg before insert on t1 for each row set new.a= 10;
322
# TODO: when triggers can contain an update, test that this update
323
# does not go into binlog.
324
# I'm not setting user vars in the trigger, because replication of user vars
325
# would take care of propagating the user var's value to slave, so even if
326
# the trigger was not executed on slave it would not be discovered.
327
create trigger trg before insert on t1 for each row set new.a= 10;
328
insert into t1 values (1);
330
sync_slave_with_master;
336
insert into t1 values (1);
338
sync_slave_with_master;
342
# ********************** PART 4 : RELATED FIXED BUGS ***************
346
# Test for bug #13969 "Routines which are replicated from master can't be
347
# executed on slave".
350
create procedure foo()
354
sync_slave_with_master;
355
# This should not fail
359
sync_slave_with_master;
365
drop database mysqltest1;
366
drop user "zedjzlcsjhd"@127.0.0.1;
368
sync_slave_with_master;
372
# Bug#14077 "Failure to replicate a stored function with a cursor":
373
# verify that stored routines with cursors work on slave.
377
drop function if exists f1;
380
create function f1() returns int reads sql data
383
declare c cursor for select a from v1;
390
create view v1 as select 1 as a;
391
create table t1 (a int);
392
insert into t1 (a) values (f1());
396
sync_slave_with_master;
401
# Bug#16621 "INSERTs in Stored Procedures causes data corruption in the Binary
405
# Prepare environment.
410
DROP PROCEDURE IF EXISTS p1;
411
DROP TABLE IF EXISTS t1;
416
CREATE TABLE t1(col VARCHAR(10));
418
CREATE PROCEDURE p1(arg VARCHAR(10))
419
INSERT INTO t1 VALUES(arg);
425
sync_slave_with_master;
434
# BUG#20438: CREATE statements for views, stored routines and triggers can be
439
--echo ---> Test for BUG#20438
441
# Prepare environment.
444
--echo ---> Preparing environment...
445
--echo ---> connection: master
449
DROP PROCEDURE IF EXISTS p1;
450
DROP FUNCTION IF EXISTS f1;
454
--echo ---> Synchronizing slave with master...
461
--echo ---> connection: master
467
--echo ---> Creating procedure...
469
/*!50003 CREATE PROCEDURE p1() SET @a = 1 */;
471
/*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */;
474
--echo ---> Checking on master...
476
SHOW CREATE PROCEDURE p1;
477
SHOW CREATE FUNCTION f1;
480
--echo ---> Synchronizing slave with master...
486
--echo ---> connection: master
489
--echo ---> Checking on slave...
491
SHOW CREATE PROCEDURE p1;
492
SHOW CREATE FUNCTION f1;
497
--echo ---> connection: master
501
--echo ---> Cleaning up...
515
sync_slave_with_master;
518
# Bug22043: MySQL don't add "USE <DATABASE>" before "DROP PROCEDURE IF EXISTS"
523
drop database if exists mysqltest;
524
drop database if exists mysqltest2;
526
create database mysqltest;
527
create database mysqltest2;
529
create table t ( t integer );
530
create procedure mysqltest.test() begin end;
531
insert into t values ( 1 );
532
--error ER_BAD_DB_ERROR
533
create procedure `\\`.test() begin end;
536
# BUG#19725: Calls to stored function in other database are not
537
# replicated correctly in some cases
542
create function f1 () returns int
544
insert into t values (1);
548
sync_slave_with_master;
549
# Let us test if we don't forget to binlog the function's database
552
set @a:= mysqltest2.f1();
553
sync_slave_with_master;
556
# Final inspection which verifies how all statements of this test file
557
# were written to the binary log.
558
source include/show_binlog_events.inc;
561
# Restore log_bin_trust_function_creators to its original value.
562
# This is a cleanup for all parts above where we tested stored
563
# functions and triggers.
564
set global log_bin_trust_function_creators=0;
566
set global log_bin_trust_function_creators=0;
569
drop database mysqltest;
570
drop database mysqltest2;
571
sync_slave_with_master;
573
--echo End of 5.0 tests
574
--echo End of 5.1 tests
added
removed
mysql-test/suite/rpl/t/rpl_sp.test
