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