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
# Note that in the .opt files we still use the old variable name
7
# log-bin-trust-routine-creators so that this test checks that it's
8
# still accepted (this test also checks that the new name is
9
# accepted). The old name could be removed in 5.1 or 6.0.
11
source include/have_binlog_format_mixed.inc;
12
source include/master-slave.inc;
14
# we need a db != test, where we don't have automatic grants
16
drop database if exists mysqltest1;
18
create database mysqltest1;
20
create table t1 (a varchar(100));
21
sync_slave_with_master;
24
# ********************** PART 1 : STORED PROCEDURES ***************
26
# Does the same proc as on master get inserted into mysql.proc ?
27
# (same definer, same properties...)
33
# Stored procedures don't have the limitations that functions have
34
# regarding binlogging: it's ok to create a procedure as not
35
# deterministic and updating data, while it's not ok to create such a
36
# function. We test this.
38
create procedure foo()
42
insert into t1 values (b);
43
insert into t1 values (unix_timestamp());
47
# we replace columns having times
48
# (even with fixed timestamp displayed time may changed based on TZ)
49
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
50
--replace_column 13 # 14 #
51
select * from mysql.proc where name='foo' and db='mysqltest1';
52
sync_slave_with_master;
53
# You will notice in the result that the definer does not match what
54
# it is on master, it is a known bug on which Alik is working
55
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
56
--replace_column 13 # 14 #
57
select * from mysql.proc where name='foo' and db='mysqltest1';
60
# see if timestamp used in SP on slave is same as on master
61
set timestamp=1000000000;
64
sync_slave_with_master;
67
# Now a SP which is not updating tables
71
create procedure foo2()
72
select * from mysqltest1.t1;
75
# check that this is allowed (it's not for functions):
76
alter procedure foo2 contains sql;
78
# SP with definer's right
81
create table t1 (a int);
82
create table t2 like t1;
84
create procedure foo3()
86
insert into t1 values (15);
88
# let's create a non-privileged user
89
grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
90
grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
91
grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;
93
# ToDo: BUG#14931: There is a race between the last grant binlogging, and
94
# the binlogging in the new connection made below, causing sporadic test
95
# failures due to switched statement order in binlog. To fix this we do
96
# SELECT 1 in the first connection before starting the second, ensuring
97
# that binlogging is done in the expected order.
98
# Please remove this SELECT 1 when BUG#14931 is fixed.
101
connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,);
104
# this routine will fail in the second INSERT because of privileges
106
create procedure foo4()
109
insert into t2 values(3);
110
insert into t1 values (5);
115
# I add ,0 so that it does not print the error in the test output,
116
# because this error is hostname-dependent
118
call foo4(); # invoker has no INSERT grant on table t1 => failure
121
call foo3(); # success (definer == root)
125
call foo4(); # definer's rights => failure
127
# we test replication of ALTER PROCEDURE
128
alter procedure foo4 sql security invoker;
129
call foo4(); # invoker's rights => success
132
# Note that half-failed procedure calls are ok with binlogging;
133
# if we compare t2 on master and slave we see they are identical:
137
sync_slave_with_master;
141
# Test of DROP PROCEDURE
143
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
144
--replace_column 13 # 14 #
145
select * from mysql.proc where name="foo4" and db='mysqltest1';
148
select * from mysql.proc where name="foo4" and db='mysqltest1';
149
sync_slave_with_master;
150
select * from mysql.proc where name="foo4" and db='mysqltest1';
152
# ********************** PART 2 : FUNCTIONS ***************
160
# check that needs "deterministic"
162
create function fn1(x int)
165
insert into t1 values (x);
168
create function fn1(x int)
172
insert into t1 values (x);
177
delete t1,t2 from t1,t2;
179
insert into t2 values(fn1(21));
183
sync_slave_with_master;
193
create function fn1()
197
return unix_timestamp();
201
# check that needs "deterministic"
203
alter function fn1 contains sql;
206
set timestamp=1000000000;
207
insert into t1 values(fn1());
212
--error 1419 # only full-global-privs user can create a function
213
create function fn2()
217
return unix_timestamp();
221
# test old variable name:
222
set global log_bin_trust_routine_creators=1;
224
set global log_bin_trust_function_creators=0;
225
set global log_bin_trust_function_creators=1;
226
# slave needs it too otherwise will not execute what master allowed:
228
set global log_bin_trust_function_creators=1;
233
create function fn2()
237
return unix_timestamp();
243
# Now a function which is supposed to not update tables
244
# as it's "reads sql data", so should not give error even if
248
create function fn3()
258
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
259
--replace_column 13 # 14 #
260
select * from mysql.proc where db='mysqltest1';
263
sync_slave_with_master;
266
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
267
--replace_column 13 # 14 #
268
select * from mysql.proc where db='mysqltest1';
270
# ********************** PART 3 : TRIGGERS ***************
273
# now fails due to missing trigger grant (err 1142 i/o 1227) due to new
274
# check in sql_trigger.cc (v1.44) by anozdrin on 2006/02/01 --azundris
275
--error ER_TABLEACCESS_DENIED_ERROR
276
create trigger trg before insert on t1 for each row set new.a= 10;
280
# TODO: when triggers can contain an update, test that this update
281
# does not go into binlog.
282
# I'm not setting user vars in the trigger, because replication of user vars
283
# would take care of propagating the user var's value to slave, so even if
284
# the trigger was not executed on slave it would not be discovered.
285
create trigger trg before insert on t1 for each row set new.a= 10;
286
insert into t1 values (1);
288
sync_slave_with_master;
294
insert into t1 values (1);
296
--replace_column 2 # 5 #
297
--replace_regex /table_id: [0-9]+/table_id: #/
298
#show binlog events in 'master-bin.000001' from 106;
299
sync_slave_with_master;
304
# Test for bug #13969 "Routines which are replicated from master can't be
305
# executed on slave".
308
create procedure foo()
312
sync_slave_with_master;
313
# This should not fail
317
sync_slave_with_master;
323
drop database mysqltest1;
324
drop user "zedjzlcsjhd"@127.0.0.1;
326
sync_slave_with_master;
330
# Bug#14077 "Failure to replicate a stored function with a cursor":
331
# verify that stored routines with cursors work on slave.
335
drop function if exists f1;
338
create function f1() returns int reads sql data
341
declare c cursor for select a from v1;
348
create view v1 as select 1 as a;
349
create table t1 (a int);
350
insert into t1 (a) values (f1());
354
sync_slave_with_master;
359
# Bug#16621 "INSERTs in Stored Procedures causes data corruption in the Binary
363
# Prepare environment.
368
DROP PROCEDURE IF EXISTS p1;
369
DROP TABLE IF EXISTS t1;
374
CREATE TABLE t1(col VARCHAR(10));
376
CREATE PROCEDURE p1(arg VARCHAR(10))
377
INSERT INTO t1 VALUES(arg);
383
sync_slave_with_master;
392
# BUG#20438: CREATE statements for views, stored routines and triggers can be
397
--echo ---> Test for BUG#20438
399
# Prepare environment.
402
--echo ---> Preparing environment...
403
--echo ---> connection: master
407
DROP PROCEDURE IF EXISTS p1;
408
DROP FUNCTION IF EXISTS f1;
412
--echo ---> Synchronizing slave with master...
419
--echo ---> connection: master
425
--echo ---> Creating procedure...
427
/*!50003 CREATE PROCEDURE p1() SET @a = 1 */;
429
/*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */;
432
--echo ---> Checking on master...
434
SHOW CREATE PROCEDURE p1;
435
SHOW CREATE FUNCTION f1;
438
--echo ---> Synchronizing slave with master...
444
--echo ---> connection: master
447
--echo ---> Checking on slave...
449
SHOW CREATE PROCEDURE p1;
450
SHOW CREATE FUNCTION f1;
455
--echo ---> connection: master
459
--echo ---> Cleaning up...
473
sync_slave_with_master;
475
# Restore log_bin_trust_function_creators to original value
476
set global log_bin_trust_function_creators=0;
478
set global log_bin_trust_function_creators=0;
479
--echo End of 5.0 tests
482
# Bug22043: MySQL don't add "USE <DATABASE>" before "DROP PROCEDURE IF EXISTS"
487
drop database if exists mysqltest;
488
drop database if exists mysqltest2;
490
create database mysqltest;
491
create database mysqltest2;
493
create table t ( t integer );
494
create procedure mysqltest.test() begin end;
495
insert into t values ( 1 );
496
#show binlog events in 'master-bin.000001' from 106;
497
--error ER_BAD_DB_ERROR
498
create procedure `\\`.test() begin end;
500
drop database mysqltest;
501
drop database mysqltest2;
503
--echo End of 5.1 tests