Debian Codegraph @

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

raw
1 # Tests for PERFORMANCE_SCHEMA
2
3 --source include/not_embedded.inc
4 --source include/have_perfschema.inc
5 --source include/have_query_cache_disabled.inc
6
7 CREATE USER user1@localhost;
8 CREATE USER user2@localhost;
9 CREATE USER user3@localhost;
10
11 grant ALL on *.* to user1@localhost;
12 grant ALL on *.* to user2@localhost;
13 grant ALL on *.* to user3@localhost;
14
15 # To aggregate to accounts
16 #SET GLOBAL show_compatibility_56=0;
17
18 TRUNCATE TABLE performance_schema.accounts;
19
20 FLUSH PRIVILEGES;
21
22 CREATE TABLE test.t_range(a int, b int, PRIMARY KEY(a));
23
24 INSERT INTO test.t_range values (1, 1), (2,2), (3, 3), (4, 4), (5, 5);
25 INSERT INTO test.t_range values (6, 6), (7,7), (8, 8), (9, 9), (10, 10);
26
27 FLUSH GLOBAL STATUS;
28
29 let $initial= `SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'`;
30
31 # Causes Select_range to increment (+1)
32 --disable_ps2_protocol
33 SELECT * from test.t_range where (a >= 3) AND (a <= 5);
34 --enable_ps2_protocol
35
36 SELECT * from performance_schema.session_status
37 WHERE VARIABLE_NAME = 'Select_range';
38
39 --disable_query_log
40 eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
41 --enable_query_log
42
43 connect(con1, localhost, user1,,);
44 # Causes Select_range to increment (+1)
45 --disable_ps2_protocol
46 SELECT * from test.t_range where (a >= 3) AND (a <= 5);
47 --enable_ps2_protocol
48
49 SELECT * from performance_schema.session_status
50 WHERE VARIABLE_NAME = 'Select_range';
51
52 --disable_query_log
53 eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
54 --enable_query_log
55
56 connect(con2, localhost, user2,,);
57 # Causes Select_range to increment (+2)
58 --disable_ps2_protocol
59 SELECT * from test.t_range where (a >= 3) AND (a <= 5);
60 SELECT * from test.t_range where (a >= 4) AND (a <= 6);
61 --enable_ps2_protocol
62
63 SELECT * from performance_schema.session_status
64 WHERE VARIABLE_NAME = 'Select_range';
65
66 --disable_query_log
67 eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
68 --enable_query_log
69
70 connect(con3, localhost, user3,,);
71 # Causes Select_range to increment (+3)
72 --disable_ps2_protocol
73 SELECT * from test.t_range where (a >= 3) AND (a <= 5);
74 SELECT * from test.t_range where (a >= 4) AND (a <= 6);
75 SELECT * from test.t_range where (a >= 5) AND (a <= 7);
76 --enable_ps2_protocol
77
78 SELECT * from performance_schema.session_status
79 WHERE VARIABLE_NAME = 'Select_range';
80
81 --disable_query_log
82 eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
83 --enable_query_log
84
85 --connection default
86
87 --disable_query_log
88 eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
89 --enable_query_log
90
91 SELECT `USER`, `HOST`, VARIABLE_NAME, VARIABLE_VALUE
92 FROM performance_schema.status_by_account WHERE VARIABLE_NAME = 'Select_range'
93 AND `USER` LIKE 'user%'
94 ORDER BY `USER`;
95
96 --disconnect con1
97 --disconnect con2
98
99 # Wait till all disconnects are completed
100 let $count_sessions= 2;
101 --source include/wait_until_count_sessions.inc
102
103 --disable_query_log
104 eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
105 --enable_query_log
106
107 TRUNCATE TABLE performance_schema.accounts;
108
109 --disable_query_log
110 eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
111 --enable_query_log
112
113 --disconnect con3
114
115 # Wait till all disconnects are completed
116 let $count_sessions= 1;
117 --source include/wait_until_count_sessions.inc
118
119 --disable_query_log
120 eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
121 --enable_query_log
122
123 # Make sure TRUNCATE on accounts does not add to global_status
124 TRUNCATE TABLE performance_schema.accounts;
125
126 --disable_query_log
127 eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range';
128 --enable_query_log
129
130 DROP TABLE test.t_range;
131
132 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
133 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user2@localhost;
134 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user3@localhost;
135 DROP USER user1@localhost;
136 DROP USER user2@localhost;
137 DROP USER user3@localhost;
138
139 #SET GLOBAL show_compatibility_56=1;
140
141 FLUSH PRIVILEGES;
142