Debian Codegraph @

mysql-test/suite/perfschema/r/idx_compare_host_cache.result

raw
1 TABLE: "host_cache"
2 INDEXES: PK (IP), KEY (HOST)
3
4 ===== SETUP =====
5 ## Connection Default
6 ## Disable events from the default connection
7
8 ## Create user1, user2, user3
9 CREATE USER user1@localhost;
10 CREATE USER user2@localhost;
11 CREATE USER user3@localhost;
12 GRANT ALL ON *.* to 'user1'@localhost;
13 GRANT ALL ON *.* to 'user2'@localhost;
14 GRANT ALL ON *.* to 'user3'@localhost;
15
16 ## Setup test connection 1
17 ## Setup test connection 2
18 ## Setup test connection 3
19
20 ## Default connection
21
22 ## Create data tables
23 CREATE TABLE test.t1(a INT, b INT, c INT, d INT DEFAULT 0, PRIMARY KEY(a), INDEX index_b(b), INDEX index_cb(c, b));
24 CREATE TABLE test.t2 LIKE test.t1;
25 CREATE TABLE test.t3 LIKE test.t1;
26
27 ## Generate event data from 3 connections
28 ## Connection 1
29 ## Connection 2
30 ## Connection 3
31 ## Connection 2
32 ## Create prepared statements on connection 2
33 PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
34 PREPARE st3 FROM 'INSERT INTO test.t1 SELECT * FROM test.t1 WHERE a<=?';
35 PREPARE st4 FROM '(SELECT a FROM test.t1) UNION (SELECT a+10 FROM test.t1) ORDER BY RAND()*0+a';
36 ## Create stored procedure connection 2
37 CREATE PROCEDURE SampleProc1()
38 BEGIN
39 SET @table = 'test.t1';
40 SET @s = CONCAT('SELECT * FROM ', @table);
41 PREPARE st2 FROM @s;
42 END|
43
44 Call SampleProc1() to prepare a statement
45 CALL SampleProc1();
46 ## Connection default
47
48 ## Use Connection 2 for default target thread id
49
50 ## Create index_test() procedure
51 call mtr.add_suppression("192.0.2.4");
52 flush status;
53 TRUNCATE TABLE performance_schema.host_cache;
54 flush user_resources;
55 flush privileges;
56 select @@global.debug;
57 @@global.debug
58
59 select @@global.max_connect_errors;
60 @@global.max_connect_errors
61 100
62 select @@global.max_user_connections;
63 @@global.max_user_connections
64 0
65 select @@global.max_connections;
66 @@global.max_connections
67 151
68 select `User`, `Host` from mysql.`user` where `host` like '%\\%%';
69 User Host
70 select `User`, `Host` from mysql.`user` where `user` like '192.%';
71 User Host
72 select `User`, `Host` from mysql.`user` where `user` like '2001:%';
73 User Host
74 select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%';
75 User Host
76 CREATE USER 'user4'@'santa.claus.ipv4.example.com';
77 GRANT SELECT ON test.* TO 'user4'@'santa.claus.ipv4.example.com';
78 SET global debug= "+d,vio_peer_addr_fake_ipv4,getnameinfo_fake_ipv4,getaddrinfo_fake_good_ipv4";
79 SELECT "Con4 is alive" AS "";
80
81 Con4 is alive
82 SELECT current_user() AS "";
83
84 [email protected]
85 "Dumping performance_schema.host_cache"
86 IP 192.0.2.4
87 HOST santa.claus.ipv4.example.com
88 HOST_VALIDATED YES
89 SUM_CONNECT_ERRORS 0
90 COUNT_HOST_BLOCKED_ERRORS 0
91 COUNT_NAMEINFO_TRANSIENT_ERRORS 0
92 COUNT_NAMEINFO_PERMANENT_ERRORS 0
93 COUNT_FORMAT_ERRORS 0
94 COUNT_ADDRINFO_TRANSIENT_ERRORS 0
95 COUNT_ADDRINFO_PERMANENT_ERRORS 0
96 COUNT_FCRDNS_ERRORS 0
97 COUNT_HOST_ACL_ERRORS 0
98 COUNT_NO_AUTH_PLUGIN_ERRORS 0
99 COUNT_AUTH_PLUGIN_ERRORS 0
100 COUNT_HANDSHAKE_ERRORS 0
101 COUNT_PROXY_USER_ERRORS 0
102 COUNT_PROXY_USER_ACL_ERRORS 0
103 COUNT_AUTHENTICATION_ERRORS 0
104 COUNT_SSL_ERRORS 0
105 COUNT_MAX_USER_CONNECTIONS_ERRORS 0
106 COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS 0
107 COUNT_DEFAULT_DATABASE_ERRORS 0
108 COUNT_INIT_CONNECT_ERRORS 0
109 COUNT_LOCAL_ERRORS 0
110 COUNT_UNKNOWN_ERRORS 0
111 FIRST_ERROR_SEEN null
112 LAST_ERROR_SEEN null
113 SET global debug= default;
114
115 ## Test IP
116 SET @pfs_columns = "IP, HOST";
117
118 ===== BEGIN TEST =====
119 ========
120 SELECT IP, HOST FROM performance_schema.host_cache WHERE IP is null
121
122 OK: Handler_read_key
123
124 OK: Result set: Empty Expected: Empty
125
126 OK: Extra rows: 0 Missing rows: 0
127 ========
128 SELECT IP, HOST FROM performance_schema.host_cache WHERE IP is not null
129
130 OK: Handler_read_key
131
132 OK: Result set: Non-empty Expected: Non-empty
133
134 OK: Extra rows: 0 Missing rows: 0
135 ========
136 SELECT IP, HOST FROM performance_schema.host_cache WHERE IP = '192.0.2.4'
137
138 OK: Handler_read_key
139
140 OK: Result set: Non-empty Expected: Non-empty
141
142 OK: Extra rows: 0 Missing rows: 0
143 ========
144 SELECT IP, HOST FROM performance_schema.host_cache WHERE IP = 'impossible'
145
146 OK: Handler_read_key
147
148 OK: Result set: Empty Expected: Empty
149
150 OK: Extra rows: 0 Missing rows: 0
151
152 ## Test HOST
153 SET @pfs_columns = "IP, HOST";
154
155 ===== BEGIN TEST =====
156 ========
157 SELECT IP, HOST FROM performance_schema.host_cache WHERE host = 'santa.claus.ipv4.example.com'
158
159 OK: Handler_read_key
160
161 OK: Result set: Non-empty Expected: Non-empty
162
163 OK: Extra rows: 0 Missing rows: 0
164 ========
165 SELECT IP, HOST FROM performance_schema.host_cache WHERE host IS NULL
166
167 OK: Handler_read_key
168
169 OK: Result set: Empty Expected: Empty
170
171 OK: Extra rows: 0 Missing rows: 0
172 ========
173 SELECT IP, HOST FROM performance_schema.host_cache WHERE host IS NOT NULL
174
175 OK: Handler_read_key
176
177 OK: Result set: Non-empty Expected: Non-empty
178
179 OK: Extra rows: 0 Missing rows: 0
180 ========
181 SELECT IP, HOST FROM performance_schema.host_cache WHERE host = 'nohost'
182
183 OK: Handler_read_key
184
185 OK: Result set: Empty Expected: Empty
186
187 OK: Extra rows: 0 Missing rows: 0
188
189 ===== CLEAN UP =====
190 ## Default connection
191 DEALLOCATE PREPARE st1;
192 DEALLOCATE PREPARE st2;
193 DEALLOCATE PREPARE st3;
194 DEALLOCATE PREPARE st4;
195 DROP PROCEDURE SampleProc1;
196 DROP PROCEDURE index_test;
197 DROP TABLE IF EXISTS test.indexes_off;
198 DROP TABLE IF EXISTS test.indexes_on;
199 DROP TABLE test.t1;
200 DROP TABLE test.t2;
201 DROP TABLE test.t3;
202 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
203 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user2@localhost;
204 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user3@localhost;
205 DROP USER user1@localhost;
206 DROP USER user2@localhost;
207 DROP USER user3@localhost;
208 UPDATE performance_schema.threads SET INSTRUMENTED = 'YES';
209 REVOKE SELECT ON test.* FROM 'user4'@'santa.claus.ipv4.example.com';
210 DROP USER 'user4'@'santa.claus.ipv4.example.com';