summaryrefslogtreecommitdiff
path: root/mysql-test/t/sp_notembedded.test
blob: 2a24ab3d0054e2aebd4fff3aba321cb296855b1e (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
# Can't test with embedded server
-- source include/not_embedded.inc
# Disable concurrent inserts to avoid test failures
set @old_concurrent_insert= @@global.concurrent_insert;
set @@global.concurrent_insert= 0;

# Save the initial number of concurrent sessions
--source include/count_sessions.inc

--disable_warnings
drop table if exists t1,t3;
--enable_warnings
delimiter |;


#
# Bug#4902 Stored procedure with SHOW WARNINGS leads to packet error
#
# Added tests for show grants command
--disable_warnings
drop procedure if exists bug4902|
--enable_warnings
create procedure bug4902()
begin
  show grants for 'root'@'localhost';
end|
--disable_parsing
show binlog events|
show storage engines|
show master status|
show slave hosts|
show slave status|
--enable_parsing

call bug4902()|
call bug4902()|

drop procedure bug4902|

# We need separate SP for SHOW PROCESSLIST  since we want use replace_column
--disable_warnings
drop procedure if exists bug4902_2|
--enable_warnings
create procedure bug4902_2()
begin
  show processlist;
end|
--disable_result_log
call bug4902_2()|
--enable_result_log
show warnings|
--disable_result_log
call bug4902_2()|
--enable_result_log
show warnings|
drop procedure bug4902_2|

#
# Bug#6807 Stored procedure crash if CREATE PROCEDURE ... KILL QUERY
#
--disable_warnings
drop procedure if exists bug6807|
--enable_warnings
create procedure bug6807()
begin
  declare a int;

  set a = connection_id();
  kill query a;
  select 'Not reached';
end|

--error ER_QUERY_INTERRUPTED
call bug6807()|
--error ER_QUERY_INTERRUPTED
call bug6807()|

drop procedure bug6807|


#
# Bug#10100 function (and stored procedure?) recursivity problem
#
--disable_warnings
drop function if exists bug10100f|
drop procedure if exists bug10100p|
drop procedure if exists bug10100t|
drop procedure if exists bug10100pt|
drop procedure if exists bug10100pv|
drop procedure if exists bug10100pd|
drop procedure if exists bug10100pc|
--enable_warnings
# routines with simple recursion
create function bug10100f(prm int) returns int
begin
  if prm > 1 then
    return prm * bug10100f(prm - 1);
  end if;
  return 1;
end|
create procedure bug10100p(prm int, inout res int)
begin
  set res = res * prm;
  if prm > 1 then
    call bug10100p(prm - 1, res);
  end if;
end|
create procedure bug10100t(prm int)
begin
  declare res int;
  set res = 1;
  call bug10100p(prm, res);
  select res;
end|

# a procedure which use tables and recursion
create table t3 (a int)|
insert into t3 values (0)|
create view v1 as select a from t3;
create procedure bug10100pt(level int, lim int)
begin
  if level < lim then
    update t3 set a=level;
    FLUSH TABLES;
    call bug10100pt(level+1, lim);
  else
    select * from t3;
  end if;
end|
# view & recursion
create procedure bug10100pv(level int, lim int)
begin
  if level < lim then
    update v1 set a=level;
    FLUSH TABLES;
    call bug10100pv(level+1, lim);
  else
    select * from v1;
  end if;
end|
# dynamic sql & recursion
prepare stmt2 from "select * from t3;";
create procedure bug10100pd(level int, lim int)
begin
  if level < lim then
    select level;
    prepare stmt1 from "update t3 set a=a+2";
    execute stmt1;
    FLUSH TABLES;
    execute stmt1;
    FLUSH TABLES;
    execute stmt1;
    FLUSH TABLES;
    deallocate prepare stmt1;
    execute stmt2;
    select * from t3;
    call bug10100pd(level+1, lim);
  else
    execute stmt2;
  end if;
end|
# cursor & recursion
create procedure bug10100pc(level int, lim int)
begin
  declare lv int;
  declare c cursor for select a from t3;
  open c;
  if level < lim then
    select level;
    fetch c into lv;
    select lv;
    update t3 set a=level+lv;
    FLUSH TABLES;
    call bug10100pc(level+1, lim);
  else
    select * from t3;
  end if;
  close c;
end|

# end of the stack checking
set @@max_sp_recursion_depth=255|
set @var=1|
# disable log because error about stack overrun contains numbers which
# depend on a system
-- disable_result_log
-- error ER_STACK_OVERRUN_NEED_MORE
call bug10100p(255, @var)|
-- error ER_STACK_OVERRUN_NEED_MORE
call bug10100pt(1,255)|
-- error ER_STACK_OVERRUN_NEED_MORE
call bug10100pv(1,255)|
-- error ER_STACK_OVERRUN_NEED_MORE
call bug10100pd(1,255)|
-- error ER_STACK_OVERRUN_NEED_MORE
call bug10100pc(1,255)|
-- enable_result_log
set @@max_sp_recursion_depth=0|

deallocate prepare stmt2|

drop function bug10100f|
drop procedure bug10100p|
drop procedure bug10100t|
drop procedure bug10100pt|
drop procedure bug10100pv|
drop procedure bug10100pd|
drop procedure bug10100pc|
drop view v1|
drop table t3|

delimiter ;|


#
# Bug#15298 SHOW GRANTS FOR CURRENT_USER: Incorrect output in DEFINER context
#
--disable_warnings
drop procedure if exists bug15298_1;
drop procedure if exists bug15298_2;
--enable_warnings
grant all privileges on test.* to 'mysqltest_1'@'localhost';
create procedure 15298_1 () sql security definer show grants for current_user;
create procedure 15298_2 () sql security definer show grants;

connect (con1,localhost,mysqltest_1,,test);
call 15298_1();
call 15298_2();

connection default;
disconnect con1;
drop user mysqltest_1@localhost;
drop procedure 15298_1;
drop procedure 15298_2;

#
# Bug#29936 Stored Procedure DML ignores low_priority_updates setting
#

--disable_warnings
drop table if exists t1;
drop procedure if exists p1;
--enable_warnings

create table t1 (value varchar(15));
create procedure p1() update t1 set value='updated' where value='old';

# load the procedure into sp cache and execute once
call p1();

insert into t1 (value) values ("old"),("irrelevant");

connect (rl_holder, localhost, root,,);
connect (rl_acquirer, localhost, root,,);
connect (rl_contender, localhost, root,,);
connect (rl_wait, localhost, root,,);

connection rl_holder;
select get_lock('b26162',120);

connection rl_acquirer;
--send select 'rl_acquirer', value from t1 where get_lock('b26162',120);

# we must wait till this select opens and locks the tables
connection rl_wait;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "User lock" and
  info = "select 'rl_acquirer', value from t1 where get_lock('b26162',120)";
--source include/wait_condition.inc

connection default;
set session low_priority_updates=on;
--send call p1();

connection rl_wait;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table level lock" and
  info = "update t1 set value='updated' where value='old'";
--source include/wait_condition.inc

connection rl_contender;
select 'rl_contender', value from t1;

connection rl_holder;
select release_lock('b26162');

connection rl_acquirer;
--reap
connection default;
--reap

disconnect rl_holder;
disconnect rl_acquirer;
disconnect rl_wait;
disconnect rl_contender;
drop procedure p1;
drop table t1;
set session low_priority_updates=default;

#
# Bug#44798 MySQL engine crashes when creating stored procedures with execute_priv=N
#
INSERT INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv,
Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv,
Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv,
Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv,
Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions,
max_updates, max_connections, max_user_connections) 
VALUES('%', 'mysqltest_1', password(''), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N',
'N', 'N', 'N', 'Y', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'Y', 'Y', 'N', '',
'', '', '', '0', '0', '0', '0');
FLUSH PRIVILEGES;

