Server System Variables

MatrixOne server system variables are variables used to control or configure the behavior of the database engine or other components in the MatrixOne server. The values of these variables can be set and changed using the SET statement.

System variables can be divided into two categories: Global variables and Session variables.

  • Global variables: Global variables apply to all connected sessions of the MatrixOne server. Their values are set when the MatrixOne server starts and remain unchanged until the server is shut down. Global variables are typically used to control the behavior of the MatrixOne server, such as specifying default backup and restore locations, the default language environment, and so on.

  • Session variables: Session variables apply only to the currently connected user’s session of the MatrixOne server. Their values can be changed anytime during the user’s connection and are automatically cleared when the user disconnects. Session variables are typically used to control session behavior, such as how to print the information displayed and specify transaction isolation levels.

How to query system variables?

You can use the following SQL to query:

SHOW VARIABLES;

This will list all system variables and their current values. If you only want to see system variables related to a specific topic, you can use the following syntax:

SHOW VARIABLES LIKE '%theme%';

This will list all system variables with names containing the word theme and their current values.

Note: The LIKE operator is used for fuzzy matching query strings, with % representing zero or more arbitrary characters. Therefore, the above command will match any system variables with names containing the word theme.

How to query global variables?

You can use the following SQL to query:

SHOW GLOBAL VARIABLES;

This will list all global variables and their current values. If you only want to see global variables related to a specific topic, you can use the following syntax:

SHOW GLOBAL VARIABLES LIKE '%theme%';

This will list all global variables with names containing the word theme and their current values.

Note: The LIKE operator is used for fuzzy matching query strings, with % representing zero or more arbitrary characters. Therefore, the above command will match any global variables with names containing the word theme.

How to query session variables?

You can use the following SQL to query:

SHOW SESSION VARIABLES;

This will list all session variables and their current values. If you only want to see session variables related to a specific topic, you can use the following syntax:

SHOW SESSION VARIABLES LIKE '%theme%';

This will list all session variables with names containing the word theme and their current values.

Note: The LIKE operator is used for fuzzy matching query strings, with % representing zero or more arbitrary characters. Therefore, the above command will match any session variables with names containing the word theme.

System Veariable Reference

Variable_name

Cmd-Line(Y/N)

Option File(Y/N)

Variable Type

System Var(Y/N)

Var Scope(Global/Both/Session)

Dynamic(Y/N)

Default Value

Optional value

auto_increment_increment

Y

N

int

Y

Both

Y

1

1-65535

auto_increment_offset

Y

N

int

Y

Both

Y

1

1-65535

autocommit

Y

N

bool

Y

Both

Y

TRUE

FALSE

character_set_client

Y

N

string

Y

Both

Y

“utf8mb4”

character_set_connection

Y

N

string

Y

Both

Y

“utf8mb4”

character_set_database

Y

N

string

Y

Both

Y

“utf8mb4”

character_set_results

Y

N

string

Y

Both

Y

“utf8mb4”

character_set_server

Y

N

string

Y

Both

Y

“utf8mb4”

collation_connection

Y

N

string

Y

Both

Y

“default”

collation_server

Y

N

string

Y

Both

Y

“utf8mb4_bin”

completion_type

Y

N

enum

Y

Both

Y

“NO_CHAIN”

“NO_CHAIN”,”CHAIN”, “RELEASE”

cte_max_memory_bytes

Y

N

int

Y

Both

Y

1073741824

0-1099511627776

cte_max_recursion_depth

Y

N

int

Y

Both

Y

1000

0-4294967295

enable_remap_hint

Y

N

bool

Y

Both

Y

FALSE

TRUE

enable_explain_scheduling

Y

N

bool

Y

Session

Y

FALSE

TRUE

event_scheduler

Y

N

enum

Y

Global

N

DISABLED

DISABLED

experimental_cagra_index

Y

N

bool

Y

Both

Y

FALSE

TRUE

experimental_fulltext2_index

Y

N

bool

Y

Both

Y

