The MySQL server implements several session state trackers. A client can enable these trackers to receive notification of changes to its session state.
Session state trackers have uses such as these:
To facilitate session migration.
To facilitate transaction switching.
To enable the exchange of plugin information between the server and client.
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:
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
The tracker mechanism is used to enable the server to pass plugin information to the client. This is required when multiple authentication plugins might be involved in authenticating the client account.
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.
session_track_state_changesystem variable controls this tracker.
Changes to the default schema name. The
session_track_schemasystem variable controls this tracker.
Changes to the session values of system variables. The
session_track_system_variablessystem variable controls this tracker.
Available GTIDs. The
session_track_gtidssystem variable controls this tracker.
Information about transaction state and characteristics. The
session_track_transaction_infosystem variable controls this tracker.
Information about plugins (implemented as of MySQL 8.0.27). This notification indicates in the OK packet that the client must prepare to perform another authentication handshake, for an authentication method additional to those already seen. No system variable is associated with this tracker. Instead, the client sets the
CLIENT_MANDATORY_SESSION_TRACKcapability bit to indicate that it can handle this tracker type. This enables sensible handling of older clients that try to connect to a newer server using an account that requires multiple authentication methods; the server knows to refuse the connection.
For descriptions of the tracker-related system variables, see Section 5.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.
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_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().
The mysqltest program has
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
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
shell> 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
TRANSACTION statement, two
TRANSACTION statements execute that set the isolation
level and access mode characteristics for the next transaction.
value indicates those next-transaction values that have been
that ends the transaction, the
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
To see information about GTIDs, enable the
SESSION_TRACK_GTIDS tracker using the