Server System Variables

You are viewing an old version of this article. View the current version here.

Contents

  1. About the Server System Variables
  2. Setting Server System Variables
  3. List of Server System Variables
    1. allow_suspicious_udfs
    2. alter_algorithm
    3. analyze_sample_percentage
    4. aria_block_size
    5. aria_checkpoint_interval
    6. aria_checkpoint_log_activity
    7. aria_encrypt_tables
    8. aria_force_start_after_recovery_failures
    9. aria_group_commit
    10. aria_group_commit_interval
    11. aria_log_file_size
    12. aria_log_purge_type
    13. aria_max_sort_file_size
    14. aria_page_checksum
    15. aria_pagecache_age_threshold
    16. aria_pagecache_buffer_size
    17. aria_pagecache_division_limit
    18. aria_pagecache_file_hash_size
    19. aria_recover
    20. aria_repair_threads
    21. aria_sort_buffer_size
    22. aria_stats_method
    23. aria_sync_log_dir
    24. aria_used_for_temp_tables
    25. auto_increment_increment
    26. auto_increment_offset
    27. autocommit
    28. automatic_sp_privileges
    29. aws_key_management_key_spec
    30. aws_key_management_log_level
    31. aws_key_management_master_key_id
    32. aws_key_management_mock
    33. aws_key_management_region
    34. aws_key_management_request_timeout
    35. aws_key_management_rotate_key
    36. back_log
    37. basedir
    38. big_tables
    39. bind_address
    40. binlog_alter_two_phase
    41. binlog_annotate_row_events
    42. binlog_cache_size
    43. binlog_checksum
    44. binlog_commit_wait_count
    45. binlog_commit_wait_usec
    46. binlog_direct_non_transactional_updates
    47. binlog_do_db
    48. binlog_expire_logs_seconds
    49. binlog_file_cache_size
    50. binlog_format
    51. binlog_gtid_index
    52. binlog_gtid_index_page_size
    53. binlog_legacy_event_pos
    54. binlog_ignore_db
    55. binlog_ignore_db
    56. binlog_optimize_thread_scheduling
    57. binlog_do_db
    58. binlog_row_event_max_size
    59. binlog_row_metadata
    60. binlog_space_limit
    61. binlog_stmt_cache_size
    62. block_encryption_mode
    63. bulk_insert_buffer_size
    64. cassandra_default_thrift_host
    65. cassandra_failure_retries
    66. cassandra_insert_batch_size
    67. cassandra_multiget_batch_size
    68. cassandra_read_consistency
    69. cassandra_rnd_batch_size
    70. cassandra_write_consistency
    71. character_set_client
    72. character_set_collations
    73. character_set_connection
    74. character_set_database
    75. character_set_filesystem
    76. character_set_results
    77. character_set_server
    78. character_set_system
    79. character_sets_dir
    80. check_constraint_checks
    81. collation_connection
    82. collation_database
    83. collation_server
    84. column_compression_threshold
    85. column_compression_zlib_level
    86. column_compression_zlib_strategy
    87. column_compression_zlib_wrap
    88. completion_type
    89. concurrent_insert
    90. connect_class_path
    91. connect_cond_push
    92. connect_conv_size
    93. connect_default_depth
    94. connect_default_prec
    95. connect_enable_mongo
    96. connect_exact_info
    97. connect_force_bson
    98. connect_indx_map
    99. connect_java_wrapper
    100. connect_json_all_path
    101. connect_json_grp_size
    102. connect_json_null
    103. connect_jvm_path
    104. connect_timeout
    105. connect_type_conv
    106. connect_use_tempfile
    107. connect_work_size
    108. connect_xtrace
    109. core_file
    110. cracklib_password_check
    111. cracklib_password_check_dictionary
    112. datadir
    113. date_format
    114. datetime_format
    115. deadlock_search_depth_long
    116. deadlock_search_depth_short
    117. deadlock_timeout_long
    118. deadlock_timeout_short
    119. debug/debug_dbug
    120. debug_no_thread_alarm
    121. debug_sync
    122. default_master_connection
    123. default_password_lifetime
    124. default_regex_flags
    125. default_storage_engine
    126. default_table_type
    127. default_tmp_storage_engine
    128. default_week_format
    129. delay_key_write
    130. delayed_insert_limit
    131. delayed_insert_timeout
    132. delayed_queue_size
    133. disconnect_on_expired_password
    134. div_precision_increment
    135. encrypt_binlog
    136. encrypt_tmp_disk_tables
    137. encrypt_tmp_files
    138. encryption_algorithm
    139. enforce_storage_engine
    140. engine_condition_pushdown
    141. eq_range_index_dive_limit
    142. error_count
    143. event_scheduler
    144. expensive_subquery_limit
    145. expire_logs_days
    146. explicit_defaults_for_timestamp
    147. external_user
    148. extra_max_connections
    149. extra_port
    150. feedback
    151. feedback_http_proxy
    152. feedback_send_retry_wait
    153. feedback_send_timeout
    154. feedback_server_uid
    155. feedback_url
    156. feedback_user_info
    157. file_key_management_encryption_algorithm
    158. file_key_management_filekey
    159. file_key_management_filename
    160. flush
    161. flush_time
    162. foreign_key_checks
    163. ft_boolean_syntax
    164. ft_max_word_len
    165. ft_min_word_len
    166. ft_query_expansion_limit
    167. ft_stopword_file
    168. general_log
    169. general_log_file
    170. group_concat_max_len
    171. gssapi_keytab_path
    172. gssapi_principal_name
    173. gssapi_mech_name
    174. gtid_binlog_pos
    175. gtid_binlog_state
    176. gtid_cleanup_batch_size
    177. gtid_current_pos
    178. gtid_domain_id
    179. gtid_ignore_duplicates
    180. gtid_seq_no
    181. gtid_slave_pos
    182. gtid_strict_mode
    183. gtid_pos_auto_engines
    184. handlersocket_accept_balance
    185. handlersocket_address
    186. handlersocket_backlog
    187. handlersocket_epoll
    188. handlersocket_port
    189. handlersocket_port_wr
    190. handlersocket_sndbuf
    191. handlersocket_rcvbuf
    192. handlersocket_readsize
    193. handlersocket_threads
    194. handlersocket_threads_wr
    195. handlersocket_timeout
    196. handlersocket_verbose
    197. handlersocket_wrlock_timeout
    198. have_compress
    199. have_crypt
    200. have_csv
    201. have_dynamic_loading
    202. have_geometry
    203. have_innodb
    204. have_ndbcluster
    205. have_openssl
    206. have_partitioning
    207. have_profiling
    208. have_query_cache
    209. have_rtree_keys
    210. have_ssl
    211. have_symlink
    212. histogram_size
    213. histogram_type
    214. host_cache_size
    215. hostname
    216. identity
    217. ignore_builtin_innodb
    218. idle_readonly_transaction_timeout
    219. idle_transaction_timeout
    220. idle_write_transaction_timeout
    221. ignore_db_dirs
    222. in_predicate_conversion_threshold
    223. in_transaction
    224. init_connect
    225. init_file
    226. init_slave
    227. innodb_adaptive_checkpoint
    228. innodb_adaptive_flushing
    229. innodb_adaptive_flushing_lwm
    230. innodb_adaptive_flushing_method
    231. innodb_adaptive_hash_index
    232. innodb_adaptive_hash_index_partitions
    233. innodb_adaptive_hash_index_parts
    234. innodb_adaptive_max_sleep_delay
    235. innodb_additional_mem_pool_size
    236. innodb_alter_copy_bulk
    237. innodb_api_bk_commit_interval
    238. innodb_api_disable_rowlock
    239. innodb_api_enable_binlog
    240. innodb_api_enable_mdl
    241. innodb_api_trx_level
    242. innodb_auto_lru_dump
    243. innodb_autoextend_increment
    244. innodb_autoinc_lock_mode
    245. innodb_background_scrub_data_check_interval
    246. innodb_background_scrub_data-compressed
    247. innodb_background_scrub_data_interval
    248. innodb_background_scrub_data-uncompressed
    249. innodb_blocking_buffer_pool_restore
    250. innodb_buf_dump_status_frequency
    251. innodb_buffer_pool_chunk_size
    252. innodb_buffer_pool_dump_at_shutdown
    253. innodb_buffer_pool_dump_now
    254. innodb_buffer_pool_evict
    255. innodb_buffer_pool_filename
    256. innodb_buffer_pool_instances
    257. innodb_buffer_pool_load_abort
    258. innodb_buffer_pool_load_at_startup
    259. innodb_buffer_pool_load_now
    260. innodb_buffer_pool_load_pages_abort
    261. innodb_buffer_pool_populate
    262. innodb_buffer_pool_restore_at_startup
    263. innodb_buffer_pool_shm_checksum
    264. innodb_buffer_pool_shm_key
    265. innodb_buffer_pool_size
    266. innodb_change_buffer_dump
    267. innodb_change_buffer_max_size
    268. innodb_change_buffering
    269. innodb_change_buffering_debug
    270. innodb_checkpoint_age_target
    271. innodb_checksum_algorithm
    272. innodb_checksums
    273. innodb_cleaner_lsn_age_factor
    274. innodb_cmp_per_index_enabled
    275. innodb_commit_concurrency
    276. innodb_compression_algorithm
    277. innodb_compression_default
    278. innodb_compression_failure_threshold_pct
    279. innodb_compression_level
    280. innodb_compression_pad_pct_max
    281. innodb_concurrency_tickets
    282. innodb_corrupt_table_action
    283. innodb_data_file_buffering
    284. innodb_data_file_path
    285. innodb_data_file_write_through
    286. innodb_data_home_dir
    287. innodb_deadlock_detect
    288. innodb_deadlock_report
    289. innodb_default_encryption_key_id
    290. innodb_default_page_encryption_key
    291. innodb_default_row_format
    292. innodb_defragment
    293. innodb_defragment_fill_factor
    294. innodb_defragment_fill_factor_n_recs
    295. innodb_defragment_frequency
    296. innodb_defragment_n_pages
    297. innodb_defragment_stats_accuracy
    298. innodb_dict_size_limit
    299. innodb_disable_sort_file_cache
    300. innodb_disallow_writes
    301. innodb_doublewrite
    302. innodb_doublewrite_file
    303. innodb_empty_free_list_algorithm
    304. innodb_enable_unsafe_group_commit
    305. innodb_encrypt_log
    306. innodb_encrypt_tables
    307. innodb_encrypt_temporary_tables
    308. innodb_encryption_rotate_key_age
    309. innodb_encryption_rotation_iops
    310. innodb_encryption_threads
    311. innodb_extra_rsegments
    312. innodb_extra_undoslots
    313. innodb_fake_changes
    314. innodb_fast_checksum
    315. innodb_fast_shutdown
    316. innodb_fatal_semaphore_wait_threshold
    317. innodb_file_format
    318. innodb_file_format_check
    319. innodb_file_format_max
    320. innodb_file_per_table
    321. innodb_fill_factor
    322. innodb_flush_log_at_timeout
    323. innodb_flush_log_at_trx_commit
    324. innodb_flush_method
    325. innodb_flush_neighbor_pages
    326. innodb_flush_neighbors
    327. innodb_flush_sync
    328. innodb_flushing_avg_loops
    329. innodb_force_load_corrupted
    330. innodb_force_primary_key
    331. innodb_force_recovery
    332. innodb_foreground_preflush
    333. innodb_ft_aux_table
    334. innodb_ft_cache_size
    335. innodb_ft_enable_diag_print
    336. innodb_ft_enable_stopword
    337. innodb_ft_max_token_size
    338. innodb_ft_min_token_size
    339. innodb_ft_num_word_optimize
    340. innodb_ft_result_cache_limit
    341. innodb_ft_server_stopword_table
    342. innodb_ft_sort_pll_degree
    343. innodb_ft_total_cache_size
    344. innodb_ft_user_stopword_table
    345. innodb_ibuf_accel_rate
    346. innodb_ibuf_active_contract
    347. innodb_ibuf_max_size
    348. innodb_idle_flush_pct
    349. innodb_immediate_scrub_data-uncompressed
    350. innodb_import_table_from_xtrabackup
    351. innodb_instant_alter_column_allowed
    352. innodb_instrument_semaphores
    353. innodb_io_capacity
    354. innodb_io_capacity_max
    355. innodb_kill_idle_transaction
    356. innodb_large_prefix
    357. innodb_lazy_drop_table
    358. innodb_lock_schedule_algorithm
    359. innodb_lock_wait_timeout
    360. innodb_locking_fake_changes
    361. innodb_locks_unsafe_for_binlog
    362. innodb_log_arch_dir
    363. innodb_log_arch_expire_sec
    364. innodb_log_archive
    365. innodb_log_block_size
    366. innodb_log_buffer_size
    367. innodb_log_checksum_algorithm
    368. innodb_log_checksums
    369. innodb_log_compressed_pages
    370. innodb_log_file_size
    371. innodb_log_file_write_through
    372. innodb_log_files_in_group
    373. innodb_log_group_home_dir
    374. innodb_log_spin_wait_delay
    375. innodb_log_optimize_ddl
    376. innodb_log_write_ahead_size
    377. innodb_lru_flush_size
    378. innodb_lru_scan_depth
    379. innodb_max_bitmap_file_size
    380. innodb_max_changed_pages
    381. innodb_max_dirty_pages_pct
    382. innodb_max_dirty_pages_pct_lwm
    383. innodb_max_purge_lag
    384. innodb_max_purge_lag_delay
    385. innodb_max_purge_lag_wait
    386. innodb_max_undo_log_size
    387. innodb_merge_sort_block_size
    388. innodb_mirrored_log_groups
    389. innodb_monitor_disable
    390. innodb_monitor_enable
    391. innodb_monitor_reset
    392. innodb_monitor_reset_all
    393. innodb_mtflush_threads
    394. innodb_numa_interleave
    395. innodb_old_blocks_pct
    396. innodb_old_blocks_time
    397. innodb_online_alter_log_max_size
    398. innodb_open_files
    399. innodb_optimize_fulltext_only
    400. innodb_page_cleaners
    401. innodb_page_size
    402. innodb_prefix_index_cluster_optimization
    403. innodb_print_all_deadlocks
    404. innodb_purge_batch_size
    405. innodb_purge_rseg_truncate_frequency
    406. innodb_purge_threads
    407. innodb_random_read_ahead
    408. innodb_read_ahead
    409. innodb_read_ahead_threshold
    410. innodb_read_io_threads
    411. innodb_read_only
    412. innodb_recovery_stats
    413. innodb_recovery_update_relay_log
    414. innodb_replication_delay
    415. innodb_rollback_on_timeout
    416. innodb_rollback_segments
    417. innodb_safe_truncate
    418. innodb_sched_priority_cleaner
    419. innodb_scrub_log
    420. innodb_scrub_log_interval
    421. innodb_scrub_log_speed
    422. innodb_show_locks_held
    423. innodb_show_verbose_locks
    424. innodb_simulate_comp_failures
    425. innodb_snapshot_isolation
    426. innodb_sort_buffer_size
    427. innodb_spin_wait_delay
    428. innodb_stats_auto_recalc
    429. innodb_stats_auto_update
    430. innodb_stats_include_delete_marked
    431. innodb_stats_method
    432. innodb_stats_modified_counter
    433. innodb_stats_on_metadata
    434. innodb_stats_persistent
    435. innodb_stats_persistent_sample_pages
    436. innodb_stats_sample_pages
    437. innodb_stats_traditional
    438. innodb_stats_transient_sample_pages
    439. innodb_stats_update_need_lock
    440. innodb_status_output
    441. innodb_status_output_locks
    442. innodb_strict_mode
    443. innodb_support_xa
    444. innodb_sync_array_size
    445. innodb_sync_spin_loops
    446. innodb_table_locks
    447. innodb_temp_data_file_path
    448. innodb_thread_concurrency
    449. innodb_thread_concurrency_timer_based
    450. innodb_thread_sleep_delay
    451. innodb_tmpdir
    452. innodb_track_changed_pages
    453. innodb_track_redo_log_now
    454. innodb_truncate_temporary_tablespace_now
    455. innodb_undo_directory
    456. innodb_undo_log_truncate
    457. innodb_undo_logs
    458. innodb_use_atomic_writes
    459. innodb_use_fallocate
    460. innodb_use_global_flush_log_at_trx_commit
    461. innodb_use_native_aio
    462. innodb_use_purge_thread
    463. innodb_use_stacktrace
    464. innodb_use_sys_malloc
    465. innodb_use_sys_stats_table
    466. innodb_version
    467. innodb_write_io_threads
    468. insert_id
    469. interactive_timeout
    470. join_buffer_size
    471. join_buffer_space_limit
    472. join_cache_level
    473. keep_files_on_create
    474. key_buffer_size
    475. key_cache_age_threshold
    476. key_cache_block_size
    477. key_cache_division_limit
    478. key_cache_file_hash_size
    479. key_cache_segments
    480. large_files_support
    481. large_page_size
    482. large_pages
    483. last_gtid
    484. last_insert_id
    485. lc_messages
    486. lc_messages_dir
    487. lc_time_names
    488. license
    489. local_infile
    490. lock_wait_timeout
    491. locked_in_memory
    492. log
    493. log_bin
    494. log_bin_basename
    495. log_bin_compress
    496. log_bin_compress_min_len
    497. log_bin_index
    498. log_bin_trust_function_creators
    499. log_disabled_statements
    500. log_error
    501. log_output
    502. log_queries_not_using_indexes
    503. log_slave_updates
    504. log_slow_admin_statements
    505. log_slow_always_query_time
    506. log_slow_disabled_statements
    507. log_slow_filter
    508. log_slow_max_warnings
    509. log_slow_min_examined_row_limit
    510. log_slow_queries
    511. log_slow_query
    512. log_slow_query_file
    513. log_slow_query_time
    514. log_slow_rate_limit
    515. log_slow_slave_statements
    516. log_slow_verbosity
    517. log_tc_size
    518. log_warnings
    519. long_query_time
    520. low_priority_updates
    521. lower_case_file_system
    522. lower_case_table_names
    523. master_verify_checksum
    524. max_allowed_packet
    525. max_binlog_cache_size
    526. max_binlog_size
    527. max_binlog_stmt_cache_size
    528. max_binlog_total_size
    529. max_connect_errors
    530. max_connections
    531. max_delayed_threads
    532. max_digest_length
    533. max_error_count
    534. max_heap_table_size
    535. max_insert_delayed_threads
    536. max_join_size
    537. max_length_for_sort_data
    538. max_long_data_size
    539. max_password_errors
    540. max_prepared_stmt_count
    541. max_recursive_iterations
    542. max_relay_log_size
    543. max_rowid_filter_size
    544. max_seeks_for_key
    545. max_session_mem_used
    546. max_sort_length
    547. max_sp_recursion_depth
    548. max_statement_time
    549. max_tmp_space_usage
    550. max_tmp_tables
    551. max_total_tmp_space_usage
    552. max_user_connections
    553. max_write_lock_count
    554. metadata_locks_cache_size
    555. metadata_locks_hash_instances
    556. mhnsw_cache_size
    557. mhnsw_distance_function
    558. mhnsw_limit_multiplier
    559. mhnsw_max_edges_per_node
    560. mhnsw_min_limit
    561. min_examined_row_limit
    562. mroonga_action_on_fulltext_query_error
    563. mroonga_boolean_mode_syntax_flags
    564. mroonga_database_path_prefix
    565. mroonga_default_parser
    566. mroonga_default_tokenizer
    567. mroonga_default_wrapper_engine
    568. mroonga_dry_write
    569. mroonga_enable_operations_recording
    570. mroonga_enable_optimization
    571. mroonga_libgroonga_embedded
    572. mroonga_libgroonga_support_zlib
    573. mroonga_libgroonga_support_zstd
    574. mroonga_libgroonga_version
    575. mroonga_log_file
    576. mroonga_log_level
    577. mroonga_match_escalation_threshold
    578. mroonga_max_n_records_for_estimate
    579. mroonga_query_log_file
    580. mroonga_vector_column_delimiter
    581. mroonga_version
    582. mrr_buffer_size
    583. multi_range_count
    584. myisam_block_size
    585. myisam_data_pointer_size
    586. myisam_max_sort_file_size
    587. myisam_mmap_size
    588. myisam_recover_options
    589. myisam_repair_threads
    590. myisam_sort_buffer_size
    591. myisam_stats_method
    592. myisam_use_mmap
    593. mysql56_temporal_format
    594. named_pipe
    595. net_buffer_length
    596. net_read_timeout
    597. net_retry_count
    598. net_write_timeout
    599. note_verbosity
    600. old
    601. old_alter_table
    602. old_mode
    603. old_passwords
    604. open_files_limit
    605. optimizer_adjust_secondary_key_costs
    606. optimizer_extra_pruning_depth
    607. optimizer_join_limit_pref_ratio
    608. optimizer_max_sel_arg_weight
    609. optimizer_max_sel_args
    610. optimizer_prune_level
    611. optimizer_search_depth
    612. optimizer_selectivity_sampling_limit
    613. optimizer_switch
    614. optimizer_trace
    615. optimizer_trace_max_mem_size
    616. optimizer_use_condition_selectivity
    617. oqgraph_allow_create_integer_latch
    618. pam_debug
    619. pam_use_cleartext_plugin
    620. pam_winbind_workaround
    621. performance_schema
    622. performance_schema_accounts_size
    623. performance_schema_digests_size
    624. performance_schema_events_stages_history_long_size
    625. performance_schema_events_stages_history_size
    626. performance_schema_events_statements_history_long_size
    627. performance_schema_events_statements_history_size
    628. performance_schema_events_transactions_history_long_size
    629. performance_schema_events_transactions_history_size
    630. performance_schema_events_waits_history_long_size
    631. performance_schema_events_waits_history_size
    632. performance_schema_hosts_size
    633. performance_schema_max_cond_classes
    634. performance_schema_max_cond_instances
    635. performance_schema_max_digest_length
    636. performance_schema_max_file_classes
    637. performance_schema_max_file_handles
    638. performance_schema_max_file_instances
    639. performance_schema_max_index_stat
    640. performance_schema_max_memory_classes
    641. performance_schema_max_metadata_locks
    642. performance_schema_max_mutex_classes
    643. performance_schema_max_mutex_instances
    644. performance_schema_max_prepared_statement_instances
    645. performance_schema_max_program_instances
    646. performance_schema_max_sql_text_length
    647. performance_schema_max_rwlock_classes
    648. performance_schema_max_rwlock_instances
    649. performance_schema_max_socket_classes
    650. performance_schema_max_socket_instances
    651. performance_schema_max_stage_classes
    652. performance_schema_max_statement_classes
    653. performance_schema_max_statement_stack
    654. performance_schema_max_table_handles
    655. performance_schema_max_table_instances
    656. performance_schema_max_table_lock_stat
    657. performance_schema_max_thread_classes
    658. performance_schema_max_thread_instances
    659. performance_schema_session_connect_attrs_size
    660. performance_schema_setup_actors_size
    661. performance_schema_setup_objects_size
    662. performance_schema_users_size
    663. pid_file
    664. plugin_dir
    665. plugin_maturity
    666. port
    667. preload_buffer_size
    668. profiling
    669. profiling_history_size
    670. progress_report_time
    671. protocol_version
    672. proxy_protocol_networks
    673. proxy_user
    674. pseudo_slave_mode
    675. pseudo_thread_id
    676. query_alloc_block_size
    677. query_cache_limit
    678. query_cache_min_res_unit
    679. query_cache_size
    680. query_cache_strip_comments
    681. query_cache_type
    682. query_cache_wlock_invalidate
    683. query_prealloc_size
    684. query_response_time_flush
    685. query_response_time_range_base
    686. query_response_time_range_exec_time_debug
    687. query_response_time_stats
    688. rand_seed1
    689. rand_seed2
    690. range_alloc_block_size
    691. read_binlog_speed_limit
    692. read_buffer_size
    693. read_only
    694. read_rnd_buffer_size
    695. redirect_url
    696. relay_log
    697. relay_log_basename
    698. relay_log_index
    699. relay_log_info_file
    700. relay_log_purge
    701. relay_log_recovery
    702. relay_log_space_limit
    703. replicate_annotate_row_events
    704. replicate_do_db
    705. replicate_do_table
    706. replicate_events_marked_for_skip
    707. replicate_ignore_db
    708. replicate_ignore_table
    709. replicate_rewrite_db
    710. replicate_wild_do_table
    711. replicate_wild_ignore_table
    712. report_host
    713. report_password
    714. report_port
    715. report_user
    716. require_secure_transport
    717. rocksdb_access_hint_on_compaction_start
    718. rocksdb_advise_random_on_open
    719. rocksdb_allow_concurrent_memtable_write
    720. rocksdb_allow_mmap_reads
    721. rocksdb_allow_mmap_writes
    722. rocksdb_allow_to_start_after_corruption
    723. rocksdb_background_sync
    724. rocksdb_base_background_compactions
    725. rocksdb_blind_delete_primary_key
    726. rocksdb_block_cache_size
    727. rocksdb_block_restart_interval
    728. rocksdb_block_size
    729. rocksdb_block_size_deviation
    730. rocksdb_bulk_load
    731. rocksdb_bulk_load_allow_sk
    732. rocksdb_bulk_load_allow_unsorted
    733. rocksdb_bulk_load_size
    734. rocksdb_bytes_per_sync
    735. rocksdb_cache_dump
    736. rocksdb_cache_high_pri_pool_ratio
    737. rocksdb_cache_index_and_filter_blocks
    738. rocksdb_cache_index_and_filter_with_high_priority
    739. rocksdb_checksums_pct
    740. rocksdb_collect_sst_properties
    741. rocksdb_commit_in_the_middle
    742. rocksdb_commit_time_batch_for_recovery
    743. rocksdb_compact_cf
    744. rocksdb_compaction_readahead_size
    745. rocksdb_compaction_sequential_deletes
    746. rocksdb_compaction_sequential_deletes_count_sd
    747. rocksdb_compaction_sequential_deletes_file_size
    748. rocksdb_compaction_sequential_deletes_window
    749. rocksdb_concurrent_prepare
    750. rocksdb_create_checkpoint
    751. rocksdb_create_if_missing
    752. rocksdb_create_missing_column_families
    753. rocksdb_datadir
    754. rocksdb_db_write_buffer_size
    755. rocksdb_deadlock_detect
    756. rocksdb_deadlock_detect_depth
    757. rocksdb_debug_manual_compaction_delay
    758. rocksdb_debug_optimizer_no_zero_cardinality
    759. rocksdb_debug_ttl_ignore_pk
    760. rocksdb_debug_ttl_read_filter_ts
    761. rocksdb_debug_ttl_rec_ts
    762. rocksdb_debug_ttl_snapshot_ts
    763. rocksdb_default_cf_options
    764. rocksdb_delayed_write_rate
    765. rocksdb_delete_cf
    766. rocksdb_delete_obsolete_files_period_micros
    767. rocksdb_enable_2pc
    768. rocksdb_enable_bulk_load_api
    769. rocksdb_enable_insert_with_update_caching
    770. rocksdb_enable_thread_tracking
    771. rocksdb_enable_ttl
    772. rocksdb_enable_ttl_read_filtering
    773. rocksdb_enable_write_thread_adaptive_yield
    774. rocksdb_error_if_exists
    775. rocksdb_error_on_suboptimal_collation
    776. rocksdb_flush_log_at_trx_commit
    777. rocksdb_flush_memtable_on_analyze
    778. rocksdb_force_compute_memtable_stats
    779. rocksdb_force_compute_memtable_stats_cachetime
    780. rocksdb_force_flush_memtable_and_lzero_now
    781. rocksdb_force_flush_memtable_now
    782. rocksdb_force_index_records_in_range
    783. rocksdb_git_hash
    784. rocksdb_hash_index_allow_collision
    785. rocksdb_ignore_unknown_options
    786. rocksdb_index_type
    787. rocksdb_info_log_level
    788. rocksdb_io_write_timeout
    789. rocksdb_is_fd_close_on_exec
    790. rocksdb_keep_log_file_num
    791. rocksdb_large_prefix
    792. rocksdb_lock_scanned_rows
    793. rocksdb_lock_wait_timeout
    794. rocksdb_log_dir
    795. rocksdb_log_file_time_to_roll
    796. rocksdb_manifest_preallocation_size
    797. rocksdb_manual_compaction_threads
    798. rocksdb_manual_wal_flush
    799. rocksdb_master_skip_tx_api
    800. rocksdb_max_background_compactions
    801. rocksdb_max_background_flushes
    802. rocksdb_max_background_jobs
    803. rocksdb_max_latest_deadlocks
    804. rocksdb_max_log_file_size
    805. rocksdb_max_manifest_file_size
    806. rocksdb_max_manual_compactions
    807. rocksdb_max_open_files
    808. rocksdb_max_row_locks
    809. rocksdb_max_subcompactions
    810. rocksdb_max_total_wal_size
    811. rocksdb_merge_buf_size
    812. rocksdb_merge_combine_read_size
    813. rocksdb_merge_tmp_file_removal_delay_ms
    814. rocksdb_new_table_reader_for_compaction_inputs
    815. rocksdb_no_block_cache
    816. rocksdb_override_cf_options
    817. rocksdb_paranoid_checks
    818. rocksdb_pause_background_work
    819. rocksdb_perf_context_level
    820. rocksdb_persistent_cache_path
    821. rocksdb_persistent_cache_size_mb
    822. rocksdb_pin_l0_filter_and_index_blocks_in_cache
    823. rocksdb_print_snapshot_conflict_queries
    824. rocksdb_rate_limiter_bytes_per_sec
    825. rocksdb_read_free_rpl_tables
    826. rocksdb_records_in_range
    827. rocksdb_remove_mariabackup_checkpoint
    828. rocksdb_reset_stats
    829. rocksdb_rollback_on_timeout
    830. rocksdb_seconds_between_stat_computes
    831. rocksdb_signal_drop_index_thread
    832. rocksdb_sim_cache_size
    833. rocksdb_skip_bloom_filter_on_read
    834. rocksdb_skip_fill_cache
    835. rocksdb_skip_unique_check_tables
    836. rocksdb-sst-mgr-rate-bytes-per-sec
    837. rocksdb_stats_dump_period_sec
    838. rocksdb_stats_level
    839. rocksdb_stats_recalc_rate
    840. rocksdb_store_row_debug_checksums
    841. rocksdb_strict_collation_check
    842. rocksdb_strict_collation_exceptions
    843. rocksdb_supported_compression_types
    844. rocksdb_table_cache_numshardbits
    845. rocksdb_table_stats_sampling_pct
    846. rocksdb_tmpdir
    847. rocksdb_trace_sst_api
    848. rocksdb_two_write_queues
    849. rocksdb_unsafe_for_binlog
    850. rocksdb_update_cf_options
    851. rocksdb_use_adaptive_mutex
    852. rocksdb_use_clock_cache
    853. rocksdb_use_direct_io_for_flush_and_compaction
    854. rocksdb_use_direct_reads
    855. rocksdb_use_fsync
    856. rocksdb_validate_tables
    857. rocksdb_verify_row_debug_checksums
    858. rocksdb_wal_bytes_per_sync
    859. rocksdb_wal_dir
    860. rocksdb_wal_recovery_mode
    861. rocksdb_wal_size_limit_mb
    862. rocksdb_wal_ttl_seconds
    863. rocksdb_whole_key_filtering
    864. rocksdb_write_batch_max_bytes
    865. rocksdb_write_disable_wal
    866. rocksdb_write_ignore_missing_column_families
    867. rocksdb_write_policy
    868. rowid_merge_buff_size
    869. rpl_recovery_rank
    870. rpl_semi_sync_master_enabled
    871. rpl_semi_sync_master_timeout
    872. rpl_semi_sync_master_trace_level
    873. rpl_semi_sync_master_wait_no_slave
    874. rpl_semi_sync_master_wait_point
    875. rpl_semi_sync_slave_delay_master
    876. rpl_semi_sync_slave_enabled
    877. rpl_semi_sync_slave_kill_conn_timeout
    878. rpl_semi_sync_slave_trace_level
    879. s3_access_key
    880. s3_block_size
    881. s3_bucket
    882. s3_debug
    883. s3_host_name
    884. s3_pagecache_age_threshold
    885. s3_pagecache_buffer_size
    886. s3_pagecache_division_limit
    887. s3_pagecache_file_hash_size
    888. s3_port
    889. s3_protocol_version
    890. s3_region
    891. s3_secret_key
    892. s3_slave_ignore_updates
    893. s3_use_http
    894. safe_show_database
    895. secure_auth
    896. secure_file_priv
    897. secure_timestamp
    898. server_audit_events
    899. server_audit_excl_users
    900. server_audit_file_path
    901. server_audit_file_rotate_now
    902. server_audit_file_rotate_size
    903. server_audit_file_rotations
    904. server_audit_incl_users
    905. server_audit_loc_info
    906. server_audit_logging
    907. server_audit_mode
    908. server_audit_output_type
    909. server_audit_query_limit
    910. server_audit_syslog_facility
    911. server_audit_syslog_ident
    912. server_audit_syslog_info
    913. server_audit_syslog_priority
    914. server_id
    915. session_track_schema
    916. session_track_state_change
    917. session_track_system_variables
    918. session_track_transaction_info
    919. shared_memory
    920. shared_memory_base_name
    921. simple_password_check_digits
    922. simple_password_check_letters_same_case
    923. simple_password_check_minimal_length
    924. simple_password_check_other_characters
    925. skip_external_locking
    926. skip_grant_tables
    927. skip_name_resolve
    928. skip_networking
    929. skip_parallel_replication
    930. skip_replication
    931. skip_show_database
    932. slave_abort_blocking_timeout
    933. slave_compressed_protocol
    934. slave_connections_needed_for_purge
    935. slave_ddl_exec_mode
    936. slave_domain_parallel_threads
    937. slave_exec_mode
    938. slave_load_tmpdir
    939. slave_max_allowed_packet
    940. slave_max_statement_time
    941. slave_net_timeout
    942. slave_parallel_max_queued
    943. slave_parallel_mode
    944. slave_parallel_threads
    945. slave_parallel_workers
    946. slave_run_triggers_for_rbr
    947. slave_skip_errors
    948. slave_sql_verify_checksum
    949. slave_transaction_retries
    950. slave_transaction_retry_errors
    951. slave_transaction_retry_interval
    952. slave_type_conversions
    953. slow_launch_time
    954. slow_query_log
    955. slow_query_log_file
    956. socket
    957. sort_buffer_size
    958. spider_auto_increment_mode
    959. spider_auto_increment_mode
    960. spider_auto_increment_mode
    961. spider_bgs_second_read
    962. spider_bka_engine
    963. spider_bka_mode
    964. spider_block_size
    965. spider_buffer_size
    966. spider_bulk_size
    967. spider_bulk_update_mode
    968. spider_bulk_update_size
    969. spider_casual_read
    970. spider_conn_recycle_mode
    971. spider_conn_recycle_strict
    972. spider_conn_wait_timeout
    973. spider_connect_error_interval
    974. spider_connect_mutex
    975. spider_connect_retry_count
    976. spider_connect_retry_interval
    977. spider_connect_timeout
    978. spider_crd_bg_mode
    979. spider_crd_interval
    980. spider_crd_mode
    981. spider_crd_sync
    982. spider_crd_type
    983. spider_crd_weight
    984. spider_delete_all_rows_type
    985. spider_direct_dup_insert
    986. spider_direct_order_limit
    987. spider_dry_access
    988. spider_error_read_mode
    989. spider_error_write_mode
    990. spider_first_read
    991. spider_force_commit
    992. spider_general_log
    993. spider_ignore_comments
    994. spider_index_hint_pushdown
    995. spider_init_sql_alloc_size
    996. spider_internal_limit
    997. spider_internal_offset
    998. spider_internal_optimize
    999. spider_internal_optimize_local
    1000. spider_internal_sql_log_off
    1001. spider_internal_unlock
    1002. spider_internal_xa
    1003. spider_internal_xa_id_type
    1004. spider_internal_xa_snapshot
    1005. spider_load_crd_at_startup
    1006. spider_load_sts_at_startup
    1007. spider_local_lock_table
    1008. spider_lock_exchange
    1009. spider_log_result_error_with_sql
    1010. spider_log_result_errors
    1011. spider_low_mem_read
    1012. spider_max_connections
    1013. spider_max_order
    1014. spider_multi_split_read
    1015. spider_net_read_timeout
    1016. spider_net_write_timeout
    1017. spider_ping_interval_at_trx_start
    1018. spider_quick_mode
    1019. spider_quick_page_byte
    1020. spider_quick_page_size
    1021. spider_read_only_mode
    1022. spider_remote_access_charset
    1023. spider_remote_autocommit
    1024. spider_remote_default_database
    1025. spider_remote_sql_log_off
    1026. spider_remote_time_zone
    1027. spider_remote_trx_isolation
    1028. spider_remote_wait_timeout
    1029. spider_reset_sql_alloc
    1030. spider_same_server_link
    1031. spider_second_read
    1032. spider_select_column_mode
    1033. spider_selupd_lock_mode
    1034. spider_semi_split_read
    1035. spider_semi_split_read_limit
    1036. spider_semi_table_lock
    1037. spider_semi_table_lock_connection
    1038. spider_semi_trx
    1039. spider_semi_trx_isolation
    1040. spider_skip_default_condition
    1041. spider_skip_parallel_search
    1042. spider_slave_trx_isolation
    1043. spider_split_read
    1044. spider_store_last_crd
    1045. spider_store_last_sts
    1046. spider_strict_group_by
    1047. spider_sts_bg_mode
    1048. spider_sts_interval
    1049. spider_sts_mode
    1050. spider_sts_sync
    1051. spider_support_xa
    1052. spider_suppress_comment_ignored_warning
    1053. spider_sync_autocommit
    1054. spider_sync_sql_mode
    1055. spider_sync_time_zone
    1056. spider_sync_trx_isolation
    1057. spider_table_crd_thread_count
    1058. spider_table_init_error_interval
    1059. spider_table_sts_thread_count
    1060. spider_udf_ct_bulk_insert_interval
    1061. spider_udf_ct_bulk_insert_rows
    1062. spider_udf_ds_bulk_insert_rows
    1063. spider_udf_ds_table_loop_mode
    1064. spider_udf_ds_use_real_table
    1065. spider_udf_table_lock_mutex_count
    1066. spider_udf_table_mon_mutex_count
    1067. spider_use_all_conns_snapshot
    1068. spider_use_cond_other_than_pk_for_update
    1069. spider_use_consistent_snapshot
    1070. spider_use_default_database
    1071. spider_use_flash_logs
    1072. spider_use_handler
    1073. spider_use_pushdown_udf
    1074. spider_use_table_charset
    1075. spider_version
    1076. spider_wait_timeout
    1077. spider_xa_register_mode
    1078. sql_auto_is_null
    1079. sql_big_selects
    1080. sql_big_tables
    1081. sql_buffer_result
    1082. sql_error_log_filename
    1083. sql_error_log_rate
    1084. sql_error_log_rotate
    1085. sql_error_log_rotations
    1086. sql_error_log_size_limit
    1087. sql_error_log_warnings
    1088. sql_if_exists
    1089. sql_log_bin
    1090. sql_log_off
    1091. sql_log_update
    1092. sql_low_priority_updates
    1093. sql_max_join_size
    1094. sql_mode
    1095. sql_notes
    1096. sql_quote_show_create
    1097. sql_safe_updates
    1098. sql_select_limit
    1099. sql_slave_skip_counter
    1100. sql_warnings
    1101. ssl_ca
    1102. ssl_capath
    1103. ssl_cert
    1104. ssl_cipher
    1105. ssl_crl
    1106. ssl_crlpath
    1107. ssl_key
    1108. storage_engine
    1109. standard_compliant_cte
    1110. stored_program_cache
    1111. strict_password_validation
    1112. sync_binlog
    1113. sync_frm
    1114. sync_master_info
    1115. sync_relay_log
    1116. sync_relay_log_info
    1117. system_time_zone
    1118. system_versioning_alter_history
    1119. system_versioning_asof
    1120. system_versioning_innodb_algorithm_simple
    1121. system_versioning_insert_history
    1122. table_definition_cache
    1123. table_lock_wait_timeout
    1124. table_open_cache
    1125. table_open_cache_instances
    1126. table_type
    1127. tcp_keepalive_interval
    1128. tcp_keepalive_probes
    1129. tcp_keepalive_time
    1130. tcp_nodelay
    1131. thread_cache_size
    1132. thread_concurrency
    1133. thread_handling
    1134. thread_pool_dedicated_listener
    1135. thread_pool_exact_stats
    1136. thread_pool_idle_timeout
    1137. thread_pool_max_threads
    1138. thread_pool_min_threads
    1139. thread_pool_oversubscribe
    1140. thread_pool_prio_kickup_timer
    1141. thread_pool_priority
    1142. thread_pool_size
    1143. thread_pool_stall_limit
    1144. thread_stack
    1145. time_format
    1146. time_zone
    1147. timed_mutexes
    1148. timestamp
    1149. tls_version
    1150. tmp_disk_table_size
    1151. tmp_memory_table_size
    1152. tmp_table_size
    1153. tmpdir
    1154. tokudb_alter_print_error
    1155. tokudb_analyze_time
    1156. tokudb_block_size
    1157. tokudb_bulk_fetch
    1158. tokudb_cache_size
    1159. tokudb_check_jemalloc
    1160. tokudb_checkpoint_lock
    1161. tokudb_checkpoint_on_flush_logs
    1162. tokudb_checkpointing_period
    1163. tokudb_cleaner_iterations
    1164. tokudb_cleaner_period
    1165. tokudb_commit_sync
    1166. tokudb_create_index_online
    1167. tokudb_data_dir
    1168. tokudb_debug
    1169. tokudb_directio
    1170. tokudb_disable_hot_alter
    1171. tokudb_disable_prefetching
    1172. tokudb_disable_slow_alter
    1173. tokudb_empty_scan
    1174. tokudb_fs_reserve_percent
    1175. tokudb_fsync_log_period
    1176. tokudb_hide_default_row_format
    1177. tokudb_killed_time
    1178. tokudb_last_lock_timeout
    1179. tokudb_load_save_space
    1180. tokudb_loader_memory_size
    1181. tokudb_lock_timeout
    1182. tokudb_lock_timeout_debug
    1183. tokudb_log_dir
    1184. tokudb_max_lock_memory
    1185. tokudb_optimize_index_fraction
    1186. tokudb_optimize_index_name
    1187. tokudb_optimize_throttle
    1188. tokudb_pk_insert_mode
    1189. tokudb_prelock_empty
    1190. tokudb_read_block_size
    1191. tokudb_read_buf_size
    1192. tokudb_read_status_frequency
    1193. tokudb_row_format
    1194. tokudb_rpl_check_readonly
    1195. tokudb_rpl_lookup_rows
    1196. tokudb_rpl_lookup_rows_delay
    1197. tokudb_rpl_unique_checks
    1198. tokudb_rpl_unique_checks_delay
    1199. tokudb_support_xa
    1200. tokudb_tmp_dir
    1201. tokudb_version
    1202. tokudb_write_status_frequency
    1203. transaction_alloc_block_size
    1204. transaction_isolation
    1205. transaction_prealloc_size
    1206. transaction_read_only
    1207. tx_isolation
    1208. tx_read_only
    1209. unique_checks
    1210. updatable_views_with_limit
    1211. use_stat_tables
    1212. userstat
    1213. version
    1214. version_comment
    1215. version_compile_machine
    1216. version_compile_os
    1217. version_malloc_library
    1218. version_source_revision
    1219. version_ssl_library
    1220. wait_timeout
    1221. warning_count
    1222. wsrep_allowlist
    1223. wsrep_auto_increment_control
    1224. wsrep_causal_reads
    1225. wsrep_certification_rules
    1226. wsrep_certify_nonPK
    1227. wsrep_cluster_address
    1228. wsrep_cluster_name
    1229. wsrep_convert_LOCK_to_trx
    1230. wsrep_data_home_dir
    1231. wsrep_dbug_option
    1232. wsrep_debug
    1233. wsrep_desync
    1234. wsrep_dirty_reads
    1235. wsrep_drupal_282555_workaround
    1236. wsrep_forced_binlog_format
    1237. wsrep_gtid_domain_id
    1238. wsrep_gtid_mode
    1239. wsrep_gtid_seq_no
    1240. wsrep_ignore_apply_errors
    1241. wsrep_load_data_splitting
    1242. wsrep_log_conflicts
    1243. wsrep_max_ws_rows
    1244. wsrep_max_ws_size
    1245. wsrep_mode
    1246. wsrep_mysql_replication_bundle
    1247. wsrep_node_address
    1248. wsrep_node_incoming_address
    1249. wsrep_node_name
    1250. wsrep_notify_cmd
    1251. wsrep_on
    1252. wsrep_OSU_method
    1253. wsrep_provider
    1254. wsrep_provider_options
    1255. wsrep_recover
    1256. wsrep_reject_queries
    1257. wsrep_replicate_myisam
    1258. wsrep_restart_slave
    1259. wsrep_retry_autocommit
    1260. wsrep_slave_FK_checks
    1261. wsrep_slave_threads
    1262. wsrep_slave_UK_checks
    1263. wsrep_sr_store
    1264. wsrep_sst_auth
    1265. wsrep_sst_donor
    1266. wsrep_sst_donor_rejects_queries
    1267. wsrep_sst_method
    1268. wsrep_sst_receive_address
    1269. wsrep_start_position
    1270. wsrep_status_file
    1271. wsrep_strict_ddl
    1272. wsrep_sync_wait
    1273. wsrep_trx_fragment_size
    1274. wsrep_trx_fragment_unit

