Skip to content

Server system variables

MatrixOne Server System Variable refers to a set of variables used in MatrixOne Server to control or configure the behavior of a database engine or other components. The values ​​of these variables can be set and changed through the SET statement.

System variables can be divided into two categories:global variables andsession variables.

-Global variable: refers to a variable that applies to all connected MatrixOne sessions. Their values ​​are set when MatrixOne Server starts and remain unchanged until MatrixOne Server is shut down. Global variables are often used to control the behavior of MatrixOne Server, such as specifying the default backup and recovery location, specifying the default locale, etc.

-Session variable: refers to a variable that only applies to MatrixOne sessions connected to the current user. Their values ​​can be changed at any time the user connects and are automatically cleared when the user disconnects. Session variables are usually used to control the behavior of a session, such as controlling the way of printing information, specifying transaction isolation levels, etc.

How to query system variables?

You can use the following SQL query:

SHOW VARIABLES;

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

SHOW VARIABLES LIKE '%theme%';

This lists all system variables with the name theme and their current values.

Note: LIKE operator is used to fuzzy match query strings, % means zero or more arbitrary characters. So, the above command will match any system variables that contain theme in the name.

How to query global variables?

You can use the following SQL query:

SHOW GLOBAL VARIABLES;

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

SHOW GLOBAL VARIABLES LIKE '%theme%';

This lists all global system variables with theme in their name and their current value.

Note: LIKE operator is used to fuzzy match query strings, % means zero or more arbitrary characters. So, the above command will match any global system variables that contain theme in the name.

How to query session variables?

You can use the following SQL query:

SHOW SESSION VARIABLES;

This lists all system variables and their current values ​​in the current session. If you want to view only session variables related to a specific topic, you can use the following syntax:

SHOW SESSION VARIABLES LIKE '%theme%';

This lists all session variables with theme in their name and their current value.

Note: LIKE operator is used to fuzzy match query strings, % means zero or more arbitrary characters. So, the above command will match any session system variable that contains theme in the name.

Variable reference table

| 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", "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 |

limit

MatrixOne is MySQL compatible, and SQL mode only supports ONLY_FULL_GROUP_BY mode; other SQL modes only support syntax and do not actually work.