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.
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.
The links for the variables lead to their descriptions in the on-premises MySQL Reference Manual; some of the default values shown for 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 HeatWave Service | Valid Values on 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 - 9,223,372,036,854,776,000 |
cte_max_recursion_depth |
1000 | Integer: 0 - 4,294,967,295 |
default_authentication_plugin |
caching_sha2_password |
Note Enumeration: This variable is ignored in MySQL 8.4.0 or higher. 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 - 9,223,372,036,854,776,000 |
global_connection_memory_tracking |
OFF |
Boolean: OFF , ON |
group_concat_max_len |
1024 | Integer: 4 - 9,223,372,036,854,776,000 |
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 - 9,223,372,036,854,776,000 |
innodb_stats_transient_sample_pages |
8 | Integer: 1 - 9,223,372,036,854,776,000 |
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 - 9,223,372,036,854,776,000 |
max_connections |
See Default User Variables. | |
max_execution_time |
0 | Integer: 0 - 9,223,372,036,854,776,000 |
max_heap_table_size |
16777216 | Integer: 16,384 - 9,223,372,036,854,776,000 |
max_prepared_stmt_count |
16382 | Integer: The maximum value is dependent on the shape or the amount of RAM provided by the shape.
|
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 - 9,223,372,036,854,776,000 |
regexp_time_limit |
32 | Integer: 0 - 2,147,483,647 |
sort_buffer_size |
262144 | Integer: 32,768 - 9,223,372,036,854,776,000 |
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 - 9,223,372,036,854,776,000 |
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:
|
Integer: 1 - 64 |
innodb_buffer_pool_size |
Dependent on the amount of RAM provided by the shape. For these HeatWave cluster shapes:
|
Integer: The maximum value is dependent on the shape or the amount of RAM provided by the shape.
|
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:
|
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 |