WL#9787: Allow SET PERSIST to set read-only variables too

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Currently SET PERSIST can only set variables that are settable as SET GLOBAL.
Expand it to support setting read-only variables too, despite the fact that the
new values will take effect only at the next server restart.

Note that few variables will still not be settable. See HLS/FRQ for the complete 
list and the reasoning. 
R1) Ability to persist a static variable to persistent config file
    mysqld-auto.cnf. This is done using new syntax SET PERSIST_ONLY statement.
R2) New syntax: SET PERSIST_ONLY <variable>=<value>; OR 
    SET @@persist_only.<variable>=<value>; OR
    SET PERSIST_ONLY <variable>=<value>,<variable>=<value>; OR
    SET @@persist_only.<variable>=<value>,@@persist_only.<variable>=<value>;
R3) Clause PERSIST_ONLY applies to all variables in the list following the
    first one if not specified. This is in consistent to other variants of SET
    statement.
R4) Clause PERSIST_ONLY or @@persist_only in SET statement will not change the 
    value of the variable being set.
R5) SET PERSIST_ONLY or SET @@persist_only .. statement can also be used with
    dynamic variables and plugin variables provided plugin is loaded.
R6) Few variables are not allowed with this new syntax, ex:persisted_globals_load
    For complete list refer HLS.
R7) User should have either SUPER OR PERSIST_RO_VARIABLES_ADMIN and 
    SYSTEM_VARIABLES_ADMIN privilege to persist read only variables.
New Syntax:
-----------
SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | [GLOBAL | SESSION | PERSIST | PERSIST_ONLY ] system_var_name = expr
    | [@@global. | @@session. | @@persist. | @@persist_only. |
       @@]system_var_name = expr

The new PERSIST_ONLY option lets global variable name and its corresponding
value to be stored in a mysqld-auto.cnf file with an exception of not changing
the current global value.

This new syntax provides an opportunity to persist read only variables.

