Oracle Cloud Infrastructure Documentation

Configuration Variables

Configurations have a default set of user, system, or initialization variables. You can edit the user and initialization variables, but not the system variables.

  • User Variables: You can edit the user variables when you create or copy a configuration. Some of the user variables are default user variables. You cannot remove these default user variables from the configuration. See User Variables and Default User Variables.
  • System Variables: Oracle defines the system variables according to the shape or requirements of the MySQL instance. You cannot edit the system variables. See System Variables.
  • Initialization Variables: These variables apply for the life span of the MySQL instance of the DB system. While you can edit configurations, and can update the DB systems with new configurations, you cannot change the initialization variables once you apply them. See Initialization Variables.
Note

Once you create a configuration, you cannot edit the variables. To add variables, you must create a new configuration with the desired variable definitions, or copy an existing configuration, edit it accordingly, and edit the DB system to use the new configuration.

User Variables

User variables are those variables that you can edit when you create or copy a configuration. Some of the user variables are default user variables. You cannot remove these default user variables from your configuration.

Note

The links for the variables lead to their descriptions in the on-premises MySQL Reference Manual; some of the default values shown for MySQL HeatWave Servicemay be different from those shown in the on-premises MySQL Reference Manual.

Table 10-1 Default and Valid Values of User Variables

User Variable Default Value on MySQL HeatWave Service Valid Values on MySQL HeatWave Service
activate_all_roles_on_login OFF Boolean: OFF, ON
audit_log_buffer_size 104,815,760 Integer: 4,096 - 18,446,744,073,709,547,520
audit_log_disable OFF Boolean: OFF, ON
audit_log_max_size See Default User Variables.  
audit_log_prune_seconds 0 Integer: 0 - 18,446,744,073,709,551,615
audit_log_rotate_on_size See Default User Variables.  
audit_log_strategy See Default User Variables.  
authentication_policy *,, String
auto_increment_offset 1 Integer: 1 - 65,535
autocommit ON Boolean: OFF, ON
auto-increment-increment 1 Integer: 1 - 65,535
automatic_sp_privileges ON Boolean: OFF, ON
big_tables OFF Boolean: OFF, ON
binlog_cache_size 32768 Integer: 4,096 - 18,446,744,073,709,547,520
binlog_expire_logs_seconds See Default User Variables.  
binlog_group_commit_sync_delay 0 Integer: 0 - 1,000,000
binlog_group_commit_sync_no_delay_count 0 Integer: 0 - 100,000
binlog_row_image FULL Enumeration: FULL, MINIMAL, NOBLOB
binlog_row_metadata MINIMAL Enumeration: FULL, MINIMAL
binlog_row_value_options See Default User Variables.  
binlog_transaction_compression OFF Boolean: OFF, ON
binlog_transaction_dependency_history_size See Default User Variables.  
block_encryption_mode aes-128-ecb Enumeration: aes-192-CFB8, aes-256-CFB8, aes-128-CFB8, aes-256-ECB, aes-256-CBC, aes-192-CFB1, aes-128-CFB1, aes-192-CFB128, aes-128-ECB, aes-128-CBC, aes-256-CFB128, aes-128-OFB, aes-192-OFB, aes-256-OFB, aes-256-CFB1, aes-128-CFB128, aes-192-ECB, aes-192-CBC
character_set_client utf8mb4 String: Any valid character set supported by the MySQL version
character_set_connection utf8mb4 String: Any valid character set supported by the MySQL version
character_set_results utf8mb4 String: Any valid character set supported by the MySQL version
character_set_server UTF8MB4 String: Any valid character set supported by the MySQL version
collation_connection utf8mb4_0900_ai_ci String: Any valid collation supported by the MySQL version
collation_server UTF8MB4_0900_AI_CI String: Any valid collation supported by the MySQL version
completion_type NO_CHAIN Enumeration: NO_CHAIN, CHAIN, RELEASE
connect_timeout 10 Integer: 2 - 31,536,000
connection_memory_chunk_size 8192 Integer: 0 - 536,870,912
connection_memory_limit There is no default value. MySQL default: 18446744073709551615 Integer: 2,097,152 - 9,223,372,036,854,775,807
connection_memory_status_limit 18,446,744,073,709,551,615 Integer: 16,777,216 - 18,446,744,073,709,551,615
cte_max_recursion_depth 1000 Integer: 0 - 4,294,967,295
default_authentication_plugin caching_sha2_password
Note

