Debian Codegraph @

mysql-test/suite/perfschema/t/setup_actors.test

raw
1 # Check the impact of different entries in performance_schema.setup_actors
2 # on when and how activity of users is recordeed in performance_schema.threads.
3 # The checks for indirect activity caused by users, system threads etc.
4 # are within setup_actors1.test.
5
6 --source include/not_windows.inc
7
8 # The initial number of rows is 1. The initial row always looks like this:
9 # mysql> select * from performance_schema.setup_actors;
10 # +------+------+------+---------+---------+
11 # | HOST | USER | ROLE | ENABLED | HISTORY |
12 # +------+------+------+---------+---------+
13 # | % | % | % | YES | YES |
14 # +------+------+------+---------+---------+
15 select * from performance_schema.setup_actors;
16
17 truncate table performance_schema.setup_actors;
18
19 insert into performance_schema.setup_actors
20 values ('hosta', 'user1', '%', 'YES', 'YES');
21
22 insert into performance_schema.setup_actors
23 values ('%', 'user2', '%', 'YES', 'YES');
24
25 insert into performance_schema.setup_actors
26 values ('localhost', 'user3', '%', 'YES', 'YES');
27
28 insert into performance_schema.setup_actors
29 values ('hostb', '%', '%', 'YES', 'YES');
30
31 select * from performance_schema.setup_actors
32 order by USER, HOST, `ROLE`;
33
34 create user user1@localhost, user2@localhost,
35 user3@localhost, user4@localhost,
36 user5@localhost;
37 grant ALL on *.* to user1@localhost;
38 grant ALL on *.* to user2@localhost;
39 grant ALL on *.* to user3@localhost;
40 grant ALL on *.* to user4@localhost;
41 grant select on test.* to user5@localhost;
42
43 flush privileges;
44
45 --echo # Switch to (con1, localhost, user1, , )
46 connect (con1, localhost, user1, , );
47
48 # INSTRUMENTED must be NO because there is no match in performance_schema.setup_actors
49 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
50 from performance_schema.threads
51 where PROCESSLIST_ID = connection_id();
52 let $con1_thread_id= `select THREAD_ID from performance_schema.threads
53 where PROCESSLIST_ID = connection_id()`;
54
55 --echo # Switch to connection default
56 --connection default
57 insert into performance_schema.setup_actors
58 values ('%', 'user1', '%', 'YES', 'YES');
59
60 --echo # Switch to connection con1
61 --connection con1
62 # INSTRUMENTED must be NO because there was no match in performance_schema.setup_actors
63 # when our current session made its connect. Later changes in setup_actors have no
64 # impact.
65 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
66 from performance_schema.threads
67 where PROCESSLIST_ID = connection_id();
68
69 --echo # Disconnect con1
70 --disconnect con1
71
72 --echo # Switch to (con2, localhost, user2, , )
73 connect (con2, localhost, user2, , );
74
75 # INSTRUMENTED must be YES because there is a match via
76 # (HOST,USER,ROLE) = ('%', 'user2', '%') in performance_schema.setup_actors.
77 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
78 from performance_schema.threads
79 where PROCESSLIST_ID=connection_id();
80 let $con2_thread_id= `select THREAD_ID from performance_schema.threads
81 where PROCESSLIST_ID = connection_id()`;
82
83 --echo # Disconnect con2
84 --disconnect con2
85
86 --echo # Switch to connection default
87 --connection default
88 # If a thread dies, we don't expect its THREAD_ID value will be re-used.
89 if ($con2_thread_id <= $con1_thread_id)
90 {
91 --echo ERROR: THREAD_ID of con2 is not bigger than THREAD_ID of con1
92 eval SELECT $con2_thread_id as THREAD_ID_con2, $con1_thread_id THREAD_ID_con1;
93 }
94
95 --disable_warnings
96 drop table if exists test.t1;
97 --enable_warnings
98 create table test.t1 (col1 bigint);
99 lock table test.t1 write;
100
101 --echo # Switch to (con3, localhost, user3, , )
102 connect (con3, localhost, user3, , );
103
104 # INSTRUMENTED must be YES because there is a match via
105 # (HOST,USER,ROLE) = ('localhost', 'user3', '%') in performance_schema.setup_actors.
106 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
107 from performance_schema.threads
108 where PROCESSLIST_ID = connection_id();
109
110 # PROCESSLIST_ columns are:
111 # (if name like '%OneConnection') all the same as what you'd get if you
112 # run a select on INFORMATION_SCHEMA.PROCESSLIST for the corresponding thread.
113 # Check at least once that this is fulfilled.
114 # Note(mleich):
115 # A join between INFORMATION_SCHEMA.PROCESSLIST and performance_schema.threads
116 # Example:
117 # select count(*) = 1
118 # from performance_schema.threads T inner join information_schema.PROCESSLIST P
119 # on T.PROCESSLIST_ID = P.ID and T.PROCESSLIST_USER = P.USER and
120 # T.PROCESSLIST_HOST = P.HOST and T.PROCESSLIST_DB = P.DB and
121 # T.PROCESSLIST_COMMAND = P.COMMAND and T.PROCESSLIST_INFO = P.INFO
122 # where T.PROCESSLIST_ID = connection_id() and T.NAME = 'thread/sql/one_connection'
123 # executed by the current connection looks like some of the most elegant solutions
124 # for revealing this. But such a join suffers from sporadic differences like
125 # column | observation
126 # -------|-------------
127 # state | "Sending data" vs. "executing"
128 # time | 0 vs. 1 (high load on the testing box)
129 # info | <full statement> vs. NULL (use of "--ps-protocol")
130 # IMHO the differences are harmless.
131 # Therefore we use here a different solution.
132 #
133 --echo # Send a statement to the server, but do not wait till the result
134 --echo # comes back. We will pull this later.
135 send
136 insert into test.t1 set col1 = 1;
137 --echo # Switch to (con4, localhost, user4, , )
138 connect (con4, localhost, user4, , );
139 --echo # Poll till INFO is no more NULL and State = 'Waiting for table metadata lock'.
140 let $wait_condition= select count(*) from information_schema.processlist
141 where user = 'user3' and info is not null
142 and state = 'Waiting for table metadata lock';
143 --source include/wait_condition.inc
144 # Expect to get 1 now
145 select count(*) = 1
146 from performance_schema.threads T inner join information_schema.PROCESSLIST P
147 on T.PROCESSLIST_ID = P.ID and T.PROCESSLIST_USER = P.USER and
148 T.PROCESSLIST_HOST = P.HOST and T.PROCESSLIST_DB = P.DB and
149 T.PROCESSLIST_COMMAND = P.COMMAND and T.PROCESSLIST_INFO = P.INFO
150 where T.PROCESSLIST_USER = 'user3' and T.NAME = 'thread/sql/one_connection';
151
152 # Resolve the situation + some cleanup
153 --echo # Switch to connection default
154 --connection default
155 unlock tables;
156 --echo # Switch to connection con3 and reap the result of the no more blocked insert
157 --connection con3
158 --reap
159 --echo # Switch to connection default
160 --connection default
161 drop table test.t1;
162 --echo # Disconnect con3
163 --disconnect con3
164
165 --echo # Switch to connection con4
166 --connection con4
167 # INSTRUMENTED must be NO because there is no match in performance_schema.setup_actors
168 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
169 from performance_schema.threads
170 where PROCESSLIST_ID = connection_id();
171
172 --echo # Disconnect con4
173 --disconnect con4
174
175 --echo # Switch to connection default
176 --connection default
177
178 insert into performance_schema.setup_actors
179 values ('localhost', '%', '%', 'YES', 'YES');
180
181 select * from performance_schema.setup_actors
182 order by USER, HOST, `ROLE`;
183
184 --echo # Switch to (con4b, localhost, user4, , )
185 connect (con4b, localhost, user4, , );
186
187 # INSTRUMENTED must be YES because there is a match via
188 # (HOST,USER,ROLE) = ('localhost', '%', '%') in performance_schema.setup_actors.
189 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
190 from performance_schema.threads
191 where PROCESSLIST_ID = connection_id();
192
193 --echo # Disconnect con4b
194 --disconnect con4b
195
196 --echo # Switch to connection default
197 --connection default
198 insert into performance_schema.setup_actors
199 values ('%', 'user5', '%', 'YES', 'YES');
200
201 create sql security definer view test.v1 as select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
202 from performance_schema.threads
203 where PROCESSLIST_ID = connection_id();
204
205 --echo # Switch to (con5, localhost, user5, , )
206 connect (con5, localhost, user5, , );
207
208 --error ER_TABLEACCESS_DENIED_ERROR
209 select * from performance_schema.threads;
210 # 1. INSTRUMENTED must be YES because there are two matches
211 # (HOST,USER,ROLE) = ('localhost', '%', '%')
212 # (HOST,USER,ROLE) = ('%', 'user5', '%')
213 # in performance_schema.setup_actors.
214 # But the instrument will only count once which means we must get only one row.
215 # 2. PROCESSLIST_USER refers to USER(), the user who connected,
216 # not the user we might be temporarily acting as (with definer's rights).
217 # Therefore PROCESSLIST_USER must be 'user5' though we run with right's of definer 'root'
218 select * from test.v1;
219
220 --echo # Disconnect con5
221 --disconnect con5
222 --source include/wait_until_disconnected.inc
223
224
225 --echo # Switch to connection default and cleanup
226 --connection default
227
228 drop view test.v1;
229 revoke all privileges, grant option from user1@localhost;
230 revoke all privileges, grant option from user2@localhost;
231 revoke all privileges, grant option from user3@localhost;
232 revoke all privileges, grant option from user4@localhost;
233 revoke all privileges, grant option from user5@localhost;
234 drop user user1@localhost;
235 drop user user2@localhost;
236 drop user user3@localhost;
237 drop user user4@localhost;
238 drop user user5@localhost;
239 flush privileges;
240
241 truncate table performance_schema.setup_actors;
242
243 insert into performance_schema.setup_actors
244 values ('%', '%', '%', 'YES', 'YES');
245
246 select * from performance_schema.setup_actors;
247