Introduction to Klustron's unique variable read and write functions
Introduction to Klustron's unique variable read and write functions
This article explains how Klustron computing nodes support reading and writing variables in storage nodes .
Function details
1、Make the syntax of set [shard] global/session/local/PERSIST/PERSIST_only VARNAME=VALUE effective; always assume that VARNAME is MySQL; the computing node will send the SET statement to all storage nodes for execution, and in a session During the lifetime, the values of all set session variables will be cached in the session of the computing node.
After switching the master node of the storage cluster, the new master node will use the cached session variable value to set to the new master node of the storage cluster to ensure a consistent session (connection) state. set session innodb_lock_wait_timeout = 3;
Set global variables:
set global innodb_lock_wait_timeout = 4;
set persist_only innodb_lock_wait_timeout = 11;
2、For the set VARNAME=VALUE statement, when the PostgreSQL client connects, it will treat them as PostgreSQL variable names by default. If the variable names do not exist, they will be regarded as MySQL session variables. When the client connects to MySQL, they will be regarded as MySQL session variables by default, and if the variable name does not exist, they will be regarded as PostgreSQL variables. set innodb_lock_wait_timeout = 2;
3、The computing node will cache the session var-value paired with MySQL, and if the shard connection is reset or reconnected, the computing node will send the cached variable to the MySQL node.
4、It also supports the set @@global/session/local/persist/persist_only.varname=value statement, and it is always assumed to be a MySQL variable, because only MySQL has such a SET syntax. But set @@VARNAME=VALUE is not supported due to shift/reduce conflicts.
set @@global.innodb_lock_wait_timeout = 4;
set @@innodb_lock_wait_timeout = 2; (this syntax is not supported)
5、Support the syntax of show [session/local/global] variables like 'wildcard-filter and always assume it is a MySQL variable, because MySQL only has such syntax, but PostgreSQL does not. The syntax for show varname always assumes a PostgreSQL variable first. If the variable name is not a PostgreSQL variable, it is assumed to be a MySQL variable.
show global variables like 'innodb_lock_wait_timeout'；
show session variables like 'innodb_lock_wait_timeout';
show local variables like 'innodb_lock_wait_timeout';
6、Support to select STRICT in the option show [session/global/local] variables like in the Klustron computing node to display the MySQL variables that only the computing node is allowed to access.
show local variables like 'innodb_lock_wait_timeout' strict;
show session variables like 'innodb_lock_wait_timeout' strict;
show global variables like 'innodb_lock_wait_timeout' strict;
7、When the show statement is executed, the value of a variable will be obtained, and it will be directly sent to any storage shard to obtain its value.
8、Support select @@[global. | session.] var_name [ [AS] alias] [, ... ] [limit N] syntax, for example: select @@global.innodb_lock_wait_timeout, @@autocommit AS ac; select @@ session.sql_log_bin limit 1
Summarize
Klustron's computing nodes support reading and writing system variables of storage nodes, which can bring great convenience to users.
For example, the user can execute a set global var=value; statement in the connection of the computing node to set the value of the var variable of all backend clusters to value. You can also set the value of the var variable in the current session (connection) to value by executing a set session var=value; on the computing node.
The corresponding test script for this part of the function is pulled from https://gitee.com/zettadb/kunlun.git . In src/test/regression/sql/vars.sql, these test cases also demonstrate the function of MyQL variable access .