WL#4797: Extending protocol's OK packet

Affects: Server-5.7   —   Status: In-Documentation   —   Priority: Medium

For several commands (e.g. SET NAMES, SET character_set, USE database, SET
SQL_MODE) the client receives an ok packet only without information about server
status changes: e.g. after SET NAMES big5 the server assumes that the client
will send big5 encoded data, while the client character set is still latin1.

Proposal: extend the OK packet to allow server to send more information.

List of server changes we need to transmit:
- Session system variables
- current database (schema)
- info is sent as a lenenc string when CLIENT_SERVER_STATE_CHANGE
  is supported. Else the previous format is followed.

Functional & Non-Functional Requirements
========================================

Func-req. (1.1) It shall be possible for a client to enable/disable
                the tracking of change in the value of any of the existing
                session system variables.

Func-req. (1.2) It shall be possible for a client to enable/disable the
                tracking of change in current schema.

Func-req. (1.3) 'session_track_system_variables' must be a read/write system
                variable of string type.

Func-req. (1.4) 'session_track_system_variables' must have both global as well
                as session scopes.

Func-req. (1.5) 'session_track_system_variables' must be settable at command
                line.

Func-req. (1.6) 'session_track_system_variables' must accept a comma-separated
                list of valid session system variable names.

Func-req. (1.7) 'session_track_system_variables' must default to :
                "time_zone,autocommit,character_set_client,
                 character_set_results,character_set_connection"

Func-req. (1.8) In case an invalid session system variable name is specified
                while setting session_track_system_variables, the SET command
                would throw the following warning and accept the specified
                value:
                "Warning 1231 At least one of the specified names is not a
                 valid system variable."

Func-req. (1.9) The value of 'session_track_system_variables' must be
                accessible through 'SHOW VARIABLES' command and INFORMATION_
                SCHEMA's SESSION_VARIABLES/GLOBAL_VARIABLES tables.

Func-req. (1.10) 'session_track_schema' must be a read/write system variable
                 of boolean type.

Func-req. (1.11) 'session_track_schema' must have both global as well as
                 session scopes.

Func-req. (1.12) 'session_track_schema' must be settable at command line.

Func-req. (1.13) 'session_track_schema' must default to ON/true.

Func-req. (1.14) The value of 'session_track_schema' must be accessible
                 through 'SHOW VARIABLES' command and INFORMATION_SCHEMA's
                 SESSION_VARIABLES/GLOBAL_VARIABLES tables.

Func-req. (1.15) Old-clients must be able to interact with the new-server
                 that has this feature.

Func-req. (1.16) Old server must be able to interact with the new-clients
                 that supports this feature.

Appended requirements:

Func-req. (1.17) All the variables must be tracked when * is provided for
                 @@session_track_system_variables.

Func-req. (1.18) '*' will be considered as any other invalid value when
                 accompanied by other variables. Please check the HLS for
                 corresponding queries.
** Protocol-related changes **

Here 'n' denotes the length of state change information.

OK packet Payload (with new fields)
===================================

    Bytes                Name
    -----                ----
    1                    [00] the OK header
    1-9 (lenenc-int)     affected rows
    1-9 (lenenc-int)     last-insert-id
if capabilities & CLIENT_PROTOCOL_41 {
    2                    status_flags
    2                    warnings
} elseif capabilities & CLIENT_TRANSACTIONS {
    2                    status_flags
}
..................... New additions ........................
if capabilities & CLIENT_SESSION_TRACK {
    1-9 (lenenc_str)     info
if n > 0 {
    1-9 (lenenc_int)     total length of session state change
                         information to follow (= n)
    n                    session state change information
}
}
..........................................................
else {
    string[EOF]          info
}


What state changes do we need to transmit ?
===========================================

As part of this worklog, only the changes in session system
variables would be tracked along with the changes to 'current
schema'.

Note: If no state change information is sent, then the total length
      of session state change would be [0] followed by no change
      information. 


Definition of 'change'
======================

