The MySQL server maintains many system variables that indicate how
it is configured. Each system variable has a default value. System
variables can be set at server startup using options on the
command line or in an option file. Most of them can be changed
dynamically while the server is running by means of the
SET
statement, which enables you to modify operation of the server
without having to stop and restart it. You can refer to system
variable values in expressions.
There are several ways to see the names and values of system variables:
To see the values that a server will use based on its compiled-in defaults and any option files that it reads, use this command:
mysqld --verbose --help
To see the values that a server will use based on its compiled-in defaults, ignoring the settings in any option files, use this command:
mysqld --no-defaults --verbose --help
To see the current values used by a running server, use the
SHOW VARIABLES
statement.
This section provides a description of each system variable. Variables with no version indicated are present in all MySQL 5.1 releases. For historical information concerning their implementation, please see http://dev.mysql.com/doc/refman/5.0/en/, and http://dev.mysql.com/doc/refman/4.1/en/.
The following table lists all available system variables:
Table 5.2. System Variable Summary
Name | Cmd-Line | Option file | System Var | Var Scope | Dynamic |
---|---|---|---|---|---|
auto_increment_increment | Yes | Yes | Yes | Both | Yes |
auto_increment_offset | Yes | Yes | Yes | Both | Yes |
autocommit | Yes | Session | Yes | ||
automatic_sp_privileges | Yes | Global | Yes | ||
back_log | Yes | Yes | Yes | Global | No |
basedir | Yes | Yes | Yes | Global | No |
big-tables | Yes | Yes | Yes | ||
- Variable: big_tables | Yes | Session | Yes | ||
binlog_cache_size | Yes | Yes | Yes | Global | Yes |
binlog-format | Yes | Yes | Yes | ||
- Variable: binlog_format | Yes | Both | Yes | ||
bulk_insert_buffer_size | Yes | Yes | Yes | Both | Yes |
character_set_client | Yes | Both | Yes | ||
character_set_connection | Yes | Both | Yes | ||
character_set_database[a] | Yes | Both | Yes | ||
character-set-filesystem | Yes | Yes | Yes | ||
- Variable: character_set_filesystem | Yes | Both | Yes | ||
character_set_results | Yes | Both | Yes | ||
character-set-server | Yes | Yes | Yes | ||
- Variable: character_set_server | Yes | Both | Yes | ||
character_set_system | Yes | Global | No | ||
character-sets-dir | Yes | Yes | No | ||
- Variable: character_sets_dir | Yes | Global | No | ||
collation_connection | Yes | Both | Yes | ||
collation_database[b] | Yes | Both | Yes | ||
collation-server | Yes | Yes | Yes | ||
- Variable: collation_server | Yes | Both | Yes | ||
completion_type | Yes | Yes | Yes | Both | Yes |
concurrent_insert | Yes | Yes | Yes | Global | Yes |
connect_timeout | Yes | Yes | Yes | Global | Yes |
datadir | Yes | Yes | Yes | Global | No |
date_format | Yes | Both | No | ||
datetime_format | Yes | Yes | Yes | Both | No |
debug | Yes | Yes | Yes | Both | Yes |
debug_sync | Yes | Both | Yes | ||
default_week_format | Yes | Yes | Yes | Both | Yes |
delay-key-write | Yes | Yes | Yes | ||
- Variable: delay_key_write | Yes | Global | Yes | ||
delayed_insert_limit | Yes | Yes | Yes | Global | Yes |
delayed_insert_timeout | Yes | Yes | Yes | Global | Yes |
delayed_queue_size | Yes | Yes | Yes | Global | Yes |
div_precision_increment | Yes | Yes | Yes | Both | Yes |
engine-condition-pushdown | Yes | Yes | Yes | ||
- Variable: engine_condition_pushdown | Yes | Both | Yes | ||
error_count | Yes | Session | No | ||
event-scheduler | Yes | Yes | Yes | ||
- Variable: event_scheduler | Yes | Global | Yes | ||
expire_logs_days | Yes | Yes | Yes | Global | Yes |
flush | Yes | Yes | Yes | Global | Yes |
flush_time | Yes | Yes | Yes | Global | Yes |
foreign_key_checks | Yes | Session | Yes | ||
ft_boolean_syntax | Yes | Yes | Yes | Global | Yes |
ft_max_word_len | Yes | Yes | Yes | Global | No |
ft_min_word_len | Yes | Yes | Yes | Global | No |
ft_query_expansion_limit | Yes | Yes | Yes | Global | No |
ft_stopword_file | Yes | Yes | Yes | Global | No |
general-log | Yes | Yes | Yes | ||
- Variable: general_log | Yes | Global | Yes | ||
general_log_file | Yes | Yes | Yes | Global | Yes |
group_concat_max_len | Yes | Yes | Yes | Both | Yes |
have_archive | Yes | Global | No | ||
have_blackhole_engine | Yes | Global | No | ||
have_community_features | Yes | Global | No | ||
have_compress | Yes | Global | No | ||
have_crypt | Yes | Global | No | ||
have_csv | Yes | Global | No | ||
have_dynamic_loading | Yes | Global | No | ||
have_example_engine | Yes | Global | No | ||
have_federated_engine | Yes | Global | No | ||
have_geometry | Yes | Global | No | ||
have_innodb | Yes | Global | No | ||
have_isam | Yes | Global | No | ||
have_merge_engine | Yes | Global | No | ||
have_ndbcluster | Yes | Global | No | ||
have_openssl | Yes | Global | No | ||
have_partitioning | Yes | Global | No | ||
have_query_cache | Yes | Global | No | ||
have_raid | Yes | Global | No | ||
have_row_based_replication | Yes | Global | No | ||
have_rtree_keys | Yes | Global | No | ||
have_ssl | Yes | Global | No | ||
have_symlink | Yes | Global | No | ||
hostname | Yes | Global | No | ||
identity | Yes | Session | Yes | ||
ignore_builtin_innodb | Yes | Yes | Yes | Global | No |
init_connect | Yes | Yes | Yes | Global | Yes |
init-file | Yes | Yes | No | ||
- Variable: init_file | Yes | Global | No | ||
init_slave | Yes | Yes | Yes | Global | Yes |
innodb_adaptive_flushing | Yes | Yes | Yes | Global | Yes |
innodb_adaptive_hash_index | Yes | Yes | Yes | Global | No |
innodb_additional_mem_pool_size | Yes | Yes | Yes | Global | No |
innodb_autoextend_increment | Yes | Yes | Yes | Global | Yes |
innodb_autoinc_lock_mode | Yes | Yes | Yes | Global | No |
innodb_buffer_pool_awe_mem_mb | Yes | Yes | Yes | Global | No |
innodb_buffer_pool_size | Yes | Yes | Yes | Global | No |
innodb_change_buffering | Yes | Yes | Yes | Global | Yes |
innodb_checksums | Yes | Yes | Yes | Global | No |
innodb_commit_concurrency | Yes | Yes | Yes | Global | Yes |
innodb_concurrency_tickets | Yes | Yes | Yes | Global | Yes |
innodb_data_file_path | Yes | Yes | Yes | Global | No |
innodb_data_home_dir | Yes | Yes | Yes | Global | No |
innodb_doublewrite | Yes | Yes | Yes | Global | No |
innodb_fast_shutdown | Yes | Yes | Yes | Global | Yes |
innodb_file_format | Yes | Yes | Yes | Global | Yes |
innodb_file_format_check | Yes | Yes | Yes | Global | Yes |
innodb_file_io_threads | Yes | Yes | Yes | Global | No |
innodb_file_per_table | Yes | Yes | Yes | Global | No |
innodb_flush_log_at_trx_commit | Yes | Yes | Yes | Global | Yes |
innodb_flush_method | Yes | Yes | Yes | Global | No |
innodb_force_recovery | Yes | Yes | Yes | Global | No |
innodb_io_capacity | Yes | Yes | Yes | Global | No |
innodb_lock_wait_timeout | Yes | Yes | Yes | Both | Yes |
innodb_locks_unsafe_for_binlog | Yes | Yes | Yes | Global | No |
innodb_log_arch_dir | Yes | Yes | Yes | Global | No |
innodb_log_archive | Yes | Yes | Yes | Global | No |
innodb_log_buffer_size | Yes | Yes | Yes | Global | No |
innodb_log_file_size | Yes | Yes | Yes | Global | No |
innodb_log_files_in_group | Yes | Yes | Yes | Global | No |
innodb_log_group_home_dir | Yes | Yes | Yes | Global | No |
innodb_max_dirty_pages_pct | Yes | Yes | Yes | Global | Yes |
innodb_max_purge_lag | Yes | Yes | Yes | Global | Yes |
innodb_mirrored_log_groups | Yes | Yes | Yes | Global | No |
innodb_old_blocks_pct | Yes | Yes | Yes | Global | Yes |
innodb_old_blocks_time | Yes | Yes | Yes | Global | Yes |
innodb_open_files | Yes | Yes | Yes | Global | No |
innodb_read_ahead_threshold | Yes | Yes | Yes | Global | Yes |
innodb_read_io_threads | Yes | Yes | Yes | Global | No |
innodb_replication_delay | Yes | Yes | Yes | Global | Yes |
innodb_rollback_on_timeout | Yes | Yes | Yes | Global | No |
innodb_spin_wait_delay | Yes | Yes | Yes | Global | Yes |
innodb_stats_on_metadata | Yes | Yes | Yes | Global | Yes |
innodb_stats_sample_pages | Yes | Yes | Yes | Global | Yes |
innodb_strict_mode | Yes | Yes | Yes | Both | Yes |
innodb_support_xa | Yes | Yes | Yes | Both | Yes |
innodb_sync_spin_loops | Yes | Yes | Yes | Global | Yes |
innodb_table_locks | Yes | Yes | Yes | Both | Yes |
innodb_thread_concurrency | Yes | Yes | Yes | Global | Yes |
innodb_thread_sleep_delay | Yes | Yes | Yes | Global | Yes |
innodb_use_legacy_cardinality_algorithm | Yes | Yes | Yes | Global | Yes |
innodb_use_sys_malloc | Yes | Yes | Yes | Global | No |
innodb_version | Yes | Global | No | ||
innodb_write_io_threads | Yes | Yes | Yes | Global | No |
insert_id | Yes | Session | Yes | ||
interactive_timeout | Yes | Yes | Yes | Both | Yes |
join_buffer_size | Yes | Yes | Yes | Both | Yes |
keep_files_on_create | Yes | Yes | Yes | Both | Yes |
key_buffer_size | Yes | Yes | Yes | Global | Yes |
key_cache_age_threshold | Yes | Yes | Yes | Global | Yes |
key_cache_block_size | Yes | Yes | Yes | Global | Yes |
key_cache_division_limit | Yes | Yes | Yes | Global | Yes |
language | Yes | Yes | Yes | Global | No |
large_files_support | Yes | Global | No | ||
large_page_size | Yes | Global | No | ||
large-pages | Yes | Yes | No | ||
- Variable: large_pages | Yes | Global | No | ||
last_insert_id | Yes | Session | Yes | ||
lc_time_names | Yes | Both | Yes | ||
license | Yes | Global | No | ||
local_infile | Yes | Global | Yes | ||
locked_in_memory | Yes | Global | No | ||
log | Yes | Yes | Yes | Global | Yes |
log_bin | Yes | Global | No | ||
log-bin | Yes | Yes | Yes | Global | No |
log-bin-trust-function-creators | Yes | Yes | Yes | ||
- Variable: log_bin_trust_function_creators | Yes | Global | Yes | ||
log-bin-trust-routine-creators | Yes | Yes | Yes | ||
- Variable: log_bin_trust_routine_creators | Yes | Global | Yes | ||
log-error | Yes | Yes | No | ||
- Variable: log_error | Yes | Global | No | ||
log-output | Yes | Yes | Yes | ||
- Variable: log_output | Yes | Global | Yes | ||
log-queries-not-using-indexes | Yes | Yes | Yes | ||
- Variable: log_queries_not_using_indexes | Yes | Global | Yes | ||
log-slave-updates | Yes | Yes | No | ||
- Variable: log_slave_updates | Yes | Global | No | ||
log-slow-queries | Yes | Yes | Yes | ||
- Variable: log_slow_queries | Yes | Global | Yes | ||
log-warnings | Yes | Yes | Yes | ||
- Variable: log_warnings | Yes | Both | Yes | ||
long_query_time | Yes | Yes | Yes | Both | Yes |
low-priority-updates | Yes | Yes | Yes | ||
- Variable: low_priority_updates | Yes | Both | Yes | ||
lower_case_file_system | Yes | Yes | Yes | Global | No |
lower_case_table_names | Yes | Yes | Yes | Global | No |
master-bind | Yes | Yes | Yes | No | |
max_allowed_packet | Yes | Yes | Yes | Both | Yes |
max_binlog_cache_size | Yes | Yes | Yes | Global | Yes |
max_binlog_size | Yes | Yes | Yes | Global | Yes |
max_connect_errors | Yes | Yes | Yes | Global | Yes |
max_connections | Yes | Yes | Yes | Global | Yes |
max_delayed_threads | Yes | Yes | Yes | Both | Yes |
max_error_count | Yes | Yes | Yes | Both | Yes |
max_heap_table_size | Yes | Yes | Yes | Both | Yes |
max_insert_delayed_threads | Yes | Both | Yes | ||
max_join_size | Yes | Yes | Yes | Both | Yes |
max_length_for_sort_data | Yes | Yes | Yes | Both | Yes |
max_prepared_stmt_count | Yes | Yes | Yes | Global | Yes |
max_relay_log_size | Yes | Yes | Yes | Global | Yes |
max_seeks_for_key | Yes | Yes | Yes | Both | Yes |
max_sort_length | Yes | Yes | Yes | Both | Yes |
max_sp_recursion_depth | Yes | Yes | Yes | Both | Yes |
max_tmp_tables | Yes | Yes | Yes | Both | Yes |
max_user_connections | Yes | Yes | Yes | Both | Yes |
max_write_lock_count | Yes | Yes | Yes | Global | Yes |
memlock | Yes | Yes | Yes | Global | No |
min-examined-row-limit | Yes | Yes | Yes | Both | Yes |
multi_range_count | Yes | Yes | Yes | Both | Yes |
myisam_data_pointer_size | Yes | Yes | Yes | Global | Yes |
myisam_max_sort_file_size | Yes | Yes | Yes | Global | Yes |
myisam_mmap_size | Yes | Yes | Yes | Global | No |
myisam_recover_options | Yes | Global | No | ||
myisam_repair_threads | Yes | Yes | Yes | Both | Yes |
myisam_sort_buffer_size | Yes | Yes | Yes | Both | Yes |
myisam_stats_method | Yes | Yes | Yes | Both | Yes |
myisam_use_mmap | Yes | Yes | Yes | Global | Yes |
named_pipe | Yes | Global | No | ||
ndb_autoincrement_prefetch_sz | Yes | Yes | Yes | Both | Yes |
ndb-batch-size | Yes | Yes | Yes | Global | No |
ndb_cache_check_time | Yes | Yes | Yes | Global | Yes |
ndb_extra_logging | Yes | Yes | Yes | Global | Yes |
ndb_force_send | Yes | Yes | Yes | Both | Yes |
ndb_log_empty_epochs | Yes | Yes | Yes | Global | Yes |
ndb_log_orig | Yes | Global | No | ||
ndb-log-update-as-write | Yes | Yes | Yes | Global | Yes |
ndb_log_updated_only | Yes | Yes | Yes | Global | Yes |
ndb_optimization_delay | Yes | Global | Yes | ||
ndb_table_no_logging | Yes | Session | Yes | ||
ndb_table_temporary | Yes | Session | Yes | ||
ndb_use_copying_alter_table | Yes | Both | No | ||
ndb_use_exact_count | Yes | Both | Yes | ||
ndb_use_transactions | Yes | Yes | Yes | Both | Yes |
ndb_wait_connected | Yes | Yes | Yes | No | |
ndb-wait-setup | Yes | Yes | Yes | No | |
net_buffer_length | Yes | Yes | Yes | Both | Yes |
net_read_timeout | Yes | Yes | Yes | Both | Yes |
net_retry_count | Yes | Yes | Yes | Both | Yes |
net_write_timeout | Yes | Yes | Yes | Both | Yes |
new | Yes | Yes | Yes | Both | Yes |
old | Yes | Yes | Yes | Global | No |
old-alter-table | Yes | Yes | Yes | ||
- Variable: old_alter_table | Yes | Both | Yes | ||
old-passwords | Yes | Yes | Yes | ||
- Variable: old_passwords | Yes | Both | Yes | ||
open-files-limit | Yes | Yes | No | ||
- Variable: open_files_limit | Yes | Global | No | ||
optimizer_prune_level | Yes | Yes | Yes | Both | Yes |
optimizer_search_depth | Yes | Yes | Yes | Both | Yes |
optimizer_switch | Yes | Yes | Yes | Both | Yes |
partition | Yes | Yes | No | ||
- Variable: have_partitioning | Yes | Global | No | ||
pid-file | Yes | Yes | No | ||
- Variable: pid_file | Yes | Global | No | ||
plugin_dir | Yes | Yes | Yes | Global | No |
port | Yes | Yes | Yes | Global | No |
preload_buffer_size | Yes | Yes | Yes | Both | Yes |
prepared_stmt_count | Yes | Global | No | ||
profiling | Yes | Session | Yes | ||
profiling_history_size | Yes | Both | Yes | ||
protocol_version | Yes | Global | No | ||
pseudo_thread_id | Yes | Session | Yes | ||
query_alloc_block_size | Yes | Yes | Yes | Both | Yes |
query_cache_limit | Yes | Yes | Yes | Global | Yes |
query_cache_min_res_unit | Yes | Yes | Yes | Global | Yes |
query_cache_size | Yes | Yes | Yes | Global | Yes |
query_cache_type | Yes | Yes | Yes | Both | Yes |
query_cache_wlock_invalidate | Yes | Yes | Yes | Both | Yes |
query_prealloc_size | Yes | Yes | Yes | Both | Yes |
rand_seed1 | Yes | Session | Yes | ||
rand_seed2 | Yes | Session | Yes | ||
range_alloc_block_size | Yes | Yes | Yes | Both | Yes |
read_buffer_size | Yes | Yes | Yes | Both | Yes |
read_only | Yes | Yes | Yes | Global | Yes |
read_rnd_buffer_size | Yes | Yes | Yes | Both | Yes |
relay_log_purge | Yes | Yes | Yes | Global | Yes |
relay_log_space_limit | Yes | Yes | Yes | Global | No |
report-host | Yes | Yes | No | ||
- Variable: report_host | Yes | Global | No | ||
report-password | Yes | Yes | No | ||
- Variable: report_password | Yes | Global | No | ||
report-port | Yes | Yes | No | ||
- Variable: report_port | Yes | Global | No | ||
report-user | Yes | Yes | No | ||
- Variable: report_user | Yes | Global | No | ||
rpl_recovery_rank | Yes | Global | Yes | ||
safe-show-database | Yes | Yes | Yes | Global | Yes |
secure-auth | Yes | Yes | Yes | ||
- Variable: secure_auth | Yes | Global | Yes | ||
secure-file-priv | Yes | Yes | No | ||
- Variable: secure_file_priv | Yes | Global | No | ||
server-id | Yes | Yes | Yes | ||
- Variable: server_id | Yes | Global | Yes | ||
shared_memory | Yes | Global | No | ||
shared_memory_base_name | Yes | Global | No | ||
skip-external-locking | Yes | Yes | No | ||
- Variable: skip_external_locking | Yes | Global | No | ||
skip-networking | Yes | Yes | No | ||
- Variable: skip_networking | Yes | Global | No | ||
skip-show-database | Yes | Yes | No | ||
- Variable: skip_show_database | Yes | Global | No | ||
slave-allow-batching | Yes | Yes | Yes | ||
- Variable: slave_allow_batching | Yes | Global | Yes | ||
slave_compressed_protocol | Yes | Yes | Yes | Global | Yes |
slave_exec_mode | Yes | Global | Yes | ||
slave-load-tmpdir | Yes | Yes | No | ||
- Variable: slave_load_tmpdir | Yes | Global | No | ||
slave-net-timeout | Yes | Yes | Yes | ||
- Variable: slave_net_timeout | Yes | Global | Yes | ||
slave-skip-errors | Yes | Yes | No | ||
- Variable: slave_skip_errors | Yes | Global | No | ||
slave_transaction_retries | Yes | Yes | Yes | Global | Yes |
slave_type_conversions | Yes | Yes | Yes | Global | No |
slow_launch_time | Yes | Yes | Yes | Global | Yes |
slow-query-log | Yes | Yes | Yes | ||
- Variable: slow_query_log | Yes | Global | Yes | ||
slow_query_log_file | Yes | Yes | Yes | Global | Yes |
socket | Yes | Yes | Yes | Global | No |
sort_buffer_size | Yes | Yes | Yes | Both | Yes |
sql_auto_is_null | Yes | Session | Yes | ||
sql_big_selects | Yes | Session | Yes | ||
sql_big_tables | Yes | Session | Yes | ||
sql_buffer_result | Yes | Session | Yes | ||
sql_log_bin | Yes | Session | Yes | ||
sql_log_off | Yes | Session | Yes | ||
sql_log_update | Yes | Session | Yes | ||
sql_low_priority_updates | Yes | Both | Yes | ||
sql_max_join_size | Yes | Both | Yes | ||
sql-mode | Yes | Yes | Yes | ||
- Variable: sql_mode | Yes | Both | Yes | ||
sql_notes | Yes | Session | Yes | ||
sql_quote_show_create | Yes | Session | Yes | ||
sql_safe_updates | Yes | Session | Yes | ||
sql_select_limit | Yes | Both | Yes | ||
sql_slave_skip_counter | Yes | Global | Yes | ||
sql_warnings | Yes | Session | Yes | ||
ssl-ca | Yes | Yes | No | ||
- Variable: ssl_ca | Yes | Global | No | ||
ssl-capath | Yes | Yes | No | ||
- Variable: ssl_capath | Yes | Global | No | ||
ssl-cert | Yes | Yes | No | ||
- Variable: ssl_cert | Yes | Global | No | ||
ssl-cipher | Yes | Yes | No | ||
- Variable: ssl_cipher | Yes | Global | No | ||
ssl-key | Yes | Yes | No | ||
- Variable: ssl_key | Yes | Global | No | ||
storage_engine | Yes | Both | Yes | ||
sync_binlog | Yes | Yes | Yes | Global | Yes |
sync_frm | Yes | Yes | Yes | Global | Yes |
system_time_zone | Yes | Global | No | ||
table_cache | Yes | Yes | Yes | Global | Yes |
table_definition_cache | Yes | Yes | Yes | Global | Yes |
table_lock_wait_timeout | Yes | Yes | Yes | Global | Yes |
table_open_cache | Yes | Yes | Yes | Global | Yes |
table_type | Yes | Both | Yes | ||
thread_cache_size | Yes | Yes | Yes | Global | Yes |
thread_concurrency | Yes | Yes | Yes | Global | No |
thread_handling | Yes | Yes | Yes | Global | No |
thread_stack | Yes | Yes | Yes | Global | No |
time_format | Yes | Yes | Yes | Both | No |
time_zone | Yes | Yes | Yes | Both | Yes |
timed_mutexes | Yes | Yes | Yes | Global | Yes |
timestamp | Yes | Session | Yes | ||
tmp_table_size | Yes | Yes | Yes | Both | Yes |
tmpdir | Yes | Yes | Yes | Global | No |
transaction_alloc_block_size | Yes | Yes | Yes | Both | Yes |
transaction_allow_batching | Yes | Session | Yes | ||
transaction_prealloc_size | Yes | Yes | Yes | Both | Yes |
tx_isolation | Yes | Both | Yes | ||
unique_checks | Yes | Session | Yes | ||
updatable_views_with_limit | Yes | Yes | Yes | Both | Yes |
version | Yes | Yes | Yes | Global | No |
version_comment | Yes | Global | No | ||
version_compile_machine | Yes | Global | No | ||
version_compile_os | Yes | Global | No | ||
wait_timeout | Yes | Yes | Yes | Both | Yes |
warning_count | Yes | Session | No | ||
[a] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. [b] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. |
For additional system variable information, see these sections:
Section 5.1.5, “Session System Variables”, describes system variables that exist only as session variables (that is, they do not have any global counterpart).
Section 5.1.6, “Using System Variables”, discusses the syntax for setting and displaying system variable values.
Section 5.1.6.2, “Dynamic System Variables”, lists the variables that can be set at runtime.
Information on tuning system variables can be found in Section 7.5.3, “Tuning Server Parameters”.
Section 13.6.3, “InnoDB
Startup Options and System Variables”, lists
InnoDB
system variables.
Section 17.3.4.3, “MySQL Cluster System Variables”, lists system variables which are specific to MySQL Cluster.
For information on server system variables specific to replication, see Section 16.1.3, “Replication and Binary Logging Options and Variables”.
Some of the following variable descriptions refer to
“enabling” or “disabling” a variable.
These variables can be enabled with the
SET
statement by setting them to ON
or
1
, or disabled by setting them to
OFF
or 0
. However, to set
such a variable on the command line or in an option file, you
must set it to 1
or 0
;
setting it to ON
or OFF
will not work. For example, on the command line,
--delay_key_write=1
works but
--delay_key_write=ON
does not.
Some system variables control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent. This means that when you assign a value to a system variable that controls a buffer size, the amount of space actually available might differ from the value assigned. In some cases, the amount might be less than the value assigned. It is also possible that the server will adjust a value upward. For example, if you assign a value of 0 to a variable for which the minimal value is 1024, the server will set the value to 1024.
Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.
Some system variables take file name values. Unless otherwise
specified, the default file location is the data directory if the
value is a relative path name. To specify the location explicitly,
use an absolute path name. Suppose that the data directory is
/var/mysql/data
. If a file-valued variable is
given as a relative path name, it will be located under
/var/mysql/data
. If the value is an absolute
path name, its location is as given by the path name.
Variable Name | automatic_sp_privileges |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | TRUE |
When this variable has a value of 1 (the default), the server
automatically grants the
EXECUTE
and
ALTER ROUTINE
privileges to the
creator of a stored routine, if the user cannot already
execute and alter or drop the routine. (The
ALTER ROUTINE
privilege is
required to drop the routine.) The server also automatically
drops those privileges from the creator when the routine is
dropped. If
automatic_sp_privileges
is 0,
the server does not automatically add or drop these
privileges.
The creator of a routine is the account used to execute the
CREATE
statement for it. This might not be
the same as the account named as the
DEFINER
in the routine definition.
See also Section 19.2.2, “Stored Routines and MySQL Privileges”.
Command-Line Format | --back_log=# |
|
Config-File Format | back_log |
|
Option Sets Variable | Yes, back_log
|
|
Variable Name | back_log |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | numeric |
|
Default | 50 |
|
Range | 1-65535 |
The number of outstanding connection requests MySQL can have.
This comes into play when the main MySQL thread gets very many
connection requests in a very short time. It then takes some
time (although very little) for the main thread to check the
connection and start a new thread. The
back_log
value indicates how
many requests can be stacked during this short time before
MySQL momentarily stops answering new requests. You need to
increase this only if you expect a large number of connections
in a short period of time.
In other words, this value is the size of the listen queue for
incoming TCP/IP connections. Your operating system has its own
limit on the size of this queue. The manual page for the Unix
listen()
system call should have more
details. Check your OS documentation for the maximum value for
this variable. back_log
cannot be set higher than your operating system limit.
Command-Line Format | --basedir=name |
|
Config-File Format | basedir |
|
Option Sets Variable | Yes, basedir
|
|
Variable Name | basedir |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
The MySQL installation base directory. This variable can be
set with the --basedir
option.
Relative path names for other variables usually are resolved
relative to the base directory.
Command-Line Format | --bulk_insert_buffer_size=# |
|
Config-File Format | bulk_insert_buffer_size |
|
Option Sets Variable | Yes, bulk_insert_buffer_size
|
|
Variable Name | bulk_insert_buffer_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 8388608 |
|
Range | 0-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 8388608 |
|
Range | 0-18446744073709547520 |
MyISAM
uses a special tree-like cache to
make bulk inserts faster for
INSERT ...
SELECT
, INSERT ... VALUES (...), (...),
...
, and
LOAD DATA
INFILE
when adding data to nonempty tables. This
variable limits the size of the cache tree in bytes per
thread. Setting it to 0 disables this optimization. The
default value is 8MB.
Variable Name | character_set_client |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
The character set for statements that arrive from the client.
The session value of this variable is set using the character
set requested by the client when the client connects to the
server. (Many clients support a
--default-character-set
option to enable this
character set to be specified explicitly. See also
Section 9.1.4, “Connection Character Sets and Collations”.) The global value of the
variable is used to set the session value in cases when the
client-requested value is unknown or not available, or the
server is configured to ignore client requests:
The client is from a version of MySQL older than MySQL 4.1, and thus does not request a character set.
The client requests a character set not known to the
server. For example, a Japanese-enabled client requests
sjis
when connecting to a server not
configured with sjis
support.
mysqld was started with the
--skip-character-set-client-handshake
option, which causes it to ignore client character set
configuration. This reproduces MySQL 4.0 behavior and is
useful should you wish to upgrade the server without
upgrading all the clients.
Variable Name | character_set_connection |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
The character set for the current connection. Used for literals that do not have an explicit character set specification and for number-to-string conversion.
Variable Name | character_set_database |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Footnote | This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. | |
Permitted Values | ||
Type | string |
The character set used by the default database. The server
sets this variable whenever the default database changes. If
there is no default database, the variable has the same value
as character_set_server
.
Version Introduced | 5.1.6 | |
Command-Line Format | --character-set-filesystem=name |
|
Config-File Format | character-set-filesystem |
|
Option Sets Variable | Yes, character_set_filesystem
|
|
Variable Name | character_set_filesystem |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
The file system character set. This variable is used to
interpret string literals that refer to file names, such as in
the LOAD DATA
INFILE
and
SELECT ... INTO
OUTFILE
statements and the
LOAD_FILE()
function. Such file
names are converted from
character_set_client
to
character_set_filesystem
before the file opening attempt occurs. The default value is
binary
, which means that no conversion
occurs. For systems on which multi-byte file names are
allowed, a different value may be more appropriate. For
example, if the system represents file names using UTF-8, set
character_set_filesystem
to
'utf8'
. This variable was added in MySQL
5.1.6.
Variable Name | character_set_results |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
The character set used for returning query results such as result sets or error messages to the client.
Command-Line Format | --character-set-server |
|
Config-File Format | character-set-server |
|
Option Sets Variable | Yes, character_set_server
|
|
Variable Name | character_set_server |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
The server's default character set.
Variable Name | character_set_system |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | string |
The character set used by the server for storing identifiers.
The value is always utf8
.
Command-Line Format | --character-sets-dir=name |
|
Config-File Format | character-sets-dir |
|
Option Sets Variable | Yes, character_sets_dir
|
|
Variable Name | character-sets-dir |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
The directory where character sets are installed.
Variable Name | collation_connection |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
The collation of the connection character set.
Variable Name | collation_database |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Footnote | This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. | |
Permitted Values | ||
Type | string |
The collation used by the default database. The server sets
this variable whenever the default database changes. If there
is no default database, the variable has the same value as
collation_server
.
Command-Line Format | --collation-server |
|
Config-File Format | collation-server |
|
Option Sets Variable | Yes, collation_server
|
|
Variable Name | collation_server |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
The server's default collation.
Command-Line Format | --completion_type=# |
|
Config-File Format | completion_type |
|
Option Sets Variable | Yes, completion_type
|
|
Variable Name | competion_type |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
|
Valid Values |
0 , 1 , 2
|
The transaction completion type:
If the value is 0 (the default),
COMMIT
and
ROLLBACK
are unaffected.
If the value is 1, COMMIT
and
ROLLBACK
are equivalent to COMMIT AND CHAIN
and
ROLLBACK AND CHAIN
, respectively. (A
new transaction starts immediately with the same isolation
level as the just-terminated transaction.)
If the value is 2, COMMIT
and
ROLLBACK
are equivalent to COMMIT RELEASE
and
ROLLBACK RELEASE
, respectively. (The
server disconnects after terminating the transaction.)
Command-Line Format | --concurrent_insert[=#] |
|
Config-File Format | concurrent_insert |
|
Option Sets Variable | Yes, concurrent_insert
|
|
Variable Name | concurrent_insert |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 1 |
|
Valid Values |
0 , 1 , 2
|
If 1 (the default), MySQL allows
INSERT
and
SELECT
statements to run
concurrently for MyISAM
tables that have no
free blocks in the middle of the data file. You can turn this
option off by starting mysqld with
--safe-mode
or
--skip-new
.
This variable can take three integer values.
Value | Description |
0 | Off |
1 | (Default) Enables concurrent insert for MyISAM tables
that don't have holes |
2 | Enables concurrent inserts for all MyISAM tables,
even those that have holes. For a table with a hole,
new rows are inserted at the end of the table if it is
in use by another thread. Otherwise, MySQL acquires a
normal write lock and inserts the row into the hole. |
See also Section 7.3.3, “Concurrent Inserts”.
Command-Line Format | --connect_timeout=# |
|
Config-File Format | connect_timeout |
|
Option Sets Variable | Yes, connect_timeout
|
|
Variable Name | connect_timeout |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values (<= 5.1.22) | ||
Type | numeric |
|
Default | 5 |
|
Permitted Values (>= 5.1.23) | ||
Type | numeric |
|
Default | 10 |
The number of seconds that the mysqld
server waits for a connect packet before responding with
Bad handshake
. The default value is 10
seconds as of MySQL 5.1.23 and 5 seconds before that.
Increasing the
connect_timeout
value might
help if clients frequently encounter errors of the form
Lost connection to MySQL server at
'
.
XXX
', system error:
errno
Command-Line Format | --datadir=name |
|
Config-File Format | datadir |
|
Option Sets Variable | Yes, datadir
|
|
Variable Name | datadir |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
The MySQL data directory. This variable can be set with the
--datadir
option.
This variable is unused.
This variable is unused.
Command-Line Format | --debug[=debug_options] |
|
Config-File Format | debug |
|
Variable Name | debug |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
|
Default | 'd:t:o,/tmp/mysqld.trace' |
This variable indicates the current debugging settings. It is
available only for servers built with debugging support. The
initial value comes from the value of instances of the
--debug
option given at server
startup. The global and session values may be set at runtime;
the SUPER
privilege is
required, even for the session value.
Assigning a value that begins with +
or
-
cause the value to added to or subtracted
from the current value:
mysql>SET debug = 'T';
mysql>SELECT @@debug;
+---------+ | @@debug | +---------+ | T | +---------+ mysql>SET debug = '+P';
mysql>SELECT @@debug;
+---------+ | @@debug | +---------+ | P:T | +---------+ mysql>SET debug = '-P';
mysql>SELECT @@debug;
+---------+ | @@debug | +---------+ | T | +---------+
This variable was added in MySQL 5.1.7.
Version Introduced | 5.1.41 | |
Variable Name | debug_sync |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
This variable is the user interface to the Debug Sync
facility. Use of Debug Sync requires that MySQL be configured
with the --enable-debug-sync
option (see Section 2.3.2, “Typical configure Options”). If Debug
Sync is not compiled in, this system variable is not
available.
The global variable value is read only and indicates whether
the facility is enabled. By default, Debug Sync is disabled
and the value of debug_sync
is OFF
. If the server is started with
--debug-sync-timeout=
,
where N
N
is a timeout value greater
than 0, Debug Sync is enabled and the value of
debug_sync
is ON -
current signal
followed by the signal name. Also,
N
becomes the default timeout for
individual synchronization points.
The session value can be read by any user and will have the
same value as the global variable. The session value can be
set by users that have the
SUPER
privilege to control
synchronization points.
For a description of the Debug Sync facility and how to use synchronization points, see MySQL Internals: Test Synchronization.
This variable was added in MySQL 5.1.41.
Command-Line Format | --default_week_format=# |
|
Config-File Format | default_week_format |
|
Option Sets Variable | Yes, default_week_format
|
|
Variable Name | default_week_format |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
|
Range | 0-7 |
The default mode value to use for the
WEEK()
function. See
Section 11.6, “Date and Time Functions”.
Command-Line Format | --delay-key-write[=name] |
|
Config-File Format | delay-key-write |
|
Option Sets Variable | Yes, delay_key_write
|
|
Variable Name | delay-key-write |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | enumeration |
|
Default | ON |
|
Valid Values |
ON , OFF , ALL
|
This option applies only to MyISAM
tables.
It can have one of the following values to affect handling of
the DELAY_KEY_WRITE
table option that can
be used in CREATE TABLE
statements.
Option | Description |
OFF |
DELAY_KEY_WRITE is ignored. |
ON |
MySQL honors any DELAY_KEY_WRITE option specified in
CREATE TABLE
statements. This is the default value. |
ALL |
All new opened tables are treated as if they were created with the
DELAY_KEY_WRITE option enabled. |
If DELAY_KEY_WRITE
is enabled for a table,
the key buffer is not flushed for the table on every index
update, but only when the table is closed. This speeds up
writes on keys a lot, but if you use this feature, you should
add automatic checking of all MyISAM
tables
by starting the server with the
--myisam-recover
option (for
example,
--myisam-recover=BACKUP,FORCE
).
See Section 5.1.2, “Server Command Options”, and
Section 13.5.1, “MyISAM
Startup Options”.
If you enable external locking with
--external-locking
, there is
no protection against index corruption for tables that use
delayed key writes.
Command-Line Format | --delayed_insert_limit=# |
|
Config-File Format | delayed_insert_limit |
|
Option Sets Variable | Yes, delayed_insert_limit
|
|
Variable Name | delayed_insert_limit |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 100 |
|
Range | 1-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 100 |
|
Range | 1-18446744073709547520 |
After inserting
delayed_insert_limit
delayed
rows, the INSERT DELAYED
handler thread checks whether there are any
SELECT
statements pending. If
so, it allows them to execute before continuing to insert
delayed rows.
Command-Line Format | --delayed_insert_timeout=# |
|
Config-File Format | delayed_insert_timeout |
|
Option Sets Variable | Yes, delayed_insert_timeout
|
|
Variable Name | delayed_insert_timeout |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 300 |
How many seconds an INSERT
DELAYED
handler thread should wait for
INSERT
statements before
terminating.
Command-Line Format | --delayed_queue_size=# |
|
Config-File Format | delayed_queue_size |
|
Option Sets Variable | Yes, delayed_queue_size
|
|
Variable Name | delayed_queue_size |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 1000 |
|
Range | 1-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 1000 |
|
Range | 1-18446744073709547520 |
This is a per-table limit on the number of rows to queue when
handling INSERT DELAYED
statements. If the queue becomes full, any client that issues
an INSERT DELAYED
statement
waits until there is room in the queue again.
Command-Line Format | --div_precision_increment=# |
|
Config-File Format | div_precision_increment |
|
Option Sets Variable | Yes, div_precision_increment
|
|
Variable Name | div_precision_increment |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 4 |
|
Range | 0-30 |
This variable indicates the number of digits by which to
increase the scale of the result of division operations
performed with the
/
operator.
The default value is 4. The minimum and maximum values are 0
and 30, respectively. The following example illustrates the
effect of increasing the default value.
mysql>SELECT 1/7;
+--------+ | 1/7 | +--------+ | 0.1429 | +--------+ mysql>SET div_precision_increment = 12;
mysql>SELECT 1/7;
+----------------+ | 1/7 | +----------------+ | 0.142857142857 | +----------------+
Command-Line Format | --engine-condition-pushdown |
|
Config-File Format | engine-condition-pushdown |
|
Option Sets Variable | Yes, engine_condition_pushdown
|
|
Variable Name | engine_condition_pushdown |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values (>= 5.1.0) | ||
Type | boolean |
|
Default | ON |
When the value of this variable is 0 (OFF
),
a query such as SELECT * FROM t WHERE mycol =
42
, where mycol
is a nonindexed
column, is executed as a full table scan. The storage engine
sends every row to the MySQL server, which applies the
WHERE
condition. If
engine_condition_pushdown
is
set to 1 (ON
), the condition is
“pushed down” to the storage engine, which uses
the condition to perform the scan, and sends back to the MySQL
server only those rows that match the condition. By default,
this variable is OFF
.
In MySQL 5.1, this variable is useful only with
the NDBCLUSTER
storage engine.
However, we intend to implement it for additional storage
engines in future MySQL releases.
Setting this variable to ON
on a MySQL
Server acting as a MySQL Cluster SQL node causes
WHERE
conditions on unindexed columns to be
evaluated on the cluster's data nodes and only the rows that
match to be sent back to the SQL node that issued the query.
This means the amount of cluster data that must be sent over
the network is greatly reduced, increasing the efficiency with
which results are returned.
For more information, see Section 7.2.7, “Condition Pushdown Optimization”.
Version Introduced | 5.1.6 | |
Command-Line Format | --event-scheduler[=value] |
|
Config-File Format | event-scheduler |
|
Option Sets Variable | Yes, event_scheduler
|
|
Variable Name | event_scheduler |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | enumeration |
|
Default | OFF |
|
Valid Values |
ON , OFF , DISABLED
|
This variable indicates the status of the Event Scheduler; as
of MySQL 5.1.12, possible values are ON
,
OFF
, and DISABLED
, with
the default being OFF
. This variable and
its effects on the Event Scheduler's operation are discussed
in greater detail in the
Overview section
of the Events chapter.
This variable was added in MySQL 5.1.6.
Command-Line Format | --expire_logs_days=# |
|
Config-File Format | expire_logs_days |
|
Option Sets Variable | Yes, expire_logs_days
|
|
Variable Name | expire_logs_days |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
|
Range | 0-99 |
The number of days for automatic binary log file removal. The default is 0, which means “no automatic removal.” Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in Section 5.2, “MySQL Server Logs”.
To remove binary log files manually, use the
PURGE BINARY LOGS
statement.
See Section 12.5.1.1, “PURGE BINARY LOGS
Syntax”.
Command-Line Format | --flush |
|
Config-File Format | flush |
|
Variable Name | flush |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | OFF |
If ON
, the server flushes (synchronizes)
all changes to disk after each SQL statement. Normally, MySQL
does a write of all changes to disk only after each SQL
statement and lets the operating system handle the
synchronizing to disk. See Section B.5.4.2, “What to Do If MySQL Keeps Crashing”. This
variable is set to ON
if you start
mysqld with the
--flush
option.
Command-Line Format | --flush_time=# |
|
Config-File Format | flush_time |
|
Option Sets Variable | Yes, flush_time
|
|
Variable Name | flush_time |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
|
Min Value | 0 |
|
Permitted Values | ||
Type (windows) | numeric |
|
Default | 1800 |
|
Min Value | 0 |
If this is set to a nonzero value, all tables are closed every
flush_time
seconds to free up
resources and synchronize unflushed data to disk. This option
is best used only on Windows 9x or Me, or on systems with
minimal resources.
Command-Line Format | --ft_boolean_syntax=name |
|
Config-File Format | ft_boolean_syntax |
|
Variable Name | ft_boolean_syntax |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
|
Default | +-><()~*:""& |
The list of operators supported by boolean full-text searches
performed using IN BOOLEAN MODE
. See
Section 11.8.2, “Boolean Full-Text Searches”.
The default variable value is
'+ -><()~*:""&|'
. The rules
for changing the value are as follows:
Operator function is determined by position within the string.
The replacement value must be 14 characters.
Each character must be an ASCII nonalphanumeric character.
Either the first or second character must be a space.
No duplicates are allowed except the phrase quoting operators in positions 11 and 12. These two characters are not required to be the same, but they are the only two that may be.
Positions 10, 13, and 14 (which by default are set to
“:
”,
“&
”, and
“|
”) are reserved for
future extensions.
Command-Line Format | --ft_max_word_len=# |
|
Config-File Format | ft_max_word_len |
|
Option Sets Variable | Yes, ft_max_word_len
|
|
Variable Name | ft_max_word_len |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | numeric |
|
Min Value | 10 |
The maximum length of the word to be included in a
FULLTEXT
index.
FULLTEXT
indexes must be rebuilt after
changing this variable. Use REPAIR TABLE
.
tbl_name
QUICK
Command-Line Format | --ft_min_word_len=# |
|
Config-File Format | ft_min_word_len |
|
Option Sets Variable | Yes, ft_min_word_len
|
|
Variable Name | ft_min_word_len |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | numeric |
|
Default | 4 |
|
Min Value | 1 |
The minimum length of the word to be included in a
FULLTEXT
index.
FULLTEXT
indexes must be rebuilt after
changing this variable. Use REPAIR TABLE
.
tbl_name
QUICK
Command-Line Format | --ft_query_expansion_limit=# |
|
Config-File Format | ft_query_expansion_limit |
|
Option Sets Variable | Yes, ft_query_expansion_limit
|
|
Variable Name | ft_query_expansion_limit |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | numeric |
|
Default | 20 |
|
Range | 0-1000 |
The number of top matches to use for full-text searches
performed using WITH QUERY EXPANSION
.
Command-Line Format | --ft_stopword_file=name |
|
Config-File Format | ft_stopword_file |
|
Option Sets Variable | Yes, ft_stopword_file
|
|
Variable Name | ft_stopword_file |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
The file from which to read the list of stopwords for
full-text searches. The server looks for the file in the data
directory unless an absolute path name is given to specify a
different directory. All the words from the file are used;
comments are not honored. By default, a
built-in list of stopwords is used (as defined in the
storage/myisam/ft_static.c
file). Setting
this variable to the empty string (''
)
disables stopword filtering.
FULLTEXT
indexes must be rebuilt after
changing this variable or the contents of the stopword file.
Use REPAIR TABLE
.
tbl_name
QUICK
Version Introduced | 5.1.12 | |
Command-Line Format | --general-log |
|
Config-File Format | general-log |
|
Option Sets Variable | Yes, general_log
|
|
Variable Name | general_log |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | OFF |
Whether the general query log is enabled. The value can be 0
(or OFF
) to disable the log or 1 (or
ON
) to enable the log. The default value
depends on whether the
--general_log
option is given
(--log
before MySQL 5.1.29).
The destination for log output is controlled by the
log_output
system variable;
if that value is NONE
, no log entries are
written even if the log is enabled. The
general_log
variable was
added in MySQL 5.1.12.
Version Introduced | 5.1.12 | |
Command-Line Format | --general-log-file=file_name |
5.1.29 |
Config-File Format | general_log_file |
5.1.29 |
Option Sets Variable | Yes, general_log_file
|
|
Variable Name | general_log_file |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | filename |
|
Default | host_name.log |
The name of the general query log file. The default value is
,
but the initial value can be changed with the
host_name
.log--general_log_file
option
(--log
before MySQL 5.1.29).
This variable was added in MySQL 5.1.12.
Command-Line Format | --group_concat_max_len=# |
|
Config-File Format | group_concat_max_len |
|
Option Sets Variable | Yes, group_concat_max_len
|
|
Variable Name | group_concat_max_len |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 1024 |
|
Range | 4-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 1024 |
|
Range | 4-18446744073709547520 |
The maximum allowed result length in bytes for the
GROUP_CONCAT()
function. The
default is 1024.
YES
if mysqld supports
ARCHIVE
tables, NO
if
not. This variable was removed in MySQL 5.1.14.
YES
if mysqld supports
BLACKHOLE
tables, NO
if
not. This variable was removed in MySQL 5.1.14.
YES
if the zlib
compression library is available to the server,
NO
if not. If not, the
COMPRESS()
and
UNCOMPRESS()
functions cannot
be used.
YES
if statement profiling is enabled,
NO
if not. See
Section 12.4.5.32, “SHOW PROFILE
Syntax”.
This variable is renamed to have_profiling
in MySQL 5.5.
YES
if the crypt()
system call is available to the server, NO
if not. If not, the ENCRYPT()
function cannot be used.
YES
if mysqld supports
ARCHIVE
tables, NO
if
not.
YES
if mysqld supports
dynamic loading of plugins, NO
if not. This
variable was added in MySQL 5.1.10.
YES
if mysqld supports
EXAMPLE
tables, NO
if
not. This variable was removed in MySQL 5.1.14.
YES
if mysqld supports
FEDERATED
tables, NO
if
not. This variable was removed in MySQL 5.1.14.
YES
if the server supports spatial data
types, NO
if not.
YES
if mysqld supports
InnoDB
tables. DISABLED
if
--skip-innodb
is used.
In MySQL 5.1, this variable appears only for
reasons of backward compatibility. It is always
NO
because ISAM
tables
are no longer supported. This variable was removed in MySQL
5.1.7.
YES
if mysqld supports
MERGE
tables. DISABLED
if --skip-merge
is used. This
variable was removed in MySQL 5.1.3.
YES
if mysqld supports
SSL connections, NO
if not. As of MySQL
5.1.17, this variable is an alias for
have_ssl
.
YES
if mysqld supports
partitioning. Added in MySQL 5.1.1 as
have_partition_engine
and renamed to
have_partioning
in 5.1.6.
YES
if mysqld supports
the query cache, NO
if not.
Version Introduced | 5.1.5 | |
Version Removed | 5.1.15 | |
Variable Name | have_row_based_replication |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | boolean |
YES
if the server can perform replication
using row-based binary logging. If the value is
NO
, the server can use only statement-based
logging. See Section 16.1.2, “Replication Formats”. This
variable was added in MySQL 5.1.5 and removed in 5.1.15.
In MySQL 5.1, this variable appears only for
reasons of backward compatibility. It is always
NO
because RAID
tables
are no longer supported. This variable was removed in MySQL
5.1.7.
YES
if RTREE
indexes are
available, NO
if not. (These are used for
spatial indexes in MyISAM
tables.)
YES
if mysqld supports
SSL connections, NO
if not. This variable
was added in MySQL 5.1.17. Before that, use
have_openssl
.
YES
if symbolic link support is enabled,
NO
if not. This is required on Unix for
support of the DATA DIRECTORY
and
INDEX DIRECTORY
table options, and on
Windows for support of data directory symlinks.
Version Introduced | 5.1.17 | |
Variable Name | hostname |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | string |
The server sets this variable to the server host name at startup. This variable was added in MySQL 5.1.17.
Command-Line Format | --init-connect=name |
|
Config-File Format | init_connect |
|
Option Sets Variable | Yes, init_connect
|
|
Variable Name | init_connect |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
A string to be executed by the server for each client that
connects. The string consists of one or more SQL statements.
To specify multiple statements, separate them by semicolon
characters. For example, each client begins by default with
autocommit mode enabled. There is no global system variable to
specify that autocommit should be disabled by default, but
init_connect
can be used to
achieve the same effect:
SET GLOBAL init_connect='SET autocommit=0';
This variable can also be set on the command line or in an option file. To set the variable as just shown using an option file, include these lines:
[mysqld] init_connect='SET autocommit=0'
Note that the content of
init_connect
is not executed
for users that have the SUPER
privilege. This is done so that an erroneous value for
init_connect
does not prevent
all clients from connecting. For example, the value might
contain a statement that has a syntax error, thus causing
client connections to fail. Not executing
init_connect
for users that
have the SUPER
privilege
enables them to open a connection and fix the
init_connect
value.
Command-Line Format | --init-file=name |
|
Config-File Format | init-file |
|
Option Sets Variable | Yes, init_file
|
|
Variable Name | init_file |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
The name of the file specified with the
--init-file
option when you
start the server. This should be a file containing SQL
statements that you want the server to execute when it starts.
Each statement must be on a single line and should not include
comments. No statement terminator such as
;
, \g
, or
\G
should be given at the end of each
statement.
Note that the --init-file
option is unavailable if MySQL was configured with the
--disable-grant-options
option. See Section 2.3.2, “Typical configure Options”.
innodb_
xxx
InnoDB
system variables are listed in
Section 13.6.3, “InnoDB
Startup Options and System Variables”.
Command-Line Format | --interactive_timeout=# |
|
Config-File Format | interactive_timeout |
|
Option Sets Variable | Yes, interactive_timeout
|
|
Variable Name | interactive_timeout |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 28800 |
|
Min Value | 1 |
The number of seconds the server waits for activity on an
interactive connection before closing it. An interactive
client is defined as a client that uses the
CLIENT_INTERACTIVE
option to
mysql_real_connect()
. See also
wait_timeout
.
Command-Line Format | --join_buffer_size=# |
|
Config-File Format | join_buffer_size |
|
Option Sets Variable | Yes, join_buffer_size
|
|
Variable Name | join_buffer_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes |
The size of the buffer that is used for plain index scans,
range index scans, and joins that do not use indexes and thus
perform full table scans. Normally, the best way to get fast
joins is to add indexes. Increase the value of
join_buffer_size
to get a
faster full join when adding indexes is not possible. One join
buffer is allocated for each full join between two tables. For
a complex join between several tables for which indexes are
not used, multiple join buffers might be necessary.
The maximum allowable setting for
join_buffer_size
is 4GB. As
of MySQL 5.1.23, values larger than 4GB are allowed for 64-bit
platforms (except 64-bit Windows, for which large values are
truncated to 4GB with a warning).
Version Introduced | 5.1.21 | |
Command-Line Format | --keep_files_on_create=# |
|
Config-File Format | keep_files_on_create |
|
Option Sets Variable | Yes, keep_files_on_create
|
|
Variable Name | keep_files_on_create |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | OFF |
If a MyISAM
table is created with no
DATA DIRECTORY
option, the
.MYD
file is created in the database
directory. By default, if MyISAM
finds an
existing .MYD
file in this case, it
overwrites it. The same applies to .MYI
files for tables created with no INDEX
DIRECTORY
option. To suppress this behavior, set the
keep_files_on_create
variable
to ON
(1), in which case
MyISAM
will not overwrite existing files
and returns an error instead. The default value is
OFF
(0).
If a MyISAM
table is created with a
DATA DIRECTORY
or INDEX
DIRECTORY
option and an existing
.MYD
or .MYI
file is
found, MyISAM always returns an error. It will not overwrite a
file in the specified directory.
This variable was added in MySQL 5.1.23.
Command-Line Format | --key_buffer_size=# |
|
Config-File Format | key_buffer_size |
|
Option Sets Variable | Yes, key_buffer_size
|
|
Variable Name | key_buffer_size |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values (<= 5.1.22) | ||
Type | numeric |
|
Default | 8388608 |
|
Range | 8-4294967295 |
|
Permitted Values (>= 5.1.23) | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 8388608 |
|
Range | 8-4294967295 |
|
Permitted Values (>= 5.1.23) | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 8388608 |
|
Range | 8-OS_PER_PROCESS_LIMIT |
Index blocks for MyISAM
tables are buffered
and are shared by all threads.
key_buffer_size
is the size
of the buffer used for index blocks. The key buffer is also
known as the key cache.
The maximum allowable setting for
key_buffer_size
is 4GB on
32-bit platforms. As of MySQL 5.1.23, values larger than 4GB
are allowed for 64-bit platforms, except 64-bit Windows prior
to MySQL 5.1.31, for which large values are truncated to 4GB
with a warning. As of MySQL 5.1.31, values larger than 4GB are
also allowed for 64-bit Windows. The effective maximum size
might be less, depending on your available physical RAM and
per-process RAM limits imposed by your operating system or
hardware platform. The value of this variable indicates the
amount of memory requested. Internally, the server allocates
as much memory as possible up to this amount, but the actual
allocation might be less.
You can increase the value to get better index handling for
all reads and multiple writes; on a system whose primary
function is to run MySQL using the
MyISAM
storage engine, 25% of the
machine's total memory is an acceptable value for this
variable. However, you should be aware that, if you make the
value too large (for example, more than 50% of the
machine's total memory), your system might start to page
and become extremely slow. This is because MySQL relies on the
operating system to perform file system caching for data
reads, so you must leave some room for the file system cache.
You should also consider the memory requirements of any other
storage engines that you may be using in addition to
MyISAM
.
For even more speed when writing many rows at the same time,
use LOCK TABLES
. See
Section 7.2.21, “Speed of INSERT
Statements”.
You can check the performance of the key buffer by issuing a
SHOW STATUS
statement and
examining the
Key_read_requests
,
Key_reads
,
Key_write_requests
, and
Key_writes
status variables.
(See Section 12.4.5, “SHOW
Syntax”.) The
Key_reads/Key_read_requests
ratio should
normally be less than 0.01. The
Key_writes/Key_write_requests
ratio is
usually near 1 if you are using mostly updates and deletes,
but might be much smaller if you tend to do updates that
affect many rows at the same time or if you are using the
DELAY_KEY_WRITE
table option.
The fraction of the key buffer in use can be determined using
key_buffer_size
in
conjunction with the
Key_blocks_unused
status
variable and the buffer block size, which is available from
the key_cache_block_size
system variable:
1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)
This value is an approximation because some space in the key buffer is be allocated internally for administrative structures.
It is possible to create multiple MyISAM
key caches. The size limit of 4GB applies to each cache
individually, not as a group. See
Section 7.4.5, “The MyISAM
Key Cache”.
Command-Line Format | --key_cache_age_threshold=# |
|
Config-File Format | key_cache_age_threshold |
|
Option Sets Variable | Yes, key_cache_age_threshold
|
|
Variable Name | key_cache_age_threshold |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 300 |
|
Range | 100-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 300 |
|
Range | 100-18446744073709547520 |
This value controls the demotion of buffers from the hot
sublist of a key cache to the warm sublist. Lower values cause
demotion to happen more quickly. The minimum value is 100. The
default value is 300. See Section 7.4.5, “The MyISAM
Key Cache”.
Command-Line Format | --key_cache_block_size=# |
|
Config-File Format | key_cache_block_size |
|
Option Sets Variable | Yes, key_cache_block_size
|
|
Variable Name | key_cache_block_size |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 1024 |
|
Range | 512-16384 |
The size in bytes of blocks in the key cache. The default
value is 1024. See Section 7.4.5, “The MyISAM
Key Cache”.
Command-Line Format | --key_cache_division_limit=# |
|
Config-File Format | key_cache_division_limit |
|
Option Sets Variable | Yes, key_cache_division_limit
|
|
Variable Name | key_cache_division_limit |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 100 |
|
Range | 1-100 |
The division point between the hot and warm sublists of the
key cache buffer list. The value is the percentage of the
buffer list to use for the warm sublist. Allowable values
range from 1 to 100. The default value is 100. See
Section 7.4.5, “The MyISAM
Key Cache”.
Command-Line Format | --language=name |
|
Config-File Format | language |
|
Option Sets Variable | Yes, language
|
|
Variable Name | language |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Deprecated | 5.5.0, by lc-messages-dir
|
|
Permitted Values | ||
Type | filename |
|
Default | /usr/local/mysql/share/mysql/english/ |
The directory where error messages are located. See Section 9.2, “Setting the Error Message Language”.
Variable Name | large_files_support |
|
Variable Scope | Global | |
Dynamic Variable | No |
Whether mysqld was compiled with options for large file support.
Command-Line Format | --large-pages |
|
Config-File Format | large-pages |
|
Option Sets Variable | Yes, large_pages
|
|
Variable Name | large_pages |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Platform Specific | linux | |
Permitted Values | ||
Type (linux) | boolean |
|
Default | FALSE |
Whether large page support is enabled (via the
--large-pages
option). See
Section 7.5.9, “Enabling Large Page Support”.
Variable Name | large_page_size |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type (linux) | numeric |
|
Default | 0 |
If large page support is enabled, this shows the size of memory pages. Currently, large memory pages are supported only on Linux; on other platforms, the value of this variable is always 0. See Section 7.5.9, “Enabling Large Page Support”.
Version Introduced | 5.1.12 | |
Variable Name | lc_time_names |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
This variable specifies the locale that controls the language
used to display day and month names and abbreviations. This
variable affects the output from the
DATE_FORMAT()
,
DAYNAME()
and
MONTHNAME()
functions. Locale
names are POSIX-style values such as
'ja_JP'
or 'pt_BR'
. The
default value is 'en_US'
regardless of your
system's locale setting. For further information, see
Section 9.7, “MySQL Server Locale Support”. This variable was added in
MySQL 5.1.12.
Variable Name | license |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | string |
|
Default | GPL |
The type of license the server has.
Variable Name | local_infile |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type |
|
Whether LOCAL
is supported for
LOAD DATA
INFILE
statements. See
Section 5.3.5, “Security Issues with LOAD
DATA LOCAL
”.
Variable Name | locked_in_memory |
|
Variable Scope | Global | |
Dynamic Variable | No |
Whether logging of all statements to the general query log is enabled. See Section 5.2.3, “The General Query Log”.
This variable is deprecated as of MySQL 5.1.29; use
general_log
instead.
Variable Name | log_bin |
|
Variable Scope | Global | |
Dynamic Variable | No |
Whether the binary log is enabled. See Section 5.2.4, “The Binary Log”.
log_bin_trust_function_creators
Command-Line Format | --log-bin-trust-function-creators |
|
Config-File Format | log-bin-trust-function-creators |
|
Option Sets Variable | Yes, log_bin_trust_function_creators
|
|
Variable Name | log_bin_trust_function_creators |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | FALSE |
This variable applies when binary logging is enabled. It
controls whether stored function creators can be trusted not
to create stored functions that will cause unsafe events to be
written to the binary log. If set to 0 (the default), users
are not allowed to create or alter stored functions unless
they have the SUPER
privilege
in addition to the CREATE
ROUTINE
or ALTER
ROUTINE
privilege. A setting of 0 also enforces the
restriction that a function must be declared with the
DETERMINISTIC
characteristic, or with the
READS SQL DATA
or NO SQL
characteristic. If the variable is set to 1, MySQL does not
enforce these restrictions on stored function creation. This
variable also applies to trigger creation. See
Section 19.6, “Binary Logging of Stored Programs”.
Command-Line Format | --log-error[=name] |
|
Config-File Format | log-error |
|
Option Sets Variable | Yes, log_error
|
|
Variable Name | log_error |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
The location of the error log.
Version Introduced | 5.1.6 | |
Command-Line Format | --log-output[=name] |
|
Config-File Format | log-output |
|
Option Sets Variable | Yes, log_output
|
|
Variable Name | log_output |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | set |
|
Default | FILE |
|
Valid Values |
TABLE , FILE , NONE
|
The destination for general query log and slow query log
output. The value can be a comma-separated list of one or more
of the words TABLE
(log to tables),
FILE
(log to files), or
NONE
(do not log to tables or files). The
default value is TABLE
.
NONE
, if present, takes precedence over any
other specifiers. If the value is NONE
log
entries are not written even if the logs are enabled. If the
logs are not enabled, no logging occurs even if the value of
log_output
is not
NONE
. For more information, see
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”. This variable was added in MySQL
5.1.6.
Command-Line Format | --log-queries-not-using-indexes |
|
Config-File Format | log-queries-not-using-indexes |
|
Option Sets Variable | Yes, log_queries_not_using_indexes
|
|
Variable Name | log_queries_not_using_indexes |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
Whether queries that do not use indexes are logged to the slow query log. See Section 5.2.5, “The Slow Query Log”. This variable was added in MySQL 5.1.11.
Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect. See Section 16.1.3, “Replication and Binary Logging Options and Variables”.
Version Deprecated | 5.1.29 | |
Command-Line Format | --log-slow-queries[=name] |
|
Config-File Format | log-slow-queries |
|
Option Sets Variable | Yes, log_slow_queries
|
|
Variable Name | log_slow_queries |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Deprecated | 5.1.29, by slow-query-log
|
|
Permitted Values | ||
Type | boolean |
Whether slow queries should be logged. “Slow” is
determined by the value of the
long_query_time
variable. See
Section 5.2.5, “The Slow Query Log”.
This variable is deprecated as of MySQL 5.1.29; use
slow_query_log
instead.
Command-Line Format | --log-warnings[=#] |
|
Config-File Format | log-warnings |
|
Option Sets Variable | Yes, log_warnings
|
|
Variable Name | log_warnings |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Disabled by | skip-log-warnings |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 1 |
|
Range | 0-18446744073709547520 |
Whether to produce additional warning messages. It is enabled (1) by default and can be disabled by setting it to 0. Aborted connections are not logged to the error log unless the value is greater than 1. As of MySQL 5.1.38, the server logs messages about statements that are unsafe for statement-based logging only if the value is greater than 0.
Command-Line Format | --long_query_time=# |
|
Config-File Format | long_query_time |
|
Option Sets Variable | Yes, long_query_time
|
|
Variable Name | long_query_time |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values (>= 5.1.21) | ||
Type | numeric |
|
Default | 10 |
|
Min Value | 0 |
If a query takes longer than this many seconds, the server
increments the Slow_queries
status variable. If the slow query log is enabled, the query
is logged to the slow query log file. This value is measured
in real time, not CPU time, so a query that is under the
threshold on a lightly loaded system might be above the
threshold on a heavily loaded one. Prior to MySQL 5.1.21, the
minimum value is 1, and the value for this variable must be an
integer. Beginning with MySQL 5.1.21, the minimum is 0, and a
resolution of microseconds is supported when logging to a
file. However, the microseconds part is ignored and only
integer values are written when logging to tables. The default
value is 10. See Section 5.2.5, “The Slow Query Log”.
Command-Line Format | --low-priority-updates |
|
Config-File Format | low-priority-updates |
|
Option Sets Variable | Yes, low_priority_updates
|
|
Variable Name | low_priority_updates |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | FALSE |
If set to 1
, all
INSERT
,
UPDATE
,
DELETE
, and LOCK TABLE
WRITE
statements wait until there is no pending
SELECT
or LOCK TABLE
READ
on the affected table. This affects only
storage engines that use only table-level locking (such as
MyISAM
, MEMORY
, and
MERGE
). This variable previously was named
sql_low_priority_updates
.
Command-Line Format | --lower_case_file_system[=#] |
|
Config-File Format | lower_case_file_system |
|
Option Sets Variable | Yes, lower_case_file_system
|
|
Variable Name | lower_case_file_system |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | boolean |
This variable describes the case sensitivity of file names on
the file system where the data directory is located.
OFF
means file names are case sensitive,
ON
means they are not case sensitive.
Command-Line Format | --lower_case_table_names[=#] |
|
Config-File Format | lower_case_table_names |
|
Option Sets Variable | Yes, lower_case_table_names
|
|
Variable Name | lower_case_table_names |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
|
Range | 0-2 |
If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional information, see Section 8.2.2, “Identifier Case Sensitivity”.
You should not set this variable to 0 if
you are running MySQL on a system that has case-insensitive
file names (such as Windows or Mac OS X). If you set this
variable to 0 on such a system and access
MyISAM
tablenames using different
lettercases, index corruption may result. On Windows the
default value is 1. On Mac OS X, the default value is 2.
If you are using InnoDB
tables, you should
set this variable to 1 on all platforms to force names to be
converted to lowercase.
Command-Line Format | --max_allowed_packet=# |
|
Config-File Format | max_allowed_packet |
|
Option Sets Variable | Yes, max_allowed_packet
|
|
Variable Name | max_allowed_packet |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 1048576 |
|
Range | 1024-1073741824 |
The maximum size of one packet or any generated/intermediate string.
The packet message buffer is initialized to
net_buffer_length
bytes, but
can grow up to
max_allowed_packet
bytes when
needed. This value by default is small, to catch large
(possibly incorrect) packets.
You must increase this value if you are using large
BLOB
columns or long strings.
It should be as big as the largest
BLOB
you want to use. The
protocol limit for
max_allowed_packet
is 1GB.
The value should be a multiple of 1024; nonmultiples are
rounded down to the nearest multiple.
When you change the message buffer size by changing the value
of the max_allowed_packet
variable, you should also change the buffer size on the client
side if your client program allows it. On the client side,
max_allowed_packet
has a
default of 1GB. Some programs such as mysql
and mysqldump enable you to change the
client-side value by setting
max_allowed_packet
on the
command line or in an option file.
As of MySQL 5.1.31, the session value of this variable is read only. Before 5.1.31, setting the session value is allowed but has no effect.
Command-Line Format | --max_connect_errors=# |
|
Config-File Format | max_connect_errors |
|
Option Sets Variable | Yes, max_connect_errors
|
|
Variable Name | max_connect_errors |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 10 |
|
Range | 1-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 10 |
|
Range | 1-18446744073709547520 |
If there are more than this number of interrupted connections
from a host, that host is blocked from further connections.
You can unblock blocked hosts with the
FLUSH HOSTS
statement. If a connection is established successfully within
fewer than max_connect_errors
attempts after a previous connection was interrupted, the
error count for the host is cleared to zero. However, once a
host is blocked, the
FLUSH HOSTS
statement is the only way to unblock it.
Command-Line Format | --max_connections=# |
|
Config-File Format | max_connections |
|
Option Sets Variable | Yes, max_connections
|
|
Variable Name | max_connections |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values (<= 5.1.14) | ||
Type | numeric |
|
Default | 100 |
|
Permitted Values (>= 5.1.15) | ||
Type | numeric |
|
Default | 151 |
|
Range | 1-16384 |
|
Permitted Values (>= 5.1.17) | ||
Type | numeric |
|
Default | 151 |
|
Range | 1-100000 |
The number of simultaneous client connections allowed. By
default, this is 151, beginning with MySQL 5.1.15.
(Previously, the default was 100.) See
Section B.5.2.7, “Too many connections
”, for more information.
MySQL Enterprise.
For notification that the maximum number of connections is
getting dangerously high and for advice on setting the
optimum value for
max_connections
subscribe
to the MySQL Enterprise Monitor. For more information, see
http://www.mysql.com/products/enterprise/advisors.html.
Increasing this value increases the number of file descriptors that mysqld requires. See Section 7.4.8, “How MySQL Opens and Closes Tables”, for comments on file descriptor limits.
Command-Line Format | --max_delayed_threads=# |
|
Config-File Format | max_delayed_threads |
|
Option Sets Variable | Yes, max_delayed_threads
|
|
Variable Name | max_delayed_threads |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 20 |
|
Range | 0-16384 |
Do not start more than this number of threads to handle
INSERT DELAYED
statements. If
you try to insert data into a new table after all
INSERT DELAYED
threads are in
use, the row is inserted as if the DELAYED
attribute wasn't specified. If you set this to 0, MySQL never
creates a thread to handle DELAYED
rows; in
effect, this disables DELAYED
entirely.
For the SESSION
value of this variable, the
only valid values are 0 or the GLOBAL
value.
Command-Line Format | --max_error_count=# |
|
Config-File Format | max_error_count |
|
Option Sets Variable | Yes, max_error_count
|
|
Variable Name | max_error_count |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 64 |
|
Range | 0-65535 |
The maximum number of error, warning, and note messages to be
stored for display by the SHOW
ERRORS
and SHOW
WARNINGS
statements.
Command-Line Format | --max_heap_table_size=# |
|
Config-File Format | max_heap_table_size |
|
Option Sets Variable | Yes, max_heap_table_size
|
|
Variable Name | max_heap_table_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 16777216 |
|
Range | 16384-4294967295 |
This variable sets the maximum size to which
MEMORY
tables are allowed to grow. The
value of the variable is used to calculate
MEMORY
table MAX_ROWS
values. Setting this variable has no effect on any existing
MEMORY
table, unless the table is
re-created with a statement such as
CREATE TABLE
or altered with
ALTER TABLE
or
TRUNCATE TABLE
. A server
restart also sets the maximum size of existing
MEMORY
tables to the global
max_heap_table_size
value.
On 64-bit platforms, the maximum value for this variable is 1844674407370954752.
MySQL Enterprise.
Subscribers to the MySQL Enterprise Monitor receive
recommendations for the optimum setting for
max_heap_table_size
. For
more information, see http://www.mysql.com/products/enterprise/advisors.html.
Variable Name | max_insert_delayed_threads |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
This variable is a synonym for
max_delayed_threads
.
Command-Line Format | --max_join_size=# |
|
Config-File Format | max_join_size |
|
Option Sets Variable | Yes, max_join_size
|
|
Variable Name | max_join_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 4294967295 |
|
Range | 1-4294967295 |
Do not allow SELECT
statements
that probably need to examine more than
max_join_size
rows (for
single-table statements) or row combinations (for
multiple-table statements) or that are likely to do more than
max_join_size
disk seeks. By
setting this value, you can catch
SELECT
statements where keys
are not used properly and that would probably take a long
time. Set it if your users tend to perform joins that lack a
WHERE
clause, that take a long time, or
that return millions of rows.
Setting this variable to a value other than
DEFAULT
resets the value of
sql_big_selects
to
0
. If you set the
sql_big_selects
value again,
the max_join_size
variable is
ignored.
If a query result is in the query cache, no result size check is performed, because the result has previously been computed and it does not burden the server to send it to the client.
This variable previously was named
sql_max_join_size
.
Command-Line Format | --max_length_for_sort_data=# |
|
Config-File Format | max_length_for_sort_data |
|
Option Sets Variable | Yes, max_length_for_sort_data
|
|
Variable Name | max_length_for_sort_data |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 1024 |
|
Range | 4-8388608 |
The cutoff on the size of index values that determines which
filesort
algorithm to use. See
Section 7.2.13, “ORDER BY
Optimization”.
Version Introduced | 5.1.10 | |
Command-Line Format | --max_prepared_stmt_count=# |
5.0.21 |
Config-File Format | max_prepared_stmt_count |
5.0.21 |
Option Sets Variable | Yes, max_prepared_stmt_count
|
|
Variable Name | max_prepared_stmt_count |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 16382 |
|
Range | 0-1048576 |
This variable limits the total number of prepared statements in the server. It can be used in environments where there is the potential for denial-of-service attacks based on running the server out of memory by preparing huge numbers of statements. If the value is set lower than the current number of prepared statements, existing statements are not affected and can be used, but no new statements can be prepared until the current number drops below the limit. The default value is 16,382. The allowable range of values is from 0 to 1 million. Setting the value to 0 disables prepared statements. This variable was added in MySQL 5.1.10.
Command-Line Format | --max_relay_log_size=# |
|
Config-File Format | max_relay_log_size |
|
Option Sets Variable | Yes, max_relay_log_size
|
|
Variable Name | max_relay_log_size |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
|
Range | 0-1073741824 |
If a write by a replication slave to its relay log causes the
current log file size to exceed the value of this variable,
the slave rotates the relay logs (closes the current file and
opens the next one). If
max_relay_log_size
is 0, the
server uses max_binlog_size
for both the binary log and the relay log. If
max_relay_log_size
is greater
than 0, it constrains the size of the relay log, which enables
you to have different sizes for the two logs. You must set
max_relay_log_size
to between
4096 bytes and 1GB (inclusive), or to 0. The default value is
0. See Section 16.2.1, “Replication Implementation Details”.
Command-Line Format | --max_seeks_for_key=# |
|
Config-File Format | max_seeks_for_key |
|
Option Sets Variable | Yes, max_seeks_for_key
|
|
Variable Name | max_seeks_for_key |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 4294967295 |
|
Range | 1-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 18446744073709547520 |
|
Range | 1-18446744073709547520 |
Limit the assumed maximum number of seeks when looking up rows
based on a key. The MySQL optimizer assumes that no more than
this number of key seeks are required when searching for
matching rows in a table by scanning an index, regardless of
the actual cardinality of the index (see
Section 12.4.5.23, “SHOW INDEX
Syntax”). By setting this to a low value
(say, 100), you can force MySQL to prefer indexes instead of
table scans.
Command-Line Format | --max_sort_length=# |
|
Config-File Format | max_sort_length |
|
Option Sets Variable | Yes, max_sort_length
|
|
Variable Name | max_sort_length |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 1024 |
|
Range | 4-8388608 |
The number of bytes to use when sorting
BLOB
or
TEXT
values. Only the first
max_sort_length
bytes of each
value are used; the rest are ignored.
Command-Line Format | --max_sp_recursion_depth[=#] |
|
Config-File Format | max_sp_recursion_depth |
|
Option Sets Variable | Yes, max_sp_recursion_depth
|
|
Variable Name | max_sp_recursion_depth |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
|
Max Value | 255 |
The number of times that any given stored procedure may be called recursively. The default value for this option is 0, which completely disallows recursion in stored procedures. The maximum value is 255.
Stored procedure recursion increases the demand on thread
stack space. If you increase the value of
max_sp_recursion_depth
, it
may be necessary to increase thread stack size by increasing
the value of thread_stack
at
server startup.
Command-Line Format | --max_tmp_tables=# |
|
Config-File Format | max_tmp_tables |
|
Option Sets Variable | Yes, max_tmp_tables
|
|
Variable Name | max_tmp_tables |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 32 |
|
Range | 1-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 32 |
|
Range | 1-18446744073709547520 |
The maximum number of temporary tables a client can keep open at the same time. (This variable does not yet do anything.)
Command-Line Format | --max_user_connections=# |
|
Config-File Format | max_user_connections |
|
Option Sets Variable | Yes, max_user_connections
|
|
Variable Name | max_user_connections |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
|
Range | 0-4294967295 |
The maximum number of simultaneous connections allowed to any given MySQL user account. A value of 0 (the default) means “no limit.”
This variable has a global value that can be set at server startup or runtime. It also has a read-only session value that indicates the effective simultaneous-connection limit that applies to the account associated with the current session. The session value is initialized as follows:
If the user account has a nonzero
MAX_USER_CONNECTIONS
resource limit,
the session
max_user_connections
value is set to that limit.
Otherwise, the session
max_user_connections
value is set to the global value.
Account resource limits are specified using the
GRANT
statement. See
Section 5.5.4, “Setting Account Resource Limits”, and Section 12.4.1.3, “GRANT
Syntax”.
Command-Line Format | --max_write_lock_count=# |
|
Config-File Format | max_write_lock_count |
|
Option Sets Variable | Yes, max_write_lock_count
|
|
Variable Name | max_write_lock_count |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 4294967295 |
|
Range | 1-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 18446744073709547520 |
|
Range | 1-18446744073709547520 |
After this many write locks, allow some pending read lock requests to be processed in between.
Version Introduced | 5.1.21 | |
Command-Line Format | --min-examined-row-limit=# |
|
Config-File Format | min-examined-row-limit |
|
Variable Name | min_examined_row_limit |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 0 |
|
Range | 0-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 0 |
|
Range | 0-18446744073709547520 |
Queries that examine fewer than this number of rows are not logged to the slow query log. This variable was added in MySQL 5.1.21.
Command-Line Format | --myisam_data_pointer_size=# |
|
Config-File Format | myisam_data_pointer_size |
|
Option Sets Variable | Yes, myisam_data_pointer_size
|
|
Variable Name | myisam_data_pointer_size |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 6 |
|
Range | 2-7 |
The default pointer size in bytes, to be used by
CREATE TABLE
for
MyISAM
tables when no
MAX_ROWS
option is specified. This variable
cannot be less than 2 or larger than 7. The default value is
6. See Section B.5.2.12, “The table is full
”.
Command-Line Format | --myisam_max_sort_file_size=# |
|
Config-File Format | myisam_max_sort_file_size |
|
Option Sets Variable | Yes, myisam_max_sort_file_size
|
|
Variable Name | myisam_max_sort_file_size |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 2147483648 |
The maximum size of the temporary file that MySQL is allowed
to use while re-creating a MyISAM
index
(during REPAIR TABLE
,
ALTER TABLE
, or
LOAD DATA
INFILE
). If the file size would be larger than this
value, the index is created using the key cache instead, which
is slower. The value is given in bytes.
The default value is 2GB. If MyISAM
index
files exceed this size and disk space is available, increasing
the value may help performance.
Version Introduced | 5.1.43 | |
Command-Line Format | --myisam_mmap_size=# |
|
Config-File Format | myisam_mmap_size |
|
Option Sets Variable | Yes, myisam_mmap_size
|
|
Variable Name | myisam_mmap_size |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 4294967295 |
|
Range | 7-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 18446744073709547520 |
|
Range | 7-18446744073709547520 |
The maximum amount of memory to use for memory mapping
compressed MyISAM
files. If many
compressed MyISAM
tables are used, the
value can be decreased to reduce the likelihood of
memory-swapping problems. This variable was added in MySQL
5.1.43.
Variable Name | myisam_recover_options |
|
Variable Scope | Global | |
Dynamic Variable | No |
The value of the
--myisam-recover
option. See
Section 5.1.2, “Server Command Options”.
Command-Line Format | --myisam_repair_threads=# |
|
Config-File Format | myisam_repair_threads |
|
Option Sets Variable | Yes, myisam_repair_threads
|
|
Variable Name | myisam_repair_threads |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 1 |
|
Range | 1-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 1 |
|
Range | 1-18446744073709547520 |
If this value is greater than 1, MyISAM
table indexes are created in parallel (each index in its own
thread) during the Repair by sorting
process. The default value is 1.
Multi-threaded repair is still beta-quality code.
Command-Line Format | --myisam_sort_buffer_size=# |
|
Config-File Format | myisam_sort_buffer_size |
|
Option Sets Variable | Yes, myisam_sort_buffer_size
|
|
Variable Name | myisam_sort_buffer_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 8388608 |
|
Range | 4-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 8388608 |
|
Range | 4-18446744073709547520 |
The size of the buffer that is allocated when sorting
MyISAM
indexes during a
REPAIR TABLE
or when creating
indexes with CREATE INDEX
or
ALTER TABLE
.
The maximum allowable setting for
myisam_sort_buffer_size
is
4GB. As of MySQL 5.1.23, values larger than 4GB are allowed
for 64-bit platforms (except 64-bit Windows, for which large
values are truncated to 4GB with a warning).
Command-Line Format | --myisam_stats_method=name |
|
Config-File Format | myisam_stats_method |
|
Option Sets Variable | Yes, myisam_stats_method
|
|
Variable Name | myisam_stats_method |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | enumeration |
|
Valid Values |
nulls_equal , nulls_unequal , nulls_ignored
|
How the server treats NULL
values when
collecting statistics about the distribution of index values
for MyISAM
tables. This variable has three
possible values, nulls_equal
,
nulls_unequal
, and
nulls_ignored
. For
nulls_equal
, all NULL
index values are considered equal and form a single value
group that has a size equal to the number of
NULL
values. For
nulls_unequal
, NULL
values are considered unequal, and each
NULL
forms a distinct value group of size
1. For nulls_ignored
,
NULL
values are ignored.
The method that is used for generating table statistics
influences how the optimizer chooses indexes for query
execution, as described in
Section 7.4.7, “MyISAM
Index Statistics Collection”.
Any unique prefix of a valid value may be used to set the value of this variable.
Version Introduced | 5.1.4 | |
Command-Line Format | --myisam_use_mmap |
|
Config-File Format | myisam_use_mmap |
|
Option Sets Variable | Yes, myisam_use_mmap
|
|
Variable Name | myisam_use_mmap |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | OFF |
Use memory mapping for reading and writing
MyISAM
tables. This variable was added in
MySQL 5.1.4.
Variable Name | named_pipe |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Platform Specific | windows |
(Windows only.) Indicates whether the server supports connections over named pipes.
Command-Line Format | --net_buffer_length=# |
|
Config-File Format | net_buffer_length |
|
Option Sets Variable | Yes, net_buffer_length
|
|
Variable Name | net_buffer_length |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 16384 |
|
Range | 1024-1048576 |
Each client thread is associated with a connection buffer and
result buffer. Both begin with a size given by
net_buffer_length
but are
dynamically enlarged up to
max_allowed_packet
bytes as
needed. The result buffer shrinks to
net_buffer_length
after each
SQL statement.
This variable should not normally be changed, but if you have
very little memory, you can set it to the expected length of
statements sent by clients. If statements exceed this length,
the connection buffer is automatically enlarged. The maximum
value to which
net_buffer_length
can be set
is 1MB.
As of MySQL 5.1.31, the session value of this variable is read only. Before 5.1.31, setting the session value is allowed but has no effect.
Command-Line Format | --net_read_timeout=# |
|
Config-File Format | net_read_timeout |
|
Option Sets Variable | Yes, net_read_timeout
|
|
Variable Name | net_read_timeout |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 30 |
|
Min Value | 1 |
The number of seconds to wait for more data from a connection
before aborting the read. This timeout applies only to TCP/IP
connections, not to connections made via Unix socket files,
named pipes, or shared memory. When the server is reading from
the client, net_read_timeout
is the timeout value controlling when to abort. When the
server is writing to the client,
net_write_timeout
is the
timeout value controlling when to abort. See also
slave_net_timeout
.
Command-Line Format | --net_retry_count=# |
|
Config-File Format | net_retry_count |
|
Option Sets Variable | Yes, net_retry_count
|
|
Variable Name | net_retry_count |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 10 |
|
Range | 1-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 10 |
|
Range | 1-18446744073709547520 |
If a read on a communication port is interrupted, retry this many times before giving up. This value should be set quite high on FreeBSD because internal interrupts are sent to all threads.
Command-Line Format | --net_write_timeout=# |
|
Config-File Format | net_write_timeout |
|
Option Sets Variable | Yes, net_write_timeout
|
|
Variable Name | net_write_timeout |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 60 |
|
Min Value | 1 |
The number of seconds to wait for a block to be written to a
connection before aborting the write. This timeout applies
only to TCP/IP connections, not to connections made via Unix
socket files, named pipes, or shared memory. See also
net_read_timeout
.
Command-Line Format | --new |
|
Config-File Format | new |
|
Option Sets Variable | Yes, new
|
|
Variable Name | new |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Disabled by | skip-new |
|
Permitted Values | ||
Type | boolean |
|
Default | FALSE |
This variable was used in MySQL 4.0 to turn on some 4.1
behaviors, and is retained for backward compatibility. In
MySQL 5.1, its value is always
OFF
.
Version Introduced | 5.1.18 | |
Command-Line Format | old |
|
Config-File Format | old |
|
Variable Name | old |
|
Variable Scope | Global | |
Dynamic Variable | No |
old
is a compatibility
variable. It is disabled by default, but can be enabled at
startup to revert the server to behaviors present in older
versions.
Currently, when old
is
enabled, it changes the default scope of index hints to that
used prior to MySQL 5.1.17. That is, index hints with no
FOR
clause apply only to how indexes are
used for row retrieval and not to resolution of ORDER
BY
or GROUP BY
clauses. (See
Section 12.2.8.2, “Index Hint Syntax”.) Take care about enabling this
in a replication setup. With statement-based binary logging,
having different modes for the master and slaves might lead to
replication errors.
This variable was added as old_mode
in
MySQL 5.1.17 and renamed to
old
in MySQL 5.1.18.
Command-Line Format | --old-alter-table |
|
Config-File Format | old-alter-table |
|
Option Sets Variable | Yes, old_alter_table
|
|
Variable Name | old-alter-table |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | OFF |
When this variable is enabled, the server does not use the
optimized method of processing an ALTER
TABLE
operation. It reverts to using a temporary
table, copying over the data, and then renaming the temporary
table to the original, as used by MySQL 5.0 and earlier. For
more information on the operation of
ALTER TABLE
, see
Section 12.1.7, “ALTER TABLE
Syntax”.
Command-Line Format | --old_passwords |
|
Config-File Format | old-passwords |
|
Option Sets Variable | Yes, old_passwords
|
|
Variable Name | old_passwords |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | FALSE |
Whether the server should use pre-4.1-style passwords for
MySQL user accounts. See Section B.5.2.4, “Client does not support authentication protocol
”.
This is not a variable, but it can be used when setting some
variables. It is described in Section 12.4.4, “SET
Syntax”.
Command-Line Format | --open-files-limit=# |
|
Config-File Format | open-files-limit |
|
Option Sets Variable | Yes, open_files_limit
|
|
Variable Name | open_files_limit |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
|
Range | 0-65535 |
The number of files that the operating system allows
mysqld to open. This is the real value
allowed by the system and might be different from the value
you gave using the
--open-files-limit
option to
mysqld or mysqld_safe.
The value is 0 on systems where MySQL can't change the number
of open files.
Command-Line Format | --optimizer_prune_level[=#] |
|
Config-File Format | optimizer_prune_level |
|
Option Sets Variable | Yes, optimizer_prune_level
|
|
Variable Name | optimizer_prune_level |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | 1 |
Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. A value of 0 disables heuristics so that the optimizer performs an exhaustive search. A value of 1 causes the optimizer to prune plans based on the number of rows retrieved by intermediate plans.
Command-Line Format | --optimizer_search_depth[=#] |
|
Config-File Format | optimizer_search_depth |
|
Option Sets Variable | Yes, optimizer_search_depth
|
|
Variable Name | optimizer_search_depth |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 62 |
The maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to generate an execution plan for a query. Values smaller than the number of relations in a query return an execution plan quicker, but the resulting plan may be far from being optimal. If set to 0, the system automatically picks a reasonable value. If set to 63, the optimizer switches to the algorithm used in MySQL 5.0.0 (and previous versions) for performing searches. The value of 63 is deprecated and will be treated as invalid in a future MySQL release.
Version Introduced | 5.1.34 | |
Command-Line Format | --optimizer_switch=value |
|
Config-File Format | optimizer_switch |
|
Option Sets Variable | Yes, optimizer_switch
|
|
Variable Name | optimizer_switch |
|
Variable Scope | Both | |
Dynamic Variable | Yes |
The optimizer_switch
system
variable enables control over optimizer behaviors. The value
of this variable is a set of flags, each of which has a value
of on
or off
to indicate
whether the corresponding optimizer behavior is enabled or
disabled. This variable has global and session values and be
changed at runtime. The global default can be set at server
startup.
To see the current set of optimizer flags, select the variable value:
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,index_merge_intersection=on
For more information about the syntax of this variable and the
optimizer behaviors that it controls, see
Section 7.2.18, “Using optimizer_switch
to Control the
Optimizer”.
Command-Line Format | --pid-file=name |
|
Config-File Format | pid-file |
|
Option Sets Variable | Yes, pid_file
|
|
Variable Name | pid_file |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
The path name of the process ID (PID) file. This variable can
be set with the --pid-file
option.
Version Introduced | 5.1.2 | |
Command-Line Format | --plugin_dir=name |
|
Config-File Format | plugin_dir |
|
Option Sets Variable | Yes, plugin_dir
|
|
Variable Name | plugin_dir |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
|
Default | /usr/local/mysql/lib/mysql |
The path name of the plugin directory. This variable was added in MySQL 5.1.2.
Command-Line Format | --port=# |
|
Config-File Format | port |
|
Option Sets Variable | Yes, port
|
|
Variable Name | port |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | numeric |
|
Default | 3306 |
The number of the port on which the server listens for TCP/IP
connections. This variable can be set with the
--port
option.
Command-Line Format | --preload_buffer_size=# |
|
Config-File Format | preload_buffer_size |
|
Option Sets Variable | Yes, preload_buffer_size
|
|
Variable Name | preload_buffer_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 32768 |
|
Range | 1024-1073741824 |
The size of the buffer that is allocated when preloading indexes.
The current number of prepared statements. (The maximum number
of statements is given by the
max_prepared_stmt_count
system variable.) This variable was added in MySQL 5.1.10. In
MySQL 5.1.14, it was converted to the global
Prepared_stmt_count
status
variable.
Variable Name | protocol_version |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | numeric |
The version of the client/server protocol used by the MySQL server.
Variable Name | pseudo_thread_id |
|
Variable Scope | Session | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
This variable is for internal server use.
Command-Line Format | --query_alloc_block_size=# |
|
Config-File Format | query_alloc_block_size |
|
Option Sets Variable | Yes, query_alloc_block_size
|
|
Variable Name | query_alloc_block_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 8192 |
|
Range | 1024-4294967295 |
|
Block Size | 1024 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 8192 |
|
Range | 1024-18446744073709547520 |
|
Block Size | 1024 |
The allocation size of memory blocks that are allocated for objects created during statement parsing and execution. If you have problems with memory fragmentation, it might help to increase this parameter.
Command-Line Format | --query_cache_limit=# |
|
Config-File Format | query_cache_limit |
|
Option Sets Variable | Yes, query_cache_limit
|
|
Variable Name | query_cache_limit |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 1048576 |
|
Range | 0-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 1048576 |
|
Range | 0-18446744073709547520 |
Don't cache results that are larger than this number of bytes. The default value is 1MB.
Command-Line Format | --query_cache_min_res_unit=# |
|
Config-File Format | query_cache_min_res_unit |
|
Option Sets Variable | Yes, query_cache_min_res_unit
|
|
Variable Name | query_cache_min_res_unit |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 4096 |
|
Range | 512-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 4096 |
|
Range | 512-18446744073709547520 |
The minimum size (in bytes) for blocks allocated by the query cache. The default value is 4096 (4KB). Tuning information for this variable is given in Section 7.5.5.3, “Query Cache Configuration”.
Command-Line Format | --query_cache_size=# |
|
Config-File Format | query_cache_size |
|
Option Sets Variable | Yes, query_cache_size
|
|
Variable Name | query_cache_size |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 0 |
|
Range | 0-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 0 |
|
Range | 0-18446744073709547520 |
The amount of memory allocated for caching query results. The
default value is 0, which disables the query cache. The
allowable values are multiples of 1024; other values are
rounded down to the nearest multiple. Note that
query_cache_size
bytes of
memory are allocated even if
query_cache_type
is set to 0.
See Section 7.5.5.3, “Query Cache Configuration”, for more
information.
The query cache needs a minimum size of about 40KB to allocate
its structures. (The exact size depends on system
architecture.) If you set the value of
query_cache_size
too small,
you'll get a warning, as described in
Section 7.5.5.3, “Query Cache Configuration”.
Command-Line Format | --query_cache_type=# |
|
Config-File Format | query_cache_type |
|
Option Sets Variable | Yes, query_cache_type
|
|
Variable Name | query_cache_type |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | enumeration |
|
Default | 1 |
|
Valid Values |
0 , 1 , 2
|
Set the query cache type. Setting the
GLOBAL
value sets the type for all clients
that connect thereafter. Individual clients can set the
SESSION
value to affect their own use of
the query cache. Possible values are shown in the following
table.
Option | Description |
0 or OFF
|
Don't cache results in or retrieve results from the query cache. Note
that this does not deallocate the query cache buffer.
To do that, you should set
query_cache_size to
0. |
1 or ON
|
Cache all cacheable query results except for those that begin with
SELECT SQL_NO_CACHE . |
2 or DEMAND
|
Cache results only for cacheable queries that begin with SELECT
SQL_CACHE . |
This variable defaults to ON
.
Any unique prefix of a valid value may be used to set the value of this variable.
Command-Line Format | --query_cache_wlock_invalidate |
|
Config-File Format | query_cache_wlock_invalidate |
|
Option Sets Variable | Yes, query_cache_wlock_invalidate
|
|
Variable Name | query_cache_wlock_invalidate |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | FALSE |
Normally, when one client acquires a WRITE
lock on a MyISAM
table, other clients are
not blocked from issuing statements that read from the table
if the query results are present in the query cache. Setting
this variable to 1 causes acquisition of a
WRITE
lock for a table to invalidate any
queries in the query cache that refer to the table. This
forces other clients that attempt to access the table to wait
while the lock is in effect.
Command-Line Format | --query_prealloc_size=# |
|
Config-File Format | query_prealloc_size |
|
Option Sets Variable | Yes, query_prealloc_size
|
|
Variable Name | query_prealloc_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 8192 |
|
Range | 8192-4294967295 |
|
Block Size | 1024 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 8192 |
|
Range | 8192-18446744073709547520 |
|
Block Size | 1024 |
The size of the persistent buffer used for statement parsing
and execution. This buffer is not freed between statements. If
you are running complex queries, a larger
query_prealloc_size
value
might be helpful in improving performance, because it can
reduce the need for the server to perform memory allocation
during query execution operations.
Command-Line Format | --range_alloc_block_size=# |
|
Config-File Format | range_alloc_block_size |
|
Option Sets Variable | Yes, range_alloc_block_size
|
|
Variable Name | range_alloc_block_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 4096 |
|
Range | 4096-4294967295 |
|
Block Size | 1024 |
The size of blocks that are allocated when doing range optimization.
Command-Line Format | --read_buffer_size=# |
|
Config-File Format | read_buffer_size |
|
Option Sets Variable | Yes, read_buffer_size
|
|
Variable Name | read_buffer_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 131072 |
|
Range | 8200-2147479552 |
Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB.
The maximum allowable setting for
read_buffer_size
is 2GB.
read_buffer_size
and
read_rnd_buffer_size
are not
specific to any storage engine and apply in a general manner
for optimization. See Section 7.5.8, “How MySQL Uses Memory”, for
example.
Command-Line Format | --read_only |
|
Config-File Format | read_only |
|
Option Sets Variable | Yes, read_only
|
|
Variable Name | read_only |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
This variable is off by default. When it is enabled, the
server allows no updates except from users that have the
SUPER
privilege or (on a slave
server) from updates performed by slave threads. On a slave
server, this can be useful to ensure that the slave accepts
updates only from its master server and not from clients. This
variable does not apply to TEMPORARY
tables, nor does it prevent the server from inserting rows
into the log tables (see Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”).
read_only
exists only as a
GLOBAL
variable, so changes to its value
require the SUPER
privilege.
Changes to read_only
on a
master server are not replicated to slave servers. The value
can be set on a slave server independent of the setting on the
master.
As of MySQL 5.1.15, the following conditions apply:
If you attempt to enable
read_only
while you have
any explicit locks (acquired with
LOCK TABLES
) or have a
pending transaction, an error occurs.
If you attempt to enable
read_only
while other
clients hold explicit table locks or have pending
transactions, the attempt blocks until the locks are
released and the transactions end. While the attempt to
enable read_only
is
pending, requests by other clients for table locks or to
begin transactions also block until
read_only
has been set.
read_only
can be enabled
while you hold a global read lock (acquired with
FLUSH TABLES WITH
READ LOCK
) because that does not involve table
locks.
Command-Line Format | --read_rnd_buffer_size=# |
|
Config-File Format | read_rnd_buffer_size |
|
Option Sets Variable | Yes, read_rnd_buffer_size
|
|
Variable Name | read_rnd_buffer_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 262144 |
|
Range | 8200-4294967295 |
When reading rows in sorted order following a key-sorting
operation, the rows are read through this buffer to avoid disk
seeks. See Section 7.2.13, “ORDER BY
Optimization”. Setting
the variable to a large value can improve ORDER
BY
performance by a lot. However, this is a buffer
allocated for each client, so you should not set the global
variable to a large value. Instead, change the session
variable only from within those clients that need to run large
queries.
The maximum allowable setting for
read_rnd_buffer_size
is 2GB.
read_buffer_size
and
read_rnd_buffer_size
are not
specific to any storage engine and apply in a general manner
for optimization. See Section 7.5.8, “How MySQL Uses Memory”, for
example.
Command-Line Format | --relay_log_purge |
|
Config-File Format | relay_log_purge |
|
Option Sets Variable | Yes, relay_log_purge
|
|
Variable Name | relay_log_purge |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | TRUE |
Disables or enables automatic purging of relay log files as
soon as they are not needed any more. The default value is 1
(ON
).
Command-Line Format | --relay_log_space_limit=# |
|
Config-File Format | relay_log_space_limit |
|
Option Sets Variable | Yes, relay_log_space_limit
|
|
Variable Name | relay_log_space_limit |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 0 |
|
Range | 0-4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 0 |
|
Range | 0-18446744073709547520 |
The maximum amount of space to use for all relay logs.
The value of the --report-host
option. This variable was added in MySQL 5.1.24.
The value of the
--report-password
option. This
variable was added in MySQL 5.1.24.
The value of the --report-port
option. This variable was added in MySQL 5.1.24.
The value of the --report-user
option. This variable was added in MySQL 5.1.24.
Command-Line Format | --secure-auth |
|
Config-File Format | secure-auth |
|
Option Sets Variable | Yes, secure_auth
|
|
Variable Name | secure_auth |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | FALSE |
If the MySQL server has been started with the
--secure-auth
option, it blocks
connections from all accounts that have passwords stored in
the old (pre-4.1) format. In that case, the value of this
variable is ON
, otherwise it is
OFF
.
You should enable this option if you want to prevent all use of passwords employing the old format (and hence insecure communication over the network).
Server startup fails with an error if this option is enabled
and the privilege tables are in pre-4.1 format. See
Section B.5.2.4, “Client does not support authentication protocol
”.
Version Introduced | 5.1.17 | |
Command-Line Format | --secure-file-priv |
|
Config-File Format | secure-file-priv |
|
Option Sets Variable | Yes, secure_file_priv
|
|
Variable Name | secure_file_priv |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | string |
By default, this variable is empty. If set to the name of a
directory, it limits the effect of the
LOAD_FILE()
function and the
LOAD DATA
and
SELECT ... INTO
OUTFILE
statements to work only with files in that
directory.
This variable was added in MySQL 5.1.17.
Command-Line Format | --server-id=# |
|
Config-File Format | server-id |
|
Option Sets Variable | Yes, server_id
|
|
Variable Name | server_id |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
|
Range | 0-4294967295 |
The server ID, used in replication to give each master and
slave a unique identity. This variable is set by the
--server-id
option. For each
server participating in replication, you should pick a
positive integer in the range from 1 to
232 – 1 to act as that
server's ID.
Variable Name | shared_memory |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Platform Specific | windows |
(Windows only.) Whether the server allows shared-memory connections.
Variable Name | shared_memory_base_name |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Platform Specific | windows |
(Windows only.) The name of shared memory to use for
shared-memory connections. This is useful when running
multiple MySQL instances on a single physical machine. The
default name is MYSQL
. The name is case
sensitive.
This is OFF
if mysqld
uses external locking, ON
if external
locking is disabled.
This is ON
if the server allows only local
(non-TCP/IP) connections. On Unix, local connections use a
Unix socket file. On Windows, local connections use a named
pipe or shared memory. On NetWare, only TCP/IP connections are
supported, so do not set this variable to
ON
. This variable can be set to
ON
with the
--skip-networking
option.
This prevents people from using the SHOW
DATABASES
statement if they do not have the
SHOW DATABASES
privilege. This
can improve security if you have concerns about users being
able to see databases belonging to other users. Its effect
depends on the SHOW DATABASES
privilege: If the variable value is ON
, the
SHOW DATABASES
statement is
allowed only to users who have the SHOW
DATABASES
privilege, and the statement displays all
database names. If the value is OFF
,
SHOW DATABASES
is allowed to
all users, but displays the names of only those databases for
which the user has the SHOW
DATABASES
or other privilege.
Command-Line Format | --slow_launch_time=# |
|
Config-File Format | slow_launch_time |
|
Option Sets Variable | Yes, slow_launch_time
|
|
Variable Name | slow_launch_time |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 2 |
If creating a thread takes longer than this many seconds, the
server increments the
Slow_launch_threads
status
variable.
Whether the slow query log is enabled. The value can be 0 (or
OFF
) to disable the log or 1 (or
ON
) to enable the log. The default value
depends on whether the
--slow_query_log
option is
given (--log-slow-queries
before MySQL 5.1.29). The destination for log output is
controlled by the log_output
system variable; if that value is NONE
, no
log entries are written even if the log is enabled. The
slow_query_log
variable was
added in MySQL 5.1.12.
Version Introduced | 5.1.12 | |
Command-Line Format | --slow-query-log-file=file_name |
|
Config-File Format | slow_query_log_file |
|
Option Sets Variable | Yes, slow_query_log_file
|
|
Variable Name | slow_query_log_file |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | filename |
The name of the slow query log file. The default value is
,
but the initial value can be changed with the
host_name
-slow.log--slow_query_log_file
option
(--log-slow-queries
before
MySQL 5.1.29). This variable was added in MySQL 5.1.12.
Command-Line Format | --socket=name |
|
Config-File Format | socket |
|
Option Sets Variable | Yes, socket
|
|
Variable Name | socket |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
|
Default | /tmp/mysql.sock |
On Unix platforms, this variable is the name of the socket
file that is used for local client connections. The default is
/tmp/mysql.sock
. (For some distribution
formats, the directory might be different, such as
/var/lib/mysql
for RPMs.)
On Windows, this variable is the name of the named pipe that
is used for local client connections. The default value is
MySQL
(not case sensitive).
Command-Line Format | --sort_buffer_size=# |
|
Config-File Format | sort_buffer_size |
|
Option Sets Variable | Yes, sort_buffer_size
|
|
Variable Name | sort_buffer_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 2097144 |
|
Max Value | 4294967295 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 2097144 |
|
Max Value | 18446744073709547520 |
Each thread that needs to do a sort allocates a buffer of this
size. Increase this value for faster ORDER
BY
or GROUP BY
operations. See
Section B.5.4.4, “Where MySQL Stores Temporary Files”.
The maximum allowable setting for
sort_buffer_size
is 4GB. As
of MySQL 5.1.23, values larger than 4GB are allowed for 64-bit
platforms (except 64-bit Windows, for which large values are
truncated to 4GB with a warning).
Command-Line Format | --sql-mode=name |
|
Config-File Format | sql-mode |
|
Option Sets Variable | Yes, sql_mode
|
|
Variable Name | sql_mode |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | set |
|
Default | '' |
|
Valid Values |
ALLOW_INVALID_DATES , ANSI_QUOTES , ERROR_FOR_DIVISION_BY_ZERO , HIGH_NOT_PRECEDENCE , IGNORE_SPACE , NO_AUTO_CREATE_USER , NO_AUTO_VALUE_ON_ZERO , NO_BACKSLASH_ESCAPES , NO_DIR_IN_CREATE , NO_ENGINE_SUBSTITUTION , NO_FIELD_OPTIONS , NO_KEY_OPTIONS , NO_TABLE_OPTIONS , NO_UNSIGNED_SUBTRACTION , NO_ZERO_DATE , NO_ZERO_IN_DATE , ONLY_FULL_GROUP_BY , PAD_CHAR_TO_FULL_LENGTH , PIPES_AS_CONCAT , REAL_AS_FLOAT , STRICT_ALL_TABLES , STRICT_TRANS_TABLES
|
The current server SQL mode, which can be set dynamically. See Section 5.1.8, “Server SQL Modes”.
Variable Name | sql_select_limit |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
The maximum number of rows to return from
SELECT
statements. The default
value for a new connection is the maximum number of rows that
the server allows per table, which depends on the server
configuration and may be affected if the server build was
configured with
--with-big-tables
. Typical
default values are (232)–1 or
(264)–1. If you have changed
the limit, the default value can be restored by assigning a
value of DEFAULT
.
If a SELECT
has a
LIMIT
clause, the LIMIT
takes precedence over the value of
sql_select_limit
.
sql_select_limit
does not
apply to SELECT
statements
executed within stored routines. It also does not apply to
SELECT
statements that do not
produce a result set to be returned to the client. These
include SELECT
statements in
subqueries,
CREATE TABLE ...
SELECT
, and
INSERT INTO ...
SELECT
.
Version Introduced | 5.1.11 | |
Command-Line Format | --ssl-ca=name |
|
Config-File Format | ssl-ca |
|
Option Sets Variable | Yes, ssl_ca
|
|
Variable Name | ssl_ca |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
The path to a file with a list of trusted SSL CAs. This variable was added in MySQL 5.1.11.
Version Introduced | 5.1.11 | |
Command-Line Format | --ssl-capath=name |
|
Config-File Format | ssl-capath |
|
Option Sets Variable | Yes, ssl_capath
|
|
Variable Name | ssl_capath |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
The path to a directory that contains trusted SSL CA certificates in PEM format. This variable was added in MySQL 5.1.11.
Version Introduced | 5.1.11 | |
Command-Line Format | --ssl-cert=name |
|
Config-File Format | ssl-cert |
|
Option Sets Variable | Yes, ssl_cert
|
|
Variable Name | ssl_cert |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
The name of the SSL certificate file to use for establishing a secure connection. This variable was added in MySQL 5.1.11.
Version Introduced | 5.1.11 | |
Command-Line Format | --ssl-cipher=name |
|
Config-File Format | ssl-cipher |
|
Option Sets Variable | Yes, ssl_cipher
|
|
Variable Name | ssl_cipher |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
A list of allowable ciphers to use for SSL encryption. This variable was added in MySQL 5.1.11.
Version Introduced | 5.1.11 | |
Command-Line Format | --ssl-key=name |
|
Config-File Format | ssl-key |
|
Option Sets Variable | Yes, ssl_key
|
|
Variable Name | ssl_key |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | string |
The name of the SSL key file to use for establishing a secure connection. This variable was added in MySQL 5.1.11.
Variable Name | storage_engine |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | enumeration |
The default storage engine (table type). To set the storage
engine at server startup, use the
--default-storage-engine
option. See Section 5.1.2, “Server Command Options”.
Command-Line Format | --sync-frm |
|
Config-File Format | sync_frm |
|
Option Sets Variable | Yes, sync_frm
|
|
Variable Name | sync_frm |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | TRUE |
If this variable is set to 1, when any nontemporary table is
created its .frm
file is synchronized to
disk (using fdatasync()
). This is slower
but safer in case of a crash. The default is 1.
Variable Name | system_time_zone |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | string |
The server system time zone. When the server begins executing,
it inherits a time zone setting from the machine defaults,
possibly modified by the environment of the account used for
running the server or the startup script. The value is used to
set system_time_zone
.
Typically the time zone is specified by the
TZ
environment variable. It also can be
specified using the
--timezone
option of the
mysqld_safe script.
The system_time_zone
variable
differs from time_zone
.
Although they might have the same value, the latter variable
is used to initialize the time zone for each client that
connects. See Section 9.6, “MySQL Server Time Zone Support”.
Version Removed | 5.1.3 | |
Version Deprecated | 5.1.3 | |
Command-Line Format | --table_cache=# |
|
Config-File Format | table_cache |
|
Option Sets Variable | Yes, table_cache
|
|
Variable Name | table_cache |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Deprecated | 5.1.3, by table_open_cache
|
|
Permitted Values | ||
Type | numeric |
|
Default | 64 |
|
Range | 1-524288 |
This is the old name of
table_open_cache
before MySQL
5.1.3. From 5.1.3 on, use
table_open_cache
instead.
Version Introduced | 5.1.3 | |
Command-Line Format | --table_definition_cache=# |
|
Config-File Format | table_definition_cache |
|
Option Sets Variable | Yes, table_definition_cache
|
|
Variable Name | table_definition_cache |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values (<= 5.1.24) | ||
Type | numeric |
|
Default | 128 |
|
Range | 1-524288 |
|
Permitted Values (>= 5.1.25) | ||
Type | numeric |
|
Default | 256 |
|
Range | 256-524288 |
The number of table definitions that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. This variable was added in MySQL 5.1.3. The minimum and default values are 1 and 128 before MySQL 5.1.25. The minimum and default are both 256 as of MySQL 5.1.25.
Command-Line Format | --table_lock_wait_timeout=# |
|
Config-File Format | table_lock_wait_timeout |
|
Option Sets Variable | Yes, table_lock_wait_timeout
|
|
Variable Name | table_lock_wait_timeout |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 50 |
|
Range | 1-1073741824 |
This variable is unused.
Version Introduced | 5.1.3 | |
Command-Line Format | --table-open-cache=# |
|
Config-File Format | table_open_cache |
|
Variable Name | table_open_cache |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 64 |
|
Range | 64-524288 |
The number of open tables for all threads. Increasing this
value increases the number of file descriptors that
mysqld requires. You can check whether you
need to increase the table cache by checking the
Opened_tables
status
variable. See Section 5.1.7, “Server Status Variables”. If
the value of Opened_tables
is large and you don't do
FLUSH TABLES
often (which just forces all tables to be closed and
reopened), then you should increase the value of the
table_open_cache
variable.
For more information about the table cache, see
Section 7.4.8, “How MySQL Opens and Closes Tables”. Before MySQL 5.1.3, this
variable is called
table_cache
.
Variable Name | table_type |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Deprecated | 5.2.5, by storage_engine
|
|
Permitted Values | ||
Type | enumeration |
This variable is a synonym for
storage_engine
. In MySQL
5.1,
storage_engine
is the
preferred name; table_type
is
deprecated and is removed in MySQL 5.5.
Command-Line Format | --thread_cache_size=# |
|
Config-File Format | thread_cache_size |
|
Option Sets Variable | Yes, thread_cache_size
|
|
Variable Name | thread_cache_size |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 0 |
|
Range | 0-16384 |
How many threads the server should cache for reuse. When a
client disconnects, the client's threads are put in the cache
if there are fewer than
thread_cache_size
threads
there. Requests for threads are satisfied by reusing threads
taken from the cache if possible, and only when the cache is
empty is a new thread created. This variable can be increased
to improve performance if you have a lot of new connections.
(Normally, this doesn't provide a notable performance
improvement if you have a good thread implementation.) By
examining the difference between the
Connections
and
Threads_created
status
variables, you can see how efficient the thread cache is. For
details, see Section 5.1.7, “Server Status Variables”.
Command-Line Format | --thread_concurrency=# |
|
Config-File Format | thread_concurrency |
|
Option Sets Variable | Yes, thread_concurrency
|
|
Variable Name | thread_concurrency |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | numeric |
|
Default | 10 |
|
Range | 1-512 |
This variable is specific to Solaris systems, for which
mysqld invokes the
thr_setconcurrency()
with the variable
value. This function enables applications to give the threads
system a hint about the desired number of threads that should
be run at the same time.
Version Introduced | 5.1.17 | |
Command-Line Format | --thread_handling=name |
|
Config-File Format | thread_handling |
|
Option Sets Variable | Yes, thread_handling
|
|
Variable Name | thread_handling |
|
Variable Scope | Global | |
Dynamic Variable | No |
The thread-handling model. The allowable values are
no-threads
(the server uses one thread) and
one-thread-per-connection
(the server uses
one thread to handle each client connection).
no-threads
is useful for debugging under
Linux; see
MySQL
Internals: Porting. This variable was added in MySQL
5.1.17
Command-Line Format | --thread_stack=# |
|
Config-File Format | thread_stack |
|
Option Sets Variable | Yes, thread_stack
|
|
Variable Name | thread_stack |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 196608 |
|
Range | 131072-4294967295 |
|
Block Size | 1024 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 262144 |
|
Range | 131072-18446744073709547520 |
|
Block Size | 1024 |
The stack size for each thread. Many of the limits detected by
the crash-me
test are dependent on this
value. See Section 7.1.3, “The MySQL Benchmark Suite”. The default of
192KB (256KB for 64-bit systems) is large enough for normal
operation. If the thread stack size is too small, it limits
the complexity of the SQL statements that the server can
handle, the recursion depth of stored procedures, and other
memory-consuming actions.
This variable is unused.
Command-Line Format | --default_time_zone=string |
|
Config-File Format | default_time_zone |
|
Variable Name | time_zone |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | string |
The current time zone. This variable is used to initialize the
time zone for each client that connects. By default, the
initial value of this is 'SYSTEM'
(which
means, “use the value of
system_time_zone
”).
The value can be specified explicitly at server startup with
the --default-time-zone
option.
See Section 9.6, “MySQL Server Time Zone Support”.
Command-Line Format | --timed_mutexes |
|
Config-File Format | timed_mutexes |
|
Option Sets Variable | Yes, timed_mutexes
|
|
Variable Name | timed_mutexes |
|
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | OFF |
This variable controls whether InnoDB
mutexes are timed. If this variable is set to 0 or
OFF
(the default), mutex timing is
disabled. If the variable is set to 1 or
ON
, mutex timing is enabled. With timing
enabled, the os_wait_times
value in the
output from SHOW
ENGINE INNODB MUTEX
indicates the amount of time (in
ms) spent in operating system waits. Otherwise, the value is
0.
Version Removed | 5.1.12 | |
Command-Line Format | --tmp_table_size=# |
|
Config-File Format | tmp_table_size |
|
Option Sets Variable | Yes, tmp_table_size
|
|
Variable Name | tmp_table_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | system dependent |
|
Range | 1024-4294967295 |
The maximum size of internal in-memory temporary tables. (The
actual limit is determined as the minimum of
tmp_table_size
and
max_heap_table_size
.) If an
in-memory temporary table exceeds the limit, MySQL
automatically converts it to an on-disk
MyISAM
table. Increase the value of
tmp_table_size
(and
max_heap_table_size
if
necessary) if you do many advanced GROUP BY
queries and you have lots of memory. This variable does not
apply to user-created MEMORY
tables.
You can compare the number of internal on-disk temporary
tables created to the total number of internal temporary
tables created by comparing the values of the
Created_tmp_disk_tables
and
Created_tmp_tables
variables.
See also Section 7.5.10, “How MySQL Uses Internal Temporary Tables”.
Command-Line Format | --tmpdir=name |
|
Config-File Format | tmpdir |
|
Option Sets Variable | Yes, tmpdir
|
|
Variable Name | tmpdir |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | filename |
The directory used for temporary files and temporary tables.
This variable can be set to a list of several paths that are
used in round-robin fashion. Paths should be separated by
colon characters (“:
”) on Unix
and semicolon characters (“;
”)
on Windows, NetWare, and OS/2.
The multiple-directory feature can be used to spread the load
between several physical disks. If the MySQL server is acting
as a replication slave, you should not set
tmpdir
to point to a
directory on a memory-based file system or to a directory that
is cleared when the server host restarts. A replication slave
needs some of its temporary files to survive a machine restart
so that it can replicate temporary tables or
LOAD DATA
INFILE
operations. If files in the temporary file
directory are lost when the server restarts, replication
fails. You can set the slave's temporary directory using the
slave_load_tmpdir
variable.
In that case, the slave won't use the general
tmpdir
value and you can set
tmpdir
to a nonpermanent
location.
Command-Line Format | --transaction_alloc_block_size=# |
|
Config-File Format | transaction_alloc_block_size |
|
Option Sets Variable | Yes, transaction_alloc_block_size
|
|
Variable Name | transaction_alloc_block_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 8192 |
|
Range | 1024-4294967295 |
|
Block Size | 1024 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 8192 |
|
Range | 1024-18446744073709547520 |
|
Block Size | 1024 |
The amount in bytes by which to increase a per-transaction
memory pool which needs memory. See the description of
transaction_prealloc_size
.
Command-Line Format | --transaction_prealloc_size=# |
|
Config-File Format | transaction_prealloc_size |
|
Option Sets Variable | Yes, transaction_prealloc_size
|
|
Variable Name | transaction_prealloc_size |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 |
|
Type | numeric |
|
Default | 4096 |
|
Range | 1024-4294967295 |
|
Block Size | 1024 |
|
Permitted Values | ||
Platform Bit Size | 64 |
|
Type | numeric |
|
Default | 4096 |
|
Range | 1024-18446744073709547520 |
|
Block Size | 1024 |
There is a per-transaction memory pool from which various
transaction-related allocations take memory. The initial size
of the pool in bytes is
transaction_prealloc_size
.
For every allocation that cannot be satisfied from the pool
because it has insufficient memory available, the pool is
increased by
transaction_alloc_block_size
bytes. When the transaction ends, the pool is truncated to
transaction_prealloc_size
bytes.
By making
transaction_prealloc_size
sufficiently large to contain all statements within a single
transaction, you can avoid many malloc()
calls.
Variable Name | tx_isolation |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | enumeration |
|
Default | REPEATABLE-READ |
|
Valid Values |
READ-UNCOMMITTED , READ-COMMITTED , REPEATABLE-READ , SERIALIZABLE
|
The default transaction isolation level. Defaults to
REPEATABLE-READ
.
This variable is set by the
SET
TRANSACTION ISOLATION LEVEL
statement. See
Section 12.3.6, “SET TRANSACTION
Syntax”. If you set
tx_isolation
directly to an
isolation level name that contains a space, the name should be
enclosed within quotes, with the space replaced by a dash. For
example:
SET tx_isolation = 'READ-COMMITTED';
Any unique prefix of a valid value may be used to set the value of this variable.
Command-Line Format | --updatable_views_with_limit=# |
|
Config-File Format | updatable_views_with_limit |
|
Option Sets Variable | Yes, updatable_views_with_limit
|
|
Variable Name | updatable_views_with_limit |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | boolean |
|
Default | 1 |
This variable controls whether updates to a view can be made
when the view does not contain all columns of the primary key
defined in the underlying table, if the update statement
contains a LIMIT
clause. (Such updates
often are generated by GUI tools.) An update is an
UPDATE
or
DELETE
statement. Primary key
here means a PRIMARY KEY
, or a
UNIQUE
index in which no column can contain
NULL
.
The variable can have two values:
1
or YES
: Issue a
warning only (not an error message). This is the default
value.
0
or NO
: Prohibit
the update.
The version number for the server.
The configure script has a
--with-comment
option that allows a comment
to be specified when building MySQL. This variable contains
the value of that comment.
The type of machine or architecture on which MySQL was built.
Variable Name | version_compile_os |
|
Variable Scope | Global | |
Dynamic Variable | No | |
Permitted Values | ||
Type | string |
The type of operating system on which MySQL was built.
Command-Line Format | --wait_timeout=# |
|
Config-File Format | wait_timeout |
|
Option Sets Variable | Yes, wait_timeout
|
|
Variable Name | wait_timeout |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 28800 |
|
Range | 1-31536000 |
|
Permitted Values | ||
Type (windows) | numeric |
|
Default | 28800 |
|
Range | 1-2147483 |
The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made via named pipes, or shared memory.
On thread startup, the session
wait_timeout
value is
initialized from the global
wait_timeout
value or from
the global
interactive_timeout
value,
depending on the type of client (as defined by the
CLIENT_INTERACTIVE
connect option to
mysql_real_connect()
). See
also interactive_timeout
.
MySQL Enterprise. Expert use of server system variables is part of the service offered by the MySQL Enterprise Monitor. To subscribe, see http://www.mysql.com/products/enterprise/advisors.html.
User Comments
if you set
[mysqld]
ft_min_word_len=3
you should also set
[myisamchk]
ft_min_word_len=3
if you use myisamchk
If your queries are mysteriously failing after running for sometime, even though the SQL syntax is correct, check how long the query was running compared to the interactive_timeout and wait_timeout variables. Prior to mysql 4.1.16 on BSD systems (including Mac OS X), the timeout period was not enforced. If you've recently upgraded to >4.1.16 and are getting these failures... this is most likely the problem.
Note that you can increase these variables, but show variables will not show the new setting until you login again because these variables are set at thread creation - so you'll need to start a new thread to see the changes.
http://bugs.mysql.com/bug.php?id=8731 for the curious.
To set variables in Query Browser use
set @@global.auto_increment_increment = <number>
and then run
set @@auto_increment_increment = <number>
will not run probably
The description for lower_case_table_names is a bit unclear with regards to the value 0 (but does explain 1 and 2).
A value of 0 means that table & database names are stored as-is, and name comparisons are case sensitive.
There's more information about lower_case_table_names on this page:
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
Documentation says "The maximum allowable setting for sort_buffer_size is 4GB." and there is reference to a chapter explaining temporary on-disk files.
However, the fact that on 32-bit GNU/Linux x86, "sort_buffer_size" must be a few MiB only to avoid exceeding maximum process space, as explained in http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html makes one infer that that sort_buffer_size is actually some kind of in-memory buffer.
Additionally, the formula should probably be extended:
total =
innodb_buffer_pool_size +
key_buffer_size +
innodb_additional_mem_pool_size +
innodb_log_buffer_size +
max_connections *
(sort_buffer_size +
read_buffer_size +
binlog_cache_size +
maximum_thread_stack_size);
Noting that "key_buffer_size" is a MyISAM parameter
Add your own comment.