FALSE

TRUE

experimental_fulltext_index

Y

N

bool

Y

Both

Y

FALSE

TRUE

experimental_hnsw_index

Y

N

bool

Y

Both

Y

FALSE

TRUE

experimental_ivf_index

Y

N

bool

Y

Both

Y

FALSE

TRUE

experimental_ivfpq_index

Y

N

bool

Y

Both

Y

FALSE

TRUE

ft2_relevancy_algorithm

Y

N

string

Y

Both

Y

“BM25”

“BM25”,”TF-IDF”

fulltext_bloom_filter_pushdown

Y

N

bool

Y

Session

Y

FALSE

TRUE

group_concat_max_len

Y

N

int

Y

Both

Y

1024

4-9223372036854775807

host

Y

N

string

Y

Both

N

“0.0.0.0”

init_connect

Y

N

string

Y

Both

Y

“”

interactive_timeout

Y

N

int

Y

Both

Y

28800

1-31536000

license

Y

N

string

Y

Both

N

“APACHE”

lock_wait_timeout

Y

N

int

Y

Both

Y

120

1-31536000

lower_case_table_names

Y

N

int

Y

Both

N

1

0-2

max_allowed_packet

Y

N

int

Y

Both

Y

16777216

1024-1073741824

mo_rollback_txn_on_error

Y

N

bool

Y

Both

Y

FALSE

TRUE

net_write_timeout

Y

N

int

Y

Both

Y

60

1-31536000

performance_schema

Y

N

int

Y

Both

Y

0

0-1

port

Y

N

int

Y

Both

N

6001

0-65535

profiling

Y

N

int

Y

Both

Y

0

0-1

protected_databases

Y

N

string

Y

Global

Y

“”

Comma-separated database names

query_max_workers

Y

N

int

Y

Session

Y

0

0-2147483647

query_pool_strict

Y

N

bool

Y

Session

Y

FALSE

TRUE

query_result_maxsize

Y

N

uint

Y

Both

Y

100

0-18446744073709551615

query_result_timeout

Y

N

uint

Y

Both

Y

24

0-18446744073709551615

remap_rewrites

Y

N

string

Y

Session

Y

“”

save_query_result

Y

N

bool

Y

Both

Y

FALSE

TRUE

server_id

Y

N

string

Y

Global

N

“”

Populated with the current CN service’s UUID at session start; read-only.

sort_spill_mem

Y

N

int

Y

Both

Y

0

0-1099511627776

sql_mode

Y

N

set

Y

Both

Y

“ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_ENGINE_SUBSTITUTION”

“ANSI”, “TRADITIONAL”, “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”, “MATRIXONE_NATIVE”

sql_safe_updates

Y

N

int

Y

Both

Y

0

0-1

sql_select_limit

Y

N

uint

Y

Both

Y

18446744073709551615

0-18446744073709551615

system_time_zone

Y

N

string

Y

Both

N

“”

time_zone

Y

N

string

Y

Both

N

“SYSTEM”

transaction_isolation

Y

N

enum

Y

Both

Y

“REPEATABLE-READ”

“READ-UNCOMMITTED”, “READ-COMMITTED”, “REPEATABLE-READ”,”REPEATABLE-READ”, “SERIALIZABLE”

transaction_read_only

Y

N

int

Y

Both

Y

0

0-1

version_comment

Y

N

string

Y

Both

N

“MatrixOne”

view_security_type

Y

N

enum

Y

Session

Y

“DEFINER”

“DEFINER”, “INVOKER”

wait_timeout

Y

N

int

Y

Both

Y

28800

1-2147483

Constraints

MatrixOne implements a tested subset of SQL mode behavior, including ANSI_QUOTES, PIPES_AS_CONCAT, strict conversion modes, division-by-zero handling, ONLY_FULL_GROUP_BY, and the MatrixOne-specific MATRIXONE_NATIVE mode. Other accepted tokens are not guaranteed to implement complete MySQL semantics. See SQL Mode for the behavior matrix.