This variable is ignored in MySQL 8.4.0 or higher.
Enumeration: mysql_native_password, sha256_password, caching_sha2_password
default_password_lifetime 0 Integer: 0 - 65,535
default_week_format 0 Integer: 0 - 7
disconnect_on_expired_password ON Boolean: OFF, ON
div_precision_increment 4 Integer: 0 - 30
end_markers_in_json OFF Boolean: OFF, ON
eq_range_index_dive_limit 200 Integer: 0 - 4,294,967,295
explain_format TRADITIONAL Enumeration: TRADITIONAL, JSON, TREE
explain_json_format_version 1 Integer: 1 - 2
explicit_defaults_for_timestamp ON Boolean: OFF, ON
flush OFF Boolean: OFF, ON
flush_time 0 Integer: 0 - 31,536,000
foreign_key_checks ON Boolean: OFF, ON
general_log OFF Boolean: OFF, ON
global_connection_memory_limit There is no default value. MySQL default: 18446744073709551615 Integer: 16,777,216 - 9,223,372,036,854,775,807
global_connection_memory_status_limit 18,446,744,073,709,551,615 Integer: 16,777,216 - 18,446,744,073,709,551,615
global_connection_memory_tracking OFF Boolean: OFF, ON
group_concat_max_len 1024 Integer: 4 - 9,223,372,036,854,775,807
group_replication_consistency See Default User Variables.  
histogram_generation_max_mem_size 20,000,000 Integer: 1,000,000 - 18,446,744,073,709,551,615
host_cache_size AUTOSIZED Integer: 0 - 65,536
information_schema_stats_expiry 86400 Integer: 0 - 31,536,000
init_connect EMPTY STRING String
innodb_adaptive_flushing ON Boolean: OFF, ON
innodb_adaptive_flushing_lwm 10 Integer: 0 - 70
innodb_adaptive_hash_index See Default User Variables.  
innodb_adaptive_hash_index_parts 8 Integer: 1 - 512
innodb_adaptive_max_sleep_delay 150,000 Integer: 0 - 1,000,000
innodb_autoextend_increment 64 Integer: 1 - 1,000
innodb_autoinc_lock_mode 2 Integer: 0 - 2
innodb_buffer_pool_chunk_size 134,217,728 Integer: 1,048,576 - 18,446,744,073,709,551,615
innodb_buffer_pool_dump_at_shutdown ON Boolean: OFF, ON
innodb_buffer_pool_dump_now OFF Boolean: OFF, ON
innodb_buffer_pool_dump_pct 25 Integer: 1 - 100
innodb_buffer_pool_instances See Default User Variables.  
innodb_buffer_pool_load_abort OFF Boolean: OFF, ON
innodb_buffer_pool_load_at_startup ON Boolean: OFF, ON
innodb_buffer_pool_load_now OFF Boolean: OFF, ON
innodb_buffer_pool_size See Default User Variables.  
innodb_change_buffering ALL Enumeration: NONE, INSERTS, DELETES, CHANGES, PURGES, ALL
innodb_cmp_per_index_enabled OFF Boolean: OFF, ON
innodb_concurrency_tickets 5000 Integer: 1 - 4,294,967,295
innodb_ddl_buffer_size 1048576 Integer: 65,536 - 4,294,967,295
innodb_ddl_threads 4 Integer: 1 - 64
innodb_default_row_format dynamic Enumeration: dynamic, compact, redundant
innodb_disable_sort_file_cache OFF Boolean: OFF, ON
innodb_doublewrite_files See Default User Variables.  
innodb_doublewrite_pages See Default User Variables.  
innodb_extend_and_initialize See Default User Variables.  
innodb_fill_factor 100 Integer: 10 - 100
innodb_flush_log_at_timeout 1 Integer: 1 - 2,700
innodb_flush_neighbors 0 Integer: 0 - 2
innodb_flush_sync ON Boolean: OFF, ON
innodb_flushing_avg_loops 30 Integer: 1 - 1,000
innodb_force_load_corrupted OFF Boolean: OFF, ON
innodb_ft_aux_table EMPTY STRING String
innodb_ft_cache_size 8,000,000 Integer: 1,600,000 - 80,000,000
innodb_ft_enable_diag_print OFF Boolean: OFF, ON
innodb_ft_enable_stopword ON Boolean: OFF, ON
innodb_ft_max_token_size 84 Integer: 10 - 84
innodb_ft_min_token_size 3 Integer: 0 - 16
innodb_ft_num_word_optimize 2000 Integer: 1,000 - 10,000
innodb_ft_result_cache_limit See Default User Variables.  
innodb_ft_server_stopword_table NULL String: <db_name>/<table_name>
innodb_ft_sort_pll_degree 2 Integer: 1 - 16
innodb_ft_total_cache_size 640,000,000 Integer: 32,000,000 - 1,600,000,000
innodb_ft_user_stopword_table NULL String: <db_name>/<table_name>
innodb_lock_wait_timeout 50 Integer: 1 - 1,073,741,824
innodb_log_buffer_size See Default User Variables.  
innodb_log_spin_cpu_abs_lwm 80 Integer: 0 - 4,294,967,295
innodb_log_spin_cpu_pct_hwm 50 Integer: 0 - 100
innodb_log_wait_for_flush_spin_hwm 400 Integer: 0 - 18,446,744,073,709,551,615
innodb_log_writer_threads ON Boolean: OFF, ON
innodb_lru_scan_depth 1024 Integer: 100 - 18,446,744,073,709,551,615
innodb_max_dirty_pages_pct 90.000000 Numeric: 0 - 99.999
innodb_max_dirty_pages_pct_lwm 10.000000 Numeric: 0 - 99.999
innodb_max_purge_lag 0 Integer: 0 - 4,294,967,295
innodb_max_purge_lag_delay See Default User Variables.  
innodb_max_undo_log_size 1,073,741,824 Integer: 10,485,760 - 18,446,744,073,709,551,615
innodb_numa_interleave See Default User Variables.  
innodb_old_blocks_pct 37 Integer: 5 - 95
innodb_old_blocks_time 1000 Integer: 0 - 4,294,967,295
innodb_online_alter_log_max_size 134217728 Integer: 65,536 - 9,223,372,036,854,775,807
innodb_optimize_fulltext_only OFF Boolean: OFF, ON
innodb_page_cleaners See Default User Variables. Integer: 1 - 64
innodb_parallel_read_threads 4 Integer: 1 - 256
innodb_print_all_deadlocks OFF Boolean: OFF, ON
innodb_print_ddl_logs OFF Boolean: OFF, ON
innodb_read_ahead_threshold 56 Integer: 0 - 64
innodb_read_io_threads See Default User Variables.  
innodb_redo_log_capacity 104857600
Note