connect (con1, localhost, mysqltest_1,,);
connection con1;
CREATE PROCEDURE p1(i INT) BEGIN END;
disconnect con1;
connection default;
DROP PROCEDURE p1;

DELETE FROM mysql.user WHERE User='mysqltest_1';
FLUSH PRIVILEGES;


--echo #
--echo # Bug#44521 Prepared Statement: CALL p() - crashes: `! thd->main_da.is_sent' failed et.al.
--echo #
SELECT GET_LOCK('Bug44521', 0);
--connect (con1,localhost,root,,)
--echo ** Connection con1
delimiter $;
CREATE PROCEDURE p()
BEGIN
  SELECT 1;
  SELECT GET_LOCK('Bug44521', 100);
  SELECT 2;
END$
delimiter ;$
--send CALL p();
--connection default
--echo ** Default connection
let $wait_condition=
  SELECT count(*) = 1 FROM information_schema.processlist
  WHERE state = "User lock" AND info = "SELECT GET_LOCK('Bug44521', 100)";
--source include/wait_condition.inc
let $conid =
  `SELECT id FROM information_schema.processlist
   WHERE state = "User lock" AND info = "SELECT GET_LOCK('Bug44521', 100)"`;
dirty_close con1;
SELECT RELEASE_LOCK('Bug44521');
let $wait_condition=
  SELECT count(*) = 0 FROM information_schema.processlist
  WHERE id = $conid;
