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
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
|
#
# WL#9787: Allow SET PERSIST to set read-only variables too
# Extended Test case to cover all the static server variables including
# plugin variables.
#
# Check there are no existing persistent variables
# Must return 0 rows.
SELECT * FROM performance_schema.persisted_variables;
VARIABLE_NAME VARIABLE_VALUE
SELECT VARIABLE_NAME FROM performance_schema.variables_info WHERE VARIABLE_SOURCE = 'PERSISTED';
VARIABLE_NAME
call mtr.add_suppression("option 'thread_stack':");
# Filtering out the warning as open_files_limit value is calculated at runtime
CALL mtr.add_suppression("Could not increase number of max_open_files to more than *");
# Allow SET PERSIST to set all the read only variables excluding plugin
# variables.
#
set @@persist_only.back_log=80;
set @@persist_only.binlog_gtid_simple_recovery=1;
set @@persist_only.disabled_storage_engines='';
set @@persist_only.ft_max_word_len=84;
set @@persist_only.ft_min_word_len=4;
set @@persist_only.ft_query_expansion_limit=20;
set @@persist_only.innodb_adaptive_hash_index_parts=8;
set @@persist_only.innodb_api_disable_rowlock=0;
set @@persist_only.innodb_api_enable_binlog=0;
set @@persist_only.innodb_api_enable_mdl=0;
set @@persist_only.innodb_autoinc_lock_mode=1;
set @@persist_only.innodb_buffer_pool_chunk_size=134217728;
set @@persist_only.innodb_buffer_pool_instances=1;
set @@persist_only.innodb_force_recovery=0;
set @@persist_only.innodb_ft_cache_size=16777216;
set @@persist_only.innodb_ft_max_token_size=84;
set @@persist_only.innodb_ft_min_token_size=4;
set @@persist_only.innodb_ft_sort_pll_degree=2;
set @@persist_only.innodb_ft_total_cache_size=640000000;
set @@persist_only.innodb_log_buffer_size=16777216;
set @@persist_only.innodb_redo_log_capacity=100663296;
set @@persist_only.innodb_open_files=100;
set @@persist_only.innodb_page_cleaners=1;
set @@persist_only.innodb_purge_threads=4;
set @@persist_only.innodb_read_io_threads=4;
set @@persist_only.innodb_rollback_on_timeout=0;
set @@persist_only.innodb_sort_buffer_size=1048576;
set @@persist_only.innodb_sync_array_size=1;
set @@persist_only.innodb_use_native_aio=1;
set @@persist_only.innodb_write_io_threads=4;
set @@persist_only.log_replica_updates=1;
set @@persist_only.max_digest_length=1024;
set @@persist_only.myisam_recover_options='OFF';
set @@persist_only.ngram_token_size=2;
set @@persist_only.old=0;
set @@persist_only.open_files_limit=5000;
set @@persist_only.performance_schema=1;
set @@persist_only.performance_schema_accounts_size=-1;
set @@persist_only.performance_schema_digests_size=10000;
set @@persist_only.performance_schema_error_size=1986;
set @@persist_only.performance_schema_events_stages_history_long_size=10000;
set @@persist_only.performance_schema_events_stages_history_size=10;
set @@persist_only.performance_schema_events_statements_history_long_size=10000;
set @@persist_only.performance_schema_events_statements_history_size=10;
set @@persist_only.performance_schema_events_transactions_history_long_size=10000;
set @@persist_only.performance_schema_events_transactions_history_size=10;
set @@persist_only.performance_schema_events_waits_history_long_size=10000;
set @@persist_only.performance_schema_events_waits_history_size=10;
set @@persist_only.performance_schema_hosts_size=-1;
set @@persist_only.performance_schema_max_cond_classes=80;
set @@persist_only.performance_schema_max_cond_instances=-1;
set @@persist_only.performance_schema_max_digest_length=1024;
set @@persist_only.performance_schema_max_file_classes=80;
set @@persist_only.performance_schema_max_file_handles=32768;
set @@persist_only.performance_schema_max_file_instances=-1;
set @@persist_only.performance_schema_max_index_stat=-1;
set @@persist_only.performance_schema_max_memory_classes=460;
set @@persist_only.performance_schema_max_metadata_locks=-1;
set @@persist_only.performance_schema_max_mutex_classes=220;
set @@persist_only.performance_schema_max_mutex_instances=-1;
set @@persist_only.performance_schema_max_prepared_statements_instances=-1;
set @@persist_only.performance_schema_max_program_instances=-1;
set @@persist_only.performance_schema_max_rwlock_classes=50;
set @@persist_only.performance_schema_max_rwlock_instances=-1;
set @@persist_only.performance_schema_max_socket_classes=10;
set @@persist_only.performance_schema_max_socket_instances=-1;
set @@persist_only.performance_schema_max_sql_text_length=1024;
set @@persist_only.performance_schema_max_stage_classes=250;
set @@persist_only.performance_schema_max_statement_classes=202;
set @@persist_only.performance_schema_max_statement_stack=10;
set @@persist_only.performance_schema_max_table_handles=-1;
set @@persist_only.performance_schema_max_table_instances=-1;
set @@persist_only.performance_schema_max_table_lock_stat=-1;
set @@persist_only.performance_schema_max_thread_classes=50;
set @@persist_only.performance_schema_max_thread_instances=-1;
set @@persist_only.performance_schema_session_connect_attrs_size=512;
set @@persist_only.performance_schema_setup_actors_size=-1;
set @@persist_only.performance_schema_setup_objects_size=-1;
set @@persist_only.performance_schema_users_size=-1;
set @@persist_only.relay_log_recovery=0;
set @@persist_only.relay_log_space_limit=0;
set @@persist_only.skip_name_resolve=0;
set @@persist_only.skip_show_database=0;
set @@persist_only.replica_type_conversions='';
set @@persist_only.table_open_cache_instances=16;
set @@persist_only.thread_handling='one-thread-per-connection';
set @@persist_only.thread_stack=286720;
set @@persist_only.tls_version='TLSv1.2,TLSv1.3';
set @@persist_only.report_host=NULL;
set @@persist_only.report_port=21000;
set @@persist_only.report_password=NULL;
set @@persist_only.report_user=NULL;
# Must return 100 rows.
SELECT count(*) from performance_schema.persisted_variables;
count(*)
94
# Restart server
CALL mtr.add_suppression("Plugin audit_log reported *");
# Both queries must return 101 rows.
SELECT * FROM performance_schema.persisted_variables ORDER BY VARIABLE_NAME;
VARIABLE_NAME VARIABLE_VALUE
back_log 80
binlog_gtid_simple_recovery ON
disabled_storage_engines
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
innodb_adaptive_hash_index_parts 8
innodb_api_disable_rowlock 0
innodb_api_enable_binlog 0
innodb_api_enable_mdl 0
innodb_autoinc_lock_mode 1
innodb_buffer_pool_chunk_size 134217728
innodb_buffer_pool_instances 1
innodb_force_recovery 0
innodb_ft_cache_size 16777216
innodb_ft_max_token_size 84
innodb_ft_min_token_size 4
innodb_ft_sort_pll_degree 2
innodb_ft_total_cache_size 640000000
innodb_log_buffer_size 16777216
innodb_open_files 100
innodb_page_cleaners 1
innodb_purge_threads 4
innodb_read_io_threads 4
innodb_redo_log_capacity 100663296
innodb_rollback_on_timeout 0
innodb_sort_buffer_size 1048576
innodb_sync_array_size 1
innodb_use_native_aio 1
innodb_write_io_threads 4
log_replica_updates ON
log_slave_updates ON
max_digest_length 1024
myisam_recover_options OFF
ngram_token_size 2
old OFF
open_files_limit 5000
performance_schema ON
performance_schema_accounts_size -1
performance_schema_digests_size 10000
performance_schema_error_size 1986
performance_schema_events_stages_history_long_size 10000
performance_schema_events_stages_history_size 10
performance_schema_events_statements_history_long_size 10000
performance_schema_events_statements_history_size 10
performance_schema_events_transactions_history_long_size 10000
performance_schema_events_transactions_history_size 10
performance_schema_events_waits_history_long_size 10000
performance_schema_events_waits_history_size 10
performance_schema_hosts_size -1
performance_schema_max_cond_classes 80
performance_schema_max_cond_instances -1
performance_schema_max_digest_length 1024
performance_schema_max_file_classes 80
performance_schema_max_file_handles 32768
performance_schema_max_file_instances -1
performance_schema_max_index_stat -1
performance_schema_max_memory_classes 460
performance_schema_max_metadata_locks -1
performance_schema_max_mutex_classes 220
performance_schema_max_mutex_instances -1
performance_schema_max_prepared_statements_instances -1
performance_schema_max_program_instances -1
performance_schema_max_rwlock_classes 50
performance_schema_max_rwlock_instances -1
performance_schema_max_socket_classes 10
performance_schema_max_socket_instances -1
performance_schema_max_sql_text_length 1024
performance_schema_max_stage_classes 250
performance_schema_max_statement_classes 202
performance_schema_max_statement_stack 10
performance_schema_max_table_handles -1
performance_schema_max_table_instances -1
performance_schema_max_table_lock_stat -1
performance_schema_max_thread_classes 50
performance_schema_max_thread_instances -1
performance_schema_session_connect_attrs_size 512
performance_schema_setup_actors_size -1
performance_schema_setup_objects_size -1
performance_schema_users_size -1
relay_log_recovery OFF
relay_log_space_limit 0
replica_type_conversions
report_host
report_password
report_port 21000
report_user
skip_name_resolve OFF
skip_show_database OFF
slave_type_conversions
table_open_cache_instances 16
thread_handling one-thread-per-connection
thread_stack 286720
tls_version TLSv1.2,TLSv1.3
SELECT VARIABLE_NAME FROM performance_schema.variables_info WHERE VARIABLE_SOURCE = 'PERSISTED' ORDER BY VARIABLE_NAME;
VARIABLE_NAME
back_log
binlog_gtid_simple_recovery
disabled_storage_engines
ft_max_word_len
ft_min_word_len
ft_query_expansion_limit
innodb_adaptive_hash_index_parts
innodb_api_disable_rowlock
innodb_api_enable_binlog
innodb_api_enable_mdl
innodb_autoinc_lock_mode
innodb_buffer_pool_chunk_size
innodb_buffer_pool_instances
innodb_force_recovery
innodb_ft_cache_size
innodb_ft_max_token_size
innodb_ft_min_token_size
innodb_ft_sort_pll_degree
innodb_ft_total_cache_size
innodb_log_buffer_size
innodb_open_files
innodb_page_cleaners
innodb_purge_threads
innodb_read_io_threads
innodb_redo_log_capacity
innodb_rollback_on_timeout
innodb_sort_buffer_size
innodb_sync_array_size
innodb_use_native_aio
innodb_write_io_threads
log_replica_updates
log_slave_updates
max_digest_length
myisam_recover_options
ngram_token_size
old
open_files_limit
performance_schema
performance_schema_accounts_size
performance_schema_digests_size
performance_schema_error_size
performance_schema_events_stages_history_long_size
performance_schema_events_stages_history_size
performance_schema_events_statements_history_long_size
performance_schema_events_statements_history_size
performance_schema_events_transactions_history_long_size
performance_schema_events_transactions_history_size
performance_schema_events_waits_history_long_size
performance_schema_events_waits_history_size
performance_schema_hosts_size
performance_schema_max_cond_classes
performance_schema_max_cond_instances
performance_schema_max_digest_length
performance_schema_max_file_classes
performance_schema_max_file_handles
performance_schema_max_file_instances
performance_schema_max_index_stat
performance_schema_max_memory_classes
performance_schema_max_metadata_locks
performance_schema_max_mutex_classes
performance_schema_max_mutex_instances
performance_schema_max_prepared_statements_instances
performance_schema_max_program_instances
performance_schema_max_rwlock_classes
performance_schema_max_rwlock_instances
performance_schema_max_socket_classes
performance_schema_max_socket_instances
performance_schema_max_sql_text_length
performance_schema_max_stage_classes
performance_schema_max_statement_classes
performance_schema_max_statement_stack
performance_schema_max_table_handles
performance_schema_max_table_instances
performance_schema_max_table_lock_stat
performance_schema_max_thread_classes
performance_schema_max_thread_instances
performance_schema_session_connect_attrs_size
performance_schema_setup_actors_size
performance_schema_setup_objects_size
performance_schema_users_size
relay_log_recovery
relay_log_space_limit
replica_type_conversions
report_host
report_password
report_port
report_user
skip_name_resolve
skip_show_database
slave_type_conversions
table_open_cache_instances
thread_handling
thread_stack
tls_version
RESET PERSIST;
# Restart server
# Install version token plugin
##################
INSTALL PLUGIN version_tokens SONAME 'version_token.so';
SELECT @@version_tokens_session_number;
@@version_tokens_session_number
0
SET @@persist_only.version_tokens_session_number=3;
ERROR HY000: Variable 'version_tokens_session_number' is a non persistent read only variable
# return 0 row
SELECT * FROM performance_schema.persisted_variables;
VARIABLE_NAME VARIABLE_VALUE
# Restart server
# return 0 row
SELECT * FROM performance_schema.persisted_variables;
VARIABLE_NAME VARIABLE_VALUE
SELECT VARIABLE_NAME FROM performance_schema.variables_info WHERE VARIABLE_SOURCE = 'PERSISTED';
VARIABLE_NAME
UNINSTALL PLUGIN version_tokens;
Warnings:
Warning 1620 Plugin is busy and will be uninstalled on shutdown
RESET PERSIST;
# Restart server
|