Example:
mysql> SET PERSIST_ONLY max_connections=42;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@max_connections;
+-------------------------+
| @@max_connections       |
+-------------------------+
| 151                     |
+-------------------------+
1 row in set (0.00 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.variables_info where variable_name = 
'max_connections'\G
*************************** 1. row ***************************
  VARIABLE_NAME: max_connections
VARIABLE_SOURCE: PERSISTED
  VARIABLE_PATH: C:\Users\bhsatish\wl9719\mysql-trunk\data\mysqld-auto.cnf
      MIN_VALUE: 0
      MAX_VALUE: 0
1 row in set (0.01 sec)

Behavior of SET PERSIST_ONLY/@@persist_only ... statement remains same for
both static and dynamic global variables.

New privilege:
--------------
PERSIST_RO_VARIABLES_ADMIN dynamic privilege will be registered with mysql
server component. User should have this privilege granted in addition to
SYSTEM_VARIABLES_ADMIN privilege in order to execute
SET @@persist_only statement, else "Access Denied" error will be reported.

Plugin should define what all plugin specific read only variables can be 
persisted. If a non persistent plugin variable is being persisted then server
reports error.
example:
mysql> set persist_only audit_log_file="\tmp\";
ERROR 1238 (HY000): Variable 'audit_log_file' is a read only non persistent 
variable.

Below is the complete list of read only variables extracted from 
https://dev.mysql.com/doc/refman/8.0/en/mysqld-option-tables.html

audit_log_buffer_size
audit_log_current_session
audit_log_file
audit_log_filter_id
audit_log_format
audit_log_policy
audit_log_strategy
auto_generate_certs
back_log
basedir
binlog_gtid_simple_recovery
bind_address
character_sets_dir
character_set_system
core_file
daemon_memcached_enable_binlog
daemon_memcached_engine_lib_name
daemon_memcached_engine_lib_path
daemon_memcached_r_batch_size
daemon_memcached_w_batch_size
datadir
date_format
datetime_format
default_authentication_plugin
disabled_storage_engines
ft_max_word_len
ft_min_word_len
ft_query_expansion_limit
ft_stopword_file
have_compress
have_crypt
have_dynamic_loading
have_geometry
have_openssl
have_profiling
have_query_cache
have_rtree_keys
have_ssl
have_statement_timeout
have_symlink
hostname
ignore_builtin_innodb
init_file
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_debug
innodb_buffer_pool_instances
innodb_buffer_pool_load_at_startup
innodb_data_file_path
innodb_data_home_dir
innodb_doublewrite
innodb_flush_method
innodb_force_load_corrupted
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_log_file_size
innodb_log_files_in_group
innodb_log_group_home_dir
innodb_numa_interleave
innodb_open_files
innodb_page_cleaners
innodb_page_size
innodb_purge_threads
innodb_read_io_threads
innodb_read_only
innodb_rollback_on_timeout
innodb_scan_directories
innodb_sort_buffer_size
innodb_sync_array_size
innodb_sync_debug
innodb_temp_data_file_path
innodb_undo_directory
innodb_undo_tablespaces
innodb_use_native_aio
innodb_version
innodb_write_io_threads
language
large_files_support
large_pages
large_page_size
lc_messages_dir
license
log_error
locked_in_memory
log_bin
log_bin_basename
log_bin_index
log_bin_use_v1_row_events
log_slave_updates
lower_case_file_system
lower_case_table_names
max_digest_length
mecab_rc_file
metadata_locks_cache_size
metadata_locks_hash_instances
myisam_mmap_size
myisam_recover_options
mysqlx_bind_address
mysqlx_port
mysqlx_port_open_timeout
mysqlx_socket
mysqlx_ssl
mysqlx_ssl_ca
mysqlx_ssl_capath
mysqlx_ssl_cert
mysqlx_ssl_cipher
mysqlx_ssl_crl
mysqlx_ssl_crlpath
mysqlx_ssl_key
named_pipe
ndb_batch_size
ndb_cluster_connection_pool
ndb_cluster_connection_pool_nodeids
ndb_log_apply_status
ndb_log_transaction_id
ndb_optimized_node_selection
Ndb_slave_max_replicated_epoch
ndb_version
ndb_version_string
ndb_wait_setup
ndbinfo_database
ndbinfo_version
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
persisted_globals_load
pid_file
plugin_dir
port
protocol_version
relay_log
relay_log_basename
relay_log_index
relay_log_info_file
relay_log_recovery
relay_log_space_limit
server_uuid
sha256_password_auto_generate_rsa_keys
sha256_password_private_key_path
sha256_password_public_key_path
shared_memory
shared_memory_base_name
skip_external_locking
skip_name_resolve
skip_networking
skip_show_database
slave_load_tmpdir
slave_type_conversions
socket
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_crl
ssl_crlpath
ssl_key
system_time_zone
table_open_cache_instances
thread_handling
thread_stack
time_format
tls_version
tmpdir
version
version_comment
version_compile_machine
version_compile_os
version_tokens_session_number
report_host
report_port
report_password
report_user
daemon_memcached_option

From the above list we will allow following list of variables to be persisted.
audit_log_buffer_size
audit_log_policy
audit_log_strategy
back_log
binlog_gtid_simple_recovery
daemon_memcached_enable_binlog
daemon_memcached_r_batch_size
daemon_memcached_w_batch_size
date_format
datetime_format
disabled_storage_engines
ft_max_word_len
ft_min_word_len
ft_query_expansion_limit
ignore_builtin_innodb
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_debug
innodb_buffer_pool_instances
innodb_doublewrite
innodb_flush_method
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_log_file_size
innodb_log_files_in_group
innodb_numa_interleave
innodb_open_files
innodb_page_cleaners
innodb_purge_threads
innodb_read_io_threads
innodb_rollback_on_timeout
innodb_sort_buffer_size
innodb_sync_array_size
innodb_sync_debug
innodb_use_native_aio
innodb_write_io_threads
large_pages
log_slave_updates
lower_case_table_names
max_digest_length
metadata_locks_cache_size
metadata_locks_hash_instances
myisam_mmap_size
myisam_recover_options
mysqlx_bind_address
mysqlx_port
mysqlx_port_open_timeout
mysqlx_socket
mysqlx_ssl
mysqlx_ssl_ca
mysqlx_ssl_capath
mysqlx_ssl_cert
mysqlx_ssl_cipher
mysqlx_ssl_crl
mysqlx_ssl_crlpath
mysqlx_ssl_key
ndb_batch_size
ndb_cluster_connection_pool
ndb_cluster_connection_pool_nodeids
ndb_log_apply_status
ndb_log_transaction_id
ndb_optimized_node_selection
Ndb_slave_max_replicated_epoch
ndb_wait_setup
ndbinfo_database
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
skip_name_resolve
skip_show_database
slave_type_conversions
ssl_cipher
table_open_cache_instances
thread_handling
thread_stack
time_format
tls_version
report_host
report_port
report_password
report_user


Global static variables which should not be persisted is categorized as below:
CATEGORY 1. All variables that accept file system (absolute or relative) paths 
or file names from which server reads or writes.
CATEGORY 2. All variables that don't have an immediate effect on the server's 
operation.
CATEGORY 3. All variables that deal with network I/O outlets should not be 
persisted.
CATEGORY 4. All variables that are using read-onlyness as a security tool.
CATEGORY 5. All variables which cause chicken-and-egg effects.
CATEGORY 6. All variables which cause corruption or cause server restart to 
fail.
CATEGORY 7. All variables which cannot be set at command line.
CATEGORY 8. All variables which are not actually configuration variables.

Variables which fall under CATEGORY 1:
--------------------------------------
basedir, audit_log_file, character_sets_dir, ft_stopword_file, ignore_db_dirs,
init_file, lc_messages_dir, log_error, mecab_rc_file, pid_file,
plugin_dir, secure_file_priv, sha256_password_private_key_path,
sha256_password_public_key_path, slave_load_tmpdir, ssl_ca, ssl_capath,
ssl_cert, ssl_crl, ssl_crlpath, ssl_key, tmpdir, relay_log, relay_log_index,
daemon_memcached_engine_lib_name, daemon_memcached_engine_lib_path,
innodb_data_home_dir, innodb_log_group_home_dir, innodb_scan_directories,
innodb_temp_data_file_path, innodb_undo_directory, log_bin_basename,
log_bin_index, relay_log_info_file, relay_log_basename, 
daemon_memcached_option

Variables which fall under CATEGORY 3:
--------------------------------------
bind_address, named_pipe, port, shared_memory, shared_memory_base_name,
skip_networking, socket, 

Variables which fall under CATEGORY 4:
--------------------------------------
default_authentication_plugin, sha256_password_auto_generate_rsa_keys,
auto_generate_certs, audit_log_current_session, innodb_read_only, core_file

Variables which fall under CATEGORY 5:
--------------------------------------
persisted_globals_load, datadir, 

Variables which fall under CATEGORY 6:
--------------------------------------
audit_log_format, innodb_data_file_path, log_bin_use_v1_row_events,
innodb_force_load_corrupted, innodb_page_size, innodb_undo_tablespaces

Variables which fall under CATEGORY 7:
--------------------------------------
version, version_comment, version_compile_machine, version_compile_os,
version_tokens_session_number, have_compress, have_crypt, have_dynamic_loading, 
have_geometry, have_openssl, have_profiling, have_query_cache, have_rtree_keys, 
have_ssl, have_statement_timeout, have_symlink, hostname, large_files_support,
large_page_size, license, locked_in_memory, protocol_version,
innodb_version, lower_case_file_system, innodb_buffer_pool_load_at_startup,
character_set_system, system_time_zone, server_uuid, log_bin,
skip_external_locking, audit_log_filter_id

For the above blacklisted static variables if SET PERSIST_ONLY statement is
executed we report an error.
example:
mysql> set persist_only persisted_globals_load=1;
ERROR 1238 (HY000): Variable 'persisted_globals_load' is a read only non 
persistent variable

Few command line options that have corresponding system variables mapped ex:
character-set-server, default-storage-engine etc, these variables can be 
persisted. All command line options which don't have any equivalent system 
variables cannot be persisted using SET PERSIST statement. Example:
defaults-extra-file, defaults-file, defaults-group-suffix,
enable-cleartext-plugin, login-path, no-defaults, print-defaults etc