This variable is ignored in versions lower than 9.3.0
Integer: 8,388,608 - 137,438,953,472
innodb_replication_delay 0 Integer: 0 - 4,294,967,295
innodb_rollback_on_timeout OFF Boolean: OFF, ON
innodb_sort_buffer_size 1048576 Integer: 65,536 - 67,108,864
innodb_stats_auto_recalc ON Boolean: OFF, ON
innodb_stats_include_delete_marked OFF Boolean: OFF, ON
innodb_stats_method nulls_equal Enumeration: nulls_equal, nulls_unequal, nulls_ignored
innodb_stats_on_metadata OFF Boolean: OFF, ON
innodb_stats_persistent_sample_pages 20 Integer: 1 - 9,223,372,036,854,775,807
innodb_stats_transient_sample_pages 8 Integer: 1 - 9,223,372,036,854,775,807
innodb_strict_mode ON Boolean: OFF, ON
innodb_sync_array_size 1 Integer: 1 - 1,024
innodb_thread_concurrency 0 Integer: 0 - 1,000
innodb_thread_sleep_delay 10,000 Integer: 0 - 1,000,000
innodb_undo_log_truncate ON Boolean: OFF, ON
innodb_write_io_threads 4 Integer: 1 - 64
interactive_timeout 28800 Integer: 1 - 31,536,000
join_buffer_size 262144 Integer: 128 - 9,223,372,036,854,775,807
lc_time_names en_US  String: Any valid locale name
local_infile See Default User Variables.  
lock_wait_timeout See Default User Variables.  
log_queries_not_using_indexes OFF Boolean: OFF, ON
log_slow_admin_statements OFF Boolean: OFF, ON
log_throttle_queries_not_using_indexes 0 Integer: 0 - 4,294,967,295
long_query_time 10 Integer: 0 - 31,536,000
mandatory_roles See Default User Variables.  
max_allowed_packet 67108864 Integer: 67,108,864 - 1,073,741,824
max_binlog_cache_size See Default User Variables.  
max_connect_errors 18,446,744,073,709,551,615 Integer: 1 - 18,446,744,073,709,551,615
max_connections See Default User Variables.  
max_digest_length 1024 Integer: 0 - 1,048,576
max_error_count 1024 Integer: 0 - 65,535
max_execution_time 0 Integer: 0 - 9,223,372,036,854,775,807
max_heap_table_size 16777216 Integer: 16,384 - 9,223,372,036,854,775,807
max_join_size 18,446,744,073,709,551,615 Integer: 1 - 18,446,744,073,709,551,615
max_points_in_geometry 65536 Integer: 3 - 1,048,576
max_prepared_stmt_count 16382 Integer: The maximum value is dependent on the shape or the amount of RAM provided by the shape.
  • MySQL.HeatWave.VM.Standard: 0 - 80,000
  • MySQL.HeatWave.BM.Standard: 0 - 80,000
  • 8GB shapes: 0 - 16,382
  • 15GB and 16GB shapes: 0 - 20,000
  • 30GB and 32GB shapes: 0 - 40,000
  • 60GB and 64GB shapes: 0 - 80,000
  • Shapes with 120GB or more: 0 - 160,000
