7.1.18 Server Tracking of Client Session State

The MySQL server implements several session state trackers. A client can enable these trackers to receive notification of changes to its session state.

• Uses for Session State Trackers

• Available Session State Trackers

• C API Session State Tracker Support

• Test Suite Session State Tracker Support

Uses for Session State Trackers

Session state trackers have uses such as these:

• To facilitate session migration.

• To facilitate transaction switching.

The tracker mechanism provides a means for MySQL connectors and client applications to determine whether any session context is available to permit session migration from one server to another. (To change sessions in a load-balanced environment, it is necessary to detect whether there is session state to take into consideration when deciding whether a switch can be made.)

The tracker mechanism permits applications to know when transactions can be moved from one session to another. Transaction state tracking enables this, which is useful for applications that may wish to move transactions from a busy server to one that is less loaded. For example, a load-balancing connector managing a client connection pool could move transactions between available sessions in the pool.

However, session switching cannot be done at arbitrary times. If a session is in the middle of a transaction for which reads or writes have been done, switching to a different session implies a transaction rollback on the original session. A session switch must be done only when a transaction does not yet have any reads or writes performed within it.

Examples of when transactions might reasonably be switched:

• Immediately after START TRANSACTION

• After COMMIT AND CHAIN

In addition to knowing transaction state, it is useful to know transaction characteristics, so as to use the same characteristics if the transaction is moved to a different session. The following characteristics are relevant for this purpose:

READ ONLY
READ WRITE
ISOLATION LEVEL
WITH CONSISTENT SNAPSHOT

Available Session State Trackers

To support the session-tracking activities, notification is available for these types of client session state information:

• Changes to these attributes of client session state:

  • The default schema (database).

  • Session-specific values for system variables.

  • User-defined variables.

  • Temporary tables.

  • Prepared statements.

  The session_track_state_change system variable controls this tracker.

• Changes to the default schema name. The session_track_schema system variable controls this tracker.

• Changes to the session values of system variables. The session_track_system_variables system variable controls this tracker. The SENSITIVE_VARIABLES_OBSERVER privilege is required to track changes to the values of sensitive system variables.

• Available GTIDs. The session_track_gtids system variable controls this tracker.

• Information about transaction state and characteristics. The session_track_transaction_info system variable controls this tracker.

For descriptions of the tracker-related system variables, see Section 7.1.8, “Server System Variables”. Those system variables permit control over which change notifications occur, but do not provide a way to access notification information. Notification occurs in the MySQL client/server protocol, which includes tracker information in OK packets so that session state changes can be detected.

C API Session State Tracker Support

To enable client applications to extract state-change information from OK packets returned by the server, the MySQL C API provides a pair of functions:

• mysql_session_track_get_first() fetches the first part of the state-change information received from the server. See mysql_session_track_get_first().

• mysql_session_track_get_next() fetches any remaining state-change information received from the server. Following a successful call to mysql_session_track_get_first(), call this function repeatedly as long as it returns success. See mysql_session_track_get_next().

Test Suite Session State Tracker Support

The mysqltest program has disable_session_track_info and enable_session_track_info commands that control whether session tracker notifications occur. You can use these commands to see from the command line what notifications SQL statements produce. Suppose that a file testscript contains the following mysqltest script:

DROP TABLE IF EXISTS test.t1;
CREATE TABLE test.t1 (i INT, f FLOAT);
--enable_session_track_info
SET @@SESSION.session_track_schema=ON;
SET @@SESSION.session_track_system_variables='*';
SET @@SESSION.session_track_state_change=ON;
USE information_schema;
SET NAMES 'utf8mb4';
SET @@SESSION.session_track_transaction_info='CHARACTERISTICS';
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ WRITE;
START TRANSACTION;
SELECT 1;
INSERT INTO test.t1 () VALUES();
INSERT INTO test.t1 () VALUES(1, RAND());
COMMIT;

Run the script as follows to see the information provided by the enabled trackers. For a description of the Tracker: information displayed by mysqltest for the various trackers, see mysql_session_track_get_first().

$> mysqltest < testscript
DROP TABLE IF EXISTS test.t1;
CREATE TABLE test.t1 (i INT, f FLOAT);
SET @@SESSION.session_track_schema=ON;
SET @@SESSION.session_track_system_variables='*';
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- session_track_system_variables
-- *

SET @@SESSION.session_track_state_change=ON;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- session_track_state_change
-- ON

USE information_schema;
-- Tracker : SESSION_TRACK_SCHEMA
-- information_schema

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

SET NAMES 'utf8mb4';
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- character_set_client
-- utf8mb4
-- character_set_connection
-- utf8mb4
-- character_set_results
-- utf8mb4

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

SET @@SESSION.session_track_transaction_info='CHARACTERISTICS';
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- session_track_transaction_info
-- CHARACTERISTICS

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
--

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET TRANSACTION READ WRITE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION READ WRITE;

START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION READ WRITE;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

SELECT 1;
1
1
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_____S_

INSERT INTO test.t1 () VALUES();
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T___W_S_

INSERT INTO test.t1 () VALUES(1, RAND());
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T___WsS_

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
--

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

ok

Preceding the START TRANSACTION statement, two SET TRANSACTION statements execute that set the isolation level and access mode characteristics for the next transaction. The SESSION_TRACK_TRANSACTION_CHARACTERISTICS value indicates those next-transaction values that have been set.

Following the COMMIT statement that ends the transaction, the SESSION_TRACK_TRANSACTION_CHARACTERISTICS value is reported as empty. This indicates that the next-transaction characteristics that were set preceding the start of the transaction have been reset, and that the session defaults apply. To track changes to those session defaults, track the session values of the transaction_isolation and transaction_read_only system variables.

To see information about GTIDs, enable the SESSION_TRACK_GTIDS tracker using the session_track_gtids system system variable.