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.

A configuration variable can be dynamic, which means changing the variable does not require restarting the DB system.

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 Service may 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
autocommit ON Boolean: OFF, ON
big_tables OFF Boolean: OFF, ON
binlog_expire_logs_seconds See Default User Variables.  
binlog_row_metadata MINIMAL Enumeration: FULL, MINIMAL
binlog_row_value_options See Default User Variables.  
binlog_transaction_compression OFF Boolean: OFF, ON
character_set_server UTF8MB4 String: Any valid character set 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 8912 Integer: 0 - 536,870,912
connection_memory_limit There is no default value. MySQL default: 18446744073709551615 Integer: 2,097,152 - 18,446,744,073,709,551,615
cte_max_recursion_depth 1000 Integer: 0 - 4294967295
default_authentication_plugin caching_sha2_password Enumeration: mysql_native_password, sha256_password, caching_sha2_password
foreign_key_checks ON Boolean: OFF, ON
global_connection_memory_limit There is no default value. MySQL default: 18446744073709551615 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 - 18,446,744,073,709,551,615
group_replication_consistency See Default User Variables.  
information_schema_stats_expiry 86400 Integer: 0 - 31,536,000
innodb_buffer_pool_dump_pct 25 Integer: 1 - 100
innodb_buffer_pool_instances See Default User Variables.  
innodb_buffer_pool_size See Default User Variables.  
innodb_ddl_buffer_size 1048576 Integer: 65,536 - 4,294,967,295
innodb_ddl_threads 4 Integer: 1 - 64
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_lock_wait_timeout 50 Integer: 1 - 1,073,741,824
innodb_log_writer_threads ON Boolean: OFF, ON
innodb_max_purge_lag 0 Integer: 0 - 4,294,967,295
innodb_max_purge_lag_delay See Default User Variables.  
innodb_stats_persistent_sample_pages 20 Integer: 1 - 18,446,744,073,709,551,615
innodb_stats_transient_sample_pages 8 Integer: 1 - 18,446,744,073,709,551,615
innodb_strict_mode ON Boolean: OFF, ON
interactive_timeout 28800 Integer: 1 - 31,536,000
local_infile See Default User Variables.  
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 18446744073709551615 Integer: 1 - 18,446,744,073,709,551,615
max_connections See Default User Variables.  
max_execution_time 0 Integer: 0 - 18,446,744,073,709,551,615
max_heap_table_size 16777216 Integer: 16,384 - 1,844,674,407,370,954,752
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
mysql_firewall_mode ON 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 28800 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_read_timeout 30 Integer: 1 - 31,536,000
net_write_timeout 60 Integer: 1 - 31,536,000
parser_max_mem_size There is no default value. MySQL default: 18446744073709551615 Integer: 10,000,000 - 18,446,744,073,709,551,615
regexp_time_limit 32 Integer: 0 - 2,147,483,647
sort_buffer_size 262144 Integer: 32,768 - 18,446,744,073,709,551,615
sql_generate_invisible_primary_key OFF 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_warnings OFF Boolean: OFF, ON
thread_pool_dedicated_listeners OFF Boolean: OFF, ON
thread_pool_max_transactions_limit 0 Integer: 0 - 100,000
time_zone See Default User Variables.  
tmp_table_size 16777216 Integer: 1,024 - 18,446,744,073,709,551,615
transaction_isolation REPEATABLE-READ Enumeration: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
wait_timeout 28800 Integer: 1 - 31,536,000

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
binlog_expire_logs_seconds 3600 Integer: 0 - 4,294,967,295
binlog_row_value_options PARTIAL_JSON Set: NULL, PARTIAL_JSON
group_replication_consistency BEFORE_ON_PRIMARY_FAILOVER Enumeration: EVENTUAL, BEFORE_ON_PRIMARY_FAILOVER, BEFORE, AFTER, BEFORE_AND_AFTER
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 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 shapes: 5MB - 9GB
  • 16GB shapes: 5MB - 10GB
  • 30GB shapes: 5MB - 24GB
  • 32GB shapes: 5MB - 26GB
  • 60GB shapes: 5MB - 54GB
  • 64GB shapes: 5MB - 57GB
  • 120GB shapes: 5MB - 108GB
  • 128GB shapes: 5MB - 115GB
  • 240GB shapes: 5MB - 216GB
  • 256GB shapes: 5MB - 230GB
  • 384GB shapes: 5MB - 345GB
  • 512GB shapes: 5MB - 660GB
  • 768GB shapes: 5MB - 691GB
  • 1024GB shapes: 5MB - 921GB
innodb_ft_result_cache_limit 33554432 Integer: 1,000,000 - 4,294,967,295
innodb_max_purge_lag_delay 300000 Integer: 1,000 - 1,000,000
local_infile ON Boolean: OFF, ON
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
sql_require_primary_key (HA shapes only) ON Boolean: OFF, ON
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.