max_seeks_for_key 1 Integer: 1 - 9,223,372,036,854,775,807
max_sort_length 1024 Integer: 4 - 8,388,608
max_sp_recursion_depth 0 Integer: 0 - 255
max_user_connections 0 Integer: 0 - 4,294,967,295
max_write_lock_count 18,446,744,073,709,551,615 Integer: 1 - 18,446,744,073,709,551,615
min_examined_row_limit 0 Integer: 0 - 18,446,744,073,709,551,615
mysql_firewall_mode ON Boolean: OFF, ON
mysql-native-password OFF
Note

This variable is ignored in MySQL 9.0.0 or higher.
Boolean: OFF, ON
mysqlx_connect_timeout 30 Integer: 1 - 1,000,000,000
mysqlx_deflate_default_compression_level 3 Integer: 1 - 9
mysqlx_deflate_max_client_compression_level 5 Integer: 1 - 9
mysqlx_interactive_timeout 28800 Integer: 1 - 2,147,483
mysqlx_lz4_default_compression_level 2 Integer: 0 - 16
mysqlx_lz4_max_client_compression_level 8 Integer: 0 - 16
mysqlx_max_allowed_packet 67108864 Integer: 67,108,864 - 1,073,741,824
mysqlx_read_timeout 30 Integer: 30 - 2,147,483
mysqlx_wait_timeout 28800 Integer: 1 - 2,147,483
mysqlx_write_timeout 60 Integer: 1 - 2,147,483
mysqlx_zstd_default_compression_level 3 Integer: 1 - 18
mysqlx_zstd_max_client_compression_level 11 Integer: 1 - 18
net_buffer_length 16,384 Integer: 1,024 - 1,048,576
net_read_timeout 30 Integer: 1 - 31,536,000
net_retry_count 10 Integer: 1 - 18,446,744,073,709,551,615
net_write_timeout 60 Integer: 1 - 31,536,000
optimizer_prune_level 1 Integer: 0 - 1
optimizer_search_depth 62 Integer: 0 - 62
optimizer_switch Set:
  • batched_key_access=off
  • block_nested_loop=on
  • condition_fanout_filter=on
  • derived_merge=on
  • duplicateweedout=on
  • engine_condition_pushdown=on
  • firstmatch=on
  • index_condition_pushdown=on
  • index_merge=on
  • index_merge_intersection=on
  • index_merge_sort_union=on
  • index_merge_union=on
  • loosescan=on
  • materialization=on
  • mrr=on
  • mrr_cost_based=on
  • semijoin=on
  • skip_scan=on
  • subquery_materialization_cost_based=on
  • use_index_extensions=on
  • use_invisible_indexes=off
 Set:
  • batched_key_access={on|off}
  • block_nested_loop={on|off}
  • condition_fanout_filter={on|off}
  • derived_merge={on|off}
  • duplicateweedout={on|off}
  • engine_condition_pushdown={on|off}
  • firstmatch={on|off}
  • index_condition_pushdown={on|off}
  • index_merge={on|off}
  • index_merge_intersection={on|off}
  • index_merge_sort_union={on|off}
  • index_merge_union={on|off}
  • loosescan={on|off}
  • materialization={on|off}
  • mrr={on|off}
  • mrr_cost_based={on|off}
  • semijoin={on|off}
  • skip_scan={on|off}
  • subquery_materialization_cost_based={on|off}
  • use_index_extensions={on|off}
  • use_invisible_indexes={on|off}
optimizer_trace Set:
  • enabled=off
  • one_line=off
Set:
  • enabled={on|off}
  • one_line={on|off}
optimizer_trace_features Set:
  • greedy_search=on
  • range_optimizer=on
  • dynamic_range=on
  • repeated_subselect=on
Set:
  • greedy_search={on|off}
  • range_optimizer={on|off}
  • dynamic_range={on|off}
  • repeated_subselect={on|off}
