Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy

 Dynamic System Variables

Many server system variables are dynamic and can be set at runtime using SET GLOBAL or SET SESSION. You can also obtain their values using SELECT. See Section 5.2.3, “Using System Variables”.

The following table shows the full list of all dynamic system variables. The last column indicates for each variable whether GLOBAL or SESSION (or both) apply. The table also lists session options that can be set with the SET statement. Section 13.5.3, “SET Syntax”, discusses these options.

Variables that have a type of “string” take a string value. Variables that have a type of “numeric” take a numeric value. Variables that have a type of “boolean” can be set to 0, 1, ON or OFF. (If you set them on the command line or in an option file, use the numeric values.) Variables that are marked as “enumeration” normally should be set to one of the available values for the variable, but can also be set to the number that corresponds to the desired enumeration value. For enumerated system variables, the first enumeration value corresponds to 0. This differs from ENUM columns, for which the first enumeration value corresponds to 1.

Variable Name Value Type Type
autocommit boolean SESSION
big_tables boolean SESSION
binlog_cache_size numeric GLOBAL
bulk_insert_buffer_size numeric GLOBAL | SESSION
character_set_client string GLOBAL | SESSION
character_set_connection string GLOBAL | SESSION
character_set_filesystem string GLOBAL | SESSION
character_set_results string GLOBAL | SESSION
character_set_server string GLOBAL | SESSION
collation_connection string GLOBAL | SESSION
collation_server string GLOBAL | SESSION
completion_type numeric GLOBAL | SESSION
concurrent_insert boolean GLOBAL
connect_timeout numeric GLOBAL
convert_character_set string GLOBAL | SESSION
default_week_format numeric GLOBAL | SESSION
delay_key_write OFF | ON | ALL GLOBAL
delayed_insert_limit numeric GLOBAL
delayed_insert_timeout numeric GLOBAL
delayed_queue_size numeric GLOBAL
div_precision_increment numeric GLOBAL | SESSION
engine_condition_pushdown boolean GLOBAL | SESSION
error_count numeric SESSION
event_scheduler boolean GLOBAL
expire_logs_days numeric GLOBAL
flush boolean GLOBAL
flush_time numeric GLOBAL
foreign_key_checks boolean SESSION
ft_boolean_syntax numeric GLOBAL
group_concat_max_len numeric GLOBAL | SESSION
identity numeric SESSION
innodb_autoextend_increment numeric GLOBAL
innodb_commit_concurrency numeric GLOBAL
innodb_concurrency_tickets numeric GLOBAL
innodb_max_dirty_pages_pct numeric GLOBAL
innodb_max_purge_lag numeric GLOBAL
innodb_support_xa boolean GLOBAL | SESSION
innodb_sync_spin_loops numeric GLOBAL
innodb_table_locks boolean GLOBAL | SESSION
innodb_thread_concurrency numeric GLOBAL
innodb_thread_sleep_delay numeric GLOBAL
insert_id boolean SESSION
interactive_timeout numeric GLOBAL | SESSION
join_buffer_size numeric GLOBAL | SESSION
key_buffer_size numeric GLOBAL
last_insert_id numeric SESSION
local_infile boolean GLOBAL
log_warnings numeric GLOBAL
long_query_time numeric GLOBAL | SESSION
low_priority_updates boolean GLOBAL | SESSION
max_allowed_packet numeric GLOBAL | SESSION
max_binlog_cache_size numeric GLOBAL
max_binlog_size numeric GLOBAL
max_connect_errors numeric GLOBAL
max_connections numeric GLOBAL
max_delayed_threads numeric GLOBAL
max_error_count numeric GLOBAL | SESSION
max_heap_table_size numeric GLOBAL | SESSION
max_insert_delayed_threads numeric GLOBAL
max_join_size numeric GLOBAL | SESSION
max_relay_log_size numeric GLOBAL
max_seeks_for_key numeric GLOBAL | SESSION
max_sort_length numeric GLOBAL | SESSION
max_tmp_tables numeric GLOBAL | SESSION
max_user_connections numeric GLOBAL
max_write_lock_count numeric GLOBAL
myisam_stats_method enum GLOBAL | SESSION
multi_read_range numeric GLOBAL | SESSION
myisam_data_pointer_size numeric GLOBAL
log_bin_trust_function_creators boolean GLOBAL
myisam_max_sort_file_size numeric GLOBAL | SESSION
myisam_repair_threads numeric GLOBAL | SESSION
myisam_sort_buffer_size numeric GLOBAL | SESSION
myisam_use_mmap boolean GLOBAL
ndb_extra_logging numeric GLOBAL
net_buffer_length numeric GLOBAL | SESSION
net_read_timeout numeric GLOBAL | SESSION
net_retry_count numeric GLOBAL | SESSION
net_write_timeout numeric GLOBAL | SESSION
old_passwords numeric GLOBAL | SESSION
optimizer_prune_level numeric GLOBAL | SESSION
optimizer_search_depth numeric GLOBAL | SESSION
preload_buffer_size numeric GLOBAL | SESSION
query_alloc_block_size numeric GLOBAL | SESSION
query_cache_limit numeric GLOBAL
query_cache_size numeric GLOBAL
query_cache_type enumeration GLOBAL | SESSION
query_cache_wlock_invalidate boolean GLOBAL | SESSION
query_prealloc_size numeric GLOBAL | SESSION
range_alloc_block_size numeric GLOBAL | SESSION
read_buffer_size numeric GLOBAL | SESSION
read_only numeric GLOBAL
read_rnd_buffer_size numeric GLOBAL | SESSION
rpl_recovery_rank numeric GLOBAL
safe_show_database boolean GLOBAL
secure_auth boolean GLOBAL
server_id numeric GLOBAL
slave_compressed_protocol boolean GLOBAL
slave_net_timeout numeric GLOBAL
slave_transaction_retries numeric GLOBAL
slow_launch_time numeric GLOBAL
sort_buffer_size numeric GLOBAL | SESSION
sql_auto_is_null boolean SESSION
sql_big_selects boolean SESSION
sql_big_tables boolean SESSION
sql_buffer_result boolean SESSION
sql_log_bin boolean SESSION
sql_log_off boolean SESSION
sql_log_update boolean SESSION
sql_low_priority_updates boolean GLOBAL | SESSION
sql_max_join_size numeric GLOBAL | SESSION
sql_mode enumeration GLOBAL | SESSION
sql_notes boolean SESSION
sql_quote_show_create boolean SESSION
sql_safe_updates boolean SESSION
sql_select_limit numeric SESSION
sql_slave_skip_counter numeric GLOBAL
updatable_views_with_limit enumeration GLOBAL | SESSION
sql_warnings boolean SESSION
sync_binlog numeric GLOBAL
sync_frm boolean GLOBAL
storage_engine enumeration GLOBAL | SESSION
table_definition_cache numeric GLOBAL
table_open_cache numeric GLOBAL
table_type enumeration GLOBAL | SESSION
thread_cache_size numeric GLOBAL
time_zone string GLOBAL | SESSION
timestamp boolean SESSION
tmp_table_size enumeration GLOBAL | SESSION
transaction_alloc_block_size numeric GLOBAL | SESSION
transaction_prealloc_size numeric GLOBAL | SESSION
tx_isolation enumeration GLOBAL | SESSION
unique_checks boolean SESSION
wait_timeout numeric GLOBAL | SESSION
warning_count numeric SESSION

  Published under the terms of the GNU General Public License Design by Interspire