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
|
#
# Test behavior of various per-account limits (aka quotas)
#
# Requires privileges to be enabled
--source include/not_embedded.inc
# Save the initial number of concurrent sessions
--source include/count_sessions.inc
set @my_max_user_connections= @@global.max_user_connections;
# Prepare play-ground
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (i int);
# Just be sure that nothing will bother us
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
delete from mysql.tables_priv where user like 'mysqltest\_%';
delete from mysql.columns_priv where user like 'mysqltest\_%';
flush privileges;
# Limits doesn't work with prepared statements (yet)
--disable_ps_protocol
# Test of MAX_QUERIES_PER_HOUR limit
grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 2;
# This ensures that counters are reset and makes test scheduling independent
flush user_resources;
connect (mqph, localhost, mysqltest_1,,);
connection mqph;
select * from t1;
select * from t1;
--error ER_USER_LIMIT_REACHED
select * from t1;
connect (mqph2, localhost, mysqltest_1,,);
connection mqph2;
--error ER_USER_LIMIT_REACHED
select * from t1;
# cleanup
connection default;
drop user mysqltest_1@localhost;
disconnect mqph;
disconnect mqph2;
# Test of MAX_UPDATES_PER_HOUR limit
grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 2;
flush user_resources;
connect (muph, localhost, mysqltest_1,,);
connection muph;
select * from t1;
select * from t1;
select * from t1;
delete from t1;
delete from t1;
--error ER_USER_LIMIT_REACHED
delete from t1;
select * from t1;
connect (muph2, localhost, mysqltest_1,,);
connection muph2;
--error ER_USER_LIMIT_REACHED
delete from t1;
select * from t1;
# Cleanup
connection default;
drop user mysqltest_1@localhost;
disconnect muph;
disconnect muph2;
# Test of MAX_CONNECTIONS_PER_HOUR limit
grant usage on *.* to mysqltest_1@localhost with max_connections_per_hour 2;
flush user_resources;
connect (mcph1, localhost, mysqltest_1,,);
connection mcph1;
select * from t1;
connect (mcph2, localhost, mysqltest_1,,);
connection mcph2;
select * from t1;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_USER_LIMIT_REACHED
connect (mcph3, localhost, mysqltest_1,,);
# Old connection is still ok
select * from t1;
# Let us try to close old connections and try again. This will also test that
# counters are not thrown away if there are no connections for this user.
disconnect mcph1;
disconnect mcph2;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_USER_LIMIT_REACHED
connect (mcph3, localhost, mysqltest_1,,);
# Cleanup
connection default;
drop user mysqltest_1@localhost;
# Test of MAX_USER_CONNECTIONS limit
# We need this to reset internal mqh_used variable
flush privileges;
grant usage on *.* to mysqltest_1@localhost with max_user_connections 2;
flush user_resources;
connect (muc1, localhost, mysqltest_1,,);
connection muc1;
select * from t1;
connect (muc2, localhost, mysqltest_1,,);
connection muc2;
select * from t1;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_USER_LIMIT_REACHED
connect (muc3, localhost, mysqltest_1,,);
# Closing of one of connections should help
disconnect muc1;
connect (muc3, localhost, mysqltest_1,,);
select * from t1;
# Changing of limit should also help (and immediately)
connection default;
grant usage on *.* to mysqltest_1@localhost with max_user_connections 3;
flush user_resources;
connect (muc4, localhost, mysqltest_1,,);
connection muc4;
select * from t1;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_USER_LIMIT_REACHED
connect (muc5, localhost, mysqltest_1,,);
connection default;
# Test with negative max_user_connections
grant usage on *.* to mysqltest_1@localhost with max_user_connections -1;
show grants for mysqltest_1@localhost;
flush user_resources;
show grants for mysqltest_1@localhost;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_USER_LIMIT_REACHED
connect (muc5, localhost, mysqltest_1,,);
# Clean up
disconnect muc2;
disconnect muc3;
disconnect muc4;
drop user mysqltest_1@localhost;
# Now let us test interaction between global and per-account
# max_user_connections limits
select @@session.max_user_connections, @@global.max_user_connections;
# Local max_user_connections variable can't be set directly
# since this limit is per-account
--error ER_VARIABLE_IS_READONLY
set session max_user_connections= 2;
# But it is ok to set global max_user_connections
set global max_user_connections= 2;
select @@session.max_user_connections, @@global.max_user_connections;
# Let us check that global limit works
grant usage on *.* to mysqltest_1@localhost;
flush user_resources;
connect (muca1, localhost, mysqltest_1,,);
connection muca1;
select @@session.max_user_connections, @@global.max_user_connections;
connect (muca2, localhost, mysqltest_1,,);
connection muca2;
select * from t1;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_TOO_MANY_USER_CONNECTIONS
connect (muca3, localhost, mysqltest_1,,);
# Now we are testing that per-account limit prevails over gloabl limit
connection default;
grant usage on *.* to mysqltest_1@localhost with max_user_connections 3;
flush user_resources;
connect (muca3, localhost, mysqltest_1,,);
connection muca3;
select @@session.max_user_connections, @@global.max_user_connections;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_USER_LIMIT_REACHED
connect (muca4, localhost, mysqltest_1,,);
# Cleanup
connection default;
disconnect muca1;
disconnect muca2;
disconnect muca3;
set global max_user_connections= 0;
--enable_ps_protocol
#
# Test setting negative values of max_user_connections
#
grant usage on *.* to mysqltest_1@localhost with max_user_connections 0;
set global max_user_connections=-1;
show variables like "max_user_user_connections";
select @@max_user_connections;
select @@global.max_user_connections;
# Check that we can't connect anymore except as root
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_TOO_MANY_USER_CONNECTIONS
connect (muca2, localhost, mysqltest_1,,);
connect (muca2, localhost, root,,);
disconnect muca2;
connection default;
set global max_user_connections=1;
# Check that we can connect one time, not two
connect (muca2, localhost, mysqltest_1,,);
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_TOO_MANY_USER_CONNECTIONS
connect (muca3, localhost, mysqltest_1,,);
disconnect muca2;
connection default;
drop user mysqltest_1@localhost;
# Final cleanup
drop table t1;
# Wait till all disconnects are completed
--source include/wait_until_count_sessions.inc
set global max_user_connections= @my_max_user_connections;
|