About the Server System Variables

MariaDB has many system variables that can be changed to suit your needs.

The full list of server variables are listed in the contents on this page, and most are described on this page, but some are described elsewhere:

See also the Full list of MariaDB options, system and status variables.

Most of these can be set with command line options and many of them can be changed at runtime. Variables that can be changed at runtime (and therefore are not read-only) are described as "Dynamic" below, and elsewhere in the documentation.

There are a few ways to see the full list of server system variables:

  • While in the mariadb client, run:
SHOW VARIABLES;
  • From your shell, run mariadbd like so:
mariadbd --verbose --help

Setting Server System Variables

There are several ways to set server system variables:

  • Specify them on the command line:
shell> ./mariadbd-safe --aria_group_commit="hard"
aria_group_commit = "hard"
  • Set them from the mariadb client using the SET command. Only variables that are dynamic can be set at runtime in this way. Note that variables set in this way will not persist after a restart.
SET GLOBAL aria_group_commit="hard";

By convention, server variables have usually been specified with an underscore in the configuration files, and a dash on the command line. You can however specify underscores as dashes - they are interchangeable.

Variables that take a numeric size can either be specified in full, or with a suffix for easier readability. Valid suffixes are:

SuffixDescriptionValue
Kkilobytes1024
Mmegabytes10242
Ggigabytes10243
Tterabytes10244 (from MariaDB 10.3.3)
Ppetabytes10245 (from MariaDB 10.3.3)
Eexabytes10246 (from MariaDB 10.3.3)

