MySQL :: Version Tokens in MySQL 5.7

In MySQL 5.7.8 we added support for version tokens. This is a new feature that enables you to prevent accessing incorrect or out-of-date data, and stray modifications executing on the wrong server.

For some backstory

When there are multiple MySQL instances running on a Linux machine, there is a reasonable chance that clients could connect to the wrong instance. One of the most common causes of this, is that connections to “localhost” implies unix socket (and ignores the --port option), but once connected you are free to run queries. You may receive an error if a query fails to execute, but wouldn’t it be useful if additional validation could be performed so you know you are connected to the right instance?

A variation of this same scenario applies in a High Availability environment. Since the role of each server in a HA group can change frequently, there is a potential to execute queries on the wrong server. This may result in data draft (i.e. corrupt data).

Version tokens provides an additional layer of validation to overcome this situation. They allow a system (of clients and servers) to effectively synchronize with each other and ensure that queries are executed on the correct instance.

Introducing Version Tokens

Version tokens are objects, which have a token name and a value in the format token1=value1;token2=value2... and so on. The server side support is implemented as an audit plugin. There are several UDFs implemented to serve various purposes. Before describing the setup steps, we will need to know the purpose and behavior of UDF and plugin variables.

Plugin Variable:
version_tokens_list acts like a global variable. It is not a pure plugin variable. This variable is not visible or directly accessible to user, but a place holder. This variable can be changed through various UDFs only. version_tokens_list holds token name and value in the format token=value. It can take multiple token and the value set which could be separated by semicolon like token1=value1;token2=value2...

version_tokens_set:
The variable version_tokens_list can be changed completely using version_tokens_set() UDF.
Syntax : select version_tokens_set("token1=value1;token2=value2");

version_tokens_session variable:
This is the session variable which contain information in same format as the plugin variable version_tokens_list. When this variable is set, before executing each query the plugin will compare the session token list and value with the global token list and will return an error if:
– a session token name is not present in the global list
– a session token value does not correspond to the value for the same token in the global list

version_tokens_lock_exclusive:
The UDF version_tokens_lock_exclusive is used to acquire write lock on tokens. It can accept any number of parameters. The last parameter is time in seconds till which it will wait to acquire lock on token. After that it will throw timeout error. Rest all parameters are token names on which lock is requested.
Syntax : SELECT version_tokens_lock_exclusive("token1","token2",......"tokenn",timeout);

version_tokens_unlock:
It will release all locks by that session.
syntax : SELECT version_tokens_unlock();

version_tokens_show :
Content of the variable version_tokens_list can be seen using the UDF version_tokens_show.
Syntax : select version_tokens_show();

version_tokens_edit :
Partial modifications are done through version_tokens_edit() UDF.
Syntax : select version_tokens_edit("token1=value1;token2=value2");

version_tokens_delete :
Individual tokens can be deleted using version_tokens_delete() UDF.
Syntax : select version_tokens_delete("token1;token3");

Sample Usage

Here is an example usage of how an application would use version tokens:

Set the global variable to a set of token value combination by using below statement:
select version_tokens_set("token1=value1;token2=value2;token3=value3");
Set the session variable version_tokens_session to a token value combination by using below statement:
set @@version_tokens_session= "token1=value1;token2=value2";
Now execute any statement. It will allow execution of queries because token and value combination of session variable appears in the global variable.
Change the session variable version_tokens_session to another token value combination.
set @@version_tokens_session= "token1=value11;token2=value22";
Here version token of session and global variable mismatch. Now try to execute any query. It will throw version token mismatch error. We can’t execute any statement until the session is cleaned. Session can be cleaned by using the statement “resetconnection”.

This is the basic behavior. There are additional facilities where tokens can be locked by an individual session until a particular operation is finished.

Broadly, two type of locks are implemented. They are implicit and explicit locks. Implicit locks are acquired automatically by a session for the duration a statement is executed. Explicit locks are acquired explicitly by calling various type of UDFs. Implicit version token locks are shared locks. Explicit version token locks can be exclusive or shared.

Below example demonstrates the usage of both implicit and explicit locks:

From connection1, execute following steps :

select version_tokens_set("a=aa;b=bb;c=cc"); # Set the global plugin variable
set @@version_tokens_session= "a=aa;b=bb"; # Set the session variable. “a” and “b” are version tokens. Version token of session variables are compared with that of global. All tokens mentioned in session variable should be there in global variable and values of all the version tokens in session and global should be same. Here “a” and “b” are there in session as well as global, and their values are also same both in session and global. token “a” and “b” appears in session variable of connection1. Hence when a statement is executed by connection1, they will be locked.
select sleep(20); — sleep for 20 seconds. # Statement will be executed for 20 seconds

From connection2, execute following steps :

select sleep(2); # So that connection1 is executed first and lock “a” and “b” is acquired by connection1;
--let $time1= select sysdate(); # Capture current time
SELECT version_tokens_lock_exclusive("a",100); # It request to acquire write lock on “a”. Here “a” is implicitly locked by connection1. It will wait for 20 seconds after which lock on “a” will be released by connection1 and then connection2 will acquire it exclusively. Last parameter 100 is maximum wait time after which it will throw timeout error. In this example, such situation will not arise as connection1 will release lock in maximum 20 seconds.
--let $time2= select sysdate(); # Capture the current time.
Difference of ($time2 - $time1) should be greater than or equal to 18 secs # connection2 will wait for connection1 to finish execution and release the lock on “a”.
Now from connection1, try to execute any statement. It will wait infinitely because the tokens used by connection1 are explicitly locked by connection2. Lock on “a” by connection2 can be released by using the UDF version_tokens_unlock. After this, statement on connection1 will be executed.

Please note, that sessions, which have version tokens in their version_tokens_session variable, cannot take explicit version token locks. No error is raised, but such sessions release all their version token locks at each statement end. It does not distinguish between explicit or implicit locks then.

Thank you for using MySQL!