Debian Codegraph @

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

raw
1
2 # Save the initial number of concurrent sessions
3 --source include/count_sessions.inc
4
5 CALL mtr.add_suppression("Plugin mysqlx reported: 'All I/O interfaces are disabled");
6
7 --disable_warnings
8 drop table if exists test.marker;
9 --enable_warnings
10
11 create table test.marker(before_marker timestamp, after_marker timestamp);
12
13 --echo # Test explicitly set options using defaults-file
14
15 # mtr invokes server with --defaults-file option. All options
16 # set as part of this file should be shown by below select
17 # as EXPLICIT
18
19 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
20 from performance_schema.variables_info
21 where VARIABLE_SOURCE = 'EXPLICIT' AND
22 VARIABLE_NAME LIKE 'performance%'
23 order by VARIABLE_NAME;
24
25 --echo # Test command line options
26
27 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
28 from performance_schema.variables_info
29 where VARIABLE_SOURCE = 'COMMAND_LINE' AND
30 (VARIABLE_NAME = 'max_connections' OR
31 VARIABLE_NAME = 'skip_networking')
32 order by VARIABLE_NAME;
33
34 --echo # Restart server with command line option
35 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
36 --shutdown_server
37 --source include/wait_until_disconnected.inc
38 --exec echo "restart:--max_connections=55 --skip-networking" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
39 --enable_reconnect
40 --source include/wait_until_connected_again.inc
41
42 select VARIABLE_NAME, VARIABLE_SOURCE, SET_USER, SET_HOST
43 from performance_schema.variables_info
44 where VARIABLE_SOURCE = 'COMMAND_LINE' AND
45 (VARIABLE_NAME = 'max_connections' OR
46 VARIABLE_NAME = 'skip_networking')
47 order by VARIABLE_NAME;
48
49 --echo # Test persisted variables
50
51 SET PERSIST sort_buffer_size=256000;
52 SET PERSIST max_heap_table_size=999424, replica_net_timeout=124;
53 SET PERSIST innodb_default_row_format=COMPACT;
54 SET @@persist.max_execution_time=44000, @@persist.max_user_connections=30;
55
56 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
57 from performance_schema.variables_info
58 where VARIABLE_SOURCE = 'PERSISTED'
59 order by VARIABLE_NAME;
60
61 --echo # Restart server with some persisted variables
62 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
63 --shutdown_server
64 --source include/wait_until_disconnected.inc
65 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
66 --enable_reconnect
67 --source include/wait_until_connected_again.inc
68
69 select VARIABLE_NAME, VARIABLE_SOURCE, SET_USER, SET_HOST
70 from performance_schema.variables_info
71 where VARIABLE_SOURCE = 'PERSISTED'
72 order by VARIABLE_NAME;
73
74 --echo # Change persisted variables to dynamic
75
76 SELECT now() into @before_timestamp;
77
78 --echo Sleep 1 second
79 sleep 1;
80
81 SET GLOBAL sort_buffer_size=256000;
82 SET GLOBAL max_heap_table_size=999424, replica_net_timeout=124;
83 SET @@global.max_execution_time=440000, @@global.max_user_connections=30;
84 SET GLOBAL innodb_default_row_format=DEFAULT;
85
86 --echo Sleep 1 second
87 sleep 1;
88
89 SELECT now() into @after_timestamp;
90
91 truncate table test.marker;
92 insert into test.marker values (@before_timestamp, @after_timestamp);
93
94 # return 0 rows
95 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
96 from performance_schema.variables_info
97 where VARIABLE_SOURCE = 'PERSISTED'
98 order by VARIABLE_NAME;
99
100 select VARIABLE_NAME, VARIABLE_SOURCE,
101 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
102 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
103 SET_USER, SET_HOST
104 from performance_schema.variables_info
105 where VARIABLE_NAME in
106 ('sort_buffer_size',
107 'max_heap_table_size',
108 'replica_net_timeout',
109 'max_execution_time',
110 'max_user_connections',
111 'innodb_default_row_format')
112 order by VARIABLE_NAME;
113
114 let $MYSQLD_DATADIR= `select @@datadir`;
115 --remove_file $MYSQLD_DATADIR/mysqld-auto.cnf
116
117 --echo # Restart server
118 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
119 --shutdown_server
120 --source include/wait_until_disconnected.inc
121 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
122 --enable_reconnect
123 --source include/wait_until_connected_again.inc
124
125 #
126 # Bug24567960: P_S.VARIABLES_INFO.VARIABLE_SOURCE INACCURATE FOR
127 # INNODB_FILE_PER_TABLE
128 #
129
130 select VARIABLE_NAME, VARIABLE_SOURCE
131 from performance_schema.variables_info
132 where VARIABLE_NAME = 'innodb_fast_shutdown' OR
133 VARIABLE_NAME = 'innodb_file_per_table';
134
135 --echo # Restart server
136 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
137 --shutdown_server
138 --source include/wait_until_disconnected.inc
139 --exec echo "restart:--innodb_fast_shutdown=1 --innodb_file_per_table" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
140 --enable_reconnect
141 --source include/wait_until_connected_again.inc
142
143 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
144 from performance_schema.variables_info
145 where VARIABLE_NAME = 'innodb_fast_shutdown' OR
146 VARIABLE_NAME = 'innodb_file_per_table';
147
148 --echo # Restart server
149 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
150 --shutdown_server
151 --source include/wait_until_disconnected.inc
152 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
153 --enable_reconnect
154 --source include/wait_until_connected_again.inc
155
156 --echo #
157 --echo # WL#9720: SET PERSIST capture user + timestamp
158 --echo #
159
160 CREATE USER WL9720@localhost;
161 GRANT ALL ON *.* to WL9720@localhost;
162
163 --connect(con1, localhost, WL9720)
164
165 SELECT now() into @before_timestamp;
166
167 --echo Sleep 1 second
168 sleep 1;
169
170 SET SESSION max_heap_table_size=999424;
171
172 --echo Sleep 1 second
173 sleep 1;
174
175 SELECT now() into @after_timestamp;
176
177 truncate table test.marker;
178 insert into test.marker values (@before_timestamp, @after_timestamp);
179
180 --echo # user must be WL9720
181 select VARIABLE_NAME, VARIABLE_SOURCE,
182 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
183 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
184 SET_USER, SET_HOST
185 from performance_schema.variables_info
186 where VARIABLE_NAME = 'max_heap_table_size';
187
188 disconnect con1;
189 connection default;
190
191 # This is a different connection, retrieve timestamps
192
193 select before_marker, after_marker into @before_timestamp, @after_timestamp
194 from test.marker;
195
196 --echo # user must be root
197 select VARIABLE_NAME, VARIABLE_SOURCE,
198 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
199 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
200 SET_USER, SET_HOST
201 from performance_schema.variables_info
202 where VARIABLE_NAME = 'max_heap_table_size';
203
204 --echo # user must be root
205 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
206 from performance_schema.variables_info
207 where VARIABLE_NAME = 'session_track_system_variables';
208
209 --connect(con1, localhost, WL9720)
210
211 SELECT now() into @before_timestamp;
212
213 --echo Sleep 1 second
214 sleep 1;
215
216 SET GLOBAL session_track_system_variables=DEFAULT;
217
218 --echo Sleep 1 second
219 sleep 1;
220
221 SELECT now() into @after_timestamp;
222
223 truncate table test.marker;
224 insert into test.marker values (@before_timestamp, @after_timestamp);
225
226 --echo # user must be WL9720
227 select VARIABLE_NAME, VARIABLE_SOURCE,
228 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
229 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
230 SET_USER, SET_HOST
231 from performance_schema.variables_info
232 where VARIABLE_NAME = 'session_track_system_variables';
233
234 disconnect con1;
235 connection default;
236
237 select before_marker, after_marker into @before_timestamp, @after_timestamp
238 from test.marker;
239
240 --echo # user must be WL9720 since variable was changed globally by wl9720 user
241 select VARIABLE_NAME, VARIABLE_SOURCE,
242 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
243 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
244 SET_USER, SET_HOST
245 from performance_schema.variables_info
246 where VARIABLE_NAME = 'session_track_system_variables';
247
248 --echo # Restart server
249 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
250 --shutdown_server
251 --source include/wait_until_disconnected.inc
252 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
253 --enable_reconnect
254 --source include/wait_until_connected_again.inc
255
256 --echo # user must be root
257 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
258 from performance_schema.variables_info
259 where VARIABLE_NAME = 'session_track_system_variables';
260
261 #Clean up
262 DROP USER WL9720@localhost;
263
264 --echo # check timestamp column values
265 select SET_TIME
266 from performance_schema.variables_info
267 where VARIABLE_NAME = 'sql_auto_is_null';
268
269 --echo Sleep 1 second
270 sleep 1;
271
272 SET SESSION sql_auto_is_null= 1;
273
274 select SET_TIME is not null as CHECK_TIME
275 from performance_schema.variables_info
276 where VARIABLE_NAME = 'sql_auto_is_null';
277
278 select SET_TIME
279 from performance_schema.variables_info
280 where VARIABLE_NAME = 'sort_buffer_size';
281
282 --echo Sleep 1 second
283 sleep 1;
284
285 SET GLOBAL sort_buffer_size= 256000;
286
287 select SET_TIME is not null as CHECK_TIME
288 from performance_schema.variables_info
289 where VARIABLE_NAME = 'sort_buffer_size';
290
291 # foreign_key_checks is a Sys_var_bit,
292 # which can NOT be set on the command line
293 # It shows as DYNAMIC, with a SET_TIME
294
295 select SET_TIME is not null as CHECK_TIME
296 from performance_schema.variables_info
297 where VARIABLE_NAME = 'foreign_key_checks';
298
299 SELECT now() into @before_timestamp;
300
301 --echo Sleep 1 second
302 sleep 1;
303
304 SET PERSIST foreign_key_checks = 0;
305
306 --echo Sleep 1 second
307 sleep 1;
308
309 SELECT now() into @after_timestamp;
310
311 select VARIABLE_NAME, VARIABLE_SOURCE,
312 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
313 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
314 SET_USER, SET_HOST
315 from performance_schema.variables_info
316 where VARIABLE_NAME = 'foreign_key_checks';
317
318 select SET_TIME
319 from performance_schema.variables_info
320 where VARIABLE_NAME = 'sql_log_bin';
321
322 --echo Sleep 1 second
323 sleep 1;
324
325 --echo # since SET statement fails there should be no change in timestamp
326 --error ER_LOCAL_VARIABLE
327 SET @@persist.sql_log_bin=0;
328
329 select SET_TIME
330 from performance_schema.variables_info
331 where VARIABLE_NAME = 'sql_log_bin';
332
333 #Cleanup
334 SET GLOBAL sort_buffer_size= DEFAULT;
335 SET PERSIST foreign_key_checks = DEFAULT;
336 RESET PERSIST;
337
338 drop table test.marker;
339
340 SELECT 'END OF TEST';