The suffix can be upper or lower-case.

List of Server System Variables

allow_suspicious_udfs

  • Description: Allows use of user-defined functions consisting of only one symbol x() without corresponding x_init() or x_deinit(). That also means that one can load any function from any library, for example exit() from libc.so. Not recommended unless you require old UDFs with one symbol that cannot be recompiled. Before MariaDB 10.10, available as an option only.
  • Commandline: --allow-suspicious-udfs
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF
  • Introduced: MariaDB 10.10

alter_algorithm

  • Description: The implied ALGORITHM for ALTER TABLE if no ALGORITHM clause is specified. The deprecated variable old_alter_table is an alias for this. The feature was removed in MariaDB 11.5. See ALGORITHM=DEFAULT.
    • COPY corresponds to the pre-MySQL 5.1 approach of creating an intermediate table, copying data one row at a time, and renaming and dropping tables.
    • INPLACE requests that the operation be refused if it cannot be done natively inside a the storage engine.
    • DEFAULT (the default) chooses INPLACE if available, and falls back to COPY.
    • NOCOPY refuses to copy a table.
    • INSTANT refuses an operation that would involve any other than metadata changes.
  • Commandline: --alter-algorithm=default
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumerated
  • Default Value: DEFAULT
  • Valid Values: DEFAULT, COPY, INPLACE, NOCOPY, INSTANT
  • Introduced: MariaDB 10.3.7
  • Deprecated: MariaDB 11.5

analyze_sample_percentage

  • Description: Percentage of rows from the table ANALYZE TABLE will sample to collect table statistics. Set to 0 to let MariaDB decide what percentage of rows to sample.
  • Commandline: --analyze-sample-percentage=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 100.000000
  • Range: 0 to 100

