WL#4797: Extending protocol's OK packet
Affects: Server-5.7
—
Status: In-Documentation
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
[]
(b) CURRENT_SCHEMA_TRACKER
[]
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 >][[] ... ]
|
`--- 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.
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.