Debian Codegraph @

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

raw
1
2 ================================================================================
3 SETUP
4 ================================================================================
5 # Save the initial number of concurrent sessions
6
7 # Verify EXAMPLE plugin is not loaded
8 SELECT COUNT(*) = 0 AS "Expect 1" FROM information_schema.plugins WHERE plugin_name = "EXAMPLE";
9 Expect 1
10 1
11
12 # Create one session to force local and global system variables;
13 connect con0, localhost, root,,;
14 connection default;
15
16 ================================================================================
17 TEST 1- NO PLUGIN VARS
18 ================================================================================
19
20 SHOW GLOBAL STATUS LIKE "example_%";
21 Variable_name Value
22
23 SHOW SESSION STATUS LIKE "example_%";
24 Variable_name Value
25
26 SHOW GLOBAL VARIABLES LIKE "example_%";
27 Variable_name Value
28
29 SHOW SESSION VARIABLES LIKE "example_%";
30 Variable_name Value
31
32 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
33 variable_name variable_value
34
35 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
36 variable_name variable_value
37
38 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
39 variable_name variable_value
40
41 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
42 variable_name variable_value
43
44 ================================================================================
45 TEST 2 - PLUGIN LOAD, UNLOAD, RELOAD
46 ================================================================================
47 ================================================================================
48 2.1 - INSTALL PLUGIN
49 ================================================================================
50 INSTALL PLUGIN example SONAME 'ha_example.so';
51
52 ================================================================================
53 2.1a - FORCE SYNC OF LOCAL AND GLOBAL SYSTEM VARS
54 ================================================================================
55
56 SHOW GLOBAL STATUS LIKE "example_%";
57 Variable_name Value
58 example_func_example enum_var is 0, ulong_var is 8, double_var is 8.500000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
59 example_status_array_var1 100
60 example_status_array_var2 20.010000
61 example_status_var3 three hundred
62 example_status_var4 ON
63 example_status_var5 OFF
64 example_status_var6 8250
65
66 SHOW SESSION STATUS LIKE "example_%";
67 Variable_name Value
68 example_func_example enum_var is 0, ulong_var is 8, double_var is 8.500000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
69 example_status_array_var1 100
70 example_status_array_var2 20.010000
71 example_status_var3 three hundred
72 example_status_var4 ON
73 example_status_var5 OFF
74 example_status_var6 8250
75
76 SHOW GLOBAL VARIABLES LIKE "example_%";
77 Variable_name Value
78 example_create_count_thdvar 0
79 example_double_thdvar 8.500000
80 example_double_var 8.500000
81 example_enum_var e1
82 example_last_create_thdvar
83 example_signed_int_thdvar -10
84 example_signed_int_var -10
85 example_signed_long_thdvar -10
86 example_signed_long_var -10
87 example_signed_longlong_thdvar -10
88 example_signed_longlong_var -10
89 example_ulong_var 8
90
91 SHOW SESSION VARIABLES LIKE "example_%";
92 Variable_name Value
93 example_create_count_thdvar 0
94 example_double_thdvar 8.500000
95 example_double_var 8.500000
96 example_enum_var e1
97 example_last_create_thdvar
98 example_signed_int_thdvar -10
99 example_signed_int_var -10
100 example_signed_long_thdvar -10
101 example_signed_long_var -10
102 example_signed_longlong_thdvar -10
103 example_signed_longlong_var -10
104 example_ulong_var 8
105
106 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
107 variable_name variable_value
108 example_func_example enum_var is 0, ulong_var is 8, double_var is 8.500000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
109 example_status_array_var1 100
110 example_status_array_var2 20.010000
111 example_status_var3 three hundred
112 example_status_var4 ON
113 example_status_var5 OFF
114 example_status_var6 8250
115
116 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
117 variable_name variable_value
118 example_func_example enum_var is 0, ulong_var is 8, double_var is 8.500000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
119 example_status_array_var1 100
120 example_status_array_var2 20.010000
121 example_status_var3 three hundred
122 example_status_var4 ON
123 example_status_var5 OFF
124 example_status_var6 8250
125
126 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
127 variable_name variable_value
128 example_create_count_thdvar 0
129 example_double_thdvar 8.500000
130 example_double_var 8.500000
131 example_enum_var e1
132 example_last_create_thdvar
133 example_signed_int_thdvar -10
134 example_signed_int_var -10
135 example_signed_long_thdvar -10
136 example_signed_long_var -10
137 example_signed_longlong_thdvar -10
138 example_signed_longlong_var -10
139 example_ulong_var 8
140
141 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
142 variable_name variable_value
143 example_create_count_thdvar 0
144 example_double_thdvar 8.500000
145 example_double_var 8.500000
146 example_enum_var e1
147 example_last_create_thdvar
148 example_signed_int_thdvar -10
149 example_signed_int_var -10
150 example_signed_long_thdvar -10
151 example_signed_long_var -10
152 example_signed_longlong_thdvar -10
153 example_signed_longlong_var -10
154 example_ulong_var 8
155 disconnect con0;
156
157 ================================================================================
158 2.2 - SET PLUGIN VARS
159 ================================================================================
160 # GLOBAL
161 SET GLOBAL example_ulong_var = 100;
162 SET GLOBAL example_enum_var = e1;
163 SET GLOBAL example_double_var = 100.9990;
164 SET GLOBAL example_double_thdvar = 101.9991;
165 # SESSION
166 SET SESSION example_double_thdvar = 102.9992;
167
168 ================================================================================
169 2.3 - VERIFY UPDATED PLUGIN VARS
170 ================================================================================
171
172 SHOW GLOBAL STATUS LIKE "example_%";
173 Variable_name Value
174 example_func_example enum_var is 0, ulong_var is 100, double_var is 100.999000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
175 example_status_array_var1 100
176 example_status_array_var2 20.010000
177 example_status_var3 three hundred
178 example_status_var4 ON
179 example_status_var5 OFF
180 example_status_var6 8250
181
182 SHOW SESSION STATUS LIKE "example_%";
183 Variable_name Value
184 example_func_example enum_var is 0, ulong_var is 100, double_var is 100.999000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
185 example_status_array_var1 100
186 example_status_array_var2 20.010000
187 example_status_var3 three hundred
188 example_status_var4 ON
189 example_status_var5 OFF
190 example_status_var6 8250
191
192 SHOW GLOBAL VARIABLES LIKE "example_%";
193 Variable_name Value
194 example_create_count_thdvar 0
195 example_double_thdvar 101.999100
196 example_double_var 100.999000
197 example_enum_var e1
198 example_last_create_thdvar
199 example_signed_int_thdvar -10
200 example_signed_int_var -10
201 example_signed_long_thdvar -10
202 example_signed_long_var -10
203 example_signed_longlong_thdvar -10
204 example_signed_longlong_var -10
205 example_ulong_var 100
206
207 SHOW SESSION VARIABLES LIKE "example_%";
208 Variable_name Value
209 example_create_count_thdvar 0
210 example_double_thdvar 102.999200
211 example_double_var 100.999000
212 example_enum_var e1
213 example_last_create_thdvar
214 example_signed_int_thdvar -10
215 example_signed_int_var -10
216 example_signed_long_thdvar -10
217 example_signed_long_var -10
218 example_signed_longlong_thdvar -10
219 example_signed_longlong_var -10
220 example_ulong_var 100
221
222 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
223 variable_name variable_value
224 example_func_example enum_var is 0, ulong_var is 100, double_var is 100.999000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
225 example_status_array_var1 100
226 example_status_array_var2 20.010000
227 example_status_var3 three hundred
228 example_status_var4 ON
229 example_status_var5 OFF
230 example_status_var6 8250
231
232 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
233 variable_name variable_value
234 example_func_example enum_var is 0, ulong_var is 100, double_var is 100.999000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
235 example_status_array_var1 100
236 example_status_array_var2 20.010000
237 example_status_var3 three hundred
238 example_status_var4 ON
239 example_status_var5 OFF
240 example_status_var6 8250
241
242 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
243 variable_name variable_value
244 example_create_count_thdvar 0
245 example_double_thdvar 101.999100
246 example_double_var 100.999000
247 example_enum_var e1
248 example_last_create_thdvar
249 example_signed_int_thdvar -10
250 example_signed_int_var -10
251 example_signed_long_thdvar -10
252 example_signed_long_var -10
253 example_signed_longlong_thdvar -10
254 example_signed_longlong_var -10
255 example_ulong_var 100
256
257 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
258 variable_name variable_value
259 example_create_count_thdvar 0
260 example_double_thdvar 102.999200
261 example_double_var 100.999000
262 example_enum_var e1
263 example_last_create_thdvar
264 example_signed_int_thdvar -10
265 example_signed_int_var -10
266 example_signed_long_thdvar -10
267 example_signed_long_var -10
268 example_signed_longlong_thdvar -10
269 example_signed_longlong_var -10
270 example_ulong_var 100
271
272 ================================================================================
273 2.4 - UNINSTALL PLUGIN
274 ================================================================================
275 UNINSTALL PLUGIN example;
276
277 ================================================================================
278 2.5 - VERIFY NO PLUGIN VARS
279 ================================================================================
280
281 SHOW GLOBAL STATUS LIKE "example_%";
282 Variable_name Value
283
284 SHOW SESSION STATUS LIKE "example_%";
285 Variable_name Value
286
287 SHOW GLOBAL VARIABLES LIKE "example_%";
288 Variable_name Value
289
290 SHOW SESSION VARIABLES LIKE "example_%";
291 Variable_name Value
292
293 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
294 variable_name variable_value
295
296 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
297 variable_name variable_value
298
299 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
300 variable_name variable_value
301
302 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
303 variable_name variable_value
304
305 ================================================================================
306 2.6 - REINSTALL PLUGIN
307 ================================================================================
308 # Reinstall EXAMPLE plugin
309 INSTALL PLUGIN example SONAME 'ha_example.so';
310
311 ================================================================================
312 2.7 - SET PLUGIN VARS AGAIN
313 ================================================================================
314 # GLOBAL
315 SET GLOBAL example_ulong_var = 200;
316 SET GLOBAL example_enum_var = e2;
317 SET GLOBAL example_double_var = 200.8880;
318 SET GLOBAL example_double_thdvar = 201.8881;
319 # SESSION
320 SET SESSION example_double_thdvar = 202.8882;
321
322 ================================================================================
323 2.8 - VERIFY PLUGIN VARS
324 ================================================================================
325
326 SHOW GLOBAL STATUS LIKE "example_%";
327 Variable_name Value
328 example_func_example enum_var is 1, ulong_var is 200, double_var is 200.888000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
329 example_status_array_var1 100
330 example_status_array_var2 20.010000
331 example_status_var3 three hundred
332 example_status_var4 ON
333 example_status_var5 OFF
334 example_status_var6 8250
335
336 SHOW SESSION STATUS LIKE "example_%";
337 Variable_name Value
338 example_func_example enum_var is 1, ulong_var is 200, double_var is 200.888000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
339 example_status_array_var1 100
340 example_status_array_var2 20.010000
341 example_status_var3 three hundred
342 example_status_var4 ON
343 example_status_var5 OFF
344 example_status_var6 8250
345
346 SHOW GLOBAL VARIABLES LIKE "example_%";
347 Variable_name Value
348 example_create_count_thdvar 0
349 example_double_thdvar 201.888100
350 example_double_var 200.888000
351 example_enum_var e2
352 example_last_create_thdvar
353 example_signed_int_thdvar -10
354 example_signed_int_var -10
355 example_signed_long_thdvar -10
356 example_signed_long_var -10
357 example_signed_longlong_thdvar -10
358 example_signed_longlong_var -10
359 example_ulong_var 200
360
361 SHOW SESSION VARIABLES LIKE "example_%";
362 Variable_name Value
363 example_create_count_thdvar 0
364 example_double_thdvar 202.888200
365 example_double_var 200.888000
366 example_enum_var e2
367 example_last_create_thdvar
368 example_signed_int_thdvar -10
369 example_signed_int_var -10
370 example_signed_long_thdvar -10
371 example_signed_long_var -10
372 example_signed_longlong_thdvar -10
373 example_signed_longlong_var -10
374 example_ulong_var 200
375
376 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
377 variable_name variable_value
378 example_func_example enum_var is 1, ulong_var is 200, double_var is 200.888000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
379 example_status_array_var1 100
380 example_status_array_var2 20.010000
381 example_status_var3 three hundred
382 example_status_var4 ON
383 example_status_var5 OFF
384 example_status_var6 8250
385
386 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
387 variable_name variable_value
388 example_func_example enum_var is 1, ulong_var is 200, double_var is 200.888000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
389 example_status_array_var1 100
390 example_status_array_var2 20.010000
391 example_status_var3 three hundred
392 example_status_var4 ON
393 example_status_var5 OFF
394 example_status_var6 8250
395
396 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
397 variable_name variable_value
398 example_create_count_thdvar 0
399 example_double_thdvar 201.888100
400 example_double_var 200.888000
401 example_enum_var e2
402 example_last_create_thdvar
403 example_signed_int_thdvar -10
404 example_signed_int_var -10
405 example_signed_long_thdvar -10
406 example_signed_long_var -10
407 example_signed_longlong_thdvar -10
408 example_signed_longlong_var -10
409 example_ulong_var 200
410
411 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
412 variable_name variable_value
413 example_create_count_thdvar 0
414 example_double_thdvar 202.888200
415 example_double_var 200.888000
416 example_enum_var e2
417 example_last_create_thdvar
418 example_signed_int_thdvar -10
419 example_signed_int_var -10
420 example_signed_long_thdvar -10
421 example_signed_long_var -10
422 example_signed_longlong_thdvar -10
423 example_signed_longlong_var -10
424 example_ulong_var 200
425
426 ================================================================================
427 2.9 - UNINSTALL PLUGIN
428 ================================================================================
429 UNINSTALL PLUGIN example;
430
431 ================================================================================
432 2.10 - VERIFY NO PLUGIN VARS
433 ================================================================================
434
435 SHOW GLOBAL STATUS LIKE "example_%";
436 Variable_name Value
437
438 SHOW SESSION STATUS LIKE "example_%";
439 Variable_name Value
440
441 SHOW GLOBAL VARIABLES LIKE "example_%";
442 Variable_name Value
443
444 SHOW SESSION VARIABLES LIKE "example_%";
445 Variable_name Value
446
447 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
448 variable_name variable_value
449
450 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
451 variable_name variable_value
452
453 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
454 variable_name variable_value
455
456 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
457 variable_name variable_value
458
459 ================================================================================
460 TEST 3 - SESSION PLUGIN VARS ON MULTIPLE CONNECTIONS
461 ================================================================================
462
463 ================================================================================
464 3.1 - INSTALL PLUGIN
465 ================================================================================
466 INSTALL PLUGIN example SONAME 'ha_example.so';
467
468 ================================================================================
469 3.2 - SET GLOBAL AND DEFAULT CONNECTION VARS
470 ================================================================================
471 connection default;
472 SET GLOBAL example_ulong_var = 300;
473 SET GLOBAL example_enum_var = e1;
474 SET GLOBAL example_double_var = 301.0000;
475 SET GLOBAL example_double_thdvar = 302.0000;
476 SET SESSION example_double_thdvar = 300.0000;
477
478 ================================================================================
479 3.3 - CONNECT 3 CLIENTS, SET LOCAL PLUGIN VARS
480 ================================================================================
481 connect con1, localhost, root,,;
482 SET SESSION example_double_thdvar = 300.1111;
483
484 connect con2, localhost, root,,;
485 SET SESSION example_double_thdvar = 300.2222;
486
487 connect con3, localhost, root,,;
488 SET SESSION example_double_thdvar = 300.3333;
489
490 connection default;
491
492 ================================================================================
493 3.4 - VERIFY GLOBAL AND SESSION PLUGIN VARS
494 ================================================================================
495
496 SHOW GLOBAL STATUS LIKE "example_%";
497 Variable_name Value
498 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
499 example_status_array_var1 100
500 example_status_array_var2 20.010000
501 example_status_var3 three hundred
502 example_status_var4 ON
503 example_status_var5 OFF
504 example_status_var6 8250
505
506 SHOW SESSION STATUS LIKE "example_%";
507 Variable_name Value
508 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
509 example_status_array_var1 100
510 example_status_array_var2 20.010000
511 example_status_var3 three hundred
512 example_status_var4 ON
513 example_status_var5 OFF
514 example_status_var6 8250
515
516 SHOW GLOBAL VARIABLES LIKE "example_%";
517 Variable_name Value
518 example_create_count_thdvar 0
519 example_double_thdvar 302.000000
520 example_double_var 301.000000
521 example_enum_var e1
522 example_last_create_thdvar
523 example_signed_int_thdvar -10
524 example_signed_int_var -10
525 example_signed_long_thdvar -10
526 example_signed_long_var -10
527 example_signed_longlong_thdvar -10
528 example_signed_longlong_var -10
529 example_ulong_var 300
530
531 SHOW SESSION VARIABLES LIKE "example_%";
532 Variable_name Value
533 example_create_count_thdvar 0
534 example_double_thdvar 300.000000
535 example_double_var 301.000000
536 example_enum_var e1
537 example_last_create_thdvar
538 example_signed_int_thdvar -10
539 example_signed_int_var -10
540 example_signed_long_thdvar -10
541 example_signed_long_var -10
542 example_signed_longlong_thdvar -10
543 example_signed_longlong_var -10
544 example_ulong_var 300
545
546 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
547 variable_name variable_value
548 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
549 example_status_array_var1 100
550 example_status_array_var2 20.010000
551 example_status_var3 three hundred
552 example_status_var4 ON
553 example_status_var5 OFF
554 example_status_var6 8250
555
556 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
557 variable_name variable_value
558 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
559 example_status_array_var1 100
560 example_status_array_var2 20.010000
561 example_status_var3 three hundred
562 example_status_var4 ON
563 example_status_var5 OFF
564 example_status_var6 8250
565
566 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
567 variable_name variable_value
568 example_create_count_thdvar 0
569 example_double_thdvar 302.000000
570 example_double_var 301.000000
571 example_enum_var e1
572 example_last_create_thdvar
573 example_signed_int_thdvar -10
574 example_signed_int_var -10
575 example_signed_long_thdvar -10
576 example_signed_long_var -10
577 example_signed_longlong_thdvar -10
578 example_signed_longlong_var -10
579 example_ulong_var 300
580
581 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
582 variable_name variable_value
583 example_create_count_thdvar 0
584 example_double_thdvar 300.000000
585 example_double_var 301.000000
586 example_enum_var e1
587 example_last_create_thdvar
588 example_signed_int_thdvar -10
589 example_signed_int_var -10
590 example_signed_long_thdvar -10
591 example_signed_long_var -10
592 example_signed_longlong_thdvar -10
593 example_signed_longlong_var -10
594 example_ulong_var 300
595
596 # Variables by thread
597 SELECT variable_name, variable_value FROM performance_schema.variables_by_thread
598 WHERE variable_name LIKE "example_%" ORDER BY variable_name, variable_value;
599 variable_name variable_value
600 example_create_count_thdvar 0
601 example_create_count_thdvar 0
602 example_create_count_thdvar 0
603 example_create_count_thdvar 0
604 example_double_thdvar 300.000000
605 example_double_thdvar 300.111100
606 example_double_thdvar 300.222200
607 example_double_thdvar 300.333300
608 example_last_create_thdvar
609 example_last_create_thdvar
610 example_last_create_thdvar
611 example_last_create_thdvar
612 example_signed_int_thdvar -10
613 example_signed_int_thdvar -10
614 example_signed_int_thdvar -10
615 example_signed_int_thdvar -10
616 example_signed_long_thdvar -10
617 example_signed_long_thdvar -10
618 example_signed_long_thdvar -10
619 example_signed_long_thdvar -10
620 example_signed_longlong_thdvar -10
621 example_signed_longlong_thdvar -10
622 example_signed_longlong_thdvar -10
623 example_signed_longlong_thdvar -10
624
625 ================================================================================
626 3.5 - DISCONNECT CLIENTS
627 ================================================================================
628 connection con1;
629 disconnect con1;
630 connection con2;
631 disconnect con2;
632 connection con3;
633 disconnect con3;
634 connection default;
635
636 ================================================================================
637 3.6 - VERIFY SESSION VARS ARE REMOVED
638 ================================================================================
639
640 SHOW GLOBAL STATUS LIKE "example_%";
641 Variable_name Value
642 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
643 example_status_array_var1 100
644 example_status_array_var2 20.010000
645 example_status_var3 three hundred
646 example_status_var4 ON
647 example_status_var5 OFF
648 example_status_var6 8250
649
650 SHOW SESSION STATUS LIKE "example_%";
651 Variable_name Value
652 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
653 example_status_array_var1 100
654 example_status_array_var2 20.010000
655 example_status_var3 three hundred
656 example_status_var4 ON
657 example_status_var5 OFF
658 example_status_var6 8250
659
660 SHOW GLOBAL VARIABLES LIKE "example_%";
661 Variable_name Value
662 example_create_count_thdvar 0
663 example_double_thdvar 302.000000
664 example_double_var 301.000000
665 example_enum_var e1
666 example_last_create_thdvar
667 example_signed_int_thdvar -10
668 example_signed_int_var -10
669 example_signed_long_thdvar -10
670 example_signed_long_var -10
671 example_signed_longlong_thdvar -10
672 example_signed_longlong_var -10
673 example_ulong_var 300
674
675 SHOW SESSION VARIABLES LIKE "example_%";
676 Variable_name Value
677 example_create_count_thdvar 0
678 example_double_thdvar 300.000000
679 example_double_var 301.000000
680 example_enum_var e1
681 example_last_create_thdvar
682 example_signed_int_thdvar -10
683 example_signed_int_var -10
684 example_signed_long_thdvar -10
685 example_signed_long_var -10
686 example_signed_longlong_thdvar -10
687 example_signed_longlong_var -10
688 example_ulong_var 300
689
690 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
691 variable_name variable_value
692 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
693 example_status_array_var1 100
694 example_status_array_var2 20.010000
695 example_status_var3 three hundred
696 example_status_var4 ON
697 example_status_var5 OFF
698 example_status_var6 8250
699
700 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
701 variable_name variable_value
702 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
703 example_status_array_var1 100
704 example_status_array_var2 20.010000
705 example_status_var3 three hundred
706 example_status_var4 ON
707 example_status_var5 OFF
708 example_status_var6 8250
709
710 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
711 variable_name variable_value
712 example_create_count_thdvar 0
713 example_double_thdvar 302.000000
714 example_double_var 301.000000
715 example_enum_var e1
716 example_last_create_thdvar
717 example_signed_int_thdvar -10
718 example_signed_int_var -10
719 example_signed_long_thdvar -10
720 example_signed_long_var -10
721 example_signed_longlong_thdvar -10
722 example_signed_longlong_var -10
723 example_ulong_var 300
724
725 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
726 variable_name variable_value
727 example_create_count_thdvar 0
728 example_double_thdvar 300.000000
729 example_double_var 301.000000
730 example_enum_var e1
731 example_last_create_thdvar
732 example_signed_int_thdvar -10
733 example_signed_int_var -10
734 example_signed_long_thdvar -10
735 example_signed_long_var -10
736 example_signed_longlong_thdvar -10
737 example_signed_longlong_var -10
738 example_ulong_var 300
739
740 # Variables by thread
741 SELECT variable_name, variable_value FROM performance_schema.variables_by_thread
742 WHERE variable_name LIKE "example_%" ORDER BY variable_name, variable_value;
743 variable_name variable_value
744 example_create_count_thdvar 0
745 example_double_thdvar 300.000000
746 example_last_create_thdvar
747 example_signed_int_thdvar -10
748 example_signed_long_thdvar -10
749 example_signed_longlong_thdvar -10
750
751 ================================================================================
752 3.7 - RECONNECT 3 CLIENTS, SET SESSION VARS FOR EACH
753 ================================================================================
754 connect con1, localhost, root,,;
755 SET SESSION example_double_thdvar = 311.1111;
756
757 connect con2, localhost, root,,;
758 SET SESSION example_double_thdvar = 322.2222;
759
760 connect con3, localhost, root,,;
761 SET SESSION example_double_thdvar = 333.3333;
762
763 connection default;
764
765 ================================================================================
766 3.8 - VERIFY GLOBAL AND SESSION VARS
767 ================================================================================
768
769 SHOW GLOBAL STATUS LIKE "example_%";
770 Variable_name Value
771 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
772 example_status_array_var1 100
773 example_status_array_var2 20.010000
774 example_status_var3 three hundred
775 example_status_var4 ON
776 example_status_var5 OFF
777 example_status_var6 8250
778
779 SHOW SESSION STATUS LIKE "example_%";
780 Variable_name Value
781 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
782 example_status_array_var1 100
783 example_status_array_var2 20.010000
784 example_status_var3 three hundred
785 example_status_var4 ON
786 example_status_var5 OFF
787 example_status_var6 8250
788
789 SHOW GLOBAL VARIABLES LIKE "example_%";
790 Variable_name Value
791 example_create_count_thdvar 0
792 example_double_thdvar 302.000000
793 example_double_var 301.000000
794 example_enum_var e1
795 example_last_create_thdvar
796 example_signed_int_thdvar -10
797 example_signed_int_var -10
798 example_signed_long_thdvar -10
799 example_signed_long_var -10
800 example_signed_longlong_thdvar -10
801 example_signed_longlong_var -10
802 example_ulong_var 300
803
804 SHOW SESSION VARIABLES LIKE "example_%";
805 Variable_name Value
806 example_create_count_thdvar 0
807 example_double_thdvar 300.000000
808 example_double_var 301.000000
809 example_enum_var e1
810 example_last_create_thdvar
811 example_signed_int_thdvar -10
812 example_signed_int_var -10
813 example_signed_long_thdvar -10
814 example_signed_long_var -10
815 example_signed_longlong_thdvar -10
816 example_signed_longlong_var -10
817 example_ulong_var 300
818
819 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
820 variable_name variable_value
821 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
822 example_status_array_var1 100
823 example_status_array_var2 20.010000
824 example_status_var3 three hundred
825 example_status_var4 ON
826 example_status_var5 OFF
827 example_status_var6 8250
828
829 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
830 variable_name variable_value
831 example_func_example enum_var is 0, ulong_var is 300, double_var is 301.000000, signed_int_var is -10, signed_long_var is -10, signed_longlong_var is -10
832 example_status_array_var1 100
833 example_status_array_var2 20.010000
834 example_status_var3 three hundred
835 example_status_var4 ON
836 example_status_var5 OFF
837 example_status_var6 8250
838
839 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
840 variable_name variable_value
841 example_create_count_thdvar 0
842 example_double_thdvar 302.000000
843 example_double_var 301.000000
844 example_enum_var e1
845 example_last_create_thdvar
846 example_signed_int_thdvar -10
847 example_signed_int_var -10
848 example_signed_long_thdvar -10
849 example_signed_long_var -10
850 example_signed_longlong_thdvar -10
851 example_signed_longlong_var -10
852 example_ulong_var 300
853
854 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
855 variable_name variable_value
856 example_create_count_thdvar 0
857 example_double_thdvar 300.000000
858 example_double_var 301.000000
859 example_enum_var e1
860 example_last_create_thdvar
861 example_signed_int_thdvar -10
862 example_signed_int_var -10
863 example_signed_long_thdvar -10
864 example_signed_long_var -10
865 example_signed_longlong_thdvar -10
866 example_signed_longlong_var -10
867 example_ulong_var 300
868
869 # Variables by thread
870 SELECT variable_name, variable_value FROM performance_schema.variables_by_thread
871 WHERE variable_name LIKE "example_%" ORDER BY variable_name, variable_value;
872 variable_name variable_value
873 example_create_count_thdvar 0
874 example_create_count_thdvar 0
875 example_create_count_thdvar 0
876 example_create_count_thdvar 0
877 example_double_thdvar 300.000000
878 example_double_thdvar 311.111100
879 example_double_thdvar 322.222200
880 example_double_thdvar 333.333300
881 example_last_create_thdvar
882 example_last_create_thdvar
883 example_last_create_thdvar
884 example_last_create_thdvar
885 example_signed_int_thdvar -10
886 example_signed_int_thdvar -10
887 example_signed_int_thdvar -10
888 example_signed_int_thdvar -10
889 example_signed_long_thdvar -10
890 example_signed_long_thdvar -10
891 example_signed_long_thdvar -10
892 example_signed_long_thdvar -10
893 example_signed_longlong_thdvar -10
894 example_signed_longlong_thdvar -10
895 example_signed_longlong_thdvar -10
896 example_signed_longlong_thdvar -10
897
898 ================================================================================
899 3.9 - UNINSTALL PLUGIN, LEAVE CLIENTS CONNECTED
900 ================================================================================
901 UNINSTALL PLUGIN example;
902
903 ================================================================================
904 3.10 - VERIFY SESSION VARS ARE REMOVED
905 ================================================================================
906
907 SHOW GLOBAL STATUS LIKE "example_%";
908 Variable_name Value
909
910 SHOW SESSION STATUS LIKE "example_%";
911 Variable_name Value
912
913 SHOW GLOBAL VARIABLES LIKE "example_%";
914 Variable_name Value
915
916 SHOW SESSION VARIABLES LIKE "example_%";
917 Variable_name Value
918
919 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
920 variable_name variable_value
921
922 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
923 variable_name variable_value
924
925 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
926 variable_name variable_value
927
928 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
929 variable_name variable_value
930
931 # VARIABLES BY THREAD
932 SELECT variable_name, variable_value FROM performance_schema.variables_by_thread
933 WHERE variable_name LIKE "example_%" ORDER BY variable_name, variable_value;
934 variable_name variable_value
935
936 ================================================================================
937 3.11 - DISCONNECT CLIENTS
938 ================================================================================
939 connection con1;
940 disconnect con1;
941 connection con2;
942 disconnect con2;
943 connection con3;
944 disconnect con3;
945 connection default;
946
947 ================================================================================
948 3.12 - VERIFY CLIENTS ARE REMOVED
949 ================================================================================
950
951 SHOW GLOBAL STATUS LIKE "example_%";
952 Variable_name Value
953
954 SHOW SESSION STATUS LIKE "example_%";
955 Variable_name Value
956
957 SHOW GLOBAL VARIABLES LIKE "example_%";
958 Variable_name Value
959
960 SHOW SESSION VARIABLES LIKE "example_%";
961 Variable_name Value
962
963 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
964 variable_name variable_value
965
966 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
967 variable_name variable_value
968
969 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
970 variable_name variable_value
971
972 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
973 variable_name variable_value
974
975 # VARIABLES BY THREAD
976 SELECT variable_name, variable_value FROM performance_schema.variables_by_thread
977 WHERE variable_name LIKE "example_%" ORDER BY variable_name, variable_value;
978 variable_name variable_value
979
980 ================================================================================
981 TEST 5 - BUG#22225549 MYSQL_CHANGE_USER/MYSQL_RESET_CONNECTION + SET INNODB...
982 Update to plugin-defined session variable triggers resync with global
983 variables and deadlocks on THD::LOCK_thd_sysvar.
984 ================================================================================
985 select @@session.innodb_strict_mode;
986 @@session.innodb_strict_mode
987 1
988 select user(), current_user();
989 user() current_user()
990 root@localhost root@localhost
991 change_user root
992 #
993 # Trigger a resync of session variables with global variables.
994 #
995 set @@session.innodb_strict_mode=off;
996
997 # Restore
998
999 set @@session.innodb_strict_mode=1;
1000 select @@session.innodb_strict_mode;
1001 @@session.innodb_strict_mode
1002 1
1003
1004 ================================================================================
1005 CLEAN UP
1006 ================================================================================
1007 connection default;
1008
1009
1010
1011 # Wait till we reached the initial number of concurrent sessions