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