autocommit

  • Description: If set to 1, the default, all queries are committed immediately. The LOCK IN SHARE MODE and FOR UPDATE clauses therefore have no effect. If set to 0, they are only committed upon a COMMIT statement, or rolled back with a ROLLBACK statement. If autocommit is set to 0, and then changed to 1, all open transactions are immediately committed.
  • Commandline: --autocommit[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

automatic_sp_privileges

  • Description: When set to 1, the default, when a stored routine is created, the creator is automatically granted permission to ALTER (which includes dropping) and to EXECUTE the routine. If set to 0, the creator is not automatically granted these privileges.
  • Commandline: --automatic-sp-privileges, --skip-automatic-sp-privileges
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

back_log

  • Description: Connections take a small amount of time to start, and this setting determines the number of outstanding connection requests MariaDB can have, or the size of the listen queue for incoming TCP/IP requests. Requests beyond this will be refused. Increase if you expect short bursts of connections. Cannot be set higher than the operating system limit (see the Unix listen() man page). If not set, set to 0, or the --autoset-back-log option is used, will be autoset to the lower of 900 and (50 + max_connections/5).
  • Commandline: --back-log=#
  • Scope: Global
  • Dynamic: No
  • Type: number
  • Default Value:

basedir

  • Description: Path to the MariaDB installation directory. Other paths are usually resolved relative to this base directory.
  • Commandline: --basedir=path or -b path
  • Scope: Global
  • Dynamic: No
  • Type: directory name

big_tables

  • Description: If this system variable is set to 1, then temporary tables will be saved to disk intead of memory.
    • This system variable's original intention was to allow result sets that were too big for memory-based temporary tables and to avoid the resulting 'table full' errors.
    • This system variable is no longer needed, because the server can automatically convert large memory-based temporary tables into disk-based temporary tables when they exceed the value of the tmp_memory_table_size system variable.
    • To prevent memory-based temporary tables from being used at all, set the tmp_memory_table_size system variable to 0.
    • In MariaDB 5.5 and earlier, sql_big_tables is a synonym.
    • In MariaDB 10.5, this system variable is deprecated.
  • Commandline: --big-tables
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0
  • Deprecated: MariaDB 10.5.0

bind_address

  • Description: By default, the MariaDB server listens for TCP/IP connections on all addresses. You can specify an alternative when the server starts using this option; either a host name, an IPv4 or an IPv6 address, "::" or "*" (all addresses). In some systems, such as Debian and Ubuntu, the bind_address is set to 127.0.0.1, which binds the server to listen on localhost only. bind_address has always been available as a mariadbd option; from MariaDB 10.3.3 its also available as a system variable. Before MariaDB 10.6.0 "::" implied listening additionally on IPv4 addresses like "*". From 10.6.0 onwards it refers to IPv6 stictly. Starting with MariaDB 10.11, a comma-separated list of addresses to bind to can be given. See also Configuring MariaDB for Remote Client Access.
  • Commandline: --bind-address=addr
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: (Empty string)
  • Valid Values: Host name, IPv4, IPv6, ::, *
  • Introduced: MariaDB 10.3.3 (as a system variable)

block_encryption_mode

  • Description: Default block encryption mode for AES_ENCRYPT() and AES_DECRYPT() functions.
  • Commandline: --block-encryption-mode=val
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: aes-128-ecb
  • Valid values: aes-128-ecb, aes-192-ecb, aes-256-ecb, aes-128-cbc, aes-192-cbc, aes-256-cbc, aes-128-ctr, aes-192-ctr, aes-256-ctr
  • Introduced: MariaDB 11.2.0

bulk_insert_buffer_size

  • Description: Size in bytes of the per-thread cache tree used to speed up bulk inserts into MyISAM and Aria tables. A value of 0 disables the cache tree.
  • Commandline: --bulk-insert-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 8388608
  • Range - 32 bit: 0 to 4294967295
  • Range - 64 bit: 0 to 18446744073709547520

character_set_client

  • Description: Determines the character set for queries arriving from the client. It can be set per session by the client, although the server can be configured to ignore client requests with the --skip-character-set-client-handshake option. If the client does not request a character set, or requests a character set that the server does not support, the global value will be used. utf16, utf16le, utf32 and ucs2 cannot be used as client character sets. From MariaDB 10.6, the utf8 character set (and related collations) is by default an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the old_mode system variable.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: utf8mb3 (>= MariaDB 10.6), utf8 (<= MariaDB 10.5)

character_set_collations

  • Description: Overrides for character set default collations. Takes a comma-delimited list of character set and collation settings, for example SET @@character_set_collations = 'utf8mb4=uca1400_ai_ci, latin2=latin2_hungarian_ci'; The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
    • Column collation
    • Table collation
    • Database collation
    • CHAR(expr USING csname)
    • CONVERT(expr USING csname)
    • CAST(expr AS CHAR CHARACTER SET csname)
    • '' - character string literal
    • _utf8mb3'text' - a character string literal with an introducer
    • _utf8mb3 X'61' - a character string literal with an introducer with hex notation
    • _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
    • @@collation_connection after a SET NAMES without COLLATE
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value:
    • utf8mb3=utf8mb3_uca1400_ai_ci, ucs2=ucs2_uca1400_ai_ci, utf8mb4=utf8mb4_uca1400_ai_ci, utf16=utf16_uca1400_ai_ci, utf32=utf32_uca1400_ai_ci (>= MariaDB 11.5)
    • Empty (<= MariaDB 11.4)
  • Introduced: MariaDB 11.2

character_set_connection

  • Description: Character set used for number to string conversion, as well as for literals that don't have a character set introducer. From MariaDB 10.6, the utf8 character set (and related collations) is by default an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the old_mode system variable.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: utf8mb3 (>= MariaDB 10.6), utf8 (<= MariaDB 10.5)

character_set_database

  • Description: Character set used by the default database, and set by the server whenever the default database is changed. If there's no default database, character_set_database contains the same value as character_set_server. This variable is dynamic, but should not be set manually, only by the server.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: utf8mb4 (>= MariaDB 11.6.0), latin1 (<= MariaDB 11.5)

character_set_filesystem

  • Description: The character set for the filesystem. Used for converting file names specified as a string literal from character_set_client to character_set_filesystem before opening the file. By default set to binary, so no conversion takes place. This could be useful for statements such as LOAD_FILE() or LOAD DATA INFILE on system where multi-byte file names are use.
  • Commandline: --character-set-filesystem=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: binary

character_set_results

  • Description: Character set used for results and error messages returned to the client. From MariaDB 10.6, the utf8 character set (and related collations) is by default an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the old_mode system variable.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: utf8mb3 (>= MariaDB 10.6), utf8 (<= MariaDB 10.5)

character_set_server


character_set_system

  • Description: Character set used by the server to store identifiers, always set to utf8, or its synonym utf8mb3 starting with MariaDB 10.6. From MariaDB 10.6, the utf8 character set (and related collations) is by default an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the old_mode system variable.
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: utf8mb3 (>= MariaDB 10.6), utf8 (<= MariaDB 10.5)

character_sets_dir

  • Description: Directory where the character sets are installed.
  • Commandline: --character-sets-dir=path
  • Scope: Global
  • Dynamic: No
  • Type: directory name

check_constraint_checks

  • Description: If set to 0, will disable constraint checks, for example when loading a table that violates some constraints that you plan to fix later.
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: boolean
  • Default: ON

collation_connection

  • Description: Collation used for the connection character set.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string

collation_database

  • Description: Collation used for the default database. Set by the server if the default database changes, if there is no default database the value from the collation_server variable is used. This variable is dynamic, but should not be set manually, only by the server.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string

collation_server

  • Description: Default collation used by the server. This is set to the default collation for a given character set automatically when character_set_server is changed, but it can also be set manually. Defaults may be different on some systems, see for example Differences in MariaDB in Debian.
  • Commandline: --collation-server=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: latin1_swedish_ci

completion_type

  • Description: The transaction completion type. If set to NO_CHAIN or 0 (the default), there is no effect on commits and rollbacks. If set to CHAIN or 1, a COMMIT statement is equivalent to COMMIT AND CHAIN, while a ROLLBACK is equivalent to ROLLBACK AND CHAIN, so a new transaction starts straight away with the same isolation level as transaction that's just finished. If set to RELEASE or 2, a COMMIT statement is equivalent to COMMIT RELEASE, while a ROLLBACK is equivalent to ROLLBACK RELEASE, so the server will disconnect after the transaction completes. Note that the transaction completion type only applies to explicit commits, not implicit commits.
  • Commandline: --completion-type=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumerated
  • Default Value: NO_CHAIN
  • Valid Values: 0, 1, 2, NO_CHAIN, CHAIN, RELEASE

concurrent_insert

  • Description: If set to AUTO or 1, the default, MariaDB allows concurrent INSERTs and SELECTs for MyISAM tables with no free blocks in the data (deleted rows in the middle). If set to NEVER or 0, concurrent inserts are disabled. If set to ALWAYS or 2, concurrent inserts are permitted for all MyISAM tables, even those with holes, in which case new rows are added at the end of a table if the table is being used by another thread.

    If the --skip-new option is used when starting the server, concurrent_insert is set to NEVER.

    Changing the variable only affects new opened tables. Use FLUSH TABLES If you want it to also affect cached tables.

    See Concurrent Inserts for more.
  • Commandline: --concurrent-insert[=value]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: enumerated
  • Default Value: AUTO
  • Valid Values: 0, 1, 2, AUTO, NEVER, ALWAYS

connect_timeout

  • Description: Time in seconds that the server waits for a connect packet before returning a 'Bad handshake'. Increasing may help if clients regularly encounter 'Lost connection to MySQL server at 'X', system error: error_number' type-errors.
  • Commandline: --connect-timeout=#
  • Scope: Global
  • Dynamic: Yes
  • Type: numeric
  • Default Value: 10

core_file

  • Description: Write a core-file on crashes. The file name and location are system dependent. On Linux it is usually called core.${PID}, and it is usually written to the data directory. However, this can be changed.
  • Commandline: --core-file
  • Scope: Global
  • Dynamic: No
  • Type: boolean
  • Default Value:

datadir

  • Description: Directory where the data is stored.
  • Commandline: --datadir=path or -h path
  • Scope: Global
  • Dynamic: No
  • Type: directory name

date_format


datetime_format


debug/debug_dbug

  • Description: Available in debug builds only (built with -DWITH_DEBUG=1). Used in debugging through the DBUG library to write to a trace file. Just using --debug will write a trace of what mariadbd is doing to the default trace file.
  • Commandline: -#, --debug[=debug_options]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value:
    • >= MariaDB 10.5: d:t:i:o,/tmp/mariadbd.trace (Unix) or d:t:i:O,\mariadbd.trace (Windows)
  • Debug Options: See the option flags on the mysql_debug page

debug_no_thread_alarm

  • Description: Disable system thread alarm calls. Disabling it may be useful in debugging or testing, never do it in production.
  • Commandline: --debug-no-thead-alarm=#
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF

debug_sync

  • Description: Used in debugging to show the interface to the Debug Sync facility. MariaDB needs to be configured with -DENABLE_DEBUG_SYNC=1 for this variable to be available.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: OFF or ON - current signal signal name

default_password_lifetime

  • Description: This defines the global password expiration policy. 0 means automatic password expiration is disabled. If the value is a positive integer N, the passwords must be changed every N days. This behavior can be overridden using the password expiration options in ALTER USER.
  • Commandline: --default-password-lifetime=#
  • Scope: Global
  • Dynamic: Yes
  • Type: numeric
  • Default Value: 0
  • Range: 0 to 4294967295

default_regex_flags

  • Description: Introduced to address remaining incompatibilities between PCRE and the old regex library. Accepts a comma-separated list of zero or more of the following values:
ValuePattern equivalentMeaning
DOTALL(?s). matches anything including NL
DUPNAMES(?J)Allow duplicate names for subpatterns
EXTENDED(?x)Ignore white space and # comments
EXTRA(?X)extra features (e.g. error on unknown escape character)
MULTILINE(?m)^ and $ match newlines within data
UNGREEDY(?U)Invert greediness of quantifiers
  • Commandline: --default-regex-flags=value
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: enumeration
  • Default Value: empty
  • Valid Values: DOTALL, DUPNAMES, EXTENDED, EXTRA, MULTILINE, UNGREEDY

default_storage_engine

  • Description: The default storage engine. The default storage engine must be enabled at server startup or the server won't start.
  • Commandline: --default-storage-engine=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: enumeration
  • Default Value: InnoDB

default_table_type


default_tmp_storage_engine

  • Description: Default storage engine that will be used for tables created with CREATE TEMPORARY TABLE where no engine is specified. For internal temporary tables see aria_used_for_temp_tables). The storage engine used must be active or the server will not start. See default_storage_engine for the default for non-temporary tables. Defaults to NULL, in which case the value from default_storage_engine is used. ROCKSDB temporary tables cannot be created. Before MariaDB 10.7, attempting to do so would silently fail, and a MyISAM table would instead be created. From MariaDB 10.7, an error is returned.
  • Commandline: --default-tmp-storage-engine=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: NULL

default_week_format

  • Description: Default mode for the WEEK() function. See that page for details on the different modes
  • Commandline: --default-week-format=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 7

delay_key_write

  • Description: Specifies how MyISAM tables handles CREATE TABLE DELAY_KEY_WRITE. If set to ON, the default, any DELAY KEY WRITEs are honored. The key buffer is then flushed only when the table closes, speeding up writes. MyISAM tables should be automatically checked upon startup in this case, and --external locking should not be used, as it can lead to index corruption. If set to OFF, DELAY KEY WRITEs are ignored, while if set to ALL, all new opened tables are treated as if created with DELAY KEY WRITEs enabled.
  • Commandline: --delay-key-write[=name]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: ON
  • Valid Values: ON, OFF, ALL

delayed_insert_limit

  • Description: After this many rows have been inserted with INSERT DELAYED, the handler will check for and execute any waiting SELECT statements.
  • Commandline: --delayed-insert-limit=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 100
  • Range: 1 to 4294967295

delayed_insert_timeout

  • Description: Time in seconds that the INSERT DELAYED handler will wait for INSERTs before terminating.
  • Commandline: --delayed-insert-timeout=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 300

delayed_queue_size

  • Description: Number of rows, per table, that can be queued when performing INSERT DELAYED statements. If the queue becomes full, clients attempting to perform INSERT DELAYED's will wait until the queue has room available again.
  • Commandline: --delayed-queue-size=#
  • Scope: Global
  • Dynamic: Yes
  • Type: numeric
  • Default Value: 1000
  • Range: 1 to 4294967295

