summaryrefslogtreecommitdiff
path: root/mysql-test/suite/engines/funcs/t/rpl_sp.test
blob: ad2cc29375be7d81b1a482739cdd4dea9fec7433 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
# row-based and statement have expected binlog difference in result files

# Test of replication of stored procedures (WL#2146 for MySQL 5.0)
# Modified by WL#2971.

source include/have_binlog_format_mixed.inc;
source include/master-slave.inc;

# we need a db != test, where we don't have automatic grants
--disable_warnings
drop database if exists mysqltest1;
--enable_warnings
create database mysqltest1;
use mysqltest1;
create table t1 (a varchar(100));
sync_slave_with_master;
use mysqltest1;

# ********************** PART 1 : STORED PROCEDURES ***************

# Does the same proc as on master get inserted into mysql.proc ?
# (same definer, same properties...)

connection master;

delimiter |;

# Stored procedures don't have the limitations that functions have
# regarding binlogging: it's ok to create a procedure as not
# deterministic and updating data, while it's not ok to create such a
# function. We test this.

create procedure foo()
begin
  declare b int;
  set b = 8;
  insert into t1 values (b);
  insert into t1 values (unix_timestamp());
end|
delimiter ;|

# we replace columns having times
# (even with fixed timestamp displayed time may changed based on TZ)
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where name='foo' and db='mysqltest1';
sync_slave_with_master;
# You will notice in the result that the definer does not match what
# it is on master, it is a known bug on which Alik is working
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where name='foo' and db='mysqltest1';

connection master;
# see if timestamp used in SP on slave is same as on master
set timestamp=1000000000;
call foo();
select * from t1;
sync_slave_with_master;
select * from t1;

# Now a SP which is not updating tables

connection master;
delete from t1;
create procedure foo2()
  select * from mysqltest1.t1;
call foo2();

# check that this is allowed (it's not for functions):
alter procedure foo2 contains sql;

# SP with definer's right

drop table t1;
create table t1 (a int);
create table t2 like t1;

create procedure foo3()
  deterministic
  insert into t1 values (15);

# let's create a non-privileged user
grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;

# ToDo: BUG#14931: There is a race between the last grant binlogging, and
# the binlogging in the new connection made below, causing sporadic test
# failures due to switched statement order in binlog. To fix this we do
# SELECT 1 in the first connection before starting the second, ensuring
# that binlogging is done in the expected order.
# Please remove this SELECT 1 when BUG#14931 is fixed.
SELECT 1;

connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,);
connection con1;

# this routine will fail in the second INSERT because of privileges
delimiter |;
create procedure foo4()
  deterministic
  begin
  insert into t2 values(3);
  insert into t1 values (5);
  end|

delimiter ;|

# I add ,0 so that it does not print the error in the test output,
# because this error is hostname-dependent
--error 1142,0
call foo4(); # invoker has no INSERT grant on table t1 => failure

connection master;
call foo3(); # success (definer == root)
show warnings;

--error 1142,0
call foo4(); # definer's rights => failure

# we test replication of ALTER PROCEDURE
alter procedure foo4 sql security invoker;
call foo4(); # invoker's rights => success
show warnings;

# Note that half-failed procedure calls are ok with binlogging;
# if we compare t2 on master and slave we see they are identical:

select * from t1;
select * from t2;
sync_slave_with_master;
select * from t1;
select * from t2;

# Test of DROP PROCEDURE

--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where name="foo4" and db='mysqltest1';
connection master;
drop procedure foo4;
select * from mysql.proc where name="foo4" and db='mysqltest1';
sync_slave_with_master;
select * from mysql.proc where name="foo4" and db='mysqltest1';

# ********************** PART 2 : FUNCTIONS ***************

connection master;
drop procedure foo;
drop procedure foo2;
drop procedure foo3;

delimiter |;
# check that needs "deterministic"
--error 1418
create function fn1(x int)
       returns int
begin
       insert into t1 values (x);
       return x+2;
end|
create function fn1(x int)
       returns int
       deterministic
begin
       insert into t1 values (x);
       return x+2;
end|

delimiter ;|
delete t1,t2 from t1,t2;
select fn1(20);
insert into t2 values(fn1(21));
--sorted_result
select * from t1;
select * from t2;
sync_slave_with_master;
--sorted_result
select * from t1;
select * from t2;

connection master;
delimiter |;

drop function fn1;

create function fn1()
       returns int
       no sql
begin
       return unix_timestamp();
end|

delimiter ;|
# check that needs "deterministic"
--error 1418
alter function fn1 contains sql;

delete from t1;
set timestamp=1000000000;
insert into t1 values(fn1()); 

connection con1;

delimiter |;
--error 1419 # only full-global-privs user can create a function
create function fn2()
       returns int
       no sql
begin
       return unix_timestamp();
end|
delimiter ;|
connection master;
set global log_bin_trust_function_creators=0;
set global log_bin_trust_function_creators=1;
# slave needs it too otherwise will not execute what master allowed:
connection slave;
set global log_bin_trust_function_creators=1;

connection con1;

delimiter |;
create function fn2()
       returns int
       no sql
begin
       return unix_timestamp();
end|
delimiter ;|

connection master;

# Now a function which is supposed to not update tables
# as it's "reads sql data", so should not give error even if
# non-deterministic.

