HeatWave on AWS  /  Configuration  /  Session Variables

18.9 Session Variables

Session variables remain in effect during the session until the variable changes or the session ends. The change has no effect on other sessions. For new connections, a session variable value is initialized with the corresponding global system variable value. Many of the following session variables are also available as user configurable variables. See: User-Configurable System Variables.

To assign a value to a session variable, precede the variable name with the SESSION or LOCAL keyword, or with the @@SESSION., @@LOCAL., or @@ qualifier, or with no keyword or modifier. For example:

mysql> SET SESSION sql_mode = 'TRADITIONAL';
mysql> SET LOCAL sql_mode = 'TRADITIONAL';
mysql> SET @@SESSION.sql_mode = 'TRADITIONAL';
mysql> SET @@LOCAL.sql_mode = 'TRADITIONAL';
mysql> SET @@sql_mode = 'TRADITIONAL';
mysql> SET sql_mode = 'TRADITIONAL';

Table 18-18 User Settable Session Variables

Name Default Value
autocommit ON
big_tables OFF
block_encryption_mode aes-128-ecb
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_results utf8mb4
character_set_server utf8mb4
collation_connection utf8mb4_0900_ai_ci
collation_database utf8mb4_0900_ai_ci
collation_server utf8mb4_0900_ai_ci
completion_type NO_CHAIN
cte_max_recursion_depth 1000
default-storage-engine InnoDB
--default_tmp_storage_engine InnoDB
default_week_format 0
div_precision_increment 4
end_markers_in_json OFF
eq_range_index_dive_limit 200
foreign_key_checks ON
generated_random_password_length 20
group_concat_max_len 1024
group_replication_consistency BEFORE_ON_PRIMARY_FAILOVER
information_schema_stats_expiry 86400
innodb_ddl_buffer_size 1048576
innodb_ddl_threads 4
innodb_ft_enable_stopword ON
innodb_ft_user_stopword_table NULL
innodb_lock_wait_timeout 50
innodb_parallel_read_threads Shape dependent. See Shape-Dependent System Variables.
internal_tmp_mem_storage_engine TempTable
join_buffer_size 262144
lc_messages en_US
lc_time_names en_US
lock_wait_timeout 86400
long_query_time 10
max_allowed_packet 67108864
max_execution_time 0
max_heap_table_size 16777216
max_join_size 18446744073709551615
max_length_for_sort_data 4096
max_points_in_geometry 65536
max_seeks_for_key 18446744073709551615
max_sort_length 1024
mysqlx_max_allowed_packet 67108864
mysqlx_read_timeout 30
mysqlx_wait_timeout 28800
mysqlx_write_timeout 60
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old_alter_table OFF
optimizer_prune_level 1
optimizer_search_depth 62
optimizer_switch index_merge_sort_union=on, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on, mrr_cost_based=on, block_nested_loop=on, batched_key_access=off, materialization=on, semijoin=on, loosescan=on, firstmatch=on, duplicateweedout=on, subquery_materialization_cost_based=on, use_index_extensions=on, condition_fanout_filter=on, derived_merge=on, use_invisible_indexes=off, skip_scan=on, hash_join=on, subquery_to_derived=off, prefer_ordering_index=on, hypergraph_optimizer=off, derived_condition_pushdown=on
optimizer_trace enabled=off,one_line=off
optimizer_trace_features greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
optimizer_trace_limit 1
optimizer_trace_max_mem_size 1048576
optimizer_trace_offset -1
parser_max_mem_size 18446744073709551615
print_identified_with_as_hex OFF
pseudo_replica_mode OFF
pseudo_slave_mode OFF
query_alloc_block_size 8192
query_prealloc_size 8192
range_alloc_block_size 4096
range_optimizer_max_mem_size 8388608
rbr_exec_mode STRICT
read_buffer_size 131072
read_rnd_buffer_size 262144
resultset_metadata FULL
secondary_engine_cost_threshold 100000.000000
session_track_gtids OFF
session_track_schema ON
session_track_state_change OFF
session_track_system_variables 262144
session_track_transaction_info OFF
show_create_table_skip_secondary_engine OFF
show_create_table_verbosity OFF
sort_buffer_size 262144
sql_auto_is_null OFF
sql_big_selects ON
sql_buffer_result OFF
sql_mode ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION
sql_notes ON
sql_quote_show_create ON
sql_safe_updates OFF
sql_select_limit 18446744073709551615
sql_warnings OFF
thread_pool_high_priority_connection 0
time_zone UTC
tmp_table_size 16777216
transaction_alloc_block_size 8192
transaction_isolation REPEATABLE-READ
transaction_prealloc_size 4096
transaction_read_only OFF
unique_checks ON
updatable_views_with_limit YES
use_secondary_engine ON
wait_timeout 28800
windowing_use_high_precision ON