disconnect_on_expired_password

  • Description: When a user password has expired (see User Password Expiry), this variable controls how the server handles clients that are not aware of the sandbox mode. If enabled, the client is not permitted to connect, otherwise the server puts the client in a sandbox mode.
  • Commandline: --disconnect-on-expired-password[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Type: boolean
  • Default Value: OFF

div_precision_increment

  • Description: The precision of the result of the decimal division will be the larger than the precision of the dividend by that number. By default it's 4, so SELECT 2/15 would return 0.1333 and SELECT 2.0/15 would return 0.13333. After setting div_precision_increment to 6, for example, the same operation would return 0.133333 and 0.1333333 respectively.

From MariaDB 10.1.46, MariaDB 10.2.33, MariaDB 10.3.24, MariaDB 10.4.14 and MariaDB 10.5.5, div_precision_increment is taken into account in intermediate calculations. Previous versions did not, and the results were dependent on the optimizer, and therefore unpredictable.

In MariaDB 10.1.46, MariaDB 10.1.47, MariaDB 10.2.33, MariaDB 10.2.34, MariaDB 10.2.35, MariaDB 10.3.24, MariaDB 10.3.25, MariaDB 10.4.14, MariaDB 10.4.15, MariaDB 10.5.5 and MariaDB 10.5.6 only, the fix truncated decimal values after every division, resulting in lower precision in some cases for those versions only.

From MariaDB 10.1.48, MariaDB 10.2.35, MariaDB 10.3.26, MariaDB 10.4.16 and MariaDB 10.5.7, a different fix was implemented. Instead of truncating decimal values after every division, they are instead truncated for comparison purposes only.

For example

SELECT (55/23244*1000);
+-----------------+
| (55/23244*1000) |
+-----------------+
|          2.3662 |
+-----------------
SELECT (55/23244*1000);
+-----------------+
| (55/23244*1000) |
+-----------------+
|          2.4000 |
+-----------------+

This is because the intermediate result, SELECT 55/23244 takes into account div_precision_increment and results were truncated after every division in those versions only.

  • Commandline: --div-precision-increment=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4
  • Range: 0 to 30

encrypt_tmp_disk_tables


encrypt_tmp_files

  • Description: Enables automatic encryption of temporary files, such as those created for filesort operations, binary log file caches, etc. See Data at Rest Encryption.
  • Commandline: --encrypt-tmp-files[={0|1}]
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF

encryption_algorithm

  • Description: Which encryption algorithm to use for table encryption. aes_cbc is the recommended one. See Table and Tablespace Encryption.
  • Commandline: --encryption-algorithm=value
  • Scope: Global
  • Dynamic: No
  • Data Type: enum
  • Default Value: none
  • Valid Values: none, aes_ecb, aes_cbc, aes_ctr
  • Introduced: MariaDB 10.1.3
  • Removed: MariaDB 10.1.4

enforce_storage_engine

  • Description: Force the use of a particular storage engine for new tables. Used to avoid unwanted creation of tables using another engine. For example, setting to InnoDB will prevent any MyISAM tables from being created. If another engine is specified in a CREATE TABLE statement, the outcome depends on whether the NO_ENGINE_SUBSTITUTION SQL_MODE has been set or not. If set, the query will fail, while if not set, a warning will be returned and the table created according to the engine specified by this variable. The variable has a session scope, but is only modifiable by a user with the SUPER privilege.
  • Commandline: None
  • Scope: Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: none

engine_condition_pushdown

  • Description: Deprecated in MariaDB 5.5 and removed and replaced by the optimizer_switch engine_condition_pushdown={on|off} flag in MariaDB 10.0.. Specifies whether the engine condition pushdown optimization is enabled. Since MariaDB 10.1.1, engine condition pushdown is enabled for all engines that support it.
  • Commandline: --engine-condition-pushdown
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Deprecated: MariaDB 5.5
  • Removed: MariaDB 10.0

eq_range_index_dive_limit

  • Description: Limit used for speeding up queries listed by long nested INs. The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0 (unlimited), index dives are always used.
  • Commandline: --eq-range-index-dive-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 200
  • Range: 0 to 4294967295

error_count

  • Description: Read-only variable denoting the number of errors from the most recent statement in the current session that generated errors. See SHOW_ERRORS().
  • Scope: Session
  • Dynamic: Yes
  • Data Type: numeric

event_scheduler

  • Description: Status of the Event Scheduler. Can be set to ON or OFF, while DISABLED means it cannot be set at runtime. Setting the variable will cause a load of events if they were not loaded at startup.
  • Commandline: --event-scheduler[=value]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: OFF
  • Valid Values: ON (or 1), OFF (or 0), DISABLED

expensive_subquery_limit

  • Description: Number of rows to be examined for a query to be considered expensive, that is, maximum number of rows a subquery may examine in order to be executed during optimization and used for constant optimization.
  • Commandline: --expensive-subquery-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 100
  • Range: 0 upwards

explicit_defaults_for_timestamp


external_user

  • Description: External user name set by the plugin used to authenticate the client. NULL if native MariaDB authentication is used.
  • Scope: Session
  • Dynamic: No
  • Data Type: string
  • Default Value: NULL

flush

  • Description: Usually, MariaDB writes changes to disk after each SQL statement, and the operating system handles synchronizing (flushing) it to disk. If set to ON, the server will synchronize all changes to disk after each statement.
  • Commandline: --flush
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

flush_time

  • Description: Interval in seconds that tables are closed to synchronize (flush) data to disk and free up resources. If set to 0, the default, there is no automatic synchronizing tables and closing of tables. This option should not be necessary on systems with sufficient resources.
  • Commandline: --flush_time=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0

foreign_key_checks

  • Description: If set to 1 (the default) foreign key constraints (including ON UPDATE and ON DELETE behavior) InnoDB tables are checked, while if set to 0, they are not checked. 0 is not recommended for normal use, though it can be useful in situations where you know the data is consistent, but want to reload data in a different order from that that specified by parent/child relationships. Setting this variable to 1 does not retrospectively check for inconsistencies introduced while set to 0.
  • Commandline: None
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

ft_boolean_syntax

  • Description: List of operators supported by an IN BOOLEAN MODE full-text search. If you wish to change, note that each character must be ASCII and non-alphanumeric, the full string must be 14 characters and the first or second character must be a space. Positions 10, 13 and 14 are reserved for future extensions. Also, no duplicates are permitted except for the phrase quoting characters in positions 11 and 12, which may be the same.
  • Commandline: --ft-boolean-syntax=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: + -><()*:""&|

ft_max_word_len

  • Description: Maximum length for a word to be included in the MyISAM full-text index. If this variable is changed, the full-text index must be rebuilt in order for the new value to take effect. The quickest way to do this is by issuing a REPAIR TABLE table_name QUICK statement. See innodb_ft_max_token_size for the InnoDB equivalent.
  • Commandline: --ft-max-word-len=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 84
  • Minimum Value: 10

ft_min_word_len

  • Description: Minimum length for a word to be included in the MyISAM full-text index. If this variable is changed, the full-text index must be rebuilt in order for the new value to take effect. The quickest way to do this is by issuing a REPAIR TABLE table_name QUICK statement. See innodb_ft_min_token_size for the InnoDB equivalent.
  • Commandline: --ft-min-word-len=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 4
  • Minimum Value: 1

ft_query_expansion_limit

  • Description: For full-text searches, denotes the numer of top matches when using WITH QUERY EXPANSION.
  • Commandline: --ft-query-expansion-limit=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 20
  • Range: 0 to 1000

ft_stopword_file

  • Description: File containing a list of stopwords for use in MyISAM full-text searches. Unless an absolute path is specified the file will be looked for in the data directory. The file is not parsed for comments, so all words found become stopwords. By default, a built-in list of words (built from storage/myisam/ft_static.c file) is used. Stopwords can be disabled by setting this variable to '' (an empty string). If this variable is changed, the full-text index must be rebuilt. The quickest way to do this is by issuing a REPAIR TABLE table_name QUICK statement. See innodb_ft_server_stopword_table for the InnoDB equivalent.
  • Commandline: --ft-stopword-file=file_name
  • Scope: Global
  • Dynamic: No
  • Data Type: file name
  • Default Value: (built-in)

general_log

  • Description: If set to 0, the default unless the --general-log option is used, the general query log is disabled, while if set to 1, the general query log is enabled. See log_output for how log files are written. If that variable is set to NONE, no logs will be written even if general_query_log is set to 1.
  • Commandline: --general-log
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0

general_log_file

  • Description: Name of the general query log file. If this is not specified, the name is taken from the log-basename setting or from your system hostname with .log as a suffix.
  • Commandline: --general-log-file=file_name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: file name
  • Default Value: host_name.log

group_concat_max_len

  • Description: Maximum length in bytes of the returned result for the functions GROUP_CONCAT(), JSON_OBJECTAGG and JSON_ARRAYAGG.
  • Commandline: --group-concat-max-len=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value:
    • 1048576 (1M)
  • Range: 4 to 4294967295

.

have_compress

  • Description: If the zlib compression library is accessible to the server, this will be set to YES, otherwise it will be NO. The COMPRESS() and UNCOMPRESS() functions will only be available if set to YES.
  • Scope: Global
  • Dynamic: No

have_crypt

  • Description: If the crypt() system call is available this variable will be set to YES, otherwise it will be set to NO. If set to NO, the ENCRYPT() function cannot be used.
  • Scope: Global
  • Dynamic: No

have_csv


have_dynamic_loading

  • Description: If the server supports dynamic loading of plugins, will be set to YES, otherwise will be set to NO.
  • Scope: Global
  • Dynamic: No

have_geometry

  • Description: If the server supports spatial data types, will be set to YES, otherwise will be set to NO.
  • Scope: Global
  • Dynamic: No

have_ndbcluster


have_partitioning

  • Description: If the server supports partitioning, will be set to YES, unless the --skip-partition option is used, in which case will be set to DISABLED. Will be set to NO otherwise. Removed in MariaDB 10.0 - SHOW PLUGINS should be used instead.
  • Scope: Global
  • Dynamic: No
  • Removed: MariaDB 10.0

have_profiling

  • Description: If statement profiling is available, will be set to YES, otherwise will be set to NO. See SHOW PROFILES() and SHOW PROFILE().
  • Scope: Global
  • Dynamic: No

have_query_cache

  • Description: If the server supports the query cache, will be set to YES, otherwise will be set to NO.
  • Scope: Global
  • Dynamic: No

have_rtree_keys

  • Description: If RTREE indexes (used for spatial indexes) are available, will be set to YES, otherwise will be set to NO.
  • Scope: Global
  • Dynamic: No

  • Description: This system variable can be used to determine whether the server supports symbolic links (note that it has no meaning on Windows).
    • If symbolic links are supported, then the value will be YES.
    • If symbolic links are not supported, then the value will be NO.
    • If symbolic links are disabled with the --symbolic-links option and the skip option prefix (i.e. --skip-symbolic-links), then the value will be DISABLED.
    • Symbolic link support is required for the INDEX DIRECTORY and DATA DIRECTORY table options.
  • Scope: Global
  • Dynamic: No

histogram_size

  • Description: Number of bytes used for a histogram, or, from MariaDB 10.7 when histogram_type is set to JSON_HB, number of buckets. If set to 0, no histograms are created by ANALYZE.
  • Commandline: --histogram-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 254
  • Range: 0 to 255

histogram_type

  • Description: Specifies the type of histograms created by ANALYZE.
    • SINGLE_PREC_HB - single precision height-balanced.
    • DOUBLE_PREC_HB - double precision height-balanced.
    • JSON_HB - JSON histograms (from MariaDB 10.7)
  • Commandline: --histogram-type=value
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value:
  • Valid Values:

host_cache_size

  • Description: Number of host names that will be cached to avoid resolving. Setting to 0 disables the cache. Changing the value while the server is running causes an implicit FLUSH HOSTS, clearing the host cache and truncating the performance_schema.host_cache table. If you are connecting from a lot of different machines you should consider increasing.
  • Commandline: --host-cache-size=#.
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 128
  • Range: 0 to 65536

hostname

  • Description: When the server starts, this variable is set to the server host name.
  • Scope: Global
  • Dynamic: No
  • Data Type: string

identity


idle_readonly_transaction_timeout


idle_transaction_timeout


idle_write_transaction_timeout


ignore_db_dirs

  • Description: Tells the server that this directory can never be a database. That means two things - firstly it is ignored by the SHOW DATABASES command and INFORMATION_SCHEMA tables. And secondly, USE, CREATE DATABASE and SELECT statements will return an error if the database from the ignored list specified. Use this option several times if you need to ignore more than one directory. To make the list empty set the void value to the option as --ignore-db-dir=. If the option or configuration is specified multiple times, viewing this value will list the ignore directories separated by commas.
  • Commandline: --ignore-db-dirs=dir.
  • Scope: Global
  • Dynamic: No
  • Data Type: string

in_predicate_conversion_threshold

  • Description: The minimum number of scalar elements in the value list of an IN predicate that triggers its conversion to an IN subquery. Set to 0 to disable the conversion. See Conversion of Big IN Predicates Into Subqueries.
  • Commandline: --in-predicate-conversion-threshold=#
  • Scope: Global, Session
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 1000
  • Range: 0 to 4294967295

in_transaction

  • Description: Session-only and read-only variable that is set to 1 if a transaction is in progress, 0 if not.
  • Commandline: No
  • Scope: Session
  • Dynamic: No
  • Data Type: boolean
  • Default Value: 0

init_connect

  • Description: String containing one or more SQL statements, separated by semicolons, that will be executed by the server for each client connecting. If there's a syntax error in the one of the statements, the client will fail to connect. For this reason, the statements are not executed for users with the SUPER privilege or, from MariaDB 10.5.2, the CONNECTION ADMIN privilege, who can then still connect and correct the error. See also init_file.
  • Commandline: --init-connect=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string

init_file

  • Description: Name of a file containing SQL statements that will be executed by the server on startup. Each statement should be on a new line, and end with a semicolon. See also init_connect.
  • Commandline: init-file=file_name
  • Scope: Global
  • Dynamic: No
  • Data Type: file name

insert_id

  • Description: Value to be used for the next statement inserting a new AUTO_INCREMENT value.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: numeric

interactive_timeout

  • Description: Time in seconds that the server waits for an interactive connection (one that connects with the mysql_real_connect() CLIENT_INTERACTIVE option) to become active before closing it. See also wait_timeout.
  • Commandline: --interactive-timeout=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 28800
  • Range: (Windows): 1 to 2147483
  • Range: (Other): 1 to 31536000

join_buffer_size

  • Description: Minimum size in bytes of the buffer used for queries that cannot use an index, and instead perform a full table scan. Increase to get faster full joins when adding indexes is not possible, although be aware of memory issues, since joins will always allocate the minimum size. Best left low globally and set high in sessions that require large full joins. In 64-bit platforms, Windows truncates values above 4GB to 4GB with a warning. See also Block-Based Join Algorithms - Size of Join Buffers.
  • Commandline: --join-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 262144 (256kB)
  • Range (non-Windows): 128 to 18446744073709547520
  • Range (Windows): 8228 to 18446744073709547520

join_buffer_space_limit

  • Description: Maximum size in bytes of the query buffer, By default 1024*128*10. See Block-based join algorithms.
  • Commandline: --join-buffer-space-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 2097152
  • Range: 2048 to 18446744073709551615

join_cache_level

  • Description: Controls which of the eight block-based algorithms can be used for join operations. See Block-based join algorithms for more information.
    • 1 – flat (Block Nested Loop) BNL
    • 2 – incremental BNL
    • 3 – flat Block Nested Loop Hash (BNLH)
    • 4 – incremental BNLH
    • 5 – flat Batch Key Access (BKA)
    • 6 – incremental BKA
    • 7 – flat Batch Key Access Hash (BKAH)
    • 8 – incremental BKAH
  • Commandline: --join-cache-level=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 2
  • Range: 0 to 8

keep_files_on_create

  • Description: If a MyISAM table is created with no DATA DIRECTORY option, the .MYD file is stored in the database directory. When set to 0, the default, if MariaDB finds another .MYD file in the database directory it will overwrite it. Setting this variable to 1 means that MariaDB will return an error instead, just as it usually does in the same situation outside of the database directory. The same applies for .MYI files and no INDEX DIRECTORY option. Deprecated in MariaDB 10.8.0.
  • Commandline: --keep-files-on-create=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Deprecated: MariaDB 10.8.0

large_files_support

  • Description: ON if the server if was compiled with large file support or not, else OFF
  • Scope: Global
  • Dynamic: No

large_page_size

  • Description: Indicates the size of memory page if large page support (Linux only) is enabled. The page size is determined from the Hugepagesize setting in /proc/meminfo. See large_pages. Deprecated and unused in MariaDB 10.5.3 since multiple page size support was added.
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: Autosized (see description)
  • Deprecated: MariaDB 10.5.3

large_pages

  • Description: Indicates whether large page support (prior to MariaDB 10.5, Linux only, by now supported Windows and BSD distros, also called huge pages) is used. This is set with --large-pages or disabled with --skip-large-pages. Large pages are used for the innodb buffer pool and for online DDL (of size 3* innodb_sort_buffer_size (or 6 when encryption is used)). To use large pages, the Linux sysctl variable kernel.shmmax must be large than the llocation. Also the sysctl variable vm.nr_hugepages multipled by large-page) must be larger than the usage. The ulimit for locked memory must be sufficient to cover the amount used (ulimit -l and equalivent in /etc/security/limits.conf / or in systemd LimitMEMLOCK). If these operating system controls or insufficient free huge pages are available, the allocation of large pages will fall back to conventional memory allocation and a warning will appear in the logs. Only allocations of the default Hugepagesize currently occur (see /proc/meminfo).
  • Commandline: --large-pages, --skip-large-pages
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF

last_insert_id

  • Description: Contains the same value as that returned by LAST_INSERT_ID(). Note that setting this variable doen't update the value returned by the underlying function.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: numeric

lc_messages

  • Description: This system variable can be specified as a locale name. The language of the associated locale will be used for error messages. See Server Locales for a list of supported locales and their associated languages.
  • Commandline: --lc-messages=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: en_us

lc_messages_dir

  • Description: This system variable can be specified either as the path to the directory storing the server's error message files or as the path to the directory storing the specific language's error message file. See Server Locales for a list of available locales and their related languages.
  • Commandline: --lc-messages-dir=path
  • Scope: Global
  • Dynamic: No
  • Data Type: directory name

lc_time_names

  • Description: The locale that determines the language used for the date and time functions DAYNAME(), MONTHNAME() and DATE_FORMAT(). Locale names are language and region subtags, for example 'en_ZA' (English - South Africa) or 'es_US: Spanish - United States'. The default is always 'en-US' regardless of the system's locale setting. See server locale for a full list of supported locales.
  • Commandline: --lc-time-names=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: en_US

license

  • Description: Server license, for example GPL.
  • Scope: Global
  • Dynamic: No
  • Data Type: string

local_infile

  • Description: If set to 1, LOCAL is supported for LOAD DATA INFILE statements. If set to 0, usually for security reasons, attempts to perform a LOAD DATA LOCAL will fail with an error message.
  • Commandline: --local-infile=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON

lock_wait_timeout

  • Description: Timeout in seconds for attempts to acquire metadata locks. Statements using metadata locks include FLUSH TABLES WITH READ LOCK, LOCK TABLES, HANDLER and DML and DDL operations on tables, stored procedures and functions, and views. The timeout is separate for each attempt, of which there may be multiple in a single statement. 0 means no wait. See WAIT and NOWAIT.
  • Commandline: --lock-wait-timeout=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value:
    • 86400 (1 day)
  • Range:
    • 0 to 31536000

locked_in_memory

  • Description: Indicates whether --memlock was used to lock mariadbd in memory.
  • Commandline: --memlock
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF

log

  • Description: Deprecated and removed in MariaDB 10.0, use general_log instead.
  • Commandline: -l [filename] or --log[=filename]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: OFF
  • Removed: MariaDB 10.0

log_disabled_statements

  • Description: If set, the specified type of statements (slave and/or stored procedure statements) will not be logged to the general log. Multiple values are comma-separated, without spaces.
  • Commandline: --log-disabled_statements=value
  • Scope: Global, Session
  • Dynamic: No
  • Data Type: set
  • Default Value: sp
  • Valid Values: slave and/or sp, or empty string for none

log_error

  • Description: Specifies the name of the error log. If --console is specified later in the configuration (Windows only) or this option isn't specified, errors will be logged to stderr. If no name is provided, errors will still be logged to hostname.err in the datadir directory by default. If a configuration file sets --log-error, one can reset it with --skip-log-error (useful to override a system wide configuration file). MariaDB always writes its error log, but the destination is configurable. See error log for details.
  • Commandline: --log-error[=name], --skip-log-error
  • Scope: Global
  • Dynamic: No
  • Data Type: file name
  • Default Value: (empty string)

log_output

  • Description: How the output for the general query log and the slow query log is stored. By default written to file (FILE), it can also be stored in the general_log and slow_log tables in the mysql database (TABLE), or not stored at all (NONE). More than one option can be chosen at the same time, with NONE taking precedence if present. Logs will not be written if logging is not enabled. See Writing logs into tables, and the slow_query_log and general_log server system variables.
  • Commandline: --log-output=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: set
  • Default Value: FILE
  • Valid Values: TABLE, FILE or NONE

log_queries_not_using_indexes

  • Description: Queries that don't use an index, or that perform a full index scan where the index doesn't limit the number of rows, will be logged to the slow query log (regardless of time taken). The slow query log needs to be enabled for this to have an effect. Mapped to log_slow_filter='not_using_index' from MariaDB 10.3.1.
  • Commandline: --log-queries-not-using-indexes
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

log_slow_admin_statements


log_slow_disabled_statements

  • Description: If set, the specified type of statements will not be logged to the slow query log. See also log_slow_admin_statements and log_slow_filter.
  • Commandline: --log-slow-disabled_statements=value
  • Scope: Global, Session
  • Dynamic: No
  • Data Type: set
  • Default Value: sp
  • Valid Vales: admin, call, slave and/or sp

log_slow_filter

  • Description: Comma-delimited string (without spaces) containing one or more settings for filtering what is logged to the slow query log. If a query matches one of the types listed in the filter, and takes longer than long_query_time, it will be logged(except for 'not_using_index' which is always logged if enabled, regardless of the time). Sets log-slow-admin-statements to ON. See also log_slow_disabled_statements.
    • admin log administrative queries (create, optimize, drop etc...)
    • filesort logs queries that use a filesort.
    • filesort_on_disk logs queries that perform a a filesort on disk.
    • filesort_priority_queue
    • full_join logs queries that perform a join without indexes.
    • full_scan logs queries that perform full table scans.
    • not_using_index logs queries that don't use an index, or that perform a full index scan where the index doesn't limit the number of rows. Disregards long_query_time, unlike other options. log_queries_not_using_indexes maps to this option. From MariaDB 10.3.1.
    • query_cache log queries that are resolved by the query cache.
    • query_cache_miss logs queries that are not found in the query cache.
    • tmp_table logs queries that create an implicit temporary table.
    • tmp_table_on_disk logs queries that create a temporary table on disk.
  • Commandline: log-slow-filter=value1[,value2...]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value:
    • admin, filesort, filesort_on_disk, filesort_priority_queue, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk
  • Valid Values:
    • admin, filesort, filesort_on_disk, filesort_priority_queue, full_join, full_scan, not_using_index, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk

log_slow_max_warnings


log_slow_min_examined_row_limit

  • Description: Don't write queries to slow query log that examine fewer rows than the set value. If set to 0, the default, no row limit is used. min_examined_row_limit is an alias. From MariaDB 11.7, queries slower than log_slow_always_query_time will always be logged.
  • Commandline: --log-slow-min-examined-row-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0-4294967295
  • Introduced: MariaDB 10.11

log_slow_queries

  • Description: Deprecated and removed in MariaDB 10.0, use slow_query_log instead.
  • Commandline: --log-slow-queries[=name]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Removed: MariaDB 10.0