An entity is said to have changed if its value has been
updated (or touched) either with a different value or a
value that it already held. Said that, setting the value
of an entity with the value that it already held would be
considered as a 'change'.

For example, the following commands would trigger a change:
(1) USE test  
    -- Changes the current schema.
(2) SET NAMES 'utf8'
    -- Changes the values of
       @@session.character_set_client,
       @@session.character_set_results and
       @@session.character_set_connection
(3) SET @@session.sql_mode='ANSI';
    -- Changes the value of @@session.sql_mode
(4) SET @@session.sql_mode=@@session.sql_mode;
    -- Self-assignment, where the value of @@session.sql_mode
       has been touched.


Different attributes that contribute to a server session state
==============================================================

A session state can depend on multiple factor, viz. session system
variables, user-defined variables, temporary tables, PS, etc.

* Presence of SERVER_STATE_CHANGED flag in the response packet
  states that at least one of the above factors has changed.


Storage format for different state types
========================================

A response packet can carry information on changes in
multiple state types, each of which would be packed &
sequenced in the following format :

  [< session-state-type >][< length >][<             data                 >]
  [         1-byte       ][ 1-9 byes ][ session_state_type specific format ]

  Where :
   (i) 'session-state-type' is an enumerated value that associates the
        transmitted change-information to a particular session state
        type (or class).

        enum enum_session_state_type (
          SESSION_SYSVARS_TRACKER,
          CURRENT_SCHEMA_TRACKER /* for 'current schema' */
        );

        Note : The reason for having a separate state for schema
               is because, in server, current schema is not stored
               as a system variable.

   (ii) 'length' is size of the actual change-information of a single
        state change entity of a particular type. This field also makes the
        addition of new session_state_types future-safe for clients
        which do not have the capability to handle those newer types,
        i.e. if a client receives a state change entity of a particular
        type which it doesn't support, all it has to do is ignore the entity
        by moving past an amount equal to the length.

   (iii) 'data' is the information (content) that represents the actual
         change. For example, for SESSION_SYSVARS_TRACKER type the data
         would be the name & new-value of the changed system variable and,
         for CURRENT_SCHEMA_TRACKER, the name of changed current schema.
         Hence, each session state type can define different formats for
         their data field.

         Data format for 2 session_state_type's introduced with this
         worklog are :

         (a) SESSION_SYSVARS_TRACKER

               [<name (lenenc-string)><value (lenenc-string)>]

         (b) CURRENT_SCHEMA_TRACKER

               [<new schema name (lenenc-string)>]

