Skip to content

Commit e36bb5b

Browse files
marcalffmarcalff
committed
Fixed blocked hosts, added tests
1 parent d7e23a4 commit e36bb5b

11 files changed

Lines changed: 939 additions & 34 deletions

‎mysql-test/suite/perfschema/r/hostcache_ipv4_blocked.result‎

Lines changed: 351 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,351 @@
1+
flush status;
2+
flush hosts;
3+
flush user_resources;
4+
select @@global.debug;
5+
@@global.debug
6+
7+
select @@global.max_connect_errors;
8+
@@global.max_connect_errors
9+
10
10+
select @@global.max_connect_errors into @saved_max_connect_errors;
11+
show global status like "%peer%";
12+
Variable_name Value
13+
Peer_address_errors 0
14+
set global max_connect_errors = 2;
15+
grant select on test.* to 'root'@'santa.claus.ipv4.example.com';
16+
select "Con1 is alive";
17+
Con1 is alive
18+
Con1 is alive
19+
select current_user();
20+
current_user()
21+
root@localhost
22+
set global debug= "+d,vio_peer_addr_fake_ipv4,getnameinfo_fake_ipv4,getaddrinfo_fake_good_ipv4,native_password_bad_reply";
23+
ERROR 28000: Access denied for user 'quota'@'santa.claus.ipv4.example.com' (using password: NO)
24+
select
25+
IP, HOST, HOST_VALIDATED, SUM_CONNECT_ERRORS,
26+
COUNT_HOST_BLOCKED_ERRORS,
27+
COUNT_NAMEINFO_TRANSIENT_ERRORS,
28+
COUNT_NAMEINFO_PERMANENT_ERRORS,
29+
COUNT_FORMAT_ERRORS,
30+
COUNT_ADDRINFO_TRANSIENT_ERRORS,
31+
COUNT_ADDRINFO_PERMANENT_ERRORS,
32+
COUNT_FCRDNS_ERRORS,
33+
COUNT_HOST_ACL_ERRORS,
34+
COUNT_NO_AUTH_PLUGIN_ERRORS,
35+
COUNT_AUTH_PLUGIN_ERRORS,
36+
COUNT_HANDSHAKE_ERRORS,
37+
COUNT_PROXY_USER_ERRORS,
38+
COUNT_PROXY_USER_ACL_ERRORS,
39+
COUNT_AUTHENTICATION_ERRORS,
40+
COUNT_SSL_ERRORS,
41+
COUNT_MAX_USER_CONNECTION_ERRORS,
42+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS,
43+
COUNT_MAX_CONNECTION_ERRORS,
44+
COUNT_USER_ACL_ERRORS,
45+
COUNT_LOCAL_ERRORS,
46+
COUNT_UNKNOWN_ERRORS,
47+
if (FIRST_ERROR_SEEN is not null, "set", "null") as FIRST_ERROR_SEEN,
48+
if (LAST_ERROR_SEEN is not null, "set", "null") as LAST_ERROR_SEEN
49+
from performance_schema.host_cache;
50+
IP 192.0.2.4
51+
HOST santa.claus.ipv4.example.com
52+
HOST_VALIDATED YES
53+
SUM_CONNECT_ERRORS 1
54+
COUNT_HOST_BLOCKED_ERRORS 0
55+
COUNT_NAMEINFO_TRANSIENT_ERRORS 0
56+
COUNT_NAMEINFO_PERMANENT_ERRORS 0
57+
COUNT_FORMAT_ERRORS 0
58+
COUNT_ADDRINFO_TRANSIENT_ERRORS 0
59+
COUNT_ADDRINFO_PERMANENT_ERRORS 0
60+
COUNT_FCRDNS_ERRORS 0
61+
COUNT_HOST_ACL_ERRORS 0
62+
COUNT_NO_AUTH_PLUGIN_ERRORS 0
63+
COUNT_AUTH_PLUGIN_ERRORS 0
64+
COUNT_HANDSHAKE_ERRORS 1
65+
COUNT_PROXY_USER_ERRORS 0
66+
COUNT_PROXY_USER_ACL_ERRORS 0
67+
COUNT_AUTHENTICATION_ERRORS 1
68+
COUNT_SSL_ERRORS 0
69+
COUNT_MAX_USER_CONNECTION_ERRORS 0
70+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS 0
71+
COUNT_MAX_CONNECTION_ERRORS 0
72+
COUNT_USER_ACL_ERRORS 0
73+
COUNT_LOCAL_ERRORS 0
74+
COUNT_UNKNOWN_ERRORS 0
75+
FIRST_ERROR_SEEN set
76+
LAST_ERROR_SEEN set
77+
ERROR 28000: Access denied for user 'quota'@'santa.claus.ipv4.example.com' (using password: NO)
78+
select
79+
IP, HOST, HOST_VALIDATED, SUM_CONNECT_ERRORS,
80+
COUNT_HOST_BLOCKED_ERRORS,
81+
COUNT_NAMEINFO_TRANSIENT_ERRORS,
82+
COUNT_NAMEINFO_PERMANENT_ERRORS,
83+
COUNT_FORMAT_ERRORS,
84+
COUNT_ADDRINFO_TRANSIENT_ERRORS,
85+
COUNT_ADDRINFO_PERMANENT_ERRORS,
86+
COUNT_FCRDNS_ERRORS,
87+
COUNT_HOST_ACL_ERRORS,
88+
COUNT_NO_AUTH_PLUGIN_ERRORS,
89+
COUNT_AUTH_PLUGIN_ERRORS,
90+
COUNT_HANDSHAKE_ERRORS,
91+
COUNT_PROXY_USER_ERRORS,
92+
COUNT_PROXY_USER_ACL_ERRORS,
93+
COUNT_AUTHENTICATION_ERRORS,
94+
COUNT_SSL_ERRORS,
95+
COUNT_MAX_USER_CONNECTION_ERRORS,
96+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS,
97+
COUNT_MAX_CONNECTION_ERRORS,
98+
COUNT_USER_ACL_ERRORS,
99+
COUNT_LOCAL_ERRORS,
100+
COUNT_UNKNOWN_ERRORS,
101+
if (FIRST_ERROR_SEEN is not null, "set", "null") as FIRST_ERROR_SEEN,
102+
if (LAST_ERROR_SEEN is not null, "set", "null") as LAST_ERROR_SEEN
103+
from performance_schema.host_cache;
104+
IP 192.0.2.4
105+
HOST santa.claus.ipv4.example.com
106+
HOST_VALIDATED YES
107+
SUM_CONNECT_ERRORS 2
108+
COUNT_HOST_BLOCKED_ERRORS 0
109+
COUNT_NAMEINFO_TRANSIENT_ERRORS 0
110+
COUNT_NAMEINFO_PERMANENT_ERRORS 0
111+
COUNT_FORMAT_ERRORS 0
112+
COUNT_ADDRINFO_TRANSIENT_ERRORS 0
113+
COUNT_ADDRINFO_PERMANENT_ERRORS 0
114+
COUNT_FCRDNS_ERRORS 0
115+
COUNT_HOST_ACL_ERRORS 0
116+
COUNT_NO_AUTH_PLUGIN_ERRORS 0
117+
COUNT_AUTH_PLUGIN_ERRORS 0
118+
COUNT_HANDSHAKE_ERRORS 2
119+
COUNT_PROXY_USER_ERRORS 0
120+
COUNT_PROXY_USER_ACL_ERRORS 0
121+
COUNT_AUTHENTICATION_ERRORS 2
122+
COUNT_SSL_ERRORS 0
123+
COUNT_MAX_USER_CONNECTION_ERRORS 0
124+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS 0
125+
COUNT_MAX_CONNECTION_ERRORS 0
126+
COUNT_USER_ACL_ERRORS 0
127+
COUNT_LOCAL_ERRORS 0
128+
COUNT_UNKNOWN_ERRORS 0
129+
FIRST_ERROR_SEEN set
130+
LAST_ERROR_SEEN set
131+
ERROR 28000: Access denied for user 'quota'@'santa.claus.ipv4.example.com' (using password: NO)
132+
select
133+
IP, HOST, HOST_VALIDATED, SUM_CONNECT_ERRORS,
134+
COUNT_HOST_BLOCKED_ERRORS,
135+
COUNT_NAMEINFO_TRANSIENT_ERRORS,
136+
COUNT_NAMEINFO_PERMANENT_ERRORS,
137+
COUNT_FORMAT_ERRORS,
138+
COUNT_ADDRINFO_TRANSIENT_ERRORS,
139+
COUNT_ADDRINFO_PERMANENT_ERRORS,
140+
COUNT_FCRDNS_ERRORS,
141+
COUNT_HOST_ACL_ERRORS,
142+
COUNT_NO_AUTH_PLUGIN_ERRORS,
143+
COUNT_AUTH_PLUGIN_ERRORS,
144+
COUNT_HANDSHAKE_ERRORS,
145+
COUNT_PROXY_USER_ERRORS,
146+
COUNT_PROXY_USER_ACL_ERRORS,
147+
COUNT_AUTHENTICATION_ERRORS,
148+
COUNT_SSL_ERRORS,
149+
COUNT_MAX_USER_CONNECTION_ERRORS,
150+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS,
151+
COUNT_MAX_CONNECTION_ERRORS,
152+
COUNT_USER_ACL_ERRORS,
153+
COUNT_LOCAL_ERRORS,
154+
COUNT_UNKNOWN_ERRORS,
155+
if (FIRST_ERROR_SEEN is not null, "set", "null") as FIRST_ERROR_SEEN,
156+
if (LAST_ERROR_SEEN is not null, "set", "null") as LAST_ERROR_SEEN
157+
from performance_schema.host_cache;
158+
IP 192.0.2.4
159+
HOST santa.claus.ipv4.example.com
160+
HOST_VALIDATED YES
161+
SUM_CONNECT_ERRORS 3
162+
COUNT_HOST_BLOCKED_ERRORS 0
163+
COUNT_NAMEINFO_TRANSIENT_ERRORS 0
164+
COUNT_NAMEINFO_PERMANENT_ERRORS 0
165+
COUNT_FORMAT_ERRORS 0
166+
COUNT_ADDRINFO_TRANSIENT_ERRORS 0
167+
COUNT_ADDRINFO_PERMANENT_ERRORS 0
168+
COUNT_FCRDNS_ERRORS 0
169+
COUNT_HOST_ACL_ERRORS 0
170+
COUNT_NO_AUTH_PLUGIN_ERRORS 0
171+
COUNT_AUTH_PLUGIN_ERRORS 0
172+
COUNT_HANDSHAKE_ERRORS 3
173+
COUNT_PROXY_USER_ERRORS 0
174+
COUNT_PROXY_USER_ACL_ERRORS 0
175+
COUNT_AUTHENTICATION_ERRORS 3
176+
COUNT_SSL_ERRORS 0
177+
COUNT_MAX_USER_CONNECTION_ERRORS 0
178+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS 0
179+
COUNT_MAX_CONNECTION_ERRORS 0
180+
COUNT_USER_ACL_ERRORS 0
181+
COUNT_LOCAL_ERRORS 0
182+
COUNT_UNKNOWN_ERRORS 0
183+
FIRST_ERROR_SEEN set
184+
LAST_ERROR_SEEN set
185+
ERROR HY000: Host '192.0.2.4' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
186+
select
187+
IP, HOST, HOST_VALIDATED, SUM_CONNECT_ERRORS,
188+
COUNT_HOST_BLOCKED_ERRORS,
189+
COUNT_NAMEINFO_TRANSIENT_ERRORS,
190+
COUNT_NAMEINFO_PERMANENT_ERRORS,
191+
COUNT_FORMAT_ERRORS,
192+
COUNT_ADDRINFO_TRANSIENT_ERRORS,
193+
COUNT_ADDRINFO_PERMANENT_ERRORS,
194+
COUNT_FCRDNS_ERRORS,
195+
COUNT_HOST_ACL_ERRORS,
196+
COUNT_NO_AUTH_PLUGIN_ERRORS,
197+
COUNT_AUTH_PLUGIN_ERRORS,
198+
COUNT_HANDSHAKE_ERRORS,
199+
COUNT_PROXY_USER_ERRORS,
200+
COUNT_PROXY_USER_ACL_ERRORS,
201+
COUNT_AUTHENTICATION_ERRORS,
202+
COUNT_SSL_ERRORS,
203+
COUNT_MAX_USER_CONNECTION_ERRORS,
204+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS,
205+
COUNT_MAX_CONNECTION_ERRORS,
206+
COUNT_USER_ACL_ERRORS,
207+
COUNT_LOCAL_ERRORS,
208+
COUNT_UNKNOWN_ERRORS,
209+
if (FIRST_ERROR_SEEN is not null, "set", "null") as FIRST_ERROR_SEEN,
210+
if (LAST_ERROR_SEEN is not null, "set", "null") as LAST_ERROR_SEEN
211+
from performance_schema.host_cache;
212+
IP 192.0.2.4
213+
HOST santa.claus.ipv4.example.com
214+
HOST_VALIDATED YES
215+
SUM_CONNECT_ERRORS 3
216+
COUNT_HOST_BLOCKED_ERRORS 1
217+
COUNT_NAMEINFO_TRANSIENT_ERRORS 0
218+
COUNT_NAMEINFO_PERMANENT_ERRORS 0
219+
COUNT_FORMAT_ERRORS 0
220+
COUNT_ADDRINFO_TRANSIENT_ERRORS 0
221+
COUNT_ADDRINFO_PERMANENT_ERRORS 0
222+
COUNT_FCRDNS_ERRORS 0
223+
COUNT_HOST_ACL_ERRORS 0
224+
COUNT_NO_AUTH_PLUGIN_ERRORS 0
225+
COUNT_AUTH_PLUGIN_ERRORS 0
226+
COUNT_HANDSHAKE_ERRORS 3
227+
COUNT_PROXY_USER_ERRORS 0
228+
COUNT_PROXY_USER_ACL_ERRORS 0
229+
COUNT_AUTHENTICATION_ERRORS 3
230+
COUNT_SSL_ERRORS 0
231+
COUNT_MAX_USER_CONNECTION_ERRORS 0
232+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS 0
233+
COUNT_MAX_CONNECTION_ERRORS 0
234+
COUNT_USER_ACL_ERRORS 0
235+
COUNT_LOCAL_ERRORS 0
236+
COUNT_UNKNOWN_ERRORS 0
237+
FIRST_ERROR_SEEN set
238+
LAST_ERROR_SEEN set
239+
ERROR HY000: Host '192.0.2.4' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
240+
select
241+
IP, HOST, HOST_VALIDATED, SUM_CONNECT_ERRORS,
242+
COUNT_HOST_BLOCKED_ERRORS,
243+
COUNT_NAMEINFO_TRANSIENT_ERRORS,
244+
COUNT_NAMEINFO_PERMANENT_ERRORS,
245+
COUNT_FORMAT_ERRORS,
246+
COUNT_ADDRINFO_TRANSIENT_ERRORS,
247+
COUNT_ADDRINFO_PERMANENT_ERRORS,
248+
COUNT_FCRDNS_ERRORS,
249+
COUNT_HOST_ACL_ERRORS,
250+
COUNT_NO_AUTH_PLUGIN_ERRORS,
251+
COUNT_AUTH_PLUGIN_ERRORS,
252+
COUNT_HANDSHAKE_ERRORS,
253+
COUNT_PROXY_USER_ERRORS,
254+
COUNT_PROXY_USER_ACL_ERRORS,
255+
COUNT_AUTHENTICATION_ERRORS,
256+
COUNT_SSL_ERRORS,
257+
COUNT_MAX_USER_CONNECTION_ERRORS,
258+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS,
259+
COUNT_MAX_CONNECTION_ERRORS,
260+
COUNT_USER_ACL_ERRORS,
261+
COUNT_LOCAL_ERRORS,
262+
COUNT_UNKNOWN_ERRORS,
263+
if (FIRST_ERROR_SEEN is not null, "set", "null") as FIRST_ERROR_SEEN,
264+
if (LAST_ERROR_SEEN is not null, "set", "null") as LAST_ERROR_SEEN
265+
from performance_schema.host_cache;
266+
IP 192.0.2.4
267+
HOST santa.claus.ipv4.example.com
268+
HOST_VALIDATED YES
269+
SUM_CONNECT_ERRORS 3
270+
COUNT_HOST_BLOCKED_ERRORS 2
271+
COUNT_NAMEINFO_TRANSIENT_ERRORS 0
272+
COUNT_NAMEINFO_PERMANENT_ERRORS 0
273+
COUNT_FORMAT_ERRORS 0
274+
COUNT_ADDRINFO_TRANSIENT_ERRORS 0
275+
COUNT_ADDRINFO_PERMANENT_ERRORS 0
276+
COUNT_FCRDNS_ERRORS 0
277+
COUNT_HOST_ACL_ERRORS 0
278+
COUNT_NO_AUTH_PLUGIN_ERRORS 0
279+
COUNT_AUTH_PLUGIN_ERRORS 0
280+
COUNT_HANDSHAKE_ERRORS 3
281+
COUNT_PROXY_USER_ERRORS 0
282+
COUNT_PROXY_USER_ACL_ERRORS 0
283+
COUNT_AUTHENTICATION_ERRORS 3
284+
COUNT_SSL_ERRORS 0
285+
COUNT_MAX_USER_CONNECTION_ERRORS 0
286+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS 0
287+
COUNT_MAX_CONNECTION_ERRORS 0
288+
COUNT_USER_ACL_ERRORS 0
289+
COUNT_LOCAL_ERRORS 0
290+
COUNT_UNKNOWN_ERRORS 0
291+
FIRST_ERROR_SEEN set
292+
LAST_ERROR_SEEN set
293+
flush hosts;
294+
ERROR 28000: Access denied for user 'quota'@'santa.claus.ipv4.example.com' (using password: NO)
295+
select
296+
IP, HOST, HOST_VALIDATED, SUM_CONNECT_ERRORS,
297+
COUNT_HOST_BLOCKED_ERRORS,
298+
COUNT_NAMEINFO_TRANSIENT_ERRORS,
299+
COUNT_NAMEINFO_PERMANENT_ERRORS,
300+
COUNT_FORMAT_ERRORS,
301+
COUNT_ADDRINFO_TRANSIENT_ERRORS,
302+
COUNT_ADDRINFO_PERMANENT_ERRORS,
303+
COUNT_FCRDNS_ERRORS,
304+
COUNT_HOST_ACL_ERRORS,
305+
COUNT_NO_AUTH_PLUGIN_ERRORS,
306+
COUNT_AUTH_PLUGIN_ERRORS,
307+
COUNT_HANDSHAKE_ERRORS,
308+
COUNT_PROXY_USER_ERRORS,
309+
COUNT_PROXY_USER_ACL_ERRORS,
310+
COUNT_AUTHENTICATION_ERRORS,
311+
COUNT_SSL_ERRORS,
312+
COUNT_MAX_USER_CONNECTION_ERRORS,
313+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS,
314+
COUNT_MAX_CONNECTION_ERRORS,
315+
COUNT_USER_ACL_ERRORS,
316+
COUNT_LOCAL_ERRORS,
317+
COUNT_UNKNOWN_ERRORS,
318+
if (FIRST_ERROR_SEEN is not null, "set", "null") as FIRST_ERROR_SEEN,
319+
if (LAST_ERROR_SEEN is not null, "set", "null") as LAST_ERROR_SEEN
320+
from performance_schema.host_cache;
321+
IP 192.0.2.4
322+
HOST santa.claus.ipv4.example.com
323+
HOST_VALIDATED YES
324+
SUM_CONNECT_ERRORS 1
325+
COUNT_HOST_BLOCKED_ERRORS 0
326+
COUNT_NAMEINFO_TRANSIENT_ERRORS 0
327+
COUNT_NAMEINFO_PERMANENT_ERRORS 0
328+
COUNT_FORMAT_ERRORS 0
329+
COUNT_ADDRINFO_TRANSIENT_ERRORS 0
330+
COUNT_ADDRINFO_PERMANENT_ERRORS 0
331+
COUNT_FCRDNS_ERRORS 0
332+
COUNT_HOST_ACL_ERRORS 0
333+
COUNT_NO_AUTH_PLUGIN_ERRORS 0
334+
COUNT_AUTH_PLUGIN_ERRORS 0
335+
COUNT_HANDSHAKE_ERRORS 1
336+
COUNT_PROXY_USER_ERRORS 0
337+
COUNT_PROXY_USER_ACL_ERRORS 0
338+
COUNT_AUTHENTICATION_ERRORS 1
339+
COUNT_SSL_ERRORS 0
340+
COUNT_MAX_USER_CONNECTION_ERRORS 0
341+
COUNT_MAX_USER_CONNECTION_PER_HOUR_ERRORS 0
342+
COUNT_MAX_CONNECTION_ERRORS 0
343+
COUNT_USER_ACL_ERRORS 0
344+
COUNT_LOCAL_ERRORS 0
345+
COUNT_UNKNOWN_ERRORS 0
346+
FIRST_ERROR_SEEN set
347+
LAST_ERROR_SEEN set
348+
revoke select on test.* from 'root'@'santa.claus.ipv4.example.com';
349+
drop user 'root'@'santa.claus.ipv4.example.com';
350+
set global max_connect_errors = @saved_max_connect_errors;
351+
set global debug= default;

0 commit comments

Comments
 (0)