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';
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, int_var is 0, double_var is 8.500000, really
59
60 SHOW SESSION STATUS LIKE "example_%";
61 Variable_name Value
62 Example_func_example enum_var is 0, ulong_var is 8, int_var is 0, double_var is 8.500000, really
63
64 SHOW GLOBAL VARIABLES LIKE "example_%";
65 Variable_name Value
66 example_double_thdvar 8.500000
67 example_double_var 8.500000
68 example_enum_var e1
69 example_int_var 0
70 example_ulong_var 8
71 example_varopt_default 5
72
73 SHOW SESSION VARIABLES LIKE "example_%";
74 Variable_name Value
75 example_double_thdvar 8.500000
76 example_double_var 8.500000
77 example_enum_var e1
78 example_int_var 0
79 example_ulong_var 8
80 example_varopt_default 5
81
82 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
83 variable_name variable_value
84
85 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
86 variable_name variable_value
87
88 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
89 variable_name variable_value
90 example_double_thdvar 8.500000
91 example_double_var 8.500000
92 example_enum_var e1
93 example_int_var 0
94 example_ulong_var 8
95 example_varopt_default 5
96
97 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
98 variable_name variable_value
99 disconnect con0;
100
101 ================================================================================
102 2.2 - SET PLUGIN VARS
103 ================================================================================
104 # GLOBAL
105 SET GLOBAL example_ulong_var = 100;
106 SET GLOBAL example_enum_var = e1;
107 SET GLOBAL example_double_var = 100.9990;
108 SET GLOBAL example_double_thdvar = 101.9991;
109 # SESSION
110 SET SESSION example_double_thdvar = 102.9992;
111
112 ================================================================================
113 2.3 - VERIFY UPDATED PLUGIN VARS
114 ================================================================================
115
116 SHOW GLOBAL STATUS LIKE "example_%";
117 Variable_name Value
118 Example_func_example enum_var is 0, ulong_var is 100, int_var is 0, double_var is 100.999000, really
119
120 SHOW SESSION STATUS LIKE "example_%";
121 Variable_name Value
122 Example_func_example enum_var is 0, ulong_var is 100, int_var is 0, double_var is 100.999000, really
123
124 SHOW GLOBAL VARIABLES LIKE "example_%";
125 Variable_name Value
126 example_double_thdvar 101.999100
127 example_double_var 100.999000
128 example_enum_var e1
129 example_int_var 0
130 example_ulong_var 100
131 example_varopt_default 5
132
133 SHOW SESSION VARIABLES LIKE "example_%";
134 Variable_name Value
135 example_double_thdvar 102.999200
136 example_double_var 100.999000
137 example_enum_var e1
138 example_int_var 0
139 example_ulong_var 100
140 example_varopt_default 5
141
142 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
143 variable_name variable_value
144
145 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
146 variable_name variable_value
147
148 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
149 variable_name variable_value
150 example_double_thdvar 101.999100
151 example_double_var 100.999000
152 example_enum_var e1
153 example_int_var 0
154 example_ulong_var 100
155 example_varopt_default 5
156
157 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
158 variable_name variable_value
159
160 ================================================================================
161 2.4 - UNINSTALL PLUGIN
162 ================================================================================
163 UNINSTALL PLUGIN example;
164
165 ================================================================================
166 2.5 - VERIFY NO PLUGIN VARS
167 ================================================================================
168
169 SHOW GLOBAL STATUS LIKE "example_%";
170 Variable_name Value
171
172 SHOW SESSION STATUS LIKE "example_%";
173 Variable_name Value
174
175 SHOW GLOBAL VARIABLES LIKE "example_%";
176 Variable_name Value
177
178 SHOW SESSION VARIABLES LIKE "example_%";
179 Variable_name Value
180
181 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
182 variable_name variable_value
183
184 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
185 variable_name variable_value
186
187 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
188 variable_name variable_value
189
190 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
191 variable_name variable_value
192
193 ================================================================================
194 2.6 - REINSTALL PLUGIN
195 ================================================================================
196 # Reinstall EXAMPLE plugin
197 INSTALL PLUGIN example SONAME 'ha_example';
198
199 ================================================================================
200 2.7 - SET PLUGIN VARS AGAIN
201 ================================================================================
202 # GLOBAL
203 SET GLOBAL example_ulong_var = 200;
204 SET GLOBAL example_enum_var = e2;
205 SET GLOBAL example_double_var = 200.8880;
206 SET GLOBAL example_double_thdvar = 201.8881;
207 # SESSION
208 SET SESSION example_double_thdvar = 202.8882;
209
210 ================================================================================
211 2.8 - VERIFY PLUGIN VARS
212 ================================================================================
213
214 SHOW GLOBAL STATUS LIKE "example_%";
215 Variable_name Value
216 Example_func_example enum_var is 1, ulong_var is 200, int_var is 0, double_var is 200.888000, really
217
218 SHOW SESSION STATUS LIKE "example_%";
219 Variable_name Value
220 Example_func_example enum_var is 1, ulong_var is 200, int_var is 0, double_var is 200.888000, really
221
222 SHOW GLOBAL VARIABLES LIKE "example_%";
223 Variable_name Value
224 example_double_thdvar 201.888100
225 example_double_var 200.888000
226 example_enum_var e2
227 example_int_var 0
228 example_ulong_var 200
229 example_varopt_default 5
230
231 SHOW SESSION VARIABLES LIKE "example_%";
232 Variable_name Value
233 example_double_thdvar 202.888200
234 example_double_var 200.888000
235 example_enum_var e2
236 example_int_var 0
237 example_ulong_var 200
238 example_varopt_default 5
239
240 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
241 variable_name variable_value
242
243 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
244 variable_name variable_value
245
246 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
247 variable_name variable_value
248 example_double_thdvar 201.888100
249 example_double_var 200.888000
250 example_enum_var e2
251 example_int_var 0
252 example_ulong_var 200
253 example_varopt_default 5
254
255 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
256 variable_name variable_value
257
258 ================================================================================
259 2.9 - UNINSTALL PLUGIN
260 ================================================================================
261 UNINSTALL PLUGIN example;
262
263 ================================================================================
264 2.10 - VERIFY NO PLUGIN VARS
265 ================================================================================
266
267 SHOW GLOBAL STATUS LIKE "example_%";
268 Variable_name Value
269
270 SHOW SESSION STATUS LIKE "example_%";
271 Variable_name Value
272
273 SHOW GLOBAL VARIABLES LIKE "example_%";
274 Variable_name Value
275
276 SHOW SESSION VARIABLES LIKE "example_%";
277 Variable_name Value
278
279 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
280 variable_name variable_value
281
282 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
283 variable_name variable_value
284
285 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
286 variable_name variable_value
287
288 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
289 variable_name variable_value
290
291 ================================================================================
292 TEST 3 - SESSION PLUGIN VARS ON MULTIPLE CONNECTIONS
293 ================================================================================
294
295 ================================================================================
296 3.1 - INSTALL PLUGIN
297 ================================================================================
298 INSTALL PLUGIN example SONAME 'ha_example';
299
300 ================================================================================
301 3.2 - SET GLOBAL AND DEFAULT CONNECTION VARS
302 ================================================================================
303 connection default;
304 SET GLOBAL example_ulong_var = 300;
305 SET GLOBAL example_enum_var = e1;
306 SET GLOBAL example_double_var = 301.0000;
307 SET GLOBAL example_double_thdvar = 302.0000;
308 SET SESSION example_double_thdvar = 300.0000;
309
310 ================================================================================
311 3.3 - CONNECT 3 CLIENTS, SET LOCAL PLUGIN VARS
312 ================================================================================
313 connect con1, localhost, root,,;
314 SET SESSION example_double_thdvar = 300.1111;
315
316 connect con2, localhost, root,,;
317 SET SESSION example_double_thdvar = 300.2222;
318
319 connect con3, localhost, root,,;
320 SET SESSION example_double_thdvar = 300.3333;
321
322 connection default;
323
324 ================================================================================
325 3.4 - VERIFY GLOBAL AND SESSION PLUGIN VARS
326 ================================================================================
327
328 SHOW GLOBAL STATUS LIKE "example_%";
329 Variable_name Value
330 Example_func_example enum_var is 0, ulong_var is 300, int_var is 0, double_var is 301.000000, really
331
332 SHOW SESSION STATUS LIKE "example_%";
333 Variable_name Value
334 Example_func_example enum_var is 0, ulong_var is 300, int_var is 0, double_var is 301.000000, really
335
336 SHOW GLOBAL VARIABLES LIKE "example_%";
337 Variable_name Value
338 example_double_thdvar 302.000000
339 example_double_var 301.000000
340 example_enum_var e1
341 example_int_var 0
342 example_ulong_var 300
343 example_varopt_default 5
344
345 SHOW SESSION VARIABLES LIKE "example_%";
346 Variable_name Value
347 example_double_thdvar 300.000000
348 example_double_var 301.000000
349 example_enum_var e1
350 example_int_var 0
351 example_ulong_var 300
352 example_varopt_default 5
353
354 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
355 variable_name variable_value
356
357 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
358 variable_name variable_value
359
360 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
361 variable_name variable_value
362 example_double_thdvar 302.000000
363 example_double_var 301.000000
364 example_enum_var e1
365 example_int_var 0
366 example_ulong_var 300
367 example_varopt_default 5
368
369 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
370 variable_name variable_value
371
372 # Variables by thread
373 SELECT variable_name, variable_value FROM performance_schema.variables_by_thread
374 WHERE variable_name LIKE "example_%" ORDER BY variable_value;
375 variable_name variable_value
376
377 ================================================================================
378 3.5 - DISCONNECT CLIENTS
379 ================================================================================
380 connection con1;
381 disconnect con1;
382 connection con2;
383 disconnect con2;
384 connection con3;
385 disconnect con3;
386 connection default;
387
388 ================================================================================
389 3.6 - VERIFY SESSION VARS ARE REMOVED
390 ================================================================================
391
392 SHOW GLOBAL STATUS LIKE "example_%";
393 Variable_name Value
394 Example_func_example enum_var is 0, ulong_var is 300, int_var is 0, double_var is 301.000000, really
395
396 SHOW SESSION STATUS LIKE "example_%";
397 Variable_name Value
398 Example_func_example enum_var is 0, ulong_var is 300, int_var is 0, double_var is 301.000000, really
399
400 SHOW GLOBAL VARIABLES LIKE "example_%";
401 Variable_name Value
402 example_double_thdvar 302.000000
403 example_double_var 301.000000
404 example_enum_var e1
405 example_int_var 0
406 example_ulong_var 300
407 example_varopt_default 5
408
409 SHOW SESSION VARIABLES LIKE "example_%";
410 Variable_name Value
411 example_double_thdvar 300.000000
412 example_double_var 301.000000
413 example_enum_var e1
414 example_int_var 0
415 example_ulong_var 300
416 example_varopt_default 5
417
418 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
419 variable_name variable_value
420
421 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
422 variable_name variable_value
423
424 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
425 variable_name variable_value
426 example_double_thdvar 302.000000
427 example_double_var 301.000000
428 example_enum_var e1
429 example_int_var 0
430 example_ulong_var 300
431 example_varopt_default 5
432
433 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
434 variable_name variable_value
435
436 # Variables by thread
437 SELECT variable_name, variable_value FROM performance_schema.variables_by_thread
438 WHERE variable_name LIKE "example_%" ORDER BY variable_value;
439 variable_name variable_value
440
441 ================================================================================
442 3.7 - RECONNECT 3 CLIENTS, SET SESSION VARS FOR EACH
443 ================================================================================
444 connect con1, localhost, root,,;
445 SET SESSION example_double_thdvar = 311.1111;
446
447 connect con2, localhost, root,,;
448 SET SESSION example_double_thdvar = 322.2222;
449
450 connect con3, localhost, root,,;
451 SET SESSION example_double_thdvar = 333.3333;
452
453 connection default;
454
455 ================================================================================
456 3.8 - VERIFY GLOBAL AND SESSION VARS
457 ================================================================================
458
459 SHOW GLOBAL STATUS LIKE "example_%";
460 Variable_name Value
461 Example_func_example enum_var is 0, ulong_var is 300, int_var is 0, double_var is 301.000000, really
462
463 SHOW SESSION STATUS LIKE "example_%";
464 Variable_name Value
465 Example_func_example enum_var is 0, ulong_var is 300, int_var is 0, double_var is 301.000000, really
466
467 SHOW GLOBAL VARIABLES LIKE "example_%";
468 Variable_name Value
469 example_double_thdvar 302.000000
470 example_double_var 301.000000
471 example_enum_var e1
472 example_int_var 0
473 example_ulong_var 300
474 example_varopt_default 5
475
476 SHOW SESSION VARIABLES LIKE "example_%";
477 Variable_name Value
478 example_double_thdvar 300.000000
479 example_double_var 301.000000
480 example_enum_var e1
481 example_int_var 0
482 example_ulong_var 300
483 example_varopt_default 5
484
485 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
486 variable_name variable_value
487
488 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
489 variable_name variable_value
490
491 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
492 variable_name variable_value
493 example_double_thdvar 302.000000
494 example_double_var 301.000000
495 example_enum_var e1
496 example_int_var 0
497 example_ulong_var 300
498 example_varopt_default 5
499
500 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
501 variable_name variable_value
502
503 # Variables by thread
504 SELECT variable_name, variable_value FROM performance_schema.variables_by_thread
505 WHERE variable_name LIKE "example_%" ORDER BY variable_value;
506 variable_name variable_value
507
508 ================================================================================
509 3.9 - UNINSTALL PLUGIN, LEAVE CLIENTS CONNECTED
510 ================================================================================
511 UNINSTALL PLUGIN example;
512
513 ================================================================================
514 3.10 - VERIFY SESSION VARS ARE REMOVED
515 ================================================================================
516
517 SHOW GLOBAL STATUS LIKE "example_%";
518 Variable_name Value
519
520 SHOW SESSION STATUS LIKE "example_%";
521 Variable_name Value
522
523 SHOW GLOBAL VARIABLES LIKE "example_%";
524 Variable_name Value
525
526 SHOW SESSION VARIABLES LIKE "example_%";
527 Variable_name Value
528
529 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
530 variable_name variable_value
531
532 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
533 variable_name variable_value
534
535 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
536 variable_name variable_value
537
538 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
539 variable_name variable_value
540
541 # VARIABLES BY THREAD
542 SELECT variable_name, variable_value FROM performance_schema.variables_by_thread
543 WHERE variable_name LIKE "example_%" ORDER BY variable_value;
544 variable_name variable_value
545
546 ================================================================================
547 3.11 - DISCONNECT CLIENTS
548 ================================================================================
549 connection con1;
550 disconnect con1;
551 connection con2;
552 disconnect con2;
553 connection con3;
554 disconnect con3;
555 connection default;
556
557 ================================================================================
558 3.12 - VERIFY CLIENTS ARE REMOVED
559 ================================================================================
560
561 SHOW GLOBAL STATUS LIKE "example_%";
562 Variable_name Value
563
564 SHOW SESSION STATUS LIKE "example_%";
565 Variable_name Value
566
567 SHOW GLOBAL VARIABLES LIKE "example_%";
568 Variable_name Value
569
570 SHOW SESSION VARIABLES LIKE "example_%";
571 Variable_name Value
572
573 SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%";
574 variable_name variable_value
575
576 SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%";
577 variable_name variable_value
578
579 SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%";
580 variable_name variable_value
581
582 SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%";
583 variable_name variable_value
584
585 # VARIABLES BY THREAD
586 SELECT variable_name, variable_value FROM performance_schema.variables_by_thread
587 WHERE variable_name LIKE "example_%" ORDER BY variable_value;
588 variable_name variable_value
589
590 ================================================================================
591 TEST 4 - BUG#18008907: DEADLOCK WITH CHANGE_USER, SHOW VARIABLES, INSTALL PLUGIN
592 ================================================================================
593
594
595 ================================================================================
596 TEST 5 - BUG#22225549 MYSQL_CHANGE_USER/MYSQL_RESET_CONNECTION + SET INNODB...
597 Update to plugin-defined session variable triggers resync with global
598 variables and deadlocks on THD::LOCK_thd_sysvar.
599 ================================================================================
600 select @@session.innodb_strict_mode;
601 @@session.innodb_strict_mode
602 1
603 select user(), current_user();
604 user() current_user()
605 root@localhost root@localhost
606 change_user root
607 #
608 # Trigger a resync of session variables with global variables.
609 #
610 set @@session.innodb_strict_mode=off;
611
612 # Restore
613
614 set @@session.innodb_strict_mode=1;
615 select @@session.innodb_strict_mode;
616 @@session.innodb_strict_mode
617 1
618
619 ================================================================================
620 CLEAN UP
621 ================================================================================
622 connection default;
623
624
625
626 # Wait till we reached the initial number of concurrent sessions