summaryrefslogtreecommitdiff
path: root/mysql-test/r/sp_notembedded.result
blob: 3cc4ff4238b594f780423d11558763e78588b454 (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
set @old_concurrent_insert= @@global.concurrent_insert;
set @@global.concurrent_insert= 0;
drop table if exists t1,t3;
drop procedure if exists bug4902|
create procedure bug4902()
begin
show grants for 'root'@'localhost';
end|
call bug4902()|
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
call bug4902()|
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
drop procedure bug4902|
drop procedure if exists bug4902_2|
create procedure bug4902_2()
begin
show processlist;
end|
call bug4902_2()|
show warnings|
Level	Code	Message
Note	1305	PROCEDURE test.bug4902_2 does not exist
call bug4902_2()|
show warnings|
Level	Code	Message
Note	1305	PROCEDURE test.bug4902_2 does not exist
drop procedure bug4902_2|
drop procedure if exists bug6807|
create procedure bug6807()
begin
declare a int;
set a = connection_id();
kill query a;
select 'Not reached';
end|
call bug6807()|
ERROR 70100: Query execution was interrupted
call bug6807()|
ERROR 70100: Query execution was interrupted
drop procedure bug6807|
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|
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|
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|
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|
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|
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|
set @@max_sp_recursion_depth=255|
set @var=1|
call bug10100p(255, @var)|
call bug10100pt(1,255)|
call bug10100pv(1,255)|
call bug10100pd(1,255)|
call bug10100pc(1,255)|
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|
drop procedure if exists bug15298_1;
drop procedure if exists bug15298_2;
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;
call 15298_1();
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
call 15298_2();
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
drop user mysqltest_1@localhost;
drop procedure 15298_1;
drop procedure 15298_2;
drop table if exists t1;
drop procedure if exists p1;
create table t1 (value varchar(15));
create procedure p1() update t1 set value='updated' where value='old';
call p1();
insert into t1 (value) values ("old"),("irrelevant");
select get_lock('b26162',120);
get_lock('b26162',120)
1
select 'rl_acquirer', value from t1 where get_lock('b26162',120);;
set session low_priority_updates=on;
call p1();;
select 'rl_contender', value from t1;
rl_contender	value
rl_contender	old
rl_contender	irrelevant
select release_lock('b26162');
release_lock('b26162')
1
rl_acquirer	value
rl_acquirer	old
rl_acquirer	irrelevant
drop procedure p1;
drop table t1;
set session low_priority_updates=default;
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');
Warnings:
Warning	1364	Field 'authentication_string' doesn't have a default value
FLUSH PRIVILEGES;
CREATE PROCEDURE p1(i INT) BEGIN END;
DROP PROCEDURE p1;
DELETE FROM mysql.user WHERE User='mysqltest_1';
FLUSH PRIVILEGES;
#
# Bug#44521 Prepared Statement: CALL p() - crashes: `! thd->main_da.is_sent' failed et.al.
#
SELECT GET_LOCK('Bug44521', 0);
GET_LOCK('Bug44521', 0)
1
** Connection con1
CREATE PROCEDURE p()
BEGIN
SELECT 1;
SELECT GET_LOCK('Bug44521', 100);
SELECT 2;
END$
CALL p();;
** Default connection
SELECT RELEASE_LOCK('Bug44521');
RELEASE_LOCK('Bug44521')
1
DROP PROCEDURE p;
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;
SELECT * FROM v1;;
SELECT * FROM v1;
ERROR 70100: Query execution was interrupted
ERROR 70100: Query execution was interrupted
DROP VIEW v1;
DROP TABLE t1;
DROP FUNCTION f1;
# ------------------------------------------------------------------
# -- End of 5.1 tests
# ------------------------------------------------------------------
#
# Test for bug#11763757 "56510: ERROR 42000: FUNCTION DOES NOT EXIST
# IF NOT-PRIV USER RECONNECTS ".
#
# The real problem was that server was unable handle properly stored
# functions in databases which names contained dot.
#
DROP DATABASE IF EXISTS `my.db`;
create database `my.db`;
use `my.db`;
CREATE FUNCTION f1(a int) RETURNS INT RETURN a;
# Create new connection.
USE `my.db`;
SELECT f1(1);
f1(1)
1
SELECT `my.db`.f1(2);
`my.db`.f1(2)
2
# Switching to default connection.
DROP DATABASE `my.db`;
USE test;
#
# Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE
#
SET @@SQL_MODE = '';
CREATE EVENT teste_bug11763507 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR 
DO SELECT 1 $
SHOW EVENTS LIKE 'teste_bug11763507';
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
test	teste_bug11763507	root@localhost	SYSTEM	ONE TIME	#	#	#	#	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
SHOW EVENTS LIKE 'TESTE_bug11763507';
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
test	teste_bug11763507	root@localhost	SYSTEM	ONE TIME	#	#	#	#	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
SHOW CREATE EVENT teste_bug11763507;
Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database Collation
teste_bug11763507		SYSTEM	#	latin1	latin1_swedish_ci	latin1_swedish_ci
SHOW CREATE EVENT TESTE_bug11763507;
Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database Collation
teste_bug11763507		SYSTEM	#	latin1	latin1_swedish_ci	latin1_swedish_ci
DROP EVENT teste_bug11763507;
#END OF BUG#11763507 test.
# ------------------------------------------------------------------
# -- End of 5.1 tests
# ------------------------------------------------------------------
grant create routine on test.* to foo1@localhost identified by 'foo';
update mysql.user set password = replace(password, '*', '-') where user='foo1';
show grants;
Grants for foo1@localhost
GRANT USAGE ON *.* TO 'foo1'@'localhost' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF'
GRANT CREATE ROUTINE ON `test`.* TO 'foo1'@'localhost'
flush privileges;
show grants;
Grants for foo1@localhost
GRANT USAGE ON *.* TO 'foo1'@'localhost' IDENTIFIED BY PASSWORD '-F3A2A51A9B0F2BE2468926B4132313728C250DBF'
GRANT CREATE ROUTINE ON `test`.* TO 'foo1'@'localhost'
create procedure spfoo() select 1;
show grants;
Grants for foo1@localhost
GRANT USAGE ON *.* TO 'foo1'@'localhost' IDENTIFIED BY PASSWORD '-F3A2A51A9B0F2BE2468926B4132313728C250DBF'
GRANT CREATE ROUTINE ON `test`.* TO 'foo1'@'localhost'
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`spfoo` TO 'foo1'@'localhost'
drop procedure spfoo;
drop user foo1@localhost;
set @@global.concurrent_insert= @old_concurrent_insert;