log_slow_query

  • Description: If set to 0, the default unless the --slow-query-log option is used, the slow query log is disabled, while if set to 1 (both global and session variables), the slow query log is enabled. Named slow_query_log before MariaDB 10.11.0, which is now an alias.
  • Commandline: --slow-query-log
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0
  • Introduced: MariaDB 10.11.0
  • See also: See log_output to see how log files are written. If that variable is set to NONE, no logs will be written even if log_slow_query is set to 1.

log_slow_query_file


log_slow_query_time


log_slow_rate_limit

  • Description: The slow query log will log every this many queries. The default is 1, or every query, while setting it to 20 would log every 20 queries, or five percent. Aims to reduce I/O usage and excessively large slow query logs. See also Slow Query Log Extended Statistics. From MariaDB 11.7, queries slower than log_slow_always_query_time will always be logged.
  • Commandline: log-slow-rate-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1
  • Range: 1 upwards

log_slow_verbosity


log_tc_size

  • Description: Defines the size in bytes of the memory-mapped file-based transaction coordinator log, which is only used if the binary log is disabled. If you have two or more XA-capable storage engines enabled, then a transaction coordinator log must be available. This size is defined in multiples of 4096. See Transaction Coordinator Log for more information. Also see the --log-tc server option and the --tc-heuristic-recover option.
  • Commandline: log-tc-size=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 24576
  • Range: 12288 to 18446744073709551615

log_warnings

  • Description: Determines which additional warnings are logged. Setting to 0 disables additional warning logging. Note that this does not prevent all warnings, there is a core set of warnings that will always be written to the error log. The additional warnings are as follows:
    • log_warnings >= 1
      • Event scheduler information.
      • System signals
      • Wrong usage of --user
      • Failed setrlimit() and mlockall()
      • Changed limits
      • Wrong values of lower_case_table_names and stack_size
      • Wrong values for command line options
      • Start log position and some master information when starting slaves
      • Slave reconnects
      • Killed slaves
      • Error reading relay logs
      • Unsafe statements for statement-based replication. If this warning occurs frequently, it is throttled to prevent flooding the log.
      • Disabled plugins that one tried to enable or use.
      • UDF files that didn't include the required init functions.
      • DNS lookup failures.
    • log_warnings >= 2
      • Access denied errors.
      • Connections aborted or closed due to errors or timeouts.
      • Table handler errors
      • Messages related to the files used to persist replication state:
        • Either the default master.info file or the file that is configured by the master_info_file option.
        • Either the default relay-log.info file or the file that is configured by the relay_log_info_file system variable.
      • Information about a master's binary log dump thread.
    • log_warnings >= 3
    • log_warnings >=4
      • Connections aborted due to "Too many connections" errors.
      • Connections closed normally without authentication.
      • Connections aborted due to KILL.
      • Connections closed due to released connections, such as when completion_type is set to RELEASE.
      • Could not read packet: (a lot more information)
      • All read/write errors for a connection are logged to the error log.
    • log_warnings >=9
      • Information about initializing plugins.
  • Commandline: -W [level] or --log-warnings[=level]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value:
    • 2
  • Range: 0 to 4294967295

long_query_time

  • Description: If a query takes longer than this many seconds to execute (microseconds can be specified too), the Slow_queries status variable is incremented and, if enabled, the query is logged to the slow query log. From MariaDB 10.11.0, this is an alias for log_slow_query_time.
  • Commandline: --long-query-time=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 10.000000
  • Range: 0 upwards

low_priority_updates

  • Description: If set to 1 (0 is the default), for storage engines that use only table-level locking (Aria, MyISAM, MEMORY and MERGE), all INSERTs, UPDATEs, DELETEs and LOCK TABLE WRITEs will wait until there are no more SELECTs or LOCK TABLE READs pending on the relevant tables. Set this to 1 if reads are prioritized over writes.
  • Commandline: --low-priority-updates
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0

lower_case_file_system

  • Description: Read-only variable describing whether the file system is case-sensitive. If set to OFF, file names are case-sensitive. If set to ON, they are not case-sensitive.
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: ##

