5.4.74 mysql_session_track_get_first()

int
mysql_session_track_get_first(MYSQL *mysql,
                              enum enum_session_state_type type,
                              const char **data,
                              size_t *length)

Description

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_xxx, such as 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 to mysql_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 the enum_session_state_type enumeration defined in mysql_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 and SESSION_TRACK_END symbols are defined to be equal to the first and last members of the enum_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 a const char * variable. Following a successful call, this variable points to the returned data, which should be considered read only.

• length: The address of a size_t variable. Following a successful call, this variable contains the length of the data pointed to by the data 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 and length is the string length. For the second call, data is a string containing the variable value and length is the string length.

  By default, notification is enabled for these session system variables:

  • autocommit

  • character_set_client

  • character_set_connection

  • character_set_results

  • time_zone

  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, set session_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', not 0x01).

  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 and transaction_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 of READ ONLY, READ WRITE, and WITH CONSISTENT SNAPSHOT). If any characteristics apply that cannot be passed to START TRANSACTION, such as ISOLATION LEVEL, a suitable SET 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 any GLOBAL or SESSION keyword, or by setting the transaction_isolation and transaction_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 to CHARACTERISTICS (which also enables the SESSION_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 traditional SERVER_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 and transaction_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 the session_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 to STATE.

  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() or UUID() 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 with LOCK 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. ________

Return Values

Zero for success. Nonzero if an error occurred.

Errors

None.

Example

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_xxx system variables that enable the notifications it wishes to receive.

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);
    }
  }
}