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
|
set @old_concurrent_insert= @@global.concurrent_insert;
set @@global.concurrent_insert= 0;
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|
create procedure bug4902_2()
begin
show processlist;
end|
call bug4902_2()|
show warnings|
Level Code Message
call bug4902_2()|
show warnings|
Level Code Message
drop procedure bug4902_2|
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|
create user 'mysqltest_1'@'localhost';
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();
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
connection default;
disconnect con1;
drop user mysqltest_1@localhost;
drop procedure 15298_1;
drop procedure 15298_2;
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");
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);
get_lock('b26162',120)
1
connection rl_acquirer;
select 'rl_acquirer', value from t1 where get_lock('b26162',120);;
connection rl_wait;
connection default;
set session low_priority_updates=on;
call p1();;
connection rl_wait;
connection rl_contender;
select 'rl_contender', value from t1;
rl_contender value
rl_contender old
rl_contender irrelevant
connection rl_holder;
select release_lock('b26162');
release_lock('b26162')
1
connection rl_acquirer;
rl_acquirer value
rl_acquirer old
rl_acquirer irrelevant
connection default;
disconnect rl_holder;
disconnect rl_acquirer;
disconnect rl_wait;
disconnect rl_contender;
drop procedure p1;
drop table t1;
set session low_priority_updates=default;
# switching from mysql.global_priv to mysql.user
INSERT IGNORE 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;
connect con1, localhost, mysqltest_1,,;
connection con1;
CREATE PROCEDURE p1(i INT) BEGIN END;
disconnect con1;
connection default;
DROP PROCEDURE p1;
# switching back from mysql.user to mysql.global_priv
#
# 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
connect con1,localhost,root,,;
CREATE PROCEDURE p()
BEGIN
SELECT 1;
SELECT GET_LOCK('Bug44521', 100);
SELECT 2;
END$
CALL p();;
connection default;
disconnect con1;
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;
connect con1, localhost, root,,;
SELECT * FROM v1;;
connect con2, localhost, root,,;
SELECT * FROM v1;
connection default;
connection con1;
ERROR 70100: Query execution was interrupted
connection con2;
ERROR 70100: Query execution was interrupted
connection default;
DROP VIEW v1;
DROP TABLE t1;
DROP FUNCTION f1;
disconnect con1;
disconnect con2;
# ------------------------------------------------------------------
# -- 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.
#
connection default;
create database `my.db`;
use `my.db`;
CREATE FUNCTION f1(a int) RETURNS INT RETURN a;
connect addcon, localhost, root,,;
connection addcon;
USE `my.db`;
SELECT f1(1);
f1(1)
1
SELECT `my.db`.f1(2);
`my.db`.f1(2)
2
connection default;
disconnect addcon;
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 $
Warnings:
Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
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
# ------------------------------------------------------------------
# switching from mysql.global_priv to mysql.user
grant create routine on test.* to foo1@localhost identified by 'foo';
update mysql.user set authentication_string = replace(authentication_string, '*', '-') where user='foo1';
connect foo,localhost,foo1,foo;
show grants;
Grants for foo1@localhost
GRANT USAGE ON *.* TO `foo1`@`localhost` IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF'
GRANT CREATE ROUTINE ON `test`.* TO `foo1`@`localhost`
connection default;
flush privileges;
connection foo;
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`
connection default;
disconnect foo;
drop procedure spfoo;
drop user foo1@localhost;
# switching back from mysql.user to mysql.global_priv
set @@global.concurrent_insert= @old_concurrent_insert;
|