lower_case_table_names

  • Description: If set to 0 (the default on Unix-based systems), table names and aliases and database names are compared in a case-sensitive manner. If set to 1 (the default on Windows), names are stored in lowercase and not compared in a case-sensitive manner. If set to 2 (the default on Mac OS X), names are stored as declared, but compared in lowercase. This system variable's value cannot be changed after the datadir has been initialized. lower_case_table_names is set when a MariaDB instance starts, and it remains constant afterwards.
  • Commandline: --lower-case-table-names[=#]
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 0 (Unix), 1 (Windows), 2 (Mac OS X)
  • Range: 0 to 2

max_allowed_packet

  • Description: Maximum size in bytes of a packet or a generated/intermediate string. The packet message buffer is initialized with the value from net_buffer_length, but can grow up to max_allowed_packet bytes. Set as large as the largest BLOB, in multiples of 1024. If this value is changed, it should be changed on the client side as well. See slave_max_allowed_packet for a specific limit for replication purposes.
  • Commandline: --max-allowed-packet=#
  • Scope: Global, Session
  • Dynamic: Yes (Global), No (Session)
  • Data Type: numeric
  • Default Value:
    • 16777216 (16M)
    • 1073741824 (1GB) (client-side)
  • Range: 1024 to 1073741824

max_connect_errors

  • Description: Limit to the number of successive failed connects from a host before the host is blocked from making further connections. The count for a host is reset to zero if they successfully connect. To unblock, flush the host cache with a FLUSH HOSTS statement or mariadb-admin flush-hosts. The performance_schema.host_cache table contains the status of the current hosts.
  • Commandline: --max-connect-errors=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 100
  • Range: 1 to 4294967295

max_connections

  • Description: The maximum number of simultaneous client connections. See also Handling Too Many Connections. Note that this value affects the number of file descriptors required on the operating system. Minimum was changed from 1 to 10 to avoid possible unexpected results for the user (MDEV-18252). Note that MariaDB always has one reserved connection for a SUPER (or CONNECTION ADMIN user). Additionally it can listen on a separate port, so will be available even when the max_connections limit is reached.
  • Commandline: --max-connections=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 151
  • Range: 10 to 100000

max_delayed_threads

  • Description: Limits to the number of INSERT DELAYED threads. Once this limit is reached, the insert is handled as if there was no DELAYED attribute. If set to 0, DELAYED is ignored entirely. The session value can only be set to 0 or to the same as the global value.
  • Commandline: --max-delayed-threads=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 20
  • Range: 0 to 16384

max_digest_length

  • Description: Maximum length considered for computing a statement digest, such as used by the Performance Schema and query rewrite plugins. Statements that differ after this many bytes produce the same digest, and are aggregated for statistics purposes. The variable is allocated per session. Increasing will allow longer statements to be distinguished from each other, but increase memory use, while decreasing will reduce memory use, but more statements may become indistinguishable.
  • Commandline: --max-digest-length=#
  • Scope: Global,
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 1024
  • Range: 0 to 1048576

max_error_count

  • Description: Specifies the maximum number of messages stored for display by SHOW ERRORS and SHOW WARNINGS statements.
  • Commandline: --max-error-count=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 64
  • Range: 0 to 65535

max_heap_table_size

  • Description: Maximum size in bytes for user-created MEMORY tables. Setting the variable while the server is active has no effect on existing tables unless they are recreated or altered. The smaller of max_heap_table_size and tmp_table_size also limits internal in-memory tables. When the maximum size is reached, any further attempts to insert data will receive a "table ... is full" error. Temporary tables created with CREATE TEMPORARY will not be converted to Aria, as occurs with internal temporary tables, but will also receive a table full error.
  • Commandline: --max-heap-table-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16777216
  • Range : 16384 to 4294966272

max_insert_delayed_threads


max_join_size

  • Description: Statements will not be performed if they are likely to need to examine more than this number of rows, row combinations or do more disk seeks. Can prevent poorly-formatted queries from taking server resources. Changing this value to anything other the default will reset sql_big_selects to 0. If sql_big_selects is set again, max_join_size will be ignored. This limit is also ignored if the query result is sitting in the query cache. Previously named sql_max_join_size, which is still a synonym.
  • Commandline: --max-join-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 18446744073709551615
  • Range: 1 to 18446744073709551615

max_length_for_sort_data

  • Description: Used to decide which algorithm to choose when sorting rows. If the total size of the column data, not including columns that are part of the sort, is less than max_length_for_sort_data, then we add these to the sort key. This can speed up the sort as we don't have to re-read the same row again later. Setting the value too high can slow things down as there will be a higher disk activity for doing the sort.
  • Commandline: --max-length-for-sort-data=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1024
  • Range: 4 to 8388608

max_long_data_size


max_password_errors

  • Description: The maximum permitted number of failed connection attempts due to an invalid password before a user is blocked from further connections. FLUSH_PRIVILEGES will permit the user to connect again. This limit is ignored for users with the SUPER privilege or, from MariaDB 10.5.2, the CONNECTION ADMIN privilege. The maximum also doesn't apply to users with a hostname of localhost, 127.0.0.1 or ::1. See also the Information Schema USERS table.
  • Commandline: --max-password-errors=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4294967295
  • Range: 1 to 4294967295

max_prepared_stmt_count

  • Description: Maximum number of prepared statements on the server. Can help prevent certain forms of denial-of-service attacks. If set to 0, no prepared statements are permitted on the server.
  • Commandline: --max-prepared-stmt-count=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16382
  • Range: 0 to 4294967295

max_recursive_iterations

  • Description: Maximum number of iterations when executing recursive queries, used to prevent infinite loops in recursive CTEs.
  • Commandline: --max-recursive-iterations=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1000 (>= MariaDB 10.6.0), 4294967295 (<= MariaDB 10.5)
  • Range: 0 to 4294967295

max_rowid_filter_size

  • Description: The maximum size of the container of a rowid filter.
  • Commandline: --max-rowid-filter-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 131072
  • Range: 1024 to 18446744073709551615

max_seeks_for_key

  • Description: The optimizer assumes that the number specified here is the most key seeks required when searching with an index, regardless of the actual index cardinality. If this value is set lower than its default and maximum, indexes will tend to be preferred over table scans.
  • Commandline: --max-seeks-for-key=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4294967295
  • Range: 1 to 4294967295

max_session_mem_used

  • Description: Amount of memory a single user session is allowed to allocate. This limits the value of the session variable Memory_used.
  • Commandline: --max-session-mem-used=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 9223372036854775807 (8192 PB)
  • Range: 8192 to 18446744073709551615

max_sort_length

  • Description: Maximum size in bytes used for sorting data values - anything exceeding this is ignored. The server uses only the first max_sort_length bytes of each value and ignores the rest. Increasing this may require sort_buffer_size to be increased (especially if ER_OUT_OF_SORTMEMORY errors start appearing). From MariaDB 11.7, a warning is generated when max_sort_length is exceeded.
  • Commandline: --max-sort-length=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1024
  • Range:

max_sp_recursion_depth

  • Description: Permitted number of recursive calls for a stored procedure. 0, the default, no recursion is permitted. Increasing this value increases the thread stack requirements, so you may need to increase thread_stack as well. This limit doesn't apply to stored functions.
  • Commandline: --max-sp-recursion-depth[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 255

max_statement_time

  • Description: Maximum time in seconds that a query can execute before being aborted. This includes all queries, not just SELECT statements, but excludes statements in stored procedures. If set to 0, no limit is applied. See Aborting statements that take longer than a certain time to execute for details and limitations. Useful when combined with SET STATEMENT for limiting the execution times of individual queries. Replicas are not affected by this variable, however, from MariaDB 10.10, there's slave_max_statement_time that sets the limit to abort queries on a replica.
  • Commandline: --max-statement-time[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0.000000
  • Range: 0 to 31536000

max_tmp_tables


max_user_connections

  • Description: Maximum simultaneous connections permitted for each user account. When set to 0, there is no per user limit. Setting it to -1 stops users without the SUPER privilege or, from MariaDB 10.5.2, the CONNECTION ADMIN privilege, from connecting to the server. The session variable is always read-only and only privileged users can modify user limits. The session variable defaults to the global max_user_connections variable, unless the user's specific MAX_USER_CONNECTIONS resource option is non-zero. When both global variable and the user resource option are set, the user's MAX_USER_CONNECTIONS is used. Note: This variable does not affect users with the SUPER privilege or, from MariaDB 10.5.2, the CONNECTION ADMIN privilege.
  • Commandline: --max-user-connections=#
  • Scope: Global, Session
  • Dynamic: Yes, (except when globally set to 0 or -1)
  • Data Type: numeric
  • Default Value: 0
  • Range: -1 to 4294967295

max_write_lock_count

  • Description: Read lock requests will be permitted for processing after this many write locks. Applies only to storage engines that use table level locks (thr_lock), so no effect with InnoDB or Archive.
  • Commandline: --max-write-lock-count=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 4294967295
  • Range: 0-4294967295

metadata_locks_cache_size

  • Description: Unused since 10.1.4
  • Commandline: --metadata-locks-cache-size=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 1024
  • Range: 1 to 1048576

metadata_locks_hash_instances

  • Description: Unused since 10.1.4
  • Commandline: --metadata-locks-hash-instances=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 8
  • Range: 1 to 1024

min_examined_row_limit

  • Description: Don't write queries to slow query log that examine fewer rows than the set value. If set to 0, the default, no row limit is used. From MariaDB 10.11.0, this is an alias for log_slow_min_examined_row_limit.
  • Commandline: --min-examined-row-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0-4294967295

mrr_buffer_size

  • Description: Size of buffer to use when using multi-range read with range access. See Multi Range Read optimization for more information.
  • Commandline: --mrr-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 262144
  • Range 8192 to 2147483648

multi_range_count


mysql56_temporal_format

  • Description: If set (the default), MariaDB uses the MySQL 5.6 low level formats for TIME, DATETIME and TIMESTAMP instead of the MariaDB 5.3 version. The version MySQL introduced in 5.6 requires more storage, but potentially allows negative dates and has some advantages in replication. There should be no reason to revert to the old MariaDB 5.3 microsecond format. See also MDEV-10723.
  • Commandline: --mysql56-temporal-format
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON

named_pipe

  • Description: On Windows systems, determines whether connections over named pipes are permitted.
  • Commandline: --named-pipe
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF

net_buffer_length

  • Description: The starting size, in bytes, for the connection and thread buffers for each client thread. The size can grow to max_allowed_packet. This variable's session value is read-only. Can be set to the expected length of client statements if memory is a limitation.
  • Commandline: --net-buffer-length=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16384
  • Range: 1024 to 1048576

net_read_timeout

  • Description: Time in seconds the server will wait for a client connection to send more data before aborting the read. See also net_write_timeout and slave_net_timeout
  • Commandline: --net-read-timeout=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 30
  • Range: 1 upwards

net_retry_count

  • Description: Permit this many retries before aborting when attempting to read or write on a communication port. On FreeBSD systems should be set higher as threads are sent internal interrupts..
  • Commandline: --net-retry-count=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 10
  • Range: 1 to 4294967295

net_write_timeout

  • Description: Time in seconds to wait on writing a block to a connection before aborting the write. See also net_read_timeout and slave_net_timeout.
  • Commandline: --net-write-timeout=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 60
  • Range: 1 upwards

note_verbosity

  • Description: Verbosity level for note-warnings given to the user. Options are added in a comma-delimited string, except for all, which sets all options. See also Notes when an index cannot be used. Be aware that if the old sql_notes variable is 0, one will not get any notes. Setting note_verbosity to "" is the recommended way to disable notes.
    • basic All old notes.
    • unusable_keys Give warnings for unusable keys for SELECT, DELETE and UPDATE.
    • explain Give warnings for unusable keys for EXPLAIN.
    • all Enables all above options. This has to be given alone.
  • Commandline: note-verbosity=value1[,value2...]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: basic,explain
  • Valid Values: basic,explain,unusable_keys or all.
  • Introduced: MariaDB 10.6.16

old

  • Description: Disabled by default, enabling it reverts index hints to those used before MySQL 5.1.17. Enabling may lead to replication errors. Deprecated and replaced by old_mode from MariaDB 10.9.
  • Commandline: --old
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Deprecated: MariaDB 10.9

old_alter_table


old_mode

  • Description: Used for getting MariaDB to emulate behavior from an old version of MySQL or MariaDB. See OLD Mode. Fully replaces the old variable from MariaDB 10.9. Non-default OLD_MODE options are by design deprecated and will eventually be removed.
  • Commandline: --old-mode
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: UTF8_IS_UTF8MB3 (>= MariaDB 10.6) (empty string) (<= MariaDB 10.5)
  • Valid Values: See OLD Mode for the full list.

old_passwords

  • Description: If set to 1 (0 is default), MariaDB reverts to using the mysql_old_password authentication plugin by default for newly created users and passwords, instead of the mysql_native_password authentication plugin.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

open_files_limit


optimizer_extra_pruning_depth

  • Description:If the optimizer needs to enumerate a join prefix of this size or larger, then it will try aggressively prune away the search space.
  • Commandline: --optimizer-extra-pruning-depth[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 8
  • Range: 0 to 62
  • Introduced: MariaDB 10.10.1

optimizer_join_limit_pref_ratio


optimizer_max_sel_arg_weight

  • Description: This is an actively enforced maximum effective SEL_ARG tree weight limit. A SEL_ARG weight is the number of effective "ranges" hanging off this root (that is, merged tree elements are "unmerged" to count the weight). During range analysis, looking for possible index merges, SEL_ARG graphs related to key ranges in query conditions are being processed. Graphs exceeding this limit will stop keys being 'and'ed and 'or'ed together to form a new larger SEL_ARG graph. After each 'and' or 'or' process, this maximum weight limit is enforced. It enforces this limit by pruning the key part being used. This key part pruning can be used to limit/disable index merge SEL_ARG graph construction on overly long query conditions. See optimizer_max_sel_arg_weight for details.
  • Commandline: --optimizer-max-sel-arg-weight=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 32000
  • Range: 0 to 18446744073709551615
  • Introduced: MariaDB 10.5.9

optimizer_max_sel_args

  • Description: The maximum number of SEL_ARG objects created when optimizing a range. If more objects would be needed, range scans will not be used by the optimizer.
  • Commandline: --optimizer-max-sel-args=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16000
  • Range: 0 to 4294967295
  • Introduced: MariaDB 10.6.16, MariaDB 10.10.7, MariaDB 10.11.6, MariaDB 11.0.4, MariaDB 11.1.3

optimizer_prune_level

  • Description:Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from the optimizer search space.
    • 0: heuristics are disabled and an exhaustive search is performed
    • 1: the optimizer will use heuristics to prune less-promising partial plans from the optimizer search space
    • 2: tables using EQ_REF will be joined together as 'one entity' and the different combinations of these tables will not be considered (from MariaDB 10.10)
  • Commandline: --optimizer-prune-level[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 2 (>= MariaDB 10.10), 1 (<= MariaDB 10.9)

optimizer_search_depth

  • Description: Maximum search depth by the query optimizer. Smaller values lead to less time spent on execution plans, but potentially less optimal results. If set to 0, MariaDB will automatically choose a reasonable value. Since the better results from more optimal planning usually offset the longer time spent on planning, this is set as high as possible by default. 63 is a valid value, but its effects (switching to the original find_best search) are deprecated.
  • Commandline: --optimizer-search-depth[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 62
  • Range: 0 to 63

optimizer_selectivity_sampling_limit

  • Description: Controls number of record samples to check condition selectivity. Only used if optimizer_use_condition_selectivity > 4.
  • Commandline: optimizer-selectivity-sampling-limit[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 100
  • Range: 10 upwards

optimizer_switch


optimizer_trace

  • Description: Controls tracing of the optimizer: optimizer_trace=option=val[,option=val...], where option is one of {enabled} and val is one of {on, off, default}
  • Commandline: --optimizer-trace=value
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enum
  • Default Value: enabled=off
  • Valid Values: enabled={on|off|default}

optimizer_trace_max_mem_size

  • Description: Limits the memory used while tracing a query by specifying the maximum allowed cumulated size, in bytes, of stored optimizer traces.
  • Commandline: --optimizer-trace-max-mem-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1048576
  • Range: 1 to 18446744073709551615

optimizer_use_condition_selectivity

  • Description: Controls which statistics can be used by the optimizer when looking for the best query execution plan.
    • 1 Use selectivity of predicates as in MariaDB 5.5.
    • 2 Use selectivity of all range predicates supported by indexes.
    • 3 Use selectivity of all range predicates estimated without histogram.
    • 4 Use selectivity of all range predicates estimated with histogram.
    • 5 Additionally use selectivity of certain non-range predicates calculated on record sample.
  • Commandline: --optimizer-use-condition-selectivity=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4
  • Range: 1 to 5

pid_file

  • Description: Full path of the process ID file.
  • Commandline: --pid-file=file_name
  • Scope: Global
  • Dynamic: No
  • Data Type: file name

plugin_dir

  • Description: Path to the plugin directory. For security reasons, either make sure this directory can only be read by the server, or set secure_file_priv.
  • Commandline: --plugin-dir=path
  • Scope: Global
  • Dynamic: No
  • Data Type: directory name
  • Default Value: BASEDIR/lib/plugin

plugin_maturity

  • Description: The lowest acceptable plugin maturity. MariaDB will not load plugins less mature than the specified level.
  • Commandline: --plugin-maturity=level
  • Scope: Global
  • Dynamic: No
  • Type: enum
  • Default Value: One less than the server maturity
  • Valid Values: unknown, experimental, alpha, beta, gamma, stable

port

  • Description: Port to listen for TCP/IP connections. If set to 0, will default to, in order of preference, my.cnf, the MYSQL_TCP_PORT environment variable, /etc/services, built-in default (3306).
  • Commandline: --port=#, -P
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 3306
  • Range: 0 to 65535

preload_buffer_size

  • Description: Size in bytes of the buffer allocated when indexes are preloaded.
  • Commandline: --preload-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 32768
  • Range: 1024 to 1073741824

profiling

  • Description: If set to 1 (0 is default), statement profiling will be enabled. See SHOW PROFILES() and SHOW PROFILE().
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

profiling_history_size

  • Description: Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See SHOW PROFILES.
  • Commandline: --profiling-history-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 15
  • Range: 0 to 100

progress_report_time

  • Description: Time in seconds between sending progress reports to the client for time-consuming statements. If set to 0, progress reporting will be disabled.
  • Commandline: --progress-report-time=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 5
  • Range: 0 to 4294967295

protocol_version

  • Description: The version of the client/server protocol used by the MariaDB server.
  • Commandline: None
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 10
  • Range: 0 to 4294967295

proxy_protocol_networks

  • Description: Enable proxy protocol for these source networks. The syntax is a comma separated list of IPv4 and IPv6 networks. If the network doesn't contain a mask, it is considered to be a single host. "*" represents all networks and must be the only directive on the line. String "localhost" represents non-TCP local connections (Unix domain socket, Windows named pipe or shared memory). See Proxy Protocol Support.
  • Commandline: --proxy-protocol-networks=value
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: (empty)

proxy_user

  • Description: Set to the proxy user account name if the current client is a proxy, else NULL.
  • Scope: Session
  • Dynamic: No
  • Data Type: string

pseudo_slave_mode

  • Description: For internal use by the server.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: OFF

pseudo_thread_id

  • Description: For internal use only.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: numeric

query_alloc_block_size

  • Description: Size in bytes of the extra blocks allocated during query parsing and execution (after query_prealloc_size is used up).
  • Commandline: --query-alloc-block-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16384
  • Range - 32 bit: 1024 to 4294967295
  • Range - 64 bit: 1024 to 18446744073709547520

query_cache_limit

  • Description: Size in bytes for which results larger than this are not stored in the query cache.
  • Commandline: --query-cache-limit=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1048576 (1MB)
  • Range: 0 to 4294967295

query_cache_min_res_unit

  • Description: Minimum size in bytes of the blocks allocated for query cache results.
  • Commandline: --query-cache-min-res-unit=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4096 (4KB)
  • Range - 32 bit: 1024 to 4294967295
  • Range - 64 bit: 1024 to 18446744073709547520

query_cache_size

  • Description: Size in bytes available to the query cache. About 40KB is needed for query cache structures, so setting a size lower than this will result in a warning. 0, the default before MariaDB 10.1.7, effectively disables the query cache.

Warning: Starting from MariaDB 10.1.7, query_cache_type is automatically set to ON if the server is started with the query_cache_size set to a non-zero (and non-default) value. This will happen even if query_cache_type is explicitly set to OFF in the configuration.

  • Commandline: --query-cache-size=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1M (although frequently given a default value in some setups)
  • Valid Values: 0 upwards in units of 1024.

query_cache_strip_comments

  • Description: If set to 1 (0 is default), the server will strip any comments from the query before searching to see if it exists in the query cache. Multiple space, line feeds, tab and other white space characters will also be removed.
  • Commandline: query-cache-strip-comments
  • Scope: Session, Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

query_cache_type

  • Description: If set to 0, the query cache is disabled (although a buffer of query_cache_size bytes is still allocated). If set to 1 all SELECT queries will be cached unless SQL_NO_CACHE is specified. If set to 2 (or DEMAND), only queries with the SQL CACHE clause will be cached. Note that if the server is started with the query cache disabled, it cannot be enabled at runtime.

Warning: Starting from MariaDB 10.1.7, query_cache_type is automatically set to ON if the server is started with the query_cache_size set to a non-zero (and non-default) value. This will happen even if query_cache_type is explicitly set to OFF in the configuration.

  • Commandline: --query-cache-type=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: OFF
  • Valid Values: 0 or OFF, 1 or ON, 2 or DEMAND

query_cache_wlock_invalidate

  • Description: If set to 0, the default, results present in the query cache will be returned even if there's a write lock on the table. If set to 1, the client will first have to wait for the lock to be released.
  • Commandline: --query-cache-wlock-invalidate
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

query_prealloc_size

  • Description: Size in bytes of the persistent buffer for query parsing and execution, allocated on connect and freed on disconnect. Increasing may be useful if complex queries are being run, as this will reduce the need for more memory allocations during query operation. See also query_alloc_block_size.
  • Commandline: --query-prealloc-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 24576
  • Range: 1024 to 4294967295

rand_seed1

  • Description: rand_seed1 and rand_seed2 facilitate replication of the RAND() function. The master passes the value of these to the slaves so that the random number generator is seeded in the same way, and generates the same value, on the slave as on the master. Until MariaDB 10.1.4, the variable value could not be viewed, with the SHOW VARIABLES output always displaying zero.
  • Commandline: None
  • Scope: Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: Varies
  • Range: 0 to 18446744073709551615

rand_seed2


range_alloc_block_size

  • Description: Size in bytes of blocks allocated during range optimization. The unit size in 1024.
  • Commandline: --range-alloc-block-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4096
  • Range - 32 bit: 4096 to 4294967295
  • Range - 64 bit: 4096 to 18446744073709547520

read_buffer_size

  • Description: Each thread performing a sequential scan (for MyISAM, Aria and MERGE tables) allocates a buffer of this size in bytes for each table scanned. Increase if you perform many sequential scans. If not in a multiple of 4KB, will be rounded down to the nearest multiple. Also used in ORDER BY's for caching indexes in a temporary file (not temporary table), for caching results of nested queries, for bulk inserts into partitions, and to determine the memory block size of MEMORY tables.
  • Commandline: --read-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 131072
  • Range: 8200 to 2147479552

read_only

  • Description: When set to 1 (0 is default), no updates are permitted except from users with the SUPER privilege or, from MariaDB 10.5.2, the READ ONLY ADMIN privilege, or replica servers updating from a primary. The read_only variable is useful for replica servers to ensure no updates are accidentally made outside of what are performed on the primary. Inserting rows to log tables, updates to temporary tables and OPTIMIZE TABLE or ANALYZE TABLE statements are excluded from this limitation. If read_only is set to 1, then the SET PASSWORD statement is limited only to users with the SUPER privilege (<= MariaDB 10.5.1) or READ ONLY ADMIN privilege (>= MariaDB 10.5.2). Attempting to set this variable to 1 will fail if the current session has table locks or transactions pending, while if other sessions hold table locks, the statement will wait until these locks are released before completing. While the attempt to set read_only is waiting, other requests for table locks or transactions will also wait until read_only has been set. See Read-Only Replicas for more. From MariaDB 10.5.2, the READ_ONLY ADMIN privilege will allow users granted that privilege to perform writes, even if the read_only variable is set. In earlier versions, and until MariaDB 10.11.0, users with the SUPER can perform writes while this variable is set.
  • Commandline: --read-only
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

read_rnd_buffer_size

  • Description: Size in bytes of the buffer used when reading rows from a MyISAM table in sorted order after a key sort. Larger values improve ORDER BY performance, although rather increase the size by SESSION where the need arises to avoid excessive memory use.
  • Commandline: --read-rnd-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 262144
  • Range: 8200 to 2147483647

redirect_url

  • Description: URL of another server to redirect clients to. Empty string means no redirection.
  • Commandline: --redirect_url=val
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: Empty
  • Introduced: MariaDB 11.3.0

-

-

require_secure_transport

  • Description: When this option is enabled, connections attempted using insecure transport will be rejected. Secure transports are SSL/TLS, Unix sockets or named pipes. Note that per-account requirements take precedence.
  • Commandline: --require-secure-transport[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Introduced: MariaDB 10.5.2

rowid_merge_buff_size

  • Description: The maximum size in bytes of the memory available to the Rowid-merge strategy. See Non-semi-join subquery optimizations for more information.
  • Commandline: --rowid-merge-buff-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 8388608
  • Range: 0 to 2147483647

rpl_recovery_rank


safe_show_database

  • Description: This variable was removed in MariaDB 5.5, and has been replaced by the more flexible SHOW DATABASES privilege.
  • Commandline: --safe-show-database (until MySQL 4.1.1)
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Removed: MariaDB 5.5

secure_auth


secure_file_priv

  • Description: LOAD DATA, SELECT ... INTO and LOAD FILE() will only work with files in the specified path. If not set, the default, or set to empty string, the statements will work with any files that can be accessed.
  • Commandline: --secure-file-priv=path
  • Scope: Global
  • Dynamic: No
  • Data Type: path name
  • Default Value: None

secure_timestamp

  • Description: Restricts direct setting of a session timestamp. Possible levels are:
    • YES - timestamp cannot deviate from the system clock. Intended to prevent tampering with system versioning history. Should not be used on replicas, as when a value based on the timestamp is inserted in statement mode, discrepancies can occur.
    • REPLICATION - replication thread can adjust timestamp to match the primary's
    • SUPER - a user with this privilege and a replication thread can adjust timestamp
    • NO - historical behavior, anyone can modify session timestamp
  • Commandline: --secure-timestamp=value
  • Scope: Global
  • Dynamic: No
  • Data Type: enum
  • Default Value: NO

session_track_schema

  • Description: Whether to track changes to the default schema within the current session.
  • Commandline: --session-track-schema={0|1}
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON

session_track_state_change

  • Description: Whether to track changes to the session state.
  • Commandline: --session-track-state-change={0|1}
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

session_track_system_variables

  • Description: Comma-separated list of session system variables for which to track changes. For compatibility with MySQL defaults, this variable should be set to "autocommit, character_set_client, character_set_connection, character_set_results, time_zone". The * character tracks all session variables.
  • Commandline: --session-track-system-variables=value
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value:
    • >= MariaDB 11.3: autocommit,character_set_client,character_set_connection,character_set_results,redirect_url,time_zone
    • <= MariaDB 11.2: autocommit, character_set_client, character_set_connection, character_set_results, time_zone

session_track_transaction_info

  • Description: Track changes to the transaction attributes. OFF to disable; STATE to track just transaction state (Is there an active transaction? Does it have any data? etc.); CHARACTERISTICS to track transaction state and report all statements needed to start a transaction with the same characteristics (isolation level, read only/read write,snapshot - but not any work done / data modified within the transaction).
  • Commandline: --session-track-transaction-info=value
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enum
  • Default Value: OFF
  • Valid Values: OFF, STATE, CHARACTERISTICS

shared_memory

  • Description: Windows only, determines whether the server permits shared memory connections. See also shared_memory_base_name.
  • Scope: Global
  • Dynamic: No

shared_memory_base_name

  • Description: Windows only, specifies the name of the shared memory to use for shared memory connection. Mainly used when running more than one instance on the same physical machine. By default the name is MYSQL and is case sensitive. See also shared_memory.
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: MYSQL

skip_external_locking

  • Description: If this system variable is set, then some kinds of external table locks will be disabled for some storage engines.
    • If this system variable is set, then the MyISAM storage engine will not use file-based locks. Otherwise, it will use the fcntl() function with the F_SETLK option to get file-based locks on Unix, and it will use the LockFileEx() function to get file-based locks on Windows.
    • If this system variable is set, then the Aria storage engine will not lock a table when it decrements the table's in-file counter that keeps track of how many connections currently have the table open. See MDEV-19393 for more information.
  • Commandline: --skip-external-locking
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: 1

skip_grant_tables


skip_name_resolve

  • Description: If set to 1 (0 is the default), only IP addresses are used for connections. Host names are not resolved. All host values in the GRANT tables must be IP addresses (or localhost).
  • Commandline: --skip-name-resolve
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: 0

skip_networking

  • Description: If set to 1, (0 is the default), the server does not listen for TCP/IP connections. All interaction with the server will be through socket files (Unix) or named pipes or shared memory (Windows). It's recommended to use this option if only local clients are permitted to connect to the server.
  • Commandline: --skip-networking
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: 0

skip_show_database

  • Description: If set to 1, (0 is the default), only users with the SHOW DATABASES privilege can use the SHOW DATABASES statement to see all database names.
  • Commandline: --skip-show-database
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: 0

slow_launch_time

  • Description: Time in seconds. If a thread takes longer than this to launch, the slow_launch_threads server status variable is incremented.
  • Commandline: --slow-launch-time=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 2

slow_query_log

  • Description: If set to 0, the default unless the --slow-query-log option is used, the slow query log is disabled, while if set to 1 (both global and session variables), the slow query log is enabled. From MariaDB 10.11.0, an alias for log_slow_query.
  • Commandline: --slow-query-log
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Data Type: boolean
  • Default Value: 0
  • See also: See log_output to see how log files are written. If that variable is set to NONE, no logs will be written even if slow_query_log is set to 1.

slow_query_log_file


socket

  • Description: On Unix-like systems, this is the name of the socket file used for local client connections, by default /tmp/mysql.sock, often changed by the distribution, for example /var/lib/mysql/mysql.sock. On Windows, this is the name of the named pipe used for local client connections, by default MySQL. On Windows, this is not case-sensitive.
  • Commandline: --socket=name
  • Scope: Global
  • Dynamic: No
  • Data Type: file name
  • Default Value: /tmp/mysql.sock (Unix), MySQL (Windows)

sort_buffer_size

  • Description: Each session performing a sort allocates a buffer with this amount of memory. Not specific to any storage engine. If the status variable sort_merge_passes is too high, you may need to look at improving your query indexes, or increasing this. Consider reducing where there are many small sorts, such as OLTP, and increasing where needed by session. 16k is a suggested minimum.
  • Commandline: --sort-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: number
  • Default Value: 2M (2097152) (some distributions increase the default)

sql_auto_is_null

  • Description: If set to 1, the query SELECT * FROM table_name WHERE auto_increment_column IS NULL will return an auto-increment that has just been successfully inserted, the same as the LAST_INSERT_ID() function. Some ODBC programs make use of this IS NULL comparison.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0

sql_big_selects

  • Description: If set to 0, MariaDB will not perform large SELECTs. See max_join_size for details. If max_join_size is set to anything but DEFAULT, sql_big_selects is automatically set to 0. If sql_big_selects is again set, max_join_size will be ignored.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

sql_big_tables

  • Description: Old variable, which if set to 1, allows large result sets by saving all temporary sets to disk, avoiding 'table full' errors. No longer needed, as the server now handles this automatically.
  • Commandline: --sql-big-tables
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0
  • Removed: MariaDB 10.0

sql_buffer_result

  • Description: If set to 1 (0 is default), results from SELECT statements are always placed into temporary tables. This can help the server when it takes a long time to send the results to the client by allowing the table locks to be freed early.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0

sql_if_exists

  • Description: If set to 1, adds an implicit IF EXISTS to ALTER, RENAME and DROP of TABLES, VIEWS, FUNCTIONS and PACKAGES. This variable is mainly used in replication to tag DDLs that can be ignored on the slave if the target table doesn't exist.
  • Commandline: --sql-if-exists[={0|1}]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Introduced: MariaDB 10.5.2

sql_log_off

  • Description: If set to 1 (0 is the default), no logging to the general query log is done for the client. Only clients with the SUPER privilege can update this variable.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0

sql_log_update

  • Description: Removed. Use sql_log_bin instead.
  • Removed: MariaDB/MySQL 5.5

sql_low_priority_updates

  • Description: If set to 1 (0 is the default), for storage engines that use only table-level locking (Aria, MyISAM, MEMORY and MERGE), all INSERTs, UPDATEs, DELETEs and LOCK TABLE WRITEs will wait until there are no more SELECTs or LOCK TABLE READs pending on the relevant tables. Set this to 1 if reads are prioritized over writes.
  • Commandline: --sql-low-priority-updates
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0
  • Removed: MariaDB 10.0

sql_max_join_size


sql_mode

  • Description: Sets the SQL Mode. Multiple modes can be set, separated by a comma.
  • Commandline: --sql-mode=value[,value[,value...]]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value:
    • STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  • Valid Values: See SQL Mode for the full list.

sql_notes

  • Description: If set to 1, the default, warning_count is incremented each time a Note warning is encountered. If set to 0, Note warnings are not recorded. mariadb-dump has outputs to set this variable to 0 so that no unnecessary increments occur when data is reloaded. See also note_verbosity, which defines which notes should be given. The recommended way, as of MariaDB 10.6.16, to disable notes is to set note_verbosity to "".
  • Commandline: None
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

sql_quote_show_create

  • Description: If set to 1, the default, the server will quote identifiers for SHOW CREATE DATABASE, SHOW CREATE TABLE and SHOW CREATE VIEW statements. Quoting is disabled if set to 0. Enable to ensure replication works when identifiers require quoting.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

sql_safe_updates

  • Description: If set to 1, UPDATEs and DELETEs must be executed by using an index (simply mentioning an indexed column in a WHERE clause is not enough, optimizer must actually use it) or they must mention an indexed column and specify a LIMIT clause. Otherwise a statement will be aborted. Prevents the common mistake of accidentally deleting or updating every row in a table. Until MariaDB 10.3.11, could not be set as a command-line option or in my.cnf.
  • Commandline: --sql-safe-updates[={0|1}]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

sql_select_limit

  • Description: Maximum number of rows that can be returned from a SELECT query. Default is the maximum number of rows permitted per table by the server, usually 232-1 or 264-1. Can be restored to the default value after being changed by assigning it a value of DEFAULT.
  • Commandline: None
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 18446744073709551615

sql_warnings

  • Description: If set to 1, single-row INSERTs will produce a string containing warning information if a warning occurs.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF (0)

storage_engine


standard_compliant_cte

  • Description: Allow only standard-compliant common table expressions. Prior to MariaDB 10.2.4, this variable was named standards_compliant_cte.
  • Commandline: --standard-compliant-cte={0|1}
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON

stored_program_cache

  • Description: Limit to the number of stored routines held in the stored procedures and stored functions caches. Each time a stored routine is executed, this limit is first checked, and if the number held in the cache exceeds this, that cache is flushed and memory freed.
  • Commandline: --stored-program-cache=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 256
  • Range: 256 to 524288

strict_password_validation

  • Description: When password validation plugins are enabled, reject passwords that cannot be validated (passwords specified as a hash). This excludes direct updates to the privilege tables.
  • Commandline: --strict-password-validation
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON

sync_frm

  • Description: If set to 1, the default, each time a non-temporary table is created, its .frm definition file is synced to disk. Fractionally slower, but safer in case of a crash.
  • Commandline: --sync-frm
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: TRUE

system_time_zone

  • Description: The system time zone is determined when the server starts. The system time zone is usually read from the operating system's environment but can be overridden by setting the 'TZ' environment variable before starting the server. See Time Zones: System Time Zone for the various ways to change the system time zone. This variable is not the same as the time_zone system variable, which is the variable that actually controls a session's active time zone. The system time zone is used for a session when time_zone is set to the special value SYSTEM.
  • Scope: Global
  • Dynamic: No
  • Data Type: string

table_definition_cache

  • Description: Number of table definitions that can be cached. Table definitions are taken from the .frm files, and if there are a large number of tables increasing the cache size can speed up table opening. Unlike the table_open_cache, as the table_definition_cache doesn't use file descriptors, and is much smaller.
  • Commandline: --table-definition-cache=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 400
  • Range: 400 to 2097152

table_lock_wait_timeout

  • Description: Unused, and removed.
  • Commandline: --table-lock-wait-timeout=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 50
  • Range: 1 to 1073741824
  • Removed: MariaDB 5.5

table_open_cache

  • Description: Maximum number of open tables cached in one table cache instance. See Optimizing table_open_cache for suggestions on optimizing. Increasing table_open_cache increases the number of file descriptors required.
  • Commandline: --table-open-cache=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 2000
  • Range:
    • 1 to 1048576 (1024K)

table_open_cache_instances

  • Description: This system variable specifies the maximum number of table cache instances. MariaDB Server initially creates just a single instance. However, whenever it detects contention on the existing instances, it will automatically create a new instance. When the number of instances has been increased due to contention, it does not decrease again. The default value of this system variable is 8, which is expected to handle up to 100 CPU cores. If your system is larger than this, then you may benefit from increasing the value of this system variable.
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 8 (>= MariaDB 10.2.2)
  • Range: 1 to 64

table_type


tcp_keepalive_interval

  • Description: The interval, in seconds, between when successive keep-alive packets are sent if no acknowledgement is received. If set to 0, the system dependent default is used.
  • Commandline: --tcp-keepalive-interval=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 2147483

tcp_keepalive_probes

  • Description: The number of unacknowledged probes to send before considering the connection dead and notifying the application layer. If set to 0, a system dependent default is used.
  • Commandline: --tcp-keepalive-probes=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 2147483

tcp_keepalive_time

  • Description: Timeout, in seconds, with no activity until the first TCP keep-alive packet is sent. If set to 0, a system dependent default is used.
  • Commandline: --tcp-keepalive-time=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 2147483

tcp_nodelay

  • Description: Set the TCP_NODELAY option (disable Nagle's algorithm) on socket.
  • Commandline: --tcp-nodelay={0|1}
  • Scope: Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

thread_cache_size

  • Description: Number of threads server caches for re-use. If this limit hasn't been reached, when a client disconnects, its threads are put into the cache, and re-used where possible. In MariaDB 10.2.0 and newer the threads are freed after 5 minutes of idle time. Normally this setting has little effect, as the other aspects of the thread implementation are more important, but increasing it can help servers with high volumes of connections per second so that most can use a cached, rather than a new, thread. The cache miss rate can be calculated as the server status variables threads_created/connections. If the thread pool is active, thread_cache_size is ignored. If thread_cache_size is set to greater than the value of max_connections, thread_cache_size will be set to the max_connections value.
  • Commandline: --thread-cache-size=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 256 (adjusted if thread pool is active)
  • Range: 0 to 16384

thread_concurrency

  • Description: Allows applications to give the system a hint about the desired number of threads. Specific to Solaris only, invokes thr_setconcurrency(). Deprecated and has no effect from MariaDB 5.5.
  • Commandline: --thread-concurrency=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 10
  • Range: 1 to 512
  • Deprecated: MariaDB 5.5
  • Removed: MariaDB 10.5.1

thread_stack

  • Description: Stack size for each thread. If set too small, limits recursion depth of stored procedures and complexity of SQL statements the server can handle in memory. Also affects limits in the crash-me test.
  • Commandline: --thread-stack=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value:
    • 299008
  • Range: 131072 to 18446744073709551615

time_format


time_zone

  • Description: The global value determines the default time zone for sessions that connect. The session value determines the session's active time zone. When it is set to SYSTEM, the session's time zone is determined by the system_time_zone system variable.
  • Commandline: --default-time-zone=string
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: SYSTEM

timed_mutexes

  • Description: Determines whether InnoDB mutexes are timed. OFF, the default, disables mutex timing, while ON enables it. See also SHOW ENGINE for more on mutex statistics. Deprecated and has no effect.
  • Commandline: --timed-mutexes
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Deprecated: MariaDB 5.5.39
  • Removed: MariaDB 10.5.1

timestamp

  • Description: Sets the time for the client. This will affect the result returned by the NOW() function, not the SYSDATE() function, unless the server is started with the --sysdate-is-now option, in which case SYSDATE becomes an alias of NOW, and will also be affected. Also used to get the original timestamp when restoring rows from the binary log.
  • Scope: Session
  • Dynamic: Yes
  • Valid Values: timestamp_value (Unix epoch timestamp, not MariaDB timestamp), DEFAULT

tmp_disk_table_size

  • Description: Max size for data for an internal temporary on-disk MyISAM or Aria table. These tables are created as part of complex queries when the result doesn't fit into the memory engine. You can set this variable if you want to limit the size of temporary tables created in your temporary directory tmpdir.
  • Commandline: --tmp-disk-table-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 18446744073709551615 (max unsigned integer, no limit)
  • Range: 1024 to 18446744073709551615

tmp_memory_table_size

  • Description: An alias for tmp_table_size.
  • Commandline: --tmp-memory-table-size=#

tmp_table_size

  • Description: The largest size for temporary tables in memory (not MEMORY tables) although if max_heap_table_size is smaller the lower limit will apply. You can see if it's necessary to increase by comparing the status variables Created_tmp_disk_tables and Created_tmp_tables to see how many temporary tables out of the total created needed to be converted to disk. Often complex GROUP BY queries are responsible for exceeding the limit. Defaults may be different on some systems, see for example Differences in MariaDB in Debian. From MariaDB 10.2.7, tmp_memory_table_size is an alias.
  • Commandline: --tmp-table-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16777216 (16MB)
  • Range:

tmpdir

  • Description: Directory for storing temporary tables and files. Can specify a list (separated by semicolons in Windows, and colons in Unix) that will then be used in round-robin fashion. This can be used for load balancing across several disks. Note that if the server is a replication replica, and slave_load_tmpdir, which overrides tmpdir for replicas, is not set, you should not set tmpdir to a directory that is cleared when the machine restarts, or else replication may fail.
  • Commandline: --tmpdir=path or -t path
  • Scope: Global
  • Dynamic: No
  • Type: directory name/s
  • Default:
    • $TMPDIR (environment variable) if set
    • otherwise $TEMP if set and on Windows
    • otherwise $TMP if set and on Windows
    • otherwise P_tmpdir ("/tmp") or C:\TEMP (unless overridden during buid time)

transaction_alloc_block_size

  • Description: Size in bytes to increase the memory pool available to each transaction when the available pool is not large enough. See transaction_prealloc_size.
  • Commandline: --transaction-alloc-block-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: numeric
  • Default Value: 8192
  • Range: 1024 to 4294967295
  • Block Size: 1024

transaction_isolation

  • Description: The transaction isolation level. See also SET TRANSACTION ISOLATION LEVEL. Introduced in MariaDB 11.1.1 to replace the tx_isolation system variable and align the option and the system variable name.
  • Commandline: --transaction-isolation=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: enumeration
  • Default Value: REPEATABLE-READ
  • Valid Values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
  • Introduced: MariaDB 11.1.1

transaction_prealloc_size

  • Description: Initial size of a memory pool available to each transaction for various memory allocations. If the memory pool is not large enough for an allocation, it is increased by transaction_alloc_block_size bytes, and truncated back to transaction_prealloc_size bytes when the transaction is completed. If set large enough to contain all statements in a transaction, extra malloc() calls are avoided.
  • Commandline: --transaction-prealloc-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: numeric
  • Default Value: 4096
  • Range: 1024 to 4294967295
  • Block Size: 1024

transaction_read_only

  • Description: Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See SET TRANSACTION and START TRANSACTION.
  • Commandline: --transaction-read-only=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: boolean
  • Default Value: OFF
  • Introduced: MariaDB 11.1

tx_isolation

  • Description: The transaction isolation level. Setting this session variable via set @@tx_isolation= will take effect for only the subsequent transaction in the current session, much like SET TRANSACTION ISOLATION LEVEL. To set for a session, use SET SESSION tx_isolation or SET @@session.tx_isolation. See MDEV-31751. See also SET TRANSACTION ISOLATION LEVEL. In MariaDB 11.1, this system variable is deprecated and replaced by transaction_isolation.
  • Commandline: --transaction-isolation=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: enumeration
  • Default Value: REPEATABLE-READ
  • Valid Values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
  • Deprecated: MariaDB 11.1

tx_read_only

  • Description: Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See SET TRANSACTION and START TRANSACTION. In MariaDB 11.1, this system variable is deprecated and replaced by transaction_read_only.
  • Commandline: --transaction-read-only=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: boolean
  • Default Value: OFF
  • Deprecated: MariaDB 11.1

unique_checks

  • Description: If set to 0, storage engines can (but are not required to) assume that duplicate keys are not present in input data. If set to 0, inserting duplicates into a UNIQUE index can succeed, causing the table to become corrupted. Set to 0 to speed up imports of large tables to InnoDB.
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: boolean
  • Default Value: 1

updatable_views_with_limit

  • Description: Determines whether view updates can be made with an UPDATE or DELETE statement with a LIMIT clause if the view does not contain all primary or not null unique key columns from the underlying table. 0 prohibits this, while 1 permits it while issuing a warning (the default).
  • Commandline: --updatable-views-with-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: boolean
  • Default Value: 1

use_stat_tables

  • Description: Controls the use of engine-independent table statistics.
    • never: The optimizer will not use data from statistics tables.
    • complementary: The optimizer uses data from statistics tables if the same kind of data is not provided by the storage engine.
    • preferably: Prefer the data from statistics tables, if it's not available there, use the data from the storage engine.
    • complementary_for_queries: Same as complementary, but for queries only (to avoid needlessly collecting for ANALYZE TABLE).
    • preferably_for_queries: Same as preferably, but for queries only (to avoid needlessly collecting for ANALYZE TABLE).
  • Commandline: --use-stat-tables=mode
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enum
  • Default Value: preferably_for_queries

version

  • Description: Server version number. It may also include a suffix with configuration or build information. -debug indicates debugging support was enabled on the server, and -log indicates at least one of the binary log, general log or slow query log are enabled, for example 10.0.1-MariaDB-mariadb1precise-log. Can be set at startup in order to fake the server version.
  • Commandline: -V, --version[=name]
  • Scope: Global
  • Dynamic: No
  • Type: string

version_comment

  • Description: Value of the COMPILATION_COMMENT option specified by CMake when building MariaDB, for example mariadb.org binary distribution.
  • Scope: Global
  • Dynamic: No
  • Type: string

version_compile_machine

  • Description: The machine type or architecture MariaDB was built on, for example i686.
  • Scope: Global
  • Dynamic: No
  • Type: string

version_compile_os

  • Description: Operating system that MariaDB was built on, for example debian-linux-gnu.
  • Scope: Global
  • Dynamic: No
  • Type: string

version_malloc_library

  • Description: Version of the used malloc library.
  • Commandline: No
  • Scope: Global
  • Dynamic: No
  • Type: string

version_source_revision

  • Description: Source control revision id for MariaDB source code, enabling one to see exactly which version of the source was used for a build.
  • Commandline: None
  • Scope: Global
  • Dynamic: No
  • Type: string

wait_timeout

  • Description: Time in seconds that the server waits for a connection to become active before closing it. The session value is initialized when a thread starts up from either the global value, if the connection is non-interactive, or from the interactive_timeout value, if the connection is interactive.
  • Commandline: --wait-timeout=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: numeric
  • Default Value: 28800
  • Range: (Windows): 1 to 2147483
  • Range: (Other): 1 to 31536000

warning_count

  • Description: Read-only variable indicating the number of warnings, errors and notes resulting from the most recent statement that generated messages. See SHOW WARNINGS for more. Note warnings will only be recorded if sql_notes is true (the default).
  • Scope: Session
  • Dynamic: No
  • Type: numeric

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.