optimizer_trace_limit 1 Integer: 0 - 2,147,483,647
optimizer_trace_max_mem_size 1,048,576 Integer: 0 - 4,294,967,295
optimizer_trace_offset -1 Integer: -2,147,483,647 - 2,147,483,647
parser_max_mem_size There is no default value. MySQL default: 18446744073709551615 Integer: 10,000,000 - 9,223,372,036,854,775,807
password_history 0 Integer: 0 - 4,294,967,295
password_reuse_interval 0 Integer: 0 - 4,294,967,295
performance_schema_accounts_size AUTOSIZED Integer: 0 - 1,048,576
performance_schema_digests_size AUTOSIZED Integer: 0 - 1,048,576
performance_schema_error_size Actual number of server error codes Integer: 0 - 1,048,576
performance_schema_events_stages_history_long_size AUTOSIZED Integer: 0 - 1,048,576
performance_schema_events_stages_history_size AUTOSIZED Integer: 0 - 1,024
performance_schema_events_statements_history_long_size AUTOSIZED Integer: 0 - 1,048,576
performance_schema_events_statements_history_size AUTOSIZED Integer: 0 - 1,024
performance_schema_events_transactions_history_long_size AUTOSIZED Integer: 0 - 1,048,576
performance_schema_events_transactions_history_size AUTOSIZED Integer: 0 - 1,024
performance_schema_events_waits_history_long_size AUTOSIZED Integer: 0 - 1,048,576
performance_schema_events_waits_history_size AUTOSIZED Integer: 0 - 1,024
performance_schema_hosts_size AUTOSIZED Integer: 0 - 1,048,576
performance_schema_max_cond_classes 150 Integer: 0 - 1,024
performance_schema_max_cond_instances AUTOSIZED Integer: 0 - 1,048,576
performance_schema_max_digest_length 1024 Integer: 0 - 1,048,576
performance_schema_max_digest_sample_age 60 Integer: 0 - 1,048,576
performance_schema_max_file_classes 80 Integer: 0 - 1,024
performance_schema_max_file_handles 32,768 Integer: 0 - 1,048,576
performance_schema_max_file_instances AUTOSIZED Integer: 0 - 1,048,576
performance_schema_max_index_stat AUTOSIZED Integer: 0 - 1,048,576
performance_schema_max_memory_classes 450 Integer: 0 - 1,024
performance_schema_max_metadata_locks AUTOSIZED Integer: 0 - 1,048,576
performance_schema_max_mutex_classes 350 Integer: 0 - 1,024
performance_schema_max_mutex_instances AUTOSIZED Integer: 0 - 1,048,576
performance_schema_max_prepared_statements_instances AUTOSIZED Integer: 0 - 4,194,304
performance_schema_max_program_instances AUTOSIZED Integer: 0 - 1,048,576
performance_schema_max_rwlock_classes 100 Integer: 0 - 1,024
performance_schema_max_rwlock_instances AUTOSIZED Integer: 0 - 104,857,600
performance_schema_max_socket_classes 10 Integer: 0 - 1,024
performance_schema_max_socket_instances AUTOSIZED Integer: 0 - 1,048,576
performance_schema_max_sql_text_length 1,024 Integer: 0 - 1,048,576
performance_schema_max_stage_classes 175 Integer: 0 - 1,024
performance_schema_max_statement_classes AUTOSIZED Integer: 0 - 256
performance_schema_max_statement_stack See Default User Variables.  
performance_schema_max_table_handles AUTOSIZED Integer: 0 - 1,048,576
performance_schema_max_table_instances AUTOSIZED Integer: 0 - 1,048,576
performance_schema_max_table_lock_stat AUTOSIZED Integer: 0 - 1,048,576
performance_schema_max_thread_classes 100 Integer: 0 - 1,024
performance_schema_max_thread_instances AUTOSIZED Integer: 0 - 1,048,576
performance_schema_session_connect_attrs_size AUTOSIZED Integer: 0 - 1,048,576
performance_schema_setup_actors_size AUTOSIZED Integer: 0 - 1,048,576
performance_schema_setup_objects_size AUTOSIZED Integer: 0 - 1,048,576
performance_schema_users_size AUTOSIZED Integer: 0 - 1,048,576
query_alloc_block_size 8,192 Integer: 1,024 - 18,446,744,073,709,551,615
range_alloc_block_size 4,096 Integer: 4,096 - 18,446,744,073,709,551,615
range_optimizer_max_mem_size 8388608 Integer: 0 - 9,223,372,036,854,775,807
read_rnd_buffer_size 262,144 Integer: 1 - 2,147,483,647
regexp_stack_limit 8,000,000 Integer: 0 - 2,147,483,647
regexp_time_limit 32 Integer: 0 - 2,147,483,647
relay_log_space_limit See Default User Variables.  
replica_net_timeout 60 Integer: 1 - 31,536,000
replica_parallel_workers See Default User Variables.  
replica_pending_jobs_size_max 134,217,728 Integer: 1,024 - 18,446,744,073,709,551,615
replica_type_conversions NULL Set: NULL, ALL_LOSSY, ALL_NON_LOSSY, ALL_SIGNED, ALL_UNSIGNED
require_secure_transport OFF Boolean: OFF, ON
restrict_fk_on_non_standard_key ON Boolean: OFF, ON
rpl_read_size 8,192 Integer: 8,192 - 4,294,959,104
session_track_gtids OFF Boolean: OFF, ON
session_track_schema ON Boolean: OFF, ON
session_track_state_change OFF Boolean: OFF, ON
session_track_system_variables time_zone,autocommit,character_set_client,character_set_results,character_set_connection Set: time_zone,autocommit,character_set_client,character_set_results,character_set_connection,*
session_track_transaction_info OFF Boolean: OFF, ON
set_operations_buffer_size 262,144 Integer: 16,384 - 1,073,741,824
skip_name_resolve OFF Boolean: OFF, ON
skip_show_database OFF Boolean: OFF, ON
slow_launch_time 2 Integer: 0 - 31,536,000
slow_query_log OFF Boolean: OFF, ON
sort_buffer_size 262144 Integer: 32,768 - 9,223,372,036,854,775,807
sql_auto_is_null OFF Boolean: OFF, ON
sql_big_selects ON Boolean: OFF, ON
sql_buffer_result OFF Boolean: OFF, ON
sql_generate_invisible_primary_key OFF
Note

