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
|
drop table if exists t1,t2;
show tables;
Tables_in_mysql
column_stats
columns_priv
db
event
func
general_log
gtid_slave_pos
help_category
help_keyword
help_relation
help_topic
host
index_stats
innodb_index_stats
innodb_table_stats
plugin
proc
procs_priv
proxies_priv
roles_mapping
servers
slow_log
table_stats
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
show tables;
Tables_in_test
connect(localhost,root,z,test2,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES)
connect(localhost,root,z,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES)
grant ALL on *.* to test@localhost identified by "gambling";
grant ALL on *.* to test@127.0.0.1 identified by "gambling";
show tables;
Tables_in_mysql
column_stats
columns_priv
db
event
func
general_log
gtid_slave_pos
help_category
help_keyword
help_relation
help_topic
host
index_stats
innodb_index_stats
innodb_table_stats
plugin
proc
procs_priv
proxies_priv
roles_mapping
servers
slow_log
table_stats
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
show tables;
Tables_in_test
connect(localhost,test,,test2,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
connect(localhost,test,,"",MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
connect(localhost,test,zorro,test2,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES)
connect(localhost,test,zorro,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES)
update mysql.user set password=old_password("gambling2") where user=_binary"test";
flush privileges;
set password="";
set password='gambling3';
ERROR HY000: Password hash should be a 41-digit hexadecimal number
set password=old_password('gambling3');
show tables;
Tables_in_mysql
column_stats
columns_priv
db
event
func
general_log
gtid_slave_pos
help_category
help_keyword
help_relation
help_topic
host
index_stats
innodb_index_stats
innodb_table_stats
plugin
proc
procs_priv
proxies_priv
roles_mapping
servers
slow_log
table_stats
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
show tables;
Tables_in_test
connect(localhost,test,,test2,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
connect(localhost,test,,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
connect(localhost,test,zorro,test2,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES)
connect(localhost,test,zorro,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES)
delete from mysql.user where user=_binary"test";
flush privileges;
create table t1 (id integer not null auto_increment primary key);
create temporary table t2(id integer not null auto_increment primary key);
set @id := 1;
delete from t1 where id like @id;
drop table t1;
# ------------------------------------------------------------------
# -- End of 4.1 tests
# ------------------------------------------------------------------
# -- Bug#33507: Event scheduler creates more threads than max_connections
# -- which results in user lockout.
GRANT USAGE ON *.* TO mysqltest_u1@localhost;
SET GLOBAL max_connections = 3;
SET GLOBAL event_scheduler = ON;
# -- Waiting for Event Scheduler to start...
# -- Disconnecting default connection...
# -- Check that we allow exactly three user connections, no matter how
# -- many threads are running.
# -- Connecting (1)...
# -- Establishing connection 'con_1' (user: mysqltest_u1)...
# -- Connection 'con_1' has been established.
# -- Connecting (2)...
# -- Establishing connection 'con_2' (user: mysqltest_u1)...
# -- Connection 'con_2' has been established.
# -- Connecting (3)...
# -- Establishing connection 'con_3' (user: mysqltest_u1)...
# -- Connection 'con_3' has been established.
# -- Connecting (4) [should fail]...
# -- Establishing connection 'con_4' (user: mysqltest_u1)...
# -- Error: can not establish connection 'con_4'.
# -- Check that we allow one extra SUPER-user connection.
# -- Connecting super (1)...
# -- Establishing connection 'con_super_1' (user: root)...
# -- Connection 'con_super_1' has been established.
# -- Connecting super (2) [should fail]...
# -- Establishing connection 'con_super_2' (user: root)...
# -- Error: can not establish connection 'con_super_2'.
# -- Ensure that we have Event Scheduler thread, 3 ordinary user
# -- connections and one extra super-user connection.
SELECT user FROM information_schema.processlist ORDER BY id;
user
event_scheduler
mysqltest_u1
mysqltest_u1
mysqltest_u1
root
# -- Resetting variables...
SET GLOBAL max_connections = 151;
# -- Stopping Event Scheduler...
SET GLOBAL event_scheduler = OFF;
# -- Waiting for Event Scheduler to stop...
# -- That's it. Closing connections...
# -- Restoring default connection...
# -- Waiting for connections to close...
DROP USER mysqltest_u1@localhost;
# -- End of Bug#33507.
# -- Bug#35074: max_used_connections is not correct.
FLUSH STATUS;
SHOW STATUS LIKE 'max_used_connections';
Variable_name Value
Max_used_connections 1
# -- Starting Event Scheduler...
SET GLOBAL event_scheduler = ON;
# -- Waiting for Event Scheduler to start...
# -- Opening a new connection to check max_used_connections...
# -- Check that max_used_connections hasn't changed.
SHOW STATUS LIKE 'max_used_connections';
Variable_name Value
Max_used_connections 2
# -- Closing new connection...
# -- Stopping Event Scheduler...
SET GLOBAL event_scheduler = OFF;
# -- Waiting for Event Scheduler to stop...
# -- End of Bug#35074.
SELECT 'Connection on extra port ok';
Connection on extra port ok
Connection on extra port ok
SELECT 'Connection on extra port 2 ok';
Connection on extra port 2 ok
Connection on extra port 2 ok
# -- Success: more than --extra-max-connections + 1 normal connections not possible
#
# -- Bug#49752: 2469.126.2 unintentionally breaks authentication
# against MySQL 5.1 server
#
GRANT ALL ON test.* TO 'O1234567890123456789012345678901234567890123456789012345678901234567890123456789'@'localhost' IDENTIFIED BY 'test123';
FLUSH PRIVILEGES;
DROP USER 'O1234567890123456789012345678901234567890123456789012345678901234567890123456789'@'localhost';
FLUSH PRIVILEGES;
#
# -- End of Bug#49752
#
# ------------------------------------------------------------------
# -- End of 5.1 tests
# ------------------------------------------------------------------
CREATE USER mysqltest_up1 IDENTIFIED VIA mysql_native_password using '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';
CREATE USER mysqltest_up2 IDENTIFIED VIA mysql_old_password using '09301740536db389';
connect(localhost,mysqltest_up1,foo,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'mysqltest_up1'@'localhost' (using password: YES)
select user(), current_user();
user() current_user()
mysqltest_up1@localhost mysqltest_up1@%
connect(localhost,mysqltest_up2,newpw,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'mysqltest_up2'@'localhost' (using password: YES)
select user(), current_user();
user() current_user()
mysqltest_up2@localhost mysqltest_up2@%
connect(localhost,mysqltest_nouser,newpw,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'mysqltest_nouser'@'localhost' (using password: YES)
connect(localhost,mysqltest_nouser,,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'mysqltest_nouser'@'localhost' (using password: NO)
update mysql.user set password=authentication_string, authentication_string=''
where user like 'mysqltest_up_';
select user, password, plugin, authentication_string from mysql.user
where user like 'mysqltest_up_';
user password plugin authentication_string
mysqltest_up1 *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB mysql_native_password
mysqltest_up2 09301740536db389 mysql_old_password
flush privileges;
select user(), current_user();
user() current_user()
mysqltest_up1@localhost mysqltest_up1@%
select user(), current_user();
user() current_user()
mysqltest_up2@localhost mysqltest_up2@%
DROP USER mysqltest_up1@'%';
DROP USER mysqltest_up2@'%';
#
# BUG#1010351: New "via" keyword in 5.2+ can't be used as identifier anymore
#
create table t1 (via int);
alter table t1 add key(via);
drop table t1;
create table t1 (col1 int);
alter table t1 add via int not null;
drop table t1;
drop procedure if exists p1;
create procedure p1(x int)
foo: loop
if x = 0 then
leave foo;
end if;
select 'test';
set x = x-1;
end loop foo|
call p1(2);
test
test
test
test
drop procedure p1;
|