delimiter |;
create function fn3()
       returns int
       not deterministic
       reads sql data
begin
  return 0;
end|
delimiter ;|

select fn3();
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where db='mysqltest1';
select * from t1;

sync_slave_with_master;
use mysqltest1;
select * from t1;
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where db='mysqltest1';

# ********************** PART 3 : TRIGGERS ***************

connection con1;
# now fails due to missing trigger grant (err 1142 i/o 1227) due to new
# check in sql_trigger.cc (v1.44) by anozdrin on 2006/02/01  --azundris
--error ER_TABLEACCESS_DENIED_ERROR
create trigger trg before insert on t1 for each row set new.a= 10;

connection master;
delete from t1;
# TODO: when triggers can contain an update, test that this update
# does not go into binlog.
# I'm not setting user vars in the trigger, because replication of user vars
# would take care of propagating the user var's value to slave, so even if
# the trigger was not executed on slave it would not be discovered.
create trigger trg before insert on t1 for each row set new.a= 10;
insert into t1 values (1);
select * from t1;
sync_slave_with_master;
select * from t1;

connection master;
delete from t1;
drop trigger trg;
insert into t1 values (1);
select * from t1;
--replace_column 2 # 5 #
--replace_regex /table_id: [0-9]+/table_id: #/
#show binlog events in 'master-bin.000001' from 106;
sync_slave_with_master;
select * from t1;


#
# Test for bug #13969 "Routines which are replicated from master can't be
# executed on slave".
# 
connection master;
create procedure foo()
  not deterministic
  reads sql data
  select * from t1;
sync_slave_with_master;
# This should not fail
call foo();
connection master;
drop procedure foo;
sync_slave_with_master;


# Clean up
connection master;
drop function fn1;
drop database mysqltest1;
drop user "zedjzlcsjhd"@127.0.0.1;
use test;
sync_slave_with_master;
use test;

#
# Bug#14077 "Failure to replicate a stored function with a cursor":
# verify that stored routines with cursors work on slave. 
#
connection master;
--disable_warnings
drop function if exists f1;
--enable_warnings
delimiter |;
create function f1() returns int reads sql data
begin
  declare var integer;
  declare c cursor for select a from v1;
  open c;
  fetch c into var;
  close c;
  return var;
end|
delimiter ;|
create view v1 as select 1 as a;
create table t1 (a int);
insert into t1 (a) values (f1());
select * from t1;
drop view v1;
drop function f1;
sync_slave_with_master;
connection slave;
select * from t1;

#
# Bug#16621 "INSERTs in Stored Procedures causes data corruption in the Binary
# Log for 5.0.18"
#

# Prepare environment.

connection master;

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
--enable_warnings

# Test case.

CREATE TABLE t1(col VARCHAR(10));

CREATE PROCEDURE p1(arg VARCHAR(10))
  INSERT INTO t1 VALUES(arg);

CALL p1('test');

SELECT * FROM t1;

sync_slave_with_master;
SELECT * FROM t1;

# Cleanup
connection master;
DROP PROCEDURE p1;


#
# BUG#20438: CREATE statements for views, stored routines and triggers can be
# not replicable.
#

--echo
--echo ---> Test for BUG#20438

# Prepare environment.

--echo
--echo ---> Preparing environment...
--echo ---> connection: master
--connection master

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
--enable_warnings

--echo
--echo ---> Synchronizing slave with master...

--save_master_pos
--connection slave
--sync_with_master

--echo
--echo ---> connection: master
--connection master

# Test.

--echo
--echo ---> Creating procedure...

/*!50003 CREATE PROCEDURE p1() SET @a = 1 */;

/*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */;

--echo
--echo ---> Checking on master...

SHOW CREATE PROCEDURE p1;
SHOW CREATE FUNCTION f1;

--echo
--echo ---> Synchronizing slave with master...

--save_master_pos
--connection slave
--sync_with_master

--echo ---> connection: master

--echo
--echo ---> Checking on slave...

SHOW CREATE PROCEDURE p1;
SHOW CREATE FUNCTION f1;

# Cleanup.

--echo
--echo ---> connection: master
--connection master

--echo
--echo ---> Cleaning up...

DROP PROCEDURE p1;
DROP FUNCTION f1;

--save_master_pos
--connection slave
--sync_with_master
--connection master


# cleanup
connection master;
drop table t1;
sync_slave_with_master;

# Restore log_bin_trust_function_creators to original value
set global log_bin_trust_function_creators=0;
connection master;
set global log_bin_trust_function_creators=0;
--echo End of 5.0 tests

#
# Bug22043: MySQL don't add "USE <DATABASE>" before "DROP PROCEDURE IF EXISTS"
#
connection master;
reset master;
--disable_warnings
drop database if exists mysqltest;
drop database if exists mysqltest2;
--enable_warnings
create database mysqltest;
create database mysqltest2;
use mysqltest2;
create table t ( t integer );
create procedure mysqltest.test() begin end;
insert into t values ( 1 );
#show binlog events in 'master-bin.000001' from 106;
--error ER_BAD_DB_ERROR
create procedure `\\`.test() begin end;
# Clean up
drop database mysqltest;
drop database mysqltest2;

--echo End of 5.1 tests