DEVELOPER ZONE ::
Login / Register
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.4, “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 |
automatic_sp_privileges |
boolean | GLOBAL |
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 |
numeric | GLOBAL |
connect_timeout |
numeric | GLOBAL |
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 |
expire_logs_days |
numeric | GLOBAL |
flush |
boolean | GLOBAL |
flush_time |
numeric | GLOBAL |
foreign_key_checks |
boolean | SESSION |
ft_boolean_syntax |
string | 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 |
numeric | SESSION |
interactive_timeout |
numeric |
GLOBAL | SESSION
|
join_buffer_size |
numeric |
GLOBAL | SESSION
|
key_buffer_size |
numeric | GLOBAL |
last_insert_id |
numeric | SESSION |
lc_time_names |
string |
GLOBAL | SESSION
|
local_infile |
boolean | GLOBAL |
log_queries_not_using_indexes |
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_prepared_stmt_count |
numeric | GLOBAL |
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 |
multi_range_count |
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_stats_method |
enum |
GLOBAL | SESSION
|
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
|
profiling |
boolean | SESSION |
profiling_history_size |
numeric | 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_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 |
MySQL Enterprise. Improper configuration of system variables can adversely affect performance and security. The MySQL Network Monitoring and Advisory Service continually monitors system variables and provides expert advice about appropriate settings. For more information see http://www.mysql.com/products/enterprise/advisors.html.
User Comments
At least in versin 4.1.19 old_passwords is boolean, not numeric.
Add your own comment.