This variable is ignored in versions lower than 8.0.30
Boolean: OFF, ON
sql_mode ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES Set: ALLOW_INVALID_DATES, ANSI_QUOTES, ERROR_FOR_DIVISION_BY_ZERO, HIGH_NOT_PRECEDENCE, IGNORE_SPACE, NO_AUTO_VALUE_ON_ZERO, NO_BACKSLASH_ESCAPES, NO_DIR_IN_CREATE, NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY, PAD_CHAR_TO_FULL_LENGTH, PIPES_AS_CONCAT, REAL_AS_FLOAT, STRICT_ALL_TABLES, STRICT_TRANS_TABLES, TIME_TRUNCATE_FRACTIONAL
sql_require_primary_key See Default User Variables.  
sql_safe_updates OFF Boolean: OFF, ON
sql_select_limit 18,446,744,073,709,551,615 Integer: 0 - 18,446,744,073,709,551,615
sql_warnings OFF Boolean: OFF, ON
ssl_cipher NULL String: The list of permissible encryption ciphers for connections that use TLSv1.2 might include:
  • ECDHE-ECDSA-AES128-GCM-SHA256
  • ECDHE-ECDSA-AES256-GCM-SHA384
  • ECDHE-RSA-AES128-GCM-SHA256
  • ECDHE-RSA-AES256-GCM-SHA384
  • ECDHE-ECDSA-CHACHA20-POLY1305
  • ECDHE-RSA-CHACHA20-POLY1305
  • ECDHE-ECDSA-AES256-CCM
  • ECDHE-ECDSA-AES128-CCM
  • DHE-RSA-AES128-GCM-SHA256
  • DHE-RSA-AES256-GCM-SHA384
  • DHE-RSA-AES256-CCM
  • DHE-RSA-AES128-CCM
  • DHE-RSA-CHACHA20-POLY1305
ssl_session_cache_mode ON Boolean: OFF, ON
ssl_session_cache_timeout 300 Integer: 0 - 86,400
stored_program_cache 256 Integer: 16 - 524,288
stored_program_definition_cache 256 Integer: 256 - 524,288
sync_binlog 1 Integer: 0 - 4,294,967,295
table_definition_cache AUTOSIZED Integer: 400 - 524,288
table_open_cache 4000 Integer: 1 - 524,288
table_open_cache_instances See Default User Variables.  
table_open_cache_triggers 524,288 Integer: 1 - 524,288
tablespace_definition_cache 256 Integer: 256 - 524,288
temptable_max_ram See Default User Variables.  
thread_pool_algorithm 0 Integer: 0 - 1
thread_pool_dedicated_listeners OFF Boolean: OFF, ON
thread_pool_high_priority_connection 0 Integer: 0 - 1
thread_pool_longrun_trx_limit 2000 Integer: 10 - 86,400,000
thread_pool_max_transactions_limit 0 Integer: 0 - 100,000
thread_pool_max_unused_threads 0 Integer: 0 - 4,096
thread_pool_prio_kickup_timer 1000 Integer: 0 - 4,294,967,294
thread_pool_query_threads_per_group 1
Note