For example, lets say following 2 (imaginary) system variables have
changed :
   name  new_value
   ----  ---------
   var1  foo       (changed entity #1)
   var2  bar       (changed entity #2)

So, the packed information (based on the given format) would look like:
   
{[22]} {[0]} {[10]} {[4][var1] [3][foo]}  {[0]} {[10]} {[4][var2] [3][bar]}
   |    ---   ----  --------------------   ---   ----   ------------------
   |     |      |           |               |      |             |
   |     |      |           `- content(#1)  |      |             `- content(#2)
   |     |      `------------- length (#1)  |      `--------------- length (#2)
   |     `-------------------- type   (#1)  `      `--------------- type   (#2)
   ` total length of session state change information



** Server-side changes **


New Server Status flag
======================

#define SERVER_STATE_CHANGED (1UL << 14)

Presence of this status infers that one of the state information
has changed on the server because of the execution of the last
statement.


New System Variables
====================

(1) session_track_system_variables

  Name                : session_track_system_variables
  Scope               : Global/Session
  Value               : A comma-separated list of valid
                        system variables.
  Default             : time_zone,autocommit,character_set_client,
                        character_set_results,character_set_connection
  Command line option : Yes

  * Providing an invalid system variable would result in
    a warning :
    "At least one of the specified names is not a valid system variable."

    Note: It is possible that a user specifies a valid system variable from
    a valid MySQL plugin (say validate_password) which has not been installed
    yet. This will result in a warning but the tracker list itself will hold
    this plugin system variable, to be tracked in future when the plugin gets
    installed.

  * On providing an invalid system variable during server start up, the boot
    will not succeed and throws an error instead.

  * This variable would accept only a list of valid system variables
    to track changes in system variables for a session.
    For example,

    mysql> set @@session_track_system_variables='sql_mode, autocommit';

  * Once set, server will start tracking & reporting the
    changes to the entities specified by this variable.

  * Setting the variable again would reset the old value i.e. its not
    cumulative.

  * If all the system variables available are to be tracked, all of them
    need not be provided as comma separated values. Instead, it will
    suffice if an astrik(*) is provided for @@session_track_system_variables.

    SET @@session_track_system_variables = '*';

    This will work only if * is provided and it is not accompanied by
    any other string.

    For ex:

    SET @@session_track_system_variables = 'autocommit, * , old_passwords';

    The above will throw a warning that * is an invalid system variable
    and only autocommit and old_passwords will be tracked.

(2) session_track_schema

  Name                : session_track_schema
  Scope               : Global/Session
  Value               : Bool
  Default             : On
  Command line option : Yes


How state changes would be packed in OK packet ?
================================================

The change information would be stored in Ok packet
and sent in the following format :

[< total run length >][[<session change information #1 >] ... ]
          |
          `--- total length of session state change information that follows


Backward/Cross Compatibility
============================

Backward/Cross compatibility should be guaranteed by the use of
above new capability and status flags.

For instance :
1) Old client <-> New server
   As the old client will not advertise CLIENT_SESSION_TRACK
   capability, the server, also, will not send the state change
   information to the client.

2) New client <-> old server
   As the old server cannot set SERVER_STATE_CHANGED flag, the
   new client should not expect state change info in the received
   OK packet.



** Client-side changes(libmysql) **

New client capability flag
==========================

#define CLIENT_SESSION_TRACK (1UL << 23)

The presence of this flag denotes that the client is capable
of accepting server state change information, i.e. the server
would send the information about the state change only if this
capability is advertised.


New libmysql APIs
=================

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

  This function would fetch the first state change information
  received from the server.

  @param mysql  [IN]        Pointer to the connection handle.
  @param type   [IN]        Type of the change information.
  @param data   [OUT]       Buffer to store the data.
  @param length [OUT]       Length of the data.

  @return
    0 - Valid data stored
    1 - No data


2) int
   mysql_session_track_get_next(MYSQL *mysql,
                               enum session_state_type type,
                               const char **data,
                               size_t *length)

  This function would fetch the subsequent state change information
  received from the server on every call.

  @param mysql  [IN]        Pointer to the connection handle. 
  @param type   [IN]        Type of the change information.
  @param data   [OUT]       Buffer to store the data.
  @param length [OUT]       Length of the data.

  @return
    0 - Valid data stored
    1 - No data


   For example, following code snippet can be used to iterate over
   all the change-information received in last OK packet.

  for (type= SESSION_SYSVARS_TRACKER; type < SESSION_TRACKER_END; type ++)
  {
    if (!mysql_session_track_get_first(mysql,
                                      (enum session_state_type) type,
                                      &data, &data_length))
    {
      some_op(data, data_length);
    }
    else
      continue;
    while (!mysql_session_track_get_next(mysql,
                                        (enum session_state_type) type,
                                        &data, &data_length))
    {
      some_op(data, data_length);
    }
  }

Note : Based on the state change information received, the following
       members of st_mysql will also be updated :

       (1) charset - client character set
       (2) db - current database

** Addition to mysqltest binary **

In order to enable the testing of this feature via MTR framewrok, the following
two mysqltest commands has been introduced to enable/disable inclusion of state
change information from OK packets in the test result.

1) enable_session_track_info :
     When executed, this command enables inclusion of state change information
     received via OK packets in the test result.

2) disable_session_track_info :
     When executed, this command disables inclusion of state change information
     received via OK packets in the test result.