Debian Codegraph @

mysql-test/suite/x/t/admin_xkill.test

raw
1 ########### ../t/admin_xkill.test #########
2 ## #
3 ## This test aims to run list_clients and kill_client admin commands #
4 ## Test runs covers below scenarios: #
5 ## - listing client with single session and multiple sessions #
6 ## - killing own session and x_root user session with privileged user #
7 ## - killing own session and x_root user session with unprivileged user #
8 ## - killing session with value of different data types than UINT #
9 ## - killing x_root session with user has only process privilege #
10 ## - listing and killing session with password expired user #
11 ## - killing valid sql_session id with xprotocol connection #
12 ## - listing and killing default mysqlx_max_connections ie 100 #
13 ## - listing,killing clients with ssl xprotocol privileged conn #
14 ## - listing and killing clients with ssl xprotocol x_root connection #
15 ## - kill_client,list_clients stats #
16 ## - check sum of these 2 vars with all mysqlx sessions should be as#
17 # that of with mysql client #
18 ######################################################################
19
20 --source include/xplugin_preamble.inc
21 --source include/xplugin_create_user.inc
22 call mtr.add_suppression("Plugin mysqlx reported: 'Unhandled network error: Bad file descriptor'");
23 create user sslcon@localhost identified by 'sslcon';
24 grant all on *.* to sslcon@localhost;
25 --write_file $MYSQL_TMP_DIR/admin_xkill.xpl
26 -->import assert_status_variable.macro
27 -->import kill_and_restart.macro
28
29 create user abc@localhost identified by 'abc',
30 xplugin@localhost identified by 'xplugin',
31 process@localhost identified by 'process',
32 expire@localhost identified by 'expire';
33 grant all on *.* to xplugin@localhost,
34 expire@localhost;
35 grant process on *.* to process@localhost;
36
37 alter user expire@localhost password expire;
38 select sleep(1);
39
40 -->echo Test data
41 -->stmtadmin list_clients
42 -->recvresult
43
44 -->echo expect Mysqlx_stmt_list_clients=1 and Mysqlx_stmt_kill_client=0
45 quiet;
46 callmacro Assert_status_variable Mysqlx_stmt_kill_client 0;
47 callmacro Assert_status_variable Mysqlx_stmt_list_clients 1;
48 callmacro Wait_for_status_variable Mysqlx_sessions 1;
49 noquiet;
50
51 -->echo create and activate new session
52 -->newsession aux x_root
53
54 -->stmtadmin list_clients
55 -->recvresult client_id user host
56
57 -->echo expect Mysqlx_stmt_list_clients=1 and Mysqlx_stmt_kill_client=0 even with another new x_root session
58 quiet;
59 callmacro Assert_status_variable Mysqlx_stmt_kill_client 0;
60 callmacro Assert_status_variable Mysqlx_stmt_list_clients 1;
61 callmacro Wait_for_status_variable Mysqlx_sessions 2;
62 noquiet;
63
64 -->echo switch back to default session
65 -->setsession
66
67 -->stmtadmin list_clients
68 -->recvresult client_id user host
69
70 -->echo expect Mysqlx_stmt_list_clients=2 and Mysqlx_stmt_kill_client=0 with default x_root session where list_clients already executed
71 quiet;
72 callmacro Assert_status_variable Mysqlx_stmt_kill_client 0;
73 callmacro Assert_status_variable Mysqlx_stmt_list_clients 2;
74 callmacro Wait_for_status_variable Mysqlx_sessions 2;
75 noquiet;
76
77 callmacro kill_client 2;
78
79 callmacro Wait_for_status_variable Mysqlx_sessions 1;
80
81 -->stmtadmin list_clients
82 -->recvresult client_id user host
83
84 -->echo expect Mysqlx_stmt_list_clients=3 and Mysqlx_stmt_kill_client=1 with default x_root session where list_clients executed 3 times and kill_client once
85 quiet;
86 callmacro Assert_status_variable Mysqlx_stmt_kill_client 1;
87 callmacro Assert_status_variable Mysqlx_stmt_list_clients 3;
88 callmacro Wait_for_status_variable Mysqlx_sessions 1;
89 noquiet;
90
91 -->echo create and activate new session abc
92 -->echo expect only abc user session id
93 -->newsession abc abc abc
94
95 -->stmtadmin list_clients
96 -->recvresult client_id user host
97
98 -->echo killing x_root user session with unprivileged user 'abc' which should be unsuccessful
99
100 callmacro kill_client_expect_error 1 ER_KILL_DENIED_ERROR;
101
102 -->echo expect Mysqlx_stmt_list_clients=1 and Mysqlx_stmt_kill_client=1 with session abc where list_clients and kill_client executed once
103 quiet;
104 callmacro Assert_status_variable Mysqlx_stmt_kill_client 1;
105 callmacro Assert_status_variable Mysqlx_stmt_list_clients 1;
106 callmacro Wait_for_status_variable Mysqlx_sessions 2;
107 noquiet;
108 select user(),current_user();
109 show grants;
110
111 -->echo open another session of abc user and kill first session of abc user,which should be successful
112 -->newsession abc1 abc abc
113
114 -->stmtadmin list_clients
115 -->recvresult client_id user host
116
117 callmacro kill_client 3;
118
119 callmacro Wait_for_status_variable Mysqlx_sessions 2;
120
121 -->stmtadmin list_clients
122 -->recvresult client_id user host
123
124
125 -->echo expect Mysqlx_stmt_list_clients=2 and Mysqlx_stmt_kill_client=1 with new session abc where list_clients executed twice and kill_client executed once
126 quiet;
127 callmacro Assert_status_variable Mysqlx_stmt_kill_client 1;
128 callmacro Assert_status_variable Mysqlx_stmt_list_clients 2;
129 callmacro Wait_for_status_variable Mysqlx_sessions 2;
130 noquiet;
131 select user(),current_user();
132 show grants;
133
134 -->echo killing abc user session with its own session 'abc' which should be successful
135 -->echo expect right error instead of End of File
136 -->stmtadmin kill_client {"id": 4 }
137 -->recvuntildisc
138
139 callmacro Wait_for_status_variable Mysqlx_sessions 1;
140
141 -->echo switch back to default session
142 -->setsession
143
144 -->stmtadmin list_clients
145 -->recvresult client_id user host
146
147 -->echo expect Mysqlx_stmt_list_clients=4 and Mysqlx_stmt_kill_client=1 with default x_root session where list_clients executed 4 times and kill_client once
148 quiet;
149 callmacro Assert_status_variable Mysqlx_stmt_kill_client 1;
150 callmacro Assert_status_variable Mysqlx_stmt_list_clients 4;
151 callmacro Wait_for_status_variable Mysqlx_sessions 1;
152 noquiet;
153 select user(),current_user();
154 show grants;
155
156 -->echo open process only privileged user seession and kill x_root user session,expect error "You are not owner of thread"
157 -->newsession process process process
158
159 -->stmtadmin list_clients
160 -->recvresult client_id user host
161
162 callmacro kill_client_expect_error 1 ER_KILL_DENIED_ERROR;
163
164 callmacro Wait_for_status_variable Mysqlx_sessions 2;
165
166 -->echo checking list of clients after x_root user session killed by xplugin user
167 -->stmtadmin list_clients
168 -->recvresult client_id user host
169
170 -->echo expect Mysqlx_stmt_list_clients=2 and Mysqlx_stmt_kill_client=1 with process user where list_clients executed 2 times and kill_client once
171 show status like 'Mysqlx_stmt%client%';
172 select user(),current_user();
173 show grants;
174
175 -->echo open privileged user seession and kill x_root user session,expect success
176 -->newsession xplugin xplugin xplugin
177
178 -->stmtadmin list_clients
179 -->recvresult client_id user host
180
181 callmacro kill_client 1;
182
183 callmacro Wait_for_status_variable Mysqlx_sessions 2;
184
185 -->echo checking list of clients after x_root user session killed by xplugin user
186 -->stmtadmin list_clients
187 -->recvresult client_id user host
188
189 -->echo expect Mysqlx_stmt_list_clients=2 and Mysqlx_stmt_kill_client=1 with xplugin user where list_clients executed 2 times and kill_client once
190 show status like 'Mysqlx_stmt%client%';
191 select user(),current_user();
192 show grants;
193
194 -->echo Cleanup
195 drop schema if exists xtest;
196 EOF
197
198 ## killing client with invalid scalar data types
199 --write_file $MYSQL_TMP_DIR/admin_xkill_invaliddtype.xpl
200 -->import assert_status_variable.macro
201 -->import kill_and_restart.macro
202
203 -->stmtadmin list_clients
204 -->recvresult client_id user host
205
206 -->echo expect error while killing client with data type other than usinged int
207 -->echo try killing xprotocol session with valid sql_session id once xprotocol connetion opened with kill_client,expect error
208
209 callmacro kill_client_expect_error 11 ER_NO_SUCH_THREAD;
210
211 -->stmtadmin kill_client {"id": "1"}
212 -->expecterror ER_X_CMD_ARGUMENT_VALUE
213 -->recvresult
214
215 -->stmtadmin kill_client {"id": true}
216 -->expecterror ER_X_CMD_ARGUMENT_VALUE
217 -->recvresult
218
219 -->stmtadmin kill_client {"id": false}
220 -->expecterror ER_X_CMD_ARGUMENT_VALUE
221 -->recvresult
222
223 -->stmtadmin kill_client {"id": 1.0}
224 -->expecterror ER_X_CMD_ARGUMENT_VALUE
225 -->recvresult
226
227 -->stmtadmin kill_client {"id": null}
228 -->expecterror ER_X_CMD_ARGUMENT_VALUE
229 -->recvresult
230
231 -->stmtadmin kill_client {"id": -1}
232 -->expecterror ER_X_CMD_ARGUMENT_VALUE
233 -->recvresult
234
235 -->echo expect Mysqlx_stmt_list_clients=1 and Mysqlx_stmt_kill_client=11 with x_root user even though kill_client returns errors
236 quiet;
237 callmacro Assert_status_variable Mysqlx_stmt_kill_client 7;
238 callmacro Assert_status_variable Mysqlx_stmt_list_clients 1;
239 callmacro Wait_for_status_variable Mysqlx_sessions 1;
240 noquiet;
241
242 EOF
243
244 --write_file $MYSQL_TMP_DIR/admin_xkill_pwd_expire.xpl
245 -->import assert_status_variable.macro
246 -->import kill_and_restart.macro
247
248 -->echo expect error if list_clients and kill_client executed from password expired user session
249 -->expecterror ER_MUST_CHANGE_PASSWORD
250 show processlist;
251
252 -->stmtadmin list_clients
253 -->expecterror ER_MUST_CHANGE_PASSWORD
254 -->recvresult
255
256 callmacro kill_client_expect_error 8 ER_MUST_CHANGE_PASSWORD;
257
258 EOF
259
260 --write_file $MYSQL_TMP_DIR/admin_mixedcase.xpl
261 -->import assert_status_variable.macro
262 -->import kill_and_restart.macro
263
264 -->echo checking list_clients and kill_client with mixed case, expect success as these commands are case insensitive
265 -->stmtadmin LIST_clients
266 -->recvresult client_id user host
267
268 -->stmtadmin kill_CLIENT {"id": 998}
269 -->expecterror ER_NO_SUCH_THREAD
270 -->recvresult
271
272 -->echo expect Mysqlx_stmt_list_clients=1 and Mysqlx_stmt_kill_client=1 with x_root user when these commads executed with mixed case
273 quiet;
274 callmacro Assert_status_variable Mysqlx_stmt_kill_client 1;
275 callmacro Assert_status_variable Mysqlx_stmt_list_clients 1;
276 callmacro Wait_for_status_variable Mysqlx_sessions 1;
277 noquiet;
278
279 EOF
280
281 ###below script is blind, it can be modified more efficiently by reducing no of lines
282 --write_file $MYSQL_TMP_DIR/admin_list_kill_100conns.xpl
283 -->import assert_status_variable.macro
284 -->import kill_and_restart.macro
285 -->import assert_notices.macro
286
287 -->macro receive_kill_notice %SESSION_ID%
288 setsession %SESSION_ID%;
289 # error 3169 = ER_SESSION_WAS_KILLED;
290 callmacro Assert_notice_warning ERROR 3169;
291 recvuntildisc show-received;
292 -->endmacro
293
294
295 -->echo checking list_clients and kill_client with default 50 connections, expect success for both list and kill
296 set @@global.mysqlx_max_connections=60;
297 create user xconnection1@localhost identified by 'xconnection1',
298 xconnection2@localhost identified by 'xconnection2',
299 xconnection3@localhost identified by 'xconnection3',
300 xconnection4@localhost identified by 'xconnection4',
301 xconnection5@localhost identified by 'xconnection5',
302 xconnection6@localhost identified by 'xconnection6',
303 xconnection7@localhost identified by 'xconnection7',
304 xconnection8@localhost identified by 'xconnection8',
305 xconnection9@localhost identified by 'xconnection9',
306 xconnection10@localhost identified by 'xconnection10',
307 xconnection11@localhost identified by 'xconnection11',
308 xconnection12@localhost identified by 'xconnection12',
309 xconnection13@localhost identified by 'xconnection13',
310 xconnection14@localhost identified by 'xconnection14',
311 xconnection15@localhost identified by 'xconnection15',
312 xconnection16@localhost identified by 'xconnection16',
313 xconnection17@localhost identified by 'xconnection17',
314 xconnection18@localhost identified by 'xconnection18',
315 xconnection19@localhost identified by 'xconnection19',
316 xconnection20@localhost identified by 'xconnection20',
317 xconnection21@localhost identified by 'xconnection21',
318 xconnection22@localhost identified by 'xconnection22',
319 xconnection23@localhost identified by 'xconnection23',
320 xconnection24@localhost identified by 'xconnection24',
321 xconnection25@localhost identified by 'xconnection25',
322 xconnection26@localhost identified by 'xconnection26',
323 xconnection27@localhost identified by 'xconnection27',
324 xconnection28@localhost identified by 'xconnection28',
325 xconnection29@localhost identified by 'xconnection29',
326 xconnection30@localhost identified by 'xconnection30',
327 xconnection31@localhost identified by 'xconnection31',
328 xconnection32@localhost identified by 'xconnection32',
329 xconnection33@localhost identified by 'xconnection33',
330 xconnection34@localhost identified by 'xconnection34',
331 xconnection35@localhost identified by 'xconnection35',
332 xconnection36@localhost identified by 'xconnection36',
333 xconnection37@localhost identified by 'xconnection37',
334 xconnection38@localhost identified by 'xconnection38',
335 xconnection39@localhost identified by 'xconnection39',
336 xconnection40@localhost identified by 'xconnection40',
337 xconnection41@localhost identified by 'xconnection41',
338 xconnection42@localhost identified by 'xconnection42',
339 xconnection43@localhost identified by 'xconnection43',
340 xconnection44@localhost identified by 'xconnection44',
341 xconnection45@localhost identified by 'xconnection45',
342 xconnection46@localhost identified by 'xconnection46',
343 xconnection47@localhost identified by 'xconnection47',
344 xconnection48@localhost identified by 'xconnection48',
345 xconnection49@localhost identified by 'xconnection49',
346 xconnection50@localhost identified by 'xconnection50';
347
348 -->echo Test data
349 stmtadmin list_clients;
350 recvresult;
351
352 -->echo expect Mysqlx_stmt_list_clients=1 and Mysqlx_stmt_kill_client=0
353 show status like 'Mysqlx_stmt%client%';
354
355 -->echo create and activate new session xconnection1
356 -->newsession xconnection1 xconnection1 xconnection1
357 -->newsession xconnection2 xconnection2 xconnection2
358 -->newsession xconnection3 xconnection3 xconnection3
359 -->newsession xconnection4 xconnection4 xconnection4
360 -->newsession xconnection5 xconnection5 xconnection5
361 -->newsession xconnection6 xconnection6 xconnection6
362 -->newsession xconnection7 xconnection7 xconnection7
363 -->newsession xconnection8 xconnection8 xconnection8
364 -->newsession xconnection9 xconnection9 xconnection9
365 -->newsession xconnection10 xconnection10 xconnection10
366 -->newsession xconnection11 xconnection11 xconnection11
367 -->newsession xconnection12 xconnection12 xconnection12
368 -->newsession xconnection13 xconnection13 xconnection13
369 -->newsession xconnection14 xconnection14 xconnection14
370 -->newsession xconnection15 xconnection15 xconnection15
371 -->newsession xconnection16 xconnection16 xconnection16
372 -->newsession xconnection17 xconnection17 xconnection17
373 -->newsession xconnection18 xconnection18 xconnection18
374 -->newsession xconnection19 xconnection19 xconnection19
375 -->newsession xconnection20 xconnection20 xconnection20
376 -->newsession xconnection21 xconnection21 xconnection21
377 -->newsession xconnection22 xconnection22 xconnection22
378 -->newsession xconnection23 xconnection23 xconnection23
379 -->newsession xconnection24 xconnection24 xconnection24
380 -->newsession xconnection25 xconnection25 xconnection25
381 -->newsession xconnection26 xconnection26 xconnection26
382 -->newsession xconnection27 xconnection27 xconnection27
383 -->newsession xconnection28 xconnection28 xconnection28
384 -->newsession xconnection29 xconnection29 xconnection29
385 -->newsession xconnection30 xconnection30 xconnection30
386 -->newsession xconnection31 xconnection31 xconnection31
387 -->newsession xconnection32 xconnection32 xconnection32
388 -->newsession xconnection33 xconnection33 xconnection33
389 -->newsession xconnection34 xconnection34 xconnection34
390 -->newsession xconnection35 xconnection35 xconnection35
391 -->newsession xconnection36 xconnection36 xconnection36
392 -->newsession xconnection37 xconnection37 xconnection37
393 -->newsession xconnection38 xconnection38 xconnection38
394 -->newsession xconnection39 xconnection39 xconnection39
395 -->newsession xconnection40 xconnection40 xconnection40
396 -->newsession xconnection41 xconnection41 xconnection41
397 -->newsession xconnection42 xconnection42 xconnection42
398 -->newsession xconnection43 xconnection43 xconnection43
399 -->newsession xconnection44 xconnection44 xconnection44
400 -->newsession xconnection45 xconnection45 xconnection45
401 -->newsession xconnection46 xconnection46 xconnection46
402 -->newsession xconnection47 xconnection47 xconnection47
403 -->newsession xconnection48 xconnection48 xconnection48
404 -->newsession xconnection49 xconnection49 xconnection49
405 -->newsession xconnection50 xconnection50 xconnection50
406
407 -->stmtadmin list_clients
408 -->recvresult client_id user host
409
410 ## switch back to default session
411 -->setsession
412
413 -->echo list all connected clients
414 -->stmtadmin list_clients
415 -->recvresult client_id user host
416
417 -->echo expect Mysqlx_stmt_list_clients=2 and Mysqlx_stmt_kill_client=0
418 quiet;
419 callmacro Assert_status_variable Mysqlx_stmt_kill_client 0;
420 callmacro Assert_status_variable Mysqlx_stmt_list_clients 2;
421 callmacro Wait_for_global_status_variable Mysqlx_sessions 51;
422 noquiet;
423
424
425 -->echo killing all newly connected clients except x_root,one by one
426 callmacro kill_client 11;
427 callmacro receive_kill_notice xconnection1;
428 callmacro kill_client 12;
429 callmacro receive_kill_notice xconnection2;
430 callmacro kill_client 13;
431 callmacro receive_kill_notice xconnection3;
432 callmacro kill_client 14;
433 callmacro receive_kill_notice xconnection4;
434 callmacro kill_client 15;
435 callmacro receive_kill_notice xconnection5;
436 callmacro kill_client 16;
437 callmacro receive_kill_notice xconnection6;
438 callmacro kill_client 17;
439 callmacro receive_kill_notice xconnection7;
440 callmacro kill_client 18;
441 callmacro receive_kill_notice xconnection8;
442 callmacro kill_client 19;
443 callmacro receive_kill_notice xconnection9;
444 callmacro kill_client 20;
445 callmacro receive_kill_notice xconnection10;
446 callmacro kill_client 21;
447 callmacro receive_kill_notice xconnection11;
448 callmacro kill_client 22;
449 callmacro receive_kill_notice xconnection12;
450 callmacro kill_client 23;
451 callmacro receive_kill_notice xconnection13;
452 callmacro kill_client 24;
453 callmacro receive_kill_notice xconnection14;
454 callmacro kill_client 25;
455 callmacro receive_kill_notice xconnection15;
456 callmacro kill_client 26;
457 callmacro receive_kill_notice xconnection16;
458 callmacro kill_client 27;
459 callmacro receive_kill_notice xconnection17;
460 callmacro kill_client 28;
461 callmacro receive_kill_notice xconnection18;
462 callmacro kill_client 29;
463 callmacro receive_kill_notice xconnection19;
464 callmacro kill_client 30;
465 callmacro receive_kill_notice xconnection20;
466 callmacro kill_client 31;
467 callmacro receive_kill_notice xconnection21;
468 callmacro kill_client 32;
469 callmacro receive_kill_notice xconnection22;
470 callmacro kill_client 33;
471 callmacro receive_kill_notice xconnection23;
472 callmacro kill_client 34;
473 callmacro receive_kill_notice xconnection24;
474 callmacro kill_client 35;
475 callmacro receive_kill_notice xconnection25;
476 callmacro kill_client 36;
477 callmacro receive_kill_notice xconnection26;
478 callmacro kill_client 37;
479 callmacro receive_kill_notice xconnection27;
480 callmacro kill_client 38;
481 callmacro receive_kill_notice xconnection28;
482 callmacro kill_client 39;
483 callmacro receive_kill_notice xconnection29;
484 callmacro kill_client 40;
485 callmacro receive_kill_notice xconnection30;
486 callmacro kill_client 41;
487 callmacro receive_kill_notice xconnection31;
488 callmacro kill_client 42;
489 callmacro receive_kill_notice xconnection32;
490 callmacro kill_client 43;
491 callmacro receive_kill_notice xconnection33;
492 callmacro kill_client 44;
493 callmacro receive_kill_notice xconnection34;
494 callmacro kill_client 45;
495 callmacro receive_kill_notice xconnection35;
496 callmacro kill_client 46;
497 callmacro receive_kill_notice xconnection36;
498 callmacro kill_client 47;
499 callmacro receive_kill_notice xconnection37;
500 callmacro kill_client 48;
501 callmacro receive_kill_notice xconnection38;
502 callmacro kill_client 49;
503 callmacro receive_kill_notice xconnection39;
504 callmacro kill_client 50;
505 callmacro receive_kill_notice xconnection40;
506 callmacro kill_client 51;
507 callmacro receive_kill_notice xconnection41;
508 callmacro kill_client 52;
509 callmacro receive_kill_notice xconnection42;
510 callmacro kill_client 53;
511 callmacro receive_kill_notice xconnection43;
512 callmacro kill_client 54;
513 callmacro receive_kill_notice xconnection44;
514 callmacro kill_client 55;
515 callmacro receive_kill_notice xconnection45;
516 callmacro kill_client 56;
517 callmacro receive_kill_notice xconnection46;
518 callmacro kill_client 57;
519 callmacro receive_kill_notice xconnection47;
520 callmacro kill_client 58;
521 callmacro receive_kill_notice xconnection48;
522 callmacro kill_client 59;
523 callmacro receive_kill_notice xconnection49;
524 callmacro kill_client 60;
525 callmacro receive_kill_notice xconnection50;
526
527
528 callmacro Wait_for_status_variable Mysqlx_sessions 1;
529
530 -->stmtadmin list_clients
531 -->recvresult client_id user host
532
533 -->echo expect Mysqlx_stmt_list_clients=3 and Mysqlx_stmt_kill_client=50
534 quiet;
535 callmacro Assert_status_variable Mysqlx_stmt_kill_client 50;
536 callmacro Assert_status_variable Mysqlx_stmt_list_clients 3;
537 callmacro Assert_status_variable Mysqlx_sessions 1;
538 callmacro Assert_status_variable Mysqlx_sessions_accepted 60;
539 callmacro Assert_status_variable Mysqlx_sessions_closed 59;
540 callmacro Assert_status_variable Mysqlx_sessions_fatal_error 0;
541 callmacro Assert_status_variable Mysqlx_sessions_killed 54;
542 callmacro Assert_status_variable Mysqlx_sessions_rejected 0;
543 noquiet;
544
545 drop user xconnection1@localhost,
546 xconnection2@localhost,
547 xconnection3@localhost,
548 xconnection4@localhost,
549 xconnection5@localhost,
550 xconnection6@localhost,
551 xconnection7@localhost,
552 xconnection8@localhost,
553 xconnection9@localhost,
554 xconnection10@localhost,
555 xconnection11@localhost,
556 xconnection12@localhost,
557 xconnection13@localhost,
558 xconnection14@localhost,
559 xconnection15@localhost,
560 xconnection16@localhost,
561 xconnection17@localhost,
562 xconnection18@localhost,
563 xconnection19@localhost,
564 xconnection20@localhost,
565 xconnection21@localhost,
566 xconnection22@localhost,
567 xconnection23@localhost,
568 xconnection24@localhost,
569 xconnection25@localhost,
570 xconnection26@localhost,
571 xconnection27@localhost,
572 xconnection28@localhost,
573 xconnection29@localhost,
574 xconnection30@localhost,
575 xconnection31@localhost,
576 xconnection32@localhost,
577 xconnection33@localhost,
578 xconnection34@localhost,
579 xconnection35@localhost,
580 xconnection36@localhost,
581 xconnection37@localhost,
582 xconnection38@localhost,
583 xconnection39@localhost,
584 xconnection40@localhost,
585 xconnection41@localhost,
586 xconnection42@localhost,
587 xconnection43@localhost,
588 xconnection44@localhost,
589 xconnection45@localhost,
590 xconnection46@localhost,
591 xconnection47@localhost,
592 xconnection48@localhost,
593 xconnection49@localhost,
594 xconnection50@localhost;
595
596 EOF
597
598 --write_file $MYSQL_TMP_DIR/admin_list_kill_sqlnamespace.xpl
599 -->echo execute list_clients,kill_client with sql namespace,expect error
600 -->echo change result file once error messages are fixed
601 Mysqlx.Sql.StmtExecute {
602 stmt: "list_clients"
603 namespace: "sql"
604 }
605
606 -->echo expect Mysqlx.Sql.StmtExecuteOk
607 -->expecterror ER_PARSE_ERROR
608 -->recvresult client_id user host
609
610 Mysqlx.Sql.StmtExecute {
611 stmt: "kill_client"
612 namespace: "sql"
613 args {
614 type: SCALAR
615 scalar {
616 type: V_UINT
617 v_unsigned_int: 11
618 }
619 }
620 }
621
622 -->echo expect Mysqlx.Sql.StmtExecuteOk
623 -->expecterror ER_X_CMD_NUM_ARGUMENTS
624 -->recvresult
625
626 Mysqlx.Sql.StmtExecute {
627 stmt: "kill_client"
628 namespace: ""
629 args {
630 type: SCALAR
631 scalar {
632 type: V_UINT
633 v_unsigned_int: 11
634 }
635 }
636 }
637
638 -->echo expect Mysqlx.Sql.StmtExecuteOk
639 -->expecterror ER_X_INVALID_NAMESPACE
640 -->recvresult
641
642 Mysqlx.Sql.StmtExecute {
643 stmt: "kill_client"
644 namespace: "invalid"
645 args {
646 type: SCALAR
647 scalar {
648 type: V_UINT
649 v_unsigned_int: 11
650 }
651 }
652 }
653
654 -->echo expect Mysqlx.Sql.StmtExecuteOk
655 -->expecterror ER_X_INVALID_NAMESPACE
656 -->recvresult
657
658 -->echo expect Mysqlx_stmt_list_clients=0 and Mysqlx_stmt_kill_client=0 as these are not executed on right namespace
659 quiet;
660 callmacro Assert_status_variable Mysqlx_stmt_kill_client 0;
661 callmacro Assert_status_variable Mysqlx_stmt_list_clients 0;
662 callmacro Assert_status_variable Mysqlx_sessions 1;
663 noquiet;
664
665 EOF
666
667 --write_file $MYSQL_TMP_DIR/admin_xkill_ssl.xpl
668 -->import assert_status_variable.macro
669 -->import kill_and_restart.macro
670
671 -->echo open xconnection with ssl and kill,expect success
672 create user con1@localhost identified by 'con1',
673 con2@localhost identified by 'con2';
674 set @@global.mysqlx_max_connections=3;
675
676 -->echo Test data
677 -->stmtadmin list_clients
678 -->recvresult client_id user host
679
680 -->echo expect Mysqlx_stmt_list_clients=1 and Mysqlx_stmt_kill_client=0
681 quiet;
682 callmacro Assert_status_variable Mysqlx_stmt_kill_client 0;
683 callmacro Assert_status_variable Mysqlx_stmt_list_clients 1;
684 callmacro Wait_for_status_variable Mysqlx_sessions 1;
685 noquiet;
686
687
688 -->echo create and activate new session con1
689 -->newsession con1 con1 con1
690
691 -->stmtadmin list_clients
692 -->recvresult client_id user host
693
694 -->echo expect Mysqlx_stmt_list_clients=1 and Mysqlx_stmt_kill_client=0
695 quiet;
696 callmacro Assert_status_variable Mysqlx_stmt_kill_client 0;
697 callmacro Assert_status_variable Mysqlx_stmt_list_clients 1;
698 callmacro Wait_for_status_variable Mysqlx_sessions 2;
699 noquiet;
700
701 -->echo open 3rd xprotocol connection where 2 sessions already opened
702 -->newsession con2 con2 con2
703
704 -->echo expect Mysqlx_stmt_list_clients=0 and Mysqlx_stmt_kill_client=0
705 quiet;
706 callmacro Assert_status_variable Mysqlx_stmt_kill_client 0;
707 callmacro Assert_status_variable Mysqlx_stmt_list_clients 0;
708 callmacro Wait_for_status_variable Mysqlx_sessions 3;
709 noquiet;
710
711 ## switch back to default session
712 -->setsession
713
714 -->stmtadmin list_clients
715 -->recvresult client_id user host
716
717 callmacro kill_client 63;
718 callmacro kill_client 64;
719
720 callmacro Wait_for_status_variable Mysqlx_sessions 1;
721
722 -->stmtadmin list_clients
723 -->recvresult client_id user host
724
725 -->echo expect Mysqlx_stmt_list_clients=3 and Mysqlx_stmt_kill_client=2
726 drop user con1@localhost,
727 con2@localhost;
728
729 quiet;
730 callmacro Assert_status_variable Mysqlx_stmt_kill_client 2;
731 callmacro Assert_status_variable Mysqlx_stmt_list_clients 3;
732 callmacro Wait_for_status_variable Mysqlx_sessions 1;
733 noquiet;
734
735 EOF
736
737 --write_file $MYSQL_TMP_DIR/admin_xkill_ssl_root.xpl
738 -->import assert_status_variable.macro
739 -->import kill_and_restart.macro
740
741 -->echo open xconnection with ssl x_root user kill,expect success
742 create user con1@localhost identified by 'con1',
743 con2@localhost identified by 'con2';
744 set @@global.mysqlx_max_connections=3;
745
746 -->echo Test data
747 -->stmtadmin list_clients
748 -->recvresult client_id user host
749
750 -->echo create and activate new session con1
751 -->newsession con1 con1 con1
752
753 -->stmtadmin list_clients
754 -->recvresult client_id user host
755
756 -->echo open 3rd xprotocol connection where 2 sessions already opened
757 -->newsession con2 con2 con2
758
759 ## switch back to default session
760 -->setsession
761
762 -->stmtadmin list_clients
763 -->recvresult client_id user host
764
765 callmacro kill_client 66;
766 callmacro kill_client 67;
767
768 callmacro Wait_for_status_variable Mysqlx_sessions 1;
769
770 -->stmtadmin list_clients
771 -->recvresult client_id user host
772
773 drop user con1@localhost,
774 con2@localhost;
775
776 EOF
777
778 --write_file $MYSQL_TMP_DIR/admin_xkill_compatibility_56_OFF.xpl
779 -->import assert_status_variable.macro
780 -->import kill_and_restart.macro
781
782 create user abc@localhost identified by 'abc',
783 xplugin@localhost identified by 'xplugin',
784 process@localhost identified by 'process',
785 expire@localhost identified by 'expire';
786 grant all on *.* to xplugin@localhost,
787 expire@localhost;
788 grant process on *.* to process@localhost;
789
790 alter user expire@localhost password expire;
791
792 -->echo Test data
793 -->stmtadmin list_clients
794 -->recvresult client_id user host
795
796 -->echo expect Mysqlx_stmt_list_clients=1 and Mysqlx_stmt_kill_client=0
797 quiet;
798 callmacro Assert_status_variable Mysqlx_stmt_kill_client 0;
799 callmacro Assert_status_variable Mysqlx_stmt_list_clients 1;
800 callmacro Assert_status_variable Mysqlx_sessions 1;
801 noquiet;
802
803
804 -->echo create and activate new session
805 -->newsession aux x_root
806
807 -->stmtadmin list_clients
808 -->recvresult client_id user host
809
810 -->echo expect Mysqlx_stmt_list_clients=1 and Mysqlx_stmt_kill_client=0 even with another new x_root session
811 quiet;
812 callmacro Assert_status_variable Mysqlx_stmt_kill_client 0;
813 callmacro Assert_status_variable Mysqlx_stmt_list_clients 1;
814 callmacro Assert_status_variable Mysqlx_sessions 2;
815 noquiet;
816
817
818 -->echo switch back to default session
819 -->setsession
820
821 -->stmtadmin list_clients
822 -->recvresult client_id user host
823
824 -->echo expect Mysqlx_stmt_list_clients=2 and Mysqlx_stmt_kill_client=0 with default x_root session where list_clients already executed
825 quiet;
826 callmacro Assert_status_variable Mysqlx_stmt_kill_client 0;
827 callmacro Assert_status_variable Mysqlx_stmt_list_clients 2;
828 callmacro Assert_status_variable Mysqlx_sessions 2;
829 noquiet;
830
831 callmacro kill_client 2;
832
833 callmacro Wait_for_status_variable Mysqlx_sessions 1;
834
835 -->stmtadmin list_clients
836 -->recvresult client_id user host
837
838 -->echo expect Mysqlx_stmt_list_clients=3 and Mysqlx_stmt_kill_client=1 with default x_root session where list_clients executed 3 times and kill_client once
839 quiet;
840 callmacro Assert_status_variable Mysqlx_stmt_kill_client 1;
841 callmacro Assert_status_variable Mysqlx_stmt_list_clients 3;
842 callmacro Assert_status_variable Mysqlx_sessions 1;
843 noquiet;
844
845 -->echo create and activate new session abc
846 -->echo expect only abc user session id
847 -->newsession abc abc abc
848
849 -->stmtadmin list_clients
850 -->recvresult client_id user host
851
852 -->echo killing x_root user session with unprivileged user 'abc' which should be unsuccessful
853 callmacro kill_client_expect_error 1 ER_KILL_DENIED_ERROR;
854
855 -->echo expect ERROR: SELECT command denied to user 'abc'@'localhost' for table 'session_status' (code 1142) for show status command
856 quiet;
857 callmacro Assert_status_variable Mysqlx_stmt_kill_client 1;
858 callmacro Assert_status_variable Mysqlx_stmt_list_clients 1;
859 callmacro Assert_status_variable Mysqlx_sessions 2;
860 noquiet;
861
862 -->echo open another session of abc user and kill first session of abc user,which should be successful
863 -->newsession abc1 abc abc
864
865 -->stmtadmin list_clients
866 -->recvresult client_id user host
867
868 callmacro kill_client 3;
869
870 callmacro Wait_for_status_variable Mysqlx_sessions 2;
871
872 -->stmtadmin list_clients
873 -->recvresult client_id user host
874
875 -->echo expect ERROR: SELECT command denied to user 'abc'@'localhost' for table 'session_status' (code 1142) for show status command
876 quiet;
877 callmacro Assert_status_variable Mysqlx_stmt_kill_client 1;
878 callmacro Assert_status_variable Mysqlx_stmt_list_clients 2;
879 callmacro Assert_status_variable Mysqlx_sessions 2;
880 noquiet;
881
882 -->echo killing abc user session with its own session 'abc' which should be successful
883 -->echo expect right error instead of End of File
884 -->stmtadmin kill_client {"id": 4}
885 -->echo switch back to default session
886 -->recvuntildisc
887
888 callmacro Wait_for_status_variable Mysqlx_sessions 1;
889
890 -->stmtadmin list_clients
891 -->recvresult client_id user host
892
893 -->echo expect Mysqlx_stmt_list_clients=4 and Mysqlx_stmt_kill_client=1 with default x_root session where list_clients executed 4 times and kill_client once
894 quiet;
895 callmacro Assert_status_variable Mysqlx_stmt_kill_client 1;
896 callmacro Assert_status_variable Mysqlx_stmt_list_clients 4;
897 callmacro Assert_status_variable Mysqlx_sessions 1;
898 noquiet;
899
900 -->echo open process only privileged user seession and kill x_root user session,expect error "You are not owner of thread"
901 -->newsession process process process
902 -->stmtadmin list_clients
903 -->recvresult client_id user host
904
905 callmacro kill_client_expect_error 1 ER_KILL_DENIED_ERROR;
906
907 callmacro Wait_for_status_variable Mysqlx_sessions 2;
908
909 -->echo checking list of clients after x_root user session killed by xplugin user
910 -->stmtadmin list_clients
911 -->recvresult client_id user host
912
913 -->echo expect ERROR: SELECT command denied to user 'process'@'localhost' for table 'session_status' (code 1142) for show command
914 quiet;
915 callmacro Assert_status_variable Mysqlx_stmt_kill_client 1;
916 callmacro Assert_status_variable Mysqlx_stmt_list_clients 2;
917 callmacro Assert_status_variable Mysqlx_sessions 2;
918 noquiet;
919
920 -->echo open privileged user seession and kill x_root user session,expect success
921 -->newsession xplugin xplugin xplugin
922
923 -->stmtadmin list_clients
924 -->recvresult client_id user host
925
926 callmacro kill_client 1;
927
928 callmacro Wait_for_status_variable Mysqlx_sessions 2;
929
930 -->echo checking list of clients after x_root user session killed by xplugin user
931
932 -->stmtadmin list_clients
933 -->recvresult client_id user host
934
935 -->echo expect Mysqlx_stmt_list_clients=2 and Mysqlx_stmt_kill_client=1 with xplugin user where list_clients executed 2 times and kill_client once
936 quiet;
937 callmacro Assert_status_variable Mysqlx_stmt_kill_client 1;
938 callmacro Assert_status_variable Mysqlx_stmt_list_clients 2;
939 callmacro Assert_status_variable Mysqlx_sessions 2;
940 noquiet;
941
942 -->echo Cleanup
943 drop schema if exists xtest;
944
945 EOF
946
947 --replace_regex /([a-z0-9]+ localhost )[0-9]+/\1$SESSION/ /You are not owner of thread [0-9]+/You are not owner of thread/
948 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/admin_xkill.xpl 2>&1
949 --replace_regex /([a-z0-9]+ localhost )[0-9]+/\1$SESSION/
950 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/admin_xkill_invaliddtype.xpl 2>&1
951
952 ##try executing list_clients and kill_client with password expired user, expect error
953 --replace_regex /([a-z0-9]+ localhost )[0-9]+/\1$SESSION/ /Query/Execute/
954 --exec $MYSQLXTEST --connect-expired-password -uexpire --password='expire' --file=$MYSQL_TMP_DIR/admin_xkill_pwd_expire.xpl 2>&1
955
956 ##execute list_clients and kill_client with mixed case,expect command should be recognized
957 --replace_regex /([a-z0-9]+ localhost )[0-9]+/\1$SESSION/
958 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/admin_mixedcase.xpl 2>&1
959
960 ##open 100 xprotocol connections and list and kill them ony by one,expect success
961 --replace_regex /([a-z0-9]+ localhost )[0-9]+/\1$SESSION/
962 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/admin_list_kill_100conns.xpl 2>&1
963
964 ##execute list_clients,kill_client with invalid name space other than xplugin,exect unsuccess
965 --replace_regex /([a-z0-9]+ localhost )[0-9]+/\1$SESSION/
966 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/admin_list_kill_sqlnamespace.xpl 2>&1
967
968 ##verify list_clients and kill_client works with ssl privileged user
969 --replace_regex /([a-z0-9]+ localhost )[0-9]+/\1$SESSION/
970 --exec $MYSQLXTEST -usslcon --password='sslcon' --ssl-key=$MYSQL_TEST_DIR/std_data/server-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/server-cert.pem --file=$MYSQL_TMP_DIR/admin_xkill_ssl.xpl 2>&1
971
972 ##verify list_clients and kill_client works with x_root user having ssl
973 --replace_regex /([a-z0-9]+ localhost )[0-9]+/\1$SESSION/
974 --exec $MYSQLXTEST -ux_root --password='' --ssl-key=$MYSQL_TEST_DIR/std_data/server-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/server-cert.pem --file=$MYSQL_TMP_DIR/admin_xkill_ssl_root.xpl 2>&1
975 ##expect sum of all stats of kill_client,list_clients with native mysql clients as these are global to mysql client and local to mysqlx session
976 show status like 'Mysqlx_stmt%client%';
977 #show status like 'Mysqlx_sessions%';
978
979 ##verify Mysqlx_stmt_kill_client and Mysqlx_stmt_list_clients
980 let $restart_parameters = restart: ;
981 --source include/restart_mysqld.inc
982 drop user abc@localhost,
983 xplugin@localhost,
984 process@localhost,
985 expire@localhost;
986 ###expects zero stats for kill_client,list_clients once server started first time
987 set @@global.mysqlx_max_connections=100;
988 show status like 'Mysqlx_stmt%client%';
989 select sleep(0.5);
990 --replace_regex /([a-z0-9]+ localhost )[0-9]+/\1$SESSION/ /You are not owner of thread [0-9]+/You are not owner of thread/
991 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/admin_xkill_compatibility_56_OFF.xpl 2>&1
992 ##expect sum of all stats of kill_client,list_clients with native mysql clients as these are global to mysql client and local to mysqlx session
993 ##seems to be it is not working that way
994 show status like 'Mysqlx_stmt%client%';
995
996 ## Cleanup
997 drop user abc@localhost,
998 xplugin@localhost,
999 process@localhost,
1000 expire@localhost,
1001 sslcon@localhost;
1002 --source ../include/xplugin_cleanup.inc