This variable is ignored in versions lower than 8.0.31
Integer: 1 - 4,096
thread_pool_size See Default User Variables.  
thread_pool_stall_limit 6 Integer: 4 - 600
thread_pool_transaction_delay 0
Note

This variable is ignored in versions lower than 8.0.31
Integer: 0 - 300,000
time_zone See Default User Variables.  
tls_ciphersuites NULL String: The value is a list of zero or more colon-separated ciphersuite names which can include the following:
  • TLS_AES_128_GCM_SHA256
  • TLS_AES_256_GCM_SHA384
  • TLS_CHACHA20_POLY1305_SHA256
  • TLS_AES_128_CCM_SHA256
tls_version TLSv1.2,TLSv1.3 String: A list of one or more comma-separated protocol names which the server permits for encrypted connections.
tmp_table_size 16777216 Integer: 1,024 - 9,223,372,036,854,775,807
transaction_alloc_block_size 8,192 Integer: 1,024 - 131,072
transaction_isolation REPEATABLE-READ Enumeration: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_prealloc_size 4,096 Integer: 1,024 - 131,072
unique_checks ON Boolean: OFF, ON
validate_password.changed_characters_percentage 0 Integer: 0 - 100
validate_password.check_user_name ON Boolean: OFF, ON
validate_password.length 8 Integer: 0 - 8
validate_password.mixed_case_count 1 Integer: 0 - 1
validate_password.number_count 1 Integer: 0 - 1
validate_password.policy MEDIUM Enumeration: MEDIUM, LOW
validate_password.special_char_count 1 Integer: 0 - 1
wait_timeout 28800 Integer: 1 - 31,536,000
windowing_use_high_precision ON Boolean: OFF, ON

Default User Variables

Default user variables are those user variables whose values are editable, but you cannot delete the variables from your configuration. The default user variable are associated with all configurations.

Table 10-2 Default and Valid Values of Default User Variables

Default User Variable Default Value Valid Values
audit_log_max_size 0 Integer: 0 - 18,446,744,073,709,551,615
audit_log_rotate_on_size 0 Integer: 0 - 18,446,744,073,709,551,615
audit_log_strategy ASYNCHRONOUS Enumeration: ASYNCHRONOUS, PERFORMANCE, SEMISYNCHRONOUS, SYNCHRONOUS
binlog_expire_logs_seconds 3600 Integer: 0 - 4,294,967,295
binlog_row_value_options PARTIAL_JSON Set: NULL, PARTIAL_JSON
binlog_transaction_dependency_history_size 25000 Integer: 1 - 1,000,000
group_replication_consistency BEFORE_ON_PRIMARY_FAILOVER Enumeration: EVENTUAL, BEFORE_ON_PRIMARY_FAILOVER, BEFORE, AFTER, BEFORE_AND_AFTER
innodb_adaptive_hash_index OFF Boolean: OFF, ON
innodb_buffer_pool_instances Dependent on the amount of RAM provided by the shape:
  • For shapes with 8-128GB: 4
  • 256GB shapes: 8
  • 384GB shapes: 12
  • 512GB shapes: 16
  • 768GB shapes: 24
  • 1024GB shapes: 32
 Integer: 1 - 64
innodb_buffer_pool_size

Dependent on the amount of RAM provided by the shape.

For these HeatWave cluster shapes:
  • MySQL.HeatWave.VM.Standard: 48GB
  • MySQL.HeatWave.BM.Standard: 192GB
For standalone shapes:
  • 8GB shapes: 2GB
  • 15GB and 16GB shapes: 10GB
  • 30GB and 32GB shapes: 20GB
  • 60GB and 64GB shapes: 48GB
  • 120GB and 128GB shapes: 96GB
  • 240GB and 256GB shapes: 192GB
  • 384GB shapes: 288GB
  • 512GB shapes: 384GB
  • 768GB shapes: 576GB
  • 1024GB shapes: 768GB
