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" |
| 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" | |
| 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 |
| 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 |
| query_result_maxsize | Y | N | uint | Y | Both | Y | 100 | 0-18446744073709551615 |
| query_result_timeout | Y | N | uint | Y | Both | Y | 24 | 0-18446744073709551615 |
| save_query_result | Y | N | bool | Y | Both | Y | FALSE | TRUE |
| 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" |
| 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" | |
| wait_timeout | Y | N | int | Y | Both | Y | 28800 | 1-2147483 |
Constraints
MatrixOne is compatible with MySQL, and the SQL mode only supports the ONLY_FULL_GROUP_BY mode; other SQL modes are only supported by syntax and have no actual effect.