--source include/wait_condition.inc 
DROP PROCEDURE p;

#
# Bug#47736 killing a select from a view when the view is processing a function, asserts
#
CREATE TABLE t1(a int);
INSERT INTO t1 VALUES (1);
CREATE FUNCTION f1 (inp TEXT) RETURNS INT NO SQL RETURN sleep(60);
CREATE VIEW v1 AS SELECT f1('a') FROM t1;

--connect (con1, localhost, root,,)
--let $ID_1= `SELECT connection_id()`
--send SELECT * FROM v1;

--connect (con2, localhost, root,,)
--let $ID_2= `SELECT connection_id()`
--send SELECT * FROM v1

--connection default
let $wait_condition=
  select count(*) = 2 from information_schema.processlist where state = "User sleep";
--source include/wait_condition.inc
--disable_query_log
--eval KILL QUERY $ID_2
--eval KILL QUERY $ID_1
--enable_query_log

--connection con1
--error ER_QUERY_INTERRUPTED
--reap
--connection con2
--error ER_QUERY_INTERRUPTED
--reap

--connection default
DROP VIEW v1;
DROP TABLE t1;
DROP FUNCTION f1;
--disconnect con1
--disconnect con2

--echo # ------------------------------------------------------------------
--echo # -- End of 5.1 tests
--echo # ------------------------------------------------------------------

--echo #
--echo # Test for bug#11763757 "56510: ERROR 42000: FUNCTION DOES NOT EXIST
--echo # IF NOT-PRIV USER RECONNECTS ".
--echo #
--echo # The real problem was that server was unable handle properly stored
--echo # functions in databases which names contained dot.
--echo #

connection default;

--disable_warnings
DROP DATABASE IF EXISTS `my.db`;
--enable_warnings

create database `my.db`;
use `my.db`;

CREATE FUNCTION f1(a int) RETURNS INT RETURN a;

--echo # Create new connection.
connect (addcon, localhost, root,,);
connection addcon;
USE `my.db`;
SELECT f1(1);
SELECT `my.db`.f1(2);

--echo # Switching to default connection.
connection default;
disconnect addcon;
DROP DATABASE `my.db`;
USE test;


--echo #
--echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE
--echo #
SET @@SQL_MODE = '';
DELIMITER $;

CREATE EVENT teste_bug11763507 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR 
DO SELECT 1 $

DELIMITER ;$
# EVENTS
--replace_column 6 # 7 # 8 # 9 #
SHOW EVENTS LIKE 'teste_bug11763507';
--replace_column 6 # 7 # 8 # 9 #
SHOW EVENTS LIKE 'TESTE_bug11763507';

--replace_column 4 # 
SHOW CREATE EVENT teste_bug11763507;
--replace_column 4 # 
SHOW CREATE EVENT TESTE_bug11763507;

DROP EVENT teste_bug11763507;
--echo #END OF BUG#11763507 test.

--echo # ------------------------------------------------------------------
--echo # -- End of 5.1 tests
--echo # ------------------------------------------------------------------

#
# A case of SHOW GRANTS
# (creating a new procedure changes the password)
#
grant create routine on test.* to foo1@localhost identified by 'foo';
update mysql.user set password = replace(password, '*', '-') where user='foo1';
--connect (foo,localhost,foo1,foo)
show grants;
--connection default
flush privileges;
--connection foo
show grants;
create procedure spfoo() select 1;
show grants;

--connection default
--disconnect foo
drop procedure spfoo;
drop user foo1@localhost;

#
# Restore global concurrent_insert value. Keep in the end of the test file.
#

set @@global.concurrent_insert= @old_concurrent_insert;

# Wait till all disconnects are completed
--source include/wait_until_count_sessions.inc