For high availability shapes:
  • 8GB shapes: 1.5GB
  • 16GB shapes: 8.5GB
  • 32GB shapes: 17GB
  • 64GB shapes:43GB
  • 128GB shapes: 89GB
  • 256GB shapes: 185GB
  • 384GB shapes: 282GB
  • 512GB shapes: 378GB
  • 768GB shapes: 570GB
  • 1024GB shapes: 752GB
 Integer: The maximum value is dependent on the shape or the amount of RAM provided by the shape.
  • MySQL.HeatWave.VM.Standard: 5MB - 48GB
  • MySQL.HeatWave.BM.Standard: 5MB - 230GB
  • 8GB shapes: 5MB - 2GB
  • 15GB and 16GB shapes: 5MB - 10GB
  • 30GB and 32GB shapes: 5MB - 20GB
  • 60GB and 64GB shapes: 5MB - 57GB
  • 120GB and 128GB shapes: 5MB - 115GB
  • 240GB and 256GB shapes: 5MB - 230GB
  • 384GB shapes: 5MB - 345GB
  • 512GB shapes: 5MB - 460GB
  • 768GB shapes: 5MB - 691GB
  • 1024GB shapes: 5MB - 921GB
innodb_doublewrite_files 2 Integer: 1 - 256
innodb_doublewrite_pages 128 Integer: 1 - 512
innodb_extend_and_initialize OFF Boolean: OFF, ON
innodb_ft_result_cache_limit 33554432 Integer: 1,000,000 - 4,294,967,295
innodb_log_buffer_size 67,108,864 Integer: 1,048,576 - 4,294,967,295
innodb_max_purge_lag_delay 300000 Integer: 1,000 - 1,000,000
innodb_numa_interleave ON Boolean: OFF, ON
innodb_page_cleaners Dependent on the amount of RAM provided by the shape:
  • For shapes with 8-128GB: 4
  • 256GB shapes: 8
  • 384GB shapes: 12
  • 512GB shapes: 16
  • 768GB shapes: 24
  • 1024GB shapes: 32
 Integer: 1 - 64
innodb_read_io_threads Dependent on the number of CPUs of the shape:
  • 1 OCPU or 2 ECPU: 1
  • 2 OCPU or 4 ECPU: 2
  • 4 or more OCPU or 8 or more ECPU: 4
 Integer: 1 - 64
local_infile ON Boolean: OFF, ON
lock_wait_timeout 86,400 Integer: 1 - 31,536,000
mandatory_roles public String: A comma-separated list of role names
max_binlog_cache_size 4294967296 Integer: 4,096 - 65,970,697,666,560
max_connections Dependent on the amount of RAM provided by the shape:
  • 8GB shapes:500
  • 16GB shapes: 1000
  • 32GB shapes: 2000
  • 64GB shapes: 4000
  • all shapes larger than 64GB: 8000
 Integer: 151 - 100,000
performance_schema_max_statement_stack 1 Integer: 1 - 256
relay_log_space_limit 8589934592 Integer: 0 - 9,223,372,036,854,776,000
replica_parallel_workers Dependent to the number of CPUs of the shape:
  • 1 OCPU or 2 ECPU: 12
  • 2 OCPU or 4 ECPU: 24
  • 4 OCPU or 8 ECPU: 48
  • 8 OCPU or 16 ECPU: 96
  • 16 OCPU or 32 ECPU: 192
  • 24 or more OCPU or 48 or more ECPU: 256
 Integer: 1 - 1,024
sql_require_primary_key (HA shapes only) ON Boolean: OFF, ON
table_open_cache_instances Dependent on the number of CPUs of the shape:
  • number of OCPU <= 24 or number of ECPU <= 48: 16
  • else: 32
 Integer: 1 - 64
temptable_max_ram 1073741824 Integer: 2,097,152 - 9,223,372,036,854,776,000
thread_pool_size 8 Integer: 1 - 512
time_zone Sets the global timezone. The default value is UTC. String: Any valid timezone name

System Variables

Oracle defines the system variables according to the shape or requirements of the MySQL instance. You cannot edit the system variables.

Table 10-3 Default Values of System Variables

System Variable Default Value
generated_random_password_length 20
mysqlx_document_id_unique_prefix 0
mysqlx_enable_hello_notice ON
mysqlx_idle_worker_thread_timeout 60
mysqlx_min_worker_threads 2
query_alloc_block_size 8192
query_prealloc_size 8192

Initialization Variables

Initialization variables apply for the life span of the DB system and, once you apply it, you cannot change it later.

Table 10-4 Default Values of Initialization Variable

Initialization Variable Default Value
lower_case_table_names 0

If you set it to 1, the table and schema names are stored in lowercase on disk and comparisons are not case-sensitive.

You cannot change the value of lower_case_table_names during the lifespan of a DB system. If you attempt to change the value in a running DB system, it results in an error. To change the value, create a new DB system with the updated value, export the data from the old DB system, and import it to the new DB system.