Debian Codegraph @

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

raw
1 CALL mtr.add_suppression("Plugin mysqlx reported: 'All I/O interfaces are disabled");
2 drop table if exists test.marker;
3 create table test.marker(before_marker timestamp, after_marker timestamp);
4 # Test explicitly set options using defaults-file
5 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
6 from performance_schema.variables_info
7 where VARIABLE_SOURCE = 'EXPLICIT' AND
8 VARIABLE_NAME LIKE 'performance%'
9 order by VARIABLE_NAME;
10 VARIABLE_NAME VARIABLE_SOURCE SET_TIME SET_USER SET_HOST
11 performance_schema EXPLICIT NULL NULL NULL
12 performance_schema_accounts_size EXPLICIT NULL NULL NULL
13 performance_schema_digests_size EXPLICIT NULL NULL NULL
14 performance_schema_events_stages_history_long_size EXPLICIT NULL NULL NULL
15 performance_schema_events_stages_history_size EXPLICIT NULL NULL NULL
16 performance_schema_events_statements_history_long_size EXPLICIT NULL NULL NULL
17 performance_schema_events_statements_history_size EXPLICIT NULL NULL NULL
18 performance_schema_events_transactions_history_long_size EXPLICIT NULL NULL NULL
19 performance_schema_events_transactions_history_size EXPLICIT NULL NULL NULL
20 performance_schema_events_waits_history_long_size EXPLICIT NULL NULL NULL
21 performance_schema_events_waits_history_size EXPLICIT NULL NULL NULL
22 performance_schema_hosts_size EXPLICIT NULL NULL NULL
23 performance_schema_session_connect_attrs_size EXPLICIT NULL NULL NULL
24 performance_schema_setup_actors_size EXPLICIT NULL NULL NULL
25 performance_schema_setup_objects_size EXPLICIT NULL NULL NULL
26 performance_schema_users_size EXPLICIT NULL NULL NULL
27 # Test command line options
28 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
29 from performance_schema.variables_info
30 where VARIABLE_SOURCE = 'COMMAND_LINE' AND
31 (VARIABLE_NAME = 'max_connections' OR
32 VARIABLE_NAME = 'skip_networking')
33 order by VARIABLE_NAME;
34 VARIABLE_NAME VARIABLE_SOURCE SET_TIME SET_USER SET_HOST
35 # Restart server with command line option
36 select VARIABLE_NAME, VARIABLE_SOURCE, SET_USER, SET_HOST
37 from performance_schema.variables_info
38 where VARIABLE_SOURCE = 'COMMAND_LINE' AND
39 (VARIABLE_NAME = 'max_connections' OR
40 VARIABLE_NAME = 'skip_networking')
41 order by VARIABLE_NAME;
42 VARIABLE_NAME VARIABLE_SOURCE SET_USER SET_HOST
43 max_connections COMMAND_LINE NULL NULL
44 skip_networking COMMAND_LINE NULL NULL
45 # Test persisted variables
46 SET PERSIST sort_buffer_size=256000;
47 SET PERSIST max_heap_table_size=999424, replica_net_timeout=124;
48 SET PERSIST innodb_default_row_format=COMPACT;
49 SET @@persist.max_execution_time=44000, @@persist.max_user_connections=30;
50 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
51 from performance_schema.variables_info
52 where VARIABLE_SOURCE = 'PERSISTED'
53 order by VARIABLE_NAME;
54 VARIABLE_NAME VARIABLE_SOURCE SET_TIME SET_USER SET_HOST
55 # Restart server with some persisted variables
56 select VARIABLE_NAME, VARIABLE_SOURCE, SET_USER, SET_HOST
57 from performance_schema.variables_info
58 where VARIABLE_SOURCE = 'PERSISTED'
59 order by VARIABLE_NAME;
60 VARIABLE_NAME VARIABLE_SOURCE SET_USER SET_HOST
61 innodb_default_row_format PERSISTED root localhost
62 max_execution_time PERSISTED root localhost
63 max_heap_table_size PERSISTED root localhost
64 max_user_connections PERSISTED root localhost
65 replica_net_timeout PERSISTED root localhost
66 slave_net_timeout PERSISTED root localhost
67 sort_buffer_size PERSISTED root localhost
68 # Change persisted variables to dynamic
69 SELECT now() into @before_timestamp;
70 Sleep 1 second
71 SET GLOBAL sort_buffer_size=256000;
72 SET GLOBAL max_heap_table_size=999424, replica_net_timeout=124;
73 SET @@global.max_execution_time=440000, @@global.max_user_connections=30;
74 SET GLOBAL innodb_default_row_format=DEFAULT;
75 Sleep 1 second
76 SELECT now() into @after_timestamp;
77 truncate table test.marker;
78 insert into test.marker values (@before_timestamp, @after_timestamp);
79 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
80 from performance_schema.variables_info
81 where VARIABLE_SOURCE = 'PERSISTED'
82 order by VARIABLE_NAME;
83 VARIABLE_NAME VARIABLE_SOURCE SET_TIME SET_USER SET_HOST
84 select VARIABLE_NAME, VARIABLE_SOURCE,
85 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
86 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
87 SET_USER, SET_HOST
88 from performance_schema.variables_info
89 where VARIABLE_NAME in
90 ('sort_buffer_size',
91 'max_heap_table_size',
92 'replica_net_timeout',
93 'max_execution_time',
94 'max_user_connections',
95 'innodb_default_row_format')
96 order by VARIABLE_NAME;
97 VARIABLE_NAME VARIABLE_SOURCE SET_TIME_BEFORE_CHECK SET_TIME_AFTER_CHECK SET_USER SET_HOST
98 innodb_default_row_format DYNAMIC pass pass root localhost
99 max_execution_time DYNAMIC pass pass root localhost
100 max_heap_table_size DYNAMIC pass pass root localhost
101 max_user_connections DYNAMIC pass pass root localhost
102 replica_net_timeout DYNAMIC pass pass root localhost
103 sort_buffer_size DYNAMIC pass pass root localhost
104 # Restart server
105 select VARIABLE_NAME, VARIABLE_SOURCE
106 from performance_schema.variables_info
107 where VARIABLE_NAME = 'innodb_fast_shutdown' OR
108 VARIABLE_NAME = 'innodb_file_per_table';
109 VARIABLE_NAME VARIABLE_SOURCE
110 innodb_fast_shutdown COMPILED
111 innodb_file_per_table COMPILED
112 # Restart server
113 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
114 from performance_schema.variables_info
115 where VARIABLE_NAME = 'innodb_fast_shutdown' OR
116 VARIABLE_NAME = 'innodb_file_per_table';
117 VARIABLE_NAME VARIABLE_SOURCE SET_TIME SET_USER SET_HOST
118 innodb_fast_shutdown COMMAND_LINE NULL NULL NULL
119 innodb_file_per_table COMMAND_LINE NULL NULL NULL
120 # Restart server
121 #
122 # WL#9720: SET PERSIST capture user + timestamp
123 #
124 CREATE USER WL9720@localhost;
125 GRANT ALL ON *.* to WL9720@localhost;
126 SELECT now() into @before_timestamp;
127 Sleep 1 second
128 SET SESSION max_heap_table_size=999424;
129 Sleep 1 second
130 SELECT now() into @after_timestamp;
131 truncate table test.marker;
132 insert into test.marker values (@before_timestamp, @after_timestamp);
133 # user must be WL9720
134 select VARIABLE_NAME, VARIABLE_SOURCE,
135 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
136 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
137 SET_USER, SET_HOST
138 from performance_schema.variables_info
139 where VARIABLE_NAME = 'max_heap_table_size';
140 VARIABLE_NAME VARIABLE_SOURCE SET_TIME_BEFORE_CHECK SET_TIME_AFTER_CHECK SET_USER SET_HOST
141 max_heap_table_size DYNAMIC pass pass WL9720 localhost
142 select before_marker, after_marker into @before_timestamp, @after_timestamp
143 from test.marker;
144 # user must be root
145 select VARIABLE_NAME, VARIABLE_SOURCE,
146 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
147 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
148 SET_USER, SET_HOST
149 from performance_schema.variables_info
150 where VARIABLE_NAME = 'max_heap_table_size';
151 VARIABLE_NAME VARIABLE_SOURCE SET_TIME_BEFORE_CHECK SET_TIME_AFTER_CHECK SET_USER SET_HOST
152 max_heap_table_size DYNAMIC pass pass WL9720 localhost
153 # user must be root
154 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
155 from performance_schema.variables_info
156 where VARIABLE_NAME = 'session_track_system_variables';
157 VARIABLE_NAME VARIABLE_SOURCE SET_TIME SET_USER SET_HOST
158 session_track_system_variables COMPILED NULL NULL NULL
159 SELECT now() into @before_timestamp;
160 Sleep 1 second
161 SET GLOBAL session_track_system_variables=DEFAULT;
162 Sleep 1 second
163 SELECT now() into @after_timestamp;
164 truncate table test.marker;
165 insert into test.marker values (@before_timestamp, @after_timestamp);
166 # user must be WL9720
167 select VARIABLE_NAME, VARIABLE_SOURCE,
168 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
169 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
170 SET_USER, SET_HOST
171 from performance_schema.variables_info
172 where VARIABLE_NAME = 'session_track_system_variables';
173 VARIABLE_NAME VARIABLE_SOURCE SET_TIME_BEFORE_CHECK SET_TIME_AFTER_CHECK SET_USER SET_HOST
174 session_track_system_variables DYNAMIC pass pass WL9720 localhost
175 select before_marker, after_marker into @before_timestamp, @after_timestamp
176 from test.marker;
177 # user must be WL9720 since variable was changed globally by wl9720 user
178 select VARIABLE_NAME, VARIABLE_SOURCE,
179 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
180 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
181 SET_USER, SET_HOST
182 from performance_schema.variables_info
183 where VARIABLE_NAME = 'session_track_system_variables';
184 VARIABLE_NAME VARIABLE_SOURCE SET_TIME_BEFORE_CHECK SET_TIME_AFTER_CHECK SET_USER SET_HOST
185 session_track_system_variables DYNAMIC pass pass WL9720 localhost
186 # Restart server
187 # user must be root
188 select VARIABLE_NAME, VARIABLE_SOURCE, SET_TIME, SET_USER, SET_HOST
189 from performance_schema.variables_info
190 where VARIABLE_NAME = 'session_track_system_variables';
191 VARIABLE_NAME VARIABLE_SOURCE SET_TIME SET_USER SET_HOST
192 session_track_system_variables COMPILED NULL NULL NULL
193 DROP USER WL9720@localhost;
194 # check timestamp column values
195 select SET_TIME
196 from performance_schema.variables_info
197 where VARIABLE_NAME = 'sql_auto_is_null';
198 SET_TIME
199 NULL
200 Sleep 1 second
201 SET SESSION sql_auto_is_null= 1;
202 select SET_TIME is not null as CHECK_TIME
203 from performance_schema.variables_info
204 where VARIABLE_NAME = 'sql_auto_is_null';
205 CHECK_TIME
206 1
207 select SET_TIME
208 from performance_schema.variables_info
209 where VARIABLE_NAME = 'sort_buffer_size';
210 SET_TIME
211 NULL
212 Sleep 1 second
213 SET GLOBAL sort_buffer_size= 256000;
214 select SET_TIME is not null as CHECK_TIME
215 from performance_schema.variables_info
216 where VARIABLE_NAME = 'sort_buffer_size';
217 CHECK_TIME
218 1
219 select SET_TIME is not null as CHECK_TIME
220 from performance_schema.variables_info
221 where VARIABLE_NAME = 'foreign_key_checks';
222 CHECK_TIME
223 1
224 SELECT now() into @before_timestamp;
225 Sleep 1 second
226 SET PERSIST foreign_key_checks = 0;
227 Sleep 1 second
228 SELECT now() into @after_timestamp;
229 select VARIABLE_NAME, VARIABLE_SOURCE,
230 if (SET_TIME >= @before_timestamp, "pass", "fail") as SET_TIME_BEFORE_CHECK,
231 if (SET_TIME <= @after_timestamp, "pass", "fail") as SET_TIME_AFTER_CHECK,
232 SET_USER, SET_HOST
233 from performance_schema.variables_info
234 where VARIABLE_NAME = 'foreign_key_checks';
235 VARIABLE_NAME VARIABLE_SOURCE SET_TIME_BEFORE_CHECK SET_TIME_AFTER_CHECK SET_USER SET_HOST
236 foreign_key_checks DYNAMIC pass pass root localhost
237 select SET_TIME
238 from performance_schema.variables_info
239 where VARIABLE_NAME = 'sql_log_bin';
240 SET_TIME
241 NULL
242 Sleep 1 second
243 # since SET statement fails there should be no change in timestamp
244 SET @@persist.sql_log_bin=0;
245 ERROR HY000: Variable 'sql_log_bin' is a SESSION variable and can't be used with SET GLOBAL
246 select SET_TIME
247 from performance_schema.variables_info
248 where VARIABLE_NAME = 'sql_log_bin';
249 SET_TIME
250 NULL
251 SET GLOBAL sort_buffer_size= DEFAULT;
252 SET PERSIST foreign_key_checks = DEFAULT;
253 RESET PERSIST;
254 drop table test.marker;
255 SELECT 'END OF TEST';
256 END OF TEST
257 END OF TEST