5.1.5.2 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.1.5, “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 5.1.4, “Server System Variables”, 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.

Table 5.3 Dynamic Variable Summary

Variable NameVariable TypeVariable Scope
auto_increment_incrementnumericGLOBAL | SESSION
auto_increment_offsetnumericGLOBAL | SESSION
autocommitbooleanSESSION
automatic_sp_privilegesbooleanGLOBAL
big_tablesbooleanSESSION
binlog_cache_sizenumericGLOBAL
bulk_insert_buffer_sizenumericGLOBAL | SESSION
character_set_clientstringGLOBAL | SESSION
character_set_connectionstringGLOBAL | SESSION
character_set_databasestringGLOBAL | SESSION
character_set_filesystemstringGLOBAL | SESSION
character_set_resultsstringGLOBAL | SESSION
character_set_serverstringGLOBAL | SESSION
collation_connectionstringGLOBAL | SESSION
collation_databasestringGLOBAL | SESSION
collation_serverstringGLOBAL | SESSION
completion_typenumericGLOBAL | SESSION
concurrent_insertnumericGLOBAL
connect_timeoutnumericGLOBAL
debugstringGLOBAL | SESSION
storage_engineenumerationGLOBAL | SESSION
default_week_formatnumericGLOBAL | SESSION
delay_key_writeenumerationGLOBAL
delayed_insert_limitnumericGLOBAL
delayed_insert_timeoutnumericGLOBAL
delayed_queue_sizenumericGLOBAL
div_precision_incrementnumericGLOBAL | SESSION
engine_condition_pushdownbooleanGLOBAL | SESSION
expire_logs_daysnumericGLOBAL
flushbooleanGLOBAL
flush_timenumericGLOBAL
foreign_key_checksbooleanSESSION
ft_boolean_syntaxstringGLOBAL
group_concat_max_lennumericGLOBAL | SESSION
identitynumericSESSION
init_connectstringGLOBAL
init_slavestringGLOBAL
innodb_autoextend_incrementnumericGLOBAL
innodb_commit_concurrencynumericGLOBAL
innodb_concurrency_ticketsnumericGLOBAL
innodb_fast_shutdownnumericGLOBAL
innodb_flush_log_at_trx_commitenumerationGLOBAL
innodb_max_dirty_pages_pctnumericGLOBAL
innodb_max_purge_lagnumericGLOBAL
innodb_support_xabooleanGLOBAL | SESSION
innodb_sync_spin_loopsnumericGLOBAL
innodb_table_locksbooleanGLOBAL | SESSION
innodb_thread_concurrencynumericGLOBAL
innodb_thread_sleep_delaynumericGLOBAL
innodb_use_legacy_cardinality_algorithmbooleanGLOBAL
insert_idnumericSESSION
interactive_timeoutnumericGLOBAL | SESSION
join_buffer_sizenumericGLOBAL | SESSION
keep_files_on_createbooleanGLOBAL | SESSION
key_buffer_sizenumericGLOBAL
key_cache_age_thresholdnumericGLOBAL
key_cache_block_sizenumericGLOBAL
key_cache_division_limitnumericGLOBAL
last_insert_idnumericSESSION
lc_time_namesstringGLOBAL | SESSION
local_infilebooleanGLOBAL
log_bin_trust_function_creatorsbooleanGLOBAL
log_bin_trust_routine_creatorsbooleanGLOBAL
log_queries_not_using_indexesbooleanGLOBAL
log_warningsnumericGLOBAL | SESSION
long_query_timenumericGLOBAL | SESSION
low_priority_updatesbooleanGLOBAL | SESSION
max_allowed_packetnumericGLOBAL
max_binlog_cache_sizenumericGLOBAL
max_binlog_sizenumericGLOBAL
max_connect_errorsnumericGLOBAL
max_connectionsnumericGLOBAL
max_delayed_threadsnumericGLOBAL | SESSION
max_error_countnumericGLOBAL | SESSION
max_heap_table_sizenumericGLOBAL | SESSION
max_insert_delayed_threadsnumericGLOBAL | SESSION
max_join_sizenumericGLOBAL | SESSION
max_length_for_sort_datanumericGLOBAL | SESSION
max_prepared_stmt_countnumericGLOBAL
max_relay_log_sizenumericGLOBAL
max_seeks_for_keynumericGLOBAL | SESSION
max_sort_lengthnumericGLOBAL | SESSION
max_sp_recursion_depthnumericGLOBAL | SESSION
max_user_connectionsnumericGLOBAL | SESSION
max_write_lock_countnumericGLOBAL
multi_range_countnumericGLOBAL | SESSION
myisam_data_pointer_sizenumericGLOBAL
myisam_max_sort_file_sizenumericGLOBAL
myisam_repair_threadsnumericGLOBAL | SESSION
myisam_sort_buffer_sizenumericGLOBAL | SESSION
myisam_stats_methodenumerationGLOBAL | SESSION
ndb_autoincrement_prefetch_sznumericGLOBAL | SESSION
ndb_cache_check_timenumericGLOBAL
ndb_force_sendbooleanGLOBAL | SESSION
ndb_index_stat_cache_entriesnumericGLOBAL | SESSION
ndb_index_stat_enablebooleanGLOBAL | SESSION
ndb_index_stat_update_freqnumericGLOBAL | SESSION
ndb_use_exact_countbooleanGLOBAL | SESSION
ndb_use_transactionsbooleanGLOBAL | SESSION
net_buffer_lengthnumericGLOBAL | SESSION
net_read_timeoutnumericGLOBAL | SESSION
net_retry_countnumericGLOBAL | SESSION
net_write_timeoutnumericGLOBAL | SESSION
newbooleanGLOBAL | SESSION
old_passwordsbooleanGLOBAL | SESSION
optimizer_prune_levelbooleanGLOBAL | SESSION
optimizer_search_depthnumericGLOBAL | SESSION
preload_buffer_sizenumericGLOBAL | SESSION
profilingbooleanSESSION
profiling_history_sizenumericGLOBAL | SESSION
pseudo_thread_idnumericSESSION
query_alloc_block_sizenumericGLOBAL | SESSION
query_cache_limitnumericGLOBAL
query_cache_min_res_unitnumericGLOBAL
query_cache_sizenumericGLOBAL
query_cache_typeenumerationGLOBAL | SESSION
query_cache_wlock_invalidatebooleanGLOBAL | SESSION
query_prealloc_sizenumericGLOBAL | SESSION
rand_seed1numericSESSION
rand_seed2numericSESSION
range_alloc_block_sizenumericGLOBAL | SESSION
read_buffer_sizenumericGLOBAL | SESSION
read_onlybooleanGLOBAL
read_rnd_buffer_sizenumericGLOBAL | SESSION
relay_log_purgebooleanGLOBAL
rpl_recovery_ranknumericGLOBAL
secure_authbooleanGLOBAL
server_idnumericGLOBAL
slave_compressed_protocolbooleanGLOBAL
slave_net_timeoutnumericGLOBAL
slave_transaction_retriesnumericGLOBAL
slow_launch_timenumericGLOBAL
sort_buffer_sizenumericGLOBAL | SESSION
sql_auto_is_nullbooleanSESSION
sql_big_selectsbooleanSESSION
sql_big_tablesbooleanSESSION
sql_buffer_resultbooleanSESSION
sql_log_binbooleanSESSION
sql_log_offbooleanSESSION
sql_log_updatebooleanSESSION
sql_low_priority_updatesbooleanGLOBAL | SESSION
sql_max_join_sizenumericGLOBAL | SESSION
sql_modesetGLOBAL | SESSION
sql_notesbooleanSESSION
sql_quote_show_createbooleanSESSION
sql_safe_updatesbooleanSESSION
sql_select_limitnumericGLOBAL | SESSION
sql_slave_skip_counternumericGLOBAL
sql_warningsbooleanSESSION
storage_engineenumerationGLOBAL | SESSION
sync_binlognumericGLOBAL
sync_frmbooleanGLOBAL
table_cachenumericGLOBAL
table_lock_wait_timeoutnumericGLOBAL
table_typeenumerationGLOBAL | SESSION
thread_cache_sizenumericGLOBAL
time_zonestringGLOBAL | SESSION
timed_mutexesbooleanGLOBAL
timestampnumericSESSION
tmp_table_sizenumericGLOBAL | SESSION
transaction_alloc_block_sizenumericGLOBAL | SESSION
transaction_prealloc_sizenumericGLOBAL | SESSION
tx_isolationenumerationGLOBAL | SESSION
unique_checksbooleanSESSION
updatable_views_with_limitbooleanGLOBAL | SESSION
wait_timeoutnumericGLOBAL | SESSION