int
mysql_session_track_get_first(MYSQL *mysql,
enum enum_session_state_type type,
const char **data,
size_t *length)
MySQL implements a session tracker mechanism whereby the
server returns information about session state changes to
clients. To control which notifications the server provides
about state changes, client applications set system variables
having names of the form
session_track_
,
such as
xxx
session_track_state_change
,
session_track_schema
, and
session_track_system_variables
.
See Server Tracking of Client Session State.
Change 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, 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.mysql_session_track_get_next()
fetches any remaining state-change information received from the server. Following a successful call tomysql_session_track_get_first()
, call this function repeatedly as long as it returns success.
The
mysql_session_track_get_first()
parameters are used as follows. These descriptions also apply
to
mysql_session_track_get_next()
,
which takes the same parameters.
mysql
: The connection handler.-
type
: The tracker type indicating what kind of information to retrieve. Permitted tracker values are the members of theenum_session_state_type
enumeration defined inmysql_com.h
:enum enum_session_state_type { SESSION_TRACK_SYSTEM_VARIABLES, /* Session system variables */ SESSION_TRACK_SCHEMA, /* Current schema */ SESSION_TRACK_STATE_CHANGE, /* Session state changes */ SESSION_TRACK_GTIDS, /* GTIDs */ SESSION_TRACK_TRANSACTION_CHARACTERISTICS, /* Transaction characteristics */ SESSION_TRACK_TRANSACTION_STATE /* Transaction state */ };
The members of that enumeration may change over time as MySQL implements additional session-information trackers. To make it easy for applications to loop over all possible tracker types regardless of the number of members, the
SESSION_TRACK_BEGIN
andSESSION_TRACK_END
symbols are defined to be equal to the first and last members of theenum_session_state_type
enumeration. The example code shown later in this section demonstrates this technique. (Of course, if the enumeration members change, you must recompile your application to enable it to take account of new trackers.) data
: The address of aconst char *
variable. Following a successful call, this variable points to the returned data, which should be considered read only.length
: The address of asize_t
variable. Following a successful call, this variable contains the length of the data pointed to by thedata
parameter.
The following discussion describes how to interpret the
data
and length
values
according to the type
value. It also
indicates which system variable enables notifications for each
tracker type.
-
SESSION_TRACK_SCHEMA
: This tracker type indicates that the default schema has been set.data
is a string containing the new default schema name.length
is the string length.To enable notifications for this tracker type, enable the
session_track_schema
system variable. -
SESSION_TRACK_SYSTEM_VARIABLES
: This tracker type indicates that one or more tracked session system variables have been assigned a value. When a session system variable is assigned, two values per variable are returned (in separate calls). For the first call,data
is a string containing the variable name andlength
is the string length. For the second call,data
is a string containing the variable value andlength
is the string length.By default, notification is enabled for these session system variables:
To change the default notification for this tracker type, set the
session_track_schema
system variable to a list of comma-separated variables for which to track changes, or*
to track changes for all variables. To disable notification of session variable assignments, setsession_track_system_variables
to the empty string. -
SESSION_TRACK_STATE_CHANGE
: This tracker type indicates a change to some tracked attribute of session state.data
is a byte containing a boolean flag that indicates whether session state changes occurred.length
should be 1. The flag is represented as an ASCII value, not a binary (for example,'1'
, not0x01
).To enable notifications for this tracker type, enable the
session_track_state_change
system variable.This tracker reports changes for these attributes of session state:
The default schema (database).
Session-specific values for system variables.
User-defined variables.
Temporary tables.
Prepared statements.
-
SESSION_TRACK_GTIDS
: This tracker type indicates that GTIDs are available.data
contains the GTID string.length
is the string length. The GTID string is in the standard format for specifying a set of GTID values; see GTID Sets.To enable notifications for this tracker type, set the
session_track_gtids
system variable. -
SESSION_TRACK_TRANSACTION_CHARACTERISTICS
: This tracker type indicates that transaction characteristics are available.data
is a string containing the characteristics data.length
is the string length. The characteristics tracker data string may be empty, or it may contain one or more SQL statements, each terminated by a semicolon:If no characteristics apply, the string is empty. The session defaults apply. (For isolation level and access mode, these defaults are given by the session values of the
transaction_isolation
andtransaction_read_only
system variables.)If a transaction was explicitly started, the string contains the statement or statements required to restart the transaction with the same characteristics. As a general rule, this is a
START TRANSACTION
statement (possibly with one or more ofREAD ONLY
,READ WRITE
, andWITH CONSISTENT SNAPSHOT
). If any characteristics apply that cannot be passed toSTART TRANSACTION
, such asISOLATION LEVEL
, a suitableSET TRANSACTION
statement is prepended (for example,SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION READ WRITE;
).-
If a transaction was not explicitly started, but one-shot characteristics that apply only to the next transaction were set up, a
SET TRANSACTION
statement suitable for replicating that setup is generated (for example,SET TRANSACTION READ ONLY;
).Next-transaction characteristics can be set using
SET TRANSACTION
without anyGLOBAL
orSESSION
keyword, or by setting thetransaction_isolation
andtransaction_read_only
system variables using the syntax that applies only to the next transaction:SET @@transaction_isolation = value; SET @@transaction_read_only = value;
For more information about transaction characteristic scope levels and how they are set, see Transaction Characteristic Scope.
To enable notifications for this tracker type, set the
session_track_transaction_info
system variable toCHARACTERISTICS
(which also enables theSESSION_TRACK_TRANSACTION_STATE
tracker type).Transaction characteristics tracking enables the client to determine how to restart a transaction in another session so it has the same characteristics as in the original session.
Because characteristics may be set using
SET TRANSACTION
before a transaction is started, it is not safe for the client to assume that there are no transaction characteristics if no transaction is active. It is therefore unsafe not to track transaction characteristics and just switch the connection when no transaction is active (whether this is detected by the transaction state tracker or the traditionalSERVER_STATUS_IN_TRANS
flag). A client must subscribe to the transaction characteristics tracker if it may wish to switch its session to another connection at some point and transactions may be used.The characteristics tracker tracks changes to the one-shot characteristics that apply only to the next transaction. It does not track changes to the session variables. Therefore, the client additionally must track the
transaction_isolation
andtransaction_read_only
system variables to correctly determine the session defaults that apply when next-transaction characteristic values are empty. (To track these variables, list them in the value of thesession_track_system_variables
system variable.) -
SESSION_TRACK_TRANSACTION_STATE
: This tracker type indicates that transaction state information is available.data
is a string containing ASCII characters, each of which indicates some aspect of the transaction state.length
is the string length (always 8).To enable notifications for this tracker type, set the
session_track_transaction_info
system variable toSTATE
.Transaction state tracking enables the client to determine whether a transaction is in progress and whether it could be moved to a different session without being rolled back.
The scope of the tracker item is the transaction. All state-indicating flags persist until the transaction is committed or rolled back. As statements are added to the transaction, additional flags may be set in successive tracker data values. However, no flags are cleared until the transaction ends.
Transaction state is reported as a string containing a sequence of ASCII characters. Each active state has a unique character assigned to it as well as a fixed position in the sequence. The following list describes the permitted values for positions 1 through 8 of the sequence:
-
Position 1: Whether an active transaction is ongoing.
T
: An explicitly started transaction is ongoing.I
: An implicitly started transaction (autocommit=0
) is ongoing._
: There is no active transaction.
-
Position 2: Whether nontransactional tables were read in the context of the current transaction.
r
: One or more nontransactional tables were read._
: No nontransactional tables were read so far.
-
Position 3: Whether transactional tables were read in the context of the current transaction.
R
: One or more transactional tables were read._
: No transactional tables were read so far.
-
Position 4: Whether unsafe writes (writes to nontransactional tables) were performed in the context of the current transaction.
w
: One or more nontransactional tables were written._
: No nontransactional tables were written so far.
-
Position 5: Whether any transactional tables were written in the context of the current transaction.
W
: One or more transactional tables were written._
: No transactional tables were written so far.
-
Position 6: Whether any unsafe statements were executed in the context of the current transaction. Statements containing nondeterministic constructs such as
RAND()
orUUID()
are unsafe for statement-based replication.s
: One or more unsafe statements were executed._
: No unsafe statements were executed so far.
-
Position 7: Whether a result set was sent to the client during the current transaction.
S
: A result set was sent._
: No result sets were sent so far.
-
Position 8: Whether a
LOCK TABLES
statement is in effect.L
: Tables are explicitly locked withLOCK TABLES
._
:LOCK TABLES
is not active in the session.
Consider a session consisting of the following statements, including one to enable the transaction state tracker:
1. SET @@SESSION.session_track_transaction_info='STATE'; 2. START TRANSACTION; 3. SELECT 1; 4. INSERT INTO t1 () VALUES(); 5. INSERT INTO t1 () VALUES(1, RAND()); 6. COMMIT;
With transaction state tracking enabled, the following
data
values result from those statements:1. ________ 2. T_______ 3. T_____S_ 4. T___W_S_ 5. T___WsS_ 6. ________
-
The following example shows how to call
mysql_session_track_get_first()
and
mysql_session_track_get_next()
to retrieve and display all available session state-change
information following successful execution of an SQL statement
string (represented by stmt_str
). It is
assumed that the application has set the
session_track_
system variables that enable the notifications it wishes to
receive.
xxx
printf("Execute: %s\n", stmt_str);
if (mysql_query(mysql, stmt_str) != 0)
{
fprintf(stderr, "Error %u: %s\n",
mysql_errno(mysql), mysql_error(mysql));
return;
}
MYSQL_RES *result = mysql_store_result(mysql);
if (result) /* there is a result set to fetch */
{
/* ... process rows here ... */
printf("Number of rows returned: %lu\n",
(unsigned long) mysql_num_rows(result));
mysql_free_result(result);
}
else /* there is no result set */
{
if (mysql_field_count(mysql) == 0)
{
printf("Number of rows affected: %lu\n",
(unsigned long) mysql_affected_rows(mysql));
}
else /* an error occurred */
{
fprintf(stderr, "Error %u: %s\n",
mysql_errno(mysql), mysql_error(mysql));
}
}
/* extract any available session state-change information */
enum enum_session_state_type type;
for (type = SESSION_TRACK_BEGIN; type <= SESSION_TRACK_END; type++)
{
const char *data;
size_t length;
if (mysql_session_track_get_first(mysql, type, &data, &length) == 0)
{
/* print info type and initial data */
printf("Type=%d:\n", type);
printf("mysql_session_track_get_first(): length=%d; data=%*.*s\n",
(int) length, (int) length, (int) length, data);
/* check for more data */
while (mysql_session_track_get_next(mysql, type, &data, &length) == 0)
{
printf("mysql_session_track_get_next(): length=%d; data=%*.*s\n",
(int) length, (int) length, (int) length, data);
}
}
}