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

  • To view the minimum and maximum values of user variables, see REST API Configuration Variables.
  • The links for the variables lead to their descriptions in the on-premise MySQL Manual; some of the default values shown for MySQL HeatWave Service may be different from those shown in the on-premise MySQL Manual.

Table 10-1 Default Values of User Variables

User Variable Default Value on MySQL HeatWave Service
autocommit ON
big_tables OFF
binlog_expire_logs_seconds See Default User Variables.
binlog_row_metadata MINIMAL
binlog_row_value_options See Default User Variables.
binlog_transaction_compression OFF
character_set_server UTF8MB4
collation_server UTF8MB4_0900_AI_CI
completion_type NO_CHAIN
connect_timeout 10
connection_memory_chunk_size 8912
connection_memory_limit There is no default value.
cte_max_recursion_depth 1000
default_authentication_plugin caching_sha2_password
foreign_key_checks ON
global_connection_memory_limit There is no default value.
global_connection_memory_tracking OFF
group_concat_max_len 1024
group_replication_consistency See Default User Variables.
information_schema_stats_expiry 86400
innodb_buffer_pool_dump_pct 25
innodb_buffer_pool_instances See Default User Variables.
innodb_buffer_pool_size See Default User Variables.
innodb_ddl_buffer_size 1048576
innodb_ddl_threads 4
innodb_ft_enable_stopword ON
innodb_ft_max_token_size 84
innodb_ft_min_token_size 3
innodb_ft_num_word_optimize 2000
innodb_ft_result_cache_limit See Default User Variables.
innodb_ft_server_stopword_table NULL
innodb_lock_wait_timeout 50
innodb_log_writer_threads ON
innodb_max_purge_lag 0
innodb_max_purge_lag_delay See Default User Variables.
innodb_stats_persistent_sample_pages 20
innodb_stats_transient_sample_pages 8
innodb_strict_mode ON
interactive_timeout 28800
local_infile See Default User Variables.
mandatory_roles See Default User Variables.
max_allowed_packet 67108864
max_binlog_cache_size See Default User Variables.
max_connect_errors 18446744073709551615
max_connections See Default User Variables.
max_execution_time 0
max_heap_table_size 16777216
max_prepared_stmt_count The default value for all shapes is 16382.

The maximum value is dependent on the amount of RAM provided by the shape.

For standalone and high availability shapes, the maximum value is as follows:

  • 8GB shape - Maximum: 16382
  • 15GB shape - Maximum: 20000
  • 16GB shape - Maximum: 20000
  • 30GB shape - Maximum: 40000
  • 32GB shape - Maximum: 40000
  • 60GB shape - Maximum: 80000
  • 64GB shape - Maximum: 80000
  • 120GB shape - Maximum: 160000
  • 128GB shape - Maximum: 160000
  • 240GB shape - Maximum: 160000
  • 256GB shape - Maximum: 160000
  • 384GB shape - Maximum: 160000
  • 512GB shape - Maximum: 160000
  • 768GB shape - Maximum: 160000
  • 1024GB shape - Maximum: 160000

For HeatWave enabled shapes, the maximum value is as follows:

  • 512GB shape - Maximum: 80000
  • 2048GB shape - Maximum: 80000
mysql_firewall_mode ON
mysqlx_connect_timeout 30
mysqlx_deflate_default_compression_level 3
mysqlx_deflate_max_client_compression_level 5
mysqlx_interactive_timeout 28800
mysqlx_lz4_default_compression_level 2
mysqlx_lz4_max_client_compression_level 8
mysqlx_max_allowed_packet 67108864
mysqlx_read_timeout 28800
mysqlx_wait_timeout 28800
mysqlx_write_timeout 60
mysqlx_zstd_default_compression_level 3
mysqlx_zstd_max_client_compression_level 11
net_read_timeout 30
net_write_timeout 60
parser_max_mem_size 10000000
regexp_time_limit 32
sort_buffer_size 262144
sql_generate_invisible_primary_key OFF
sql_mode ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES
sql_require_primary_key See Default User Variables.
sql_warnings OFF
thread_pool_dedicated_listeners OFF
thread_pool_max_transactions_limit 0
time_zone See Default User Variables.
tmp_table_size 16777216
transaction_isolation REPEATABLE-READ
wait_timeout 28800

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.

Note

To view the minimum and maximum values of default user variables, see REST API Configuration Variables.

Table 10-2 Default Values of Default User Variables

Default User Variable Default Value
binlog_expire_logs_seconds 3600
binlog_row_value_options PARTIAL_JSON
group_replication_consistency BEFORE_ON_PRIMARY_FAILOVER
innodb_buffer_pool_instances Dependent on the amount of RAM provisioned by the shape. The default values are as follows:
  • For shapes which provision 8-128GB RAM - 4
  • 256GB shapes - 8
  • 384GB shapes - 12
  • 512GB shapes - 16
  • 768GB shapes - 24
  • 1024GB shapes - 32
innodb_buffer_pool_size

Dependent on the amount of RAM provided by the shape.

For standalone shapes, the default and maximum values are as follows:
  • 8GB shape - Default: 2GB, maximum: 2GB
  • 15GB shape - Default: 10GB, maximum: 10GB
  • 16GB shape - Default: 10GB, maximum: 10GB
  • 30GB shape - Default: 20GB, maximum: 20GB
  • 32GB shape - Default: 20GB, maximum: 20GB
  • 60GB shape - Default: 48GB, maximum: 57GB
  • 64GB shape - Default: 48GB, maximum: 57GB
  • 120GB shape - Default: 96GB, maximum: 115GB
  • 128GB shape - Default: 96GB, maximum: 115GB
  • 240GB shape - Default: 192GB, maximum: 230GB
  • 256GB shape - Default: 192GB, maximum: 230GB
  • 384GB shape - Default: 288GB, maximum: 345GB
  • 512GB shape - Default: 384GB, maximum: 460GB
  • 768GB shape - Default: 576GB, maximum: 691GB
  • 1024GB shape - Default: 768GB, maximum: 921GB

For high availability shapes, the default values are as follows:

  • 8GB shape - Default: 1.5GB, maximum: 2GB
  • 16GB shape - Default: 8.5GB, maximum: 10GB
  • 32GB shape - Default: 17GB, maximum: 20GB
  • 64GB shape - Default:43GB, maximum: 57GB
  • 128GB shape - Default: 89GB, maximum: 115GB
  • 256GB shape - Default: 185GB, maximum: 230GB
  • 384GB shape - Default: 282GB, maximum: 345GB
  • 512GB shape - Default: 378GB, maximum: 460GB
  • 768GB shape - Default: 570GB, maximum: 691GB
  • 1024GB shape - Default: 752GB, maximum: 921GB

For HeatWave enabled shapes, the default and maximum values are as follows:

  • 512GB shape - Default: 48GB, maximum: 48GB
  • 2048GB shape - Default: 192GB, maximum: 230GB
innodb_ft_result_cache_limit 33554432
innodb_max_purge_lag_delay 300000
local_infile ON
mandatory_roles public
max_binlog_cache_size 4294967296
max_connections Dependent on shape. The default values per shape are:
  • 8GB shapes - 500
  • 16GB shapes - 1000
  • 32GB shapes - 2000
  • 64GB shapes - 4000
  • all shapes larger than 64GB - 8000
sql_require_primary_key (HA shapes only) ON
time_zone Sets the global timezone. The default value is UTC.

System Variables

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

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.