WL#7766: Deprecate the EOF packet

Affects: Server-5.7   —   Status: Complete   —   Priority: Medium

EOF and OK packets serve the same purpose (to mark the end of a query execution
result. 
Yet they're different and this makes it a little redundant.
In order to benefit from all of the recent enhancements to the OK packet we need
to either redo them in the EOF packet or replace it with an OK packet. 
This worklog is about doing the latter. 
* FR1 : Keep the protocol backward compatible, i.e. send EOF packets to
non-supporting clients and servers. 
* FR2 : Have clients and servers provide one additional capability flag to
denote their support for EOF packet deprecation.
* FR3 : Any sql statement which produces result set will send OK packet as 
a marker to indicate end of data rather than EOF packet.
Consider below example where we create a function in which there exists a 
session context. When we call this function using a SELECT statement OK packet
is sent which propagates session information.

delimiter |;
create function f3() returns int
begin
create temporary table t4 (id int);
return 10 < 20;
end;|
delimiter ;|
set session_track_state_change=ON;
select f3();
f3()
1
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

* FR4 : All the session state information which is sent as part of OK packet
will be sent only for that particular sql statement which produces the result 
set.
Consider below example where we create a procedure f10(). In f10() just before 
SELECT 1 is executed there is a session state. This session state information 
is sent as part of result sets associated with SELECT 1 rather than with other 
sql statement which follow.

create procedure f10()
begin
create temporary table t5 (id int);
select 1;
select 2;
end;|
set session_track_state_change=ON;
call f10();
1
1
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

2
2

* FR5 : OUT parameters associated with stored procedures will be terminated
with OK packet.
* FR6 : Old clients always expect EOF as part of result sets when talking to
new server.
* FR7 : New server will always send EOF as part of result sets when talking to
old client.
* FR8 : Binary result sets are same as protocol_text result sets and hence
these result sets will also be terminated with OK packet.
* FR9 : For embedded server there will be no change.
New implementation w.r.t this WL:
---------------------------------
Recent extensions to OK packet contains more additional information which might
be needed to clients and connectors even when EOF packet is sent. Since it would
be redundant to apply the same extensions in EOF packet, this WL will take care
of handling the changes to deprecate EOF packet and send OK packet wherever needed. 
Ok packet contains all the information which is present in EOF packet too.
Contents of EOF packet include packet marker, server status and warning count.
This information can also be extracted from OK packet along with more 
additional information if needed.

With this WL EOF packet will no longer be used.

Identifying the packet:
-----------------------
If buff is considered to be the packet received from server then,
if buff[0] == 0 and length of buff is greater than 7 bytes then its an
OK packet.
if buff[0] == 254 and length of buff is less than 9 bytes then its an
EOF packet.

OK packet identifier:
---------------------
With this WL OK packet identifier i.e the first byte which is 0x00 will be 
replaced with 0xFE which is the identifier for EOF packet, only in case 
where result sets are sent from server to client. This changes are done as
the existing OK packet identifier 0 can be part of data and there
is no way to distinguish whether the received packet processed is a data row
or an OK packet.  

Protocol Changes:
-----------------
Metadata result set will no longer be terminated with EOF packet as the field
count information present will be enough for client to process the metadata.
Row result set(Text or Binary) will now be terminated with OK packet.

With this protocol change the OK packet can start either with 0x00 byte
or with 0xFE byte (before it could start only with 0x00 byte). Ok packet 
identified with 0xFE will be sent by server only as part of row result sets.
Server will never send OK packet longer than 16777216 bytes thus limiting
size of OK packet to be 16777215 bytes. If OK packet length exceeds this 
limitation then an error will be returned.
In all other situations where server sends OK packet (including reply to
a query which does not produce result sets), it will start with 0x00 as before.

Backward Compatibility:
A new capability flag CLIENT_DEPRECATE_EOF will be introduced to ensure
backward compatibility.

For instance :
1) Old client <-> New server 
   Here client can never advertise its capability CLIENT_DEPRECATE_EOF and
   hence server will never send OK packet instead of EOF packet.
2) New client <-> old server
   Here server will never send OK packet as a replacement to EOF packet.

Example1:
--------

mysql> create table t1(i int, j char(10));
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t1 values (1,'abc'), (2,'def');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delimiter |
mysql> create procedure t1_sel()
    -> begin
    -> select * from t1;
    -> end |
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call t1_sel();
+------+------+
| i    | j    |
+------+------+
|    1 | abc  |
|    2 | def  |
+------+------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

In the above example when t1_sel() procedure is called following information
is sent from server to client:
1.result set metadata followed by EOF packet.
2.result set followed by EOF packet.
3.OK packet.
With this WL changes will be as shown below:
1.result set metadata.
2.result set followed by OK packet (This OK packet is identified with 0xFE).
3.OK packet (The final OK packet for CALL statement).
Final OK packet is identified with 0x00.

Example2: In case of multiple result sets
-----------------------------------------

mysql> delimiter |
mysql> create procedure t1t2_sel()
    -> begin
    -> select * from t1;
    -> select * from t2;
    -> end |
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call t1t2_sel();
+------+------+
| i    | j    |
+------+------+
|    1 | abc  |
|    2 | def  |
+------+------+
2 rows in set (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

In the above example when t1t2_sel() procedure is called following information
is sent from server to client:
1.result set metadata for table t1 followed by EOF packet.
2.result set for table t1 followed by EOF packet.
3.result set metadata for table t2 followed by EOF packet.
4.empty result set for table t2 followed by EOF packet.
5.OK packet.
Again this WL will replace EOF with OK packet as shown below:
1.result set metadata for table t1.
2.result set for table t1 followed by OK packet.
3.result set metadata for table for table t2.
4.empty result set for table t2 followed by OK packet.
5.OK packet.

OK packet which are sent as part of result set row are identified with 0xFE.
OK packet which is sent as part of result set of table t1 will have the 
SERVER_MORE_RESULTS_EXIST flag so that the next result sets can be processed.

Example3: In case where there exists a session state:
-----------------------------------------------------

mysql> create procedure f10()
    ->  begin
    ->    create temporary table t5 (id int);
    ->    select 1;
    ->    select 2;
    -> end;|
Query OK, 0 rows affected (3.33 sec)

mysql> set serack_state_change=ON;|
Query OK, 0 rows affession_tcted (2.92 sec)

mysql> call f10();|
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (5.64 sec)

+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (7.64 sec)

Query OK, 0 rows affected (10.00 sec)

In above example OK packet which is sent as part of SELECT 1 will have the 
session state information.

Similarly stored procedures with OUT parameters which are sent as a result
set row to client will be terminated with OK packet.

Example4: In case of cursors:
-----------------------------

#define SELECT_SAMPLE "SELECT * FROM test_table"

MYSQL_STMT *stmt;
int rc;
unsigned long type;
unsigned long prefetch_rows = 5;

stmt = mysql_stmt_init(mysql);

rc= mysql_stmt_prepare(stmt, SELECT_SAMPLE, (ulong)strlen(SELECT_SAMPLE));
/* ... check return value ... */
type = (unsigned long) CURSOR_TYPE_READ_ONLY;
rc = mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (void*) &type);
/* ... check return value ... */
rc= mysql_stmt_execute(stmt);

In above example with existing code when COM_STMT_EXECUTE response is received 
from server the result set has <metadata><EOF> this <EOF> packet has the flag
SERVER_STATUS_CURSOR_EXISTS. Existence of this flag is checked in client and 
then client sends COM_STMT_FETCH command to retrieve the result set rows. Once 
all the data is sent server will send final EOF packet with flag 
SERVER_STATUS_LAST_ROW_SENT. 

With this WL the sequence will be as follows after PREPARE:
(a) Client sends COM_STMT_EXECUTE with stmt->flags set to
    CURSOR_TYPE_READ_ONLY.
(b) Server response by opening cursor and sends COM_STMT_EXECUTE response whose
    format is as follows: <metadata><OK>
(c) Client reads metadata result set by calling cli_read_rows().
    In cli_read_rows() client reads metadata row followed by reading OK packet 
    as shown below:

cli_read_rows()
{
  ...
  while (field_count > 0 )
  {
     read column meta-data;
     field_count--;
  }
  while (!OK packet)
  {
    read row data;
  }
  if(stmt->flags & CURSOR_TYPE_READ_ONLY)
  {
    read OK packet;
  }
  if (SERVER_STATUS_CURSOR_EXISTS flag set in OK packet)
  {
    use cursor to read rows;
  } 
  ...
}

(d) Client checks for this flag SERVER_STATUS_CURSOR_EXISTS and sends 
    COM_STMT_FETCH to fetch the data. 
(e) Once all the data packets are sent by server the last OK packet which is 
    sent as part of result set row will have the SERVER_STATUS_LAST_ROW_SENT 
    flag.


Server side changes:
--------------------
In MySQL there are two types of SQL statements:
case1: SQL statements that send only status information.
case2: SQL statements that produce result sets.

In case1 any status information is followed by OK packet.
In case2 result sets are of two parts 
(1). result set metadata followed by EOF packet
(2). result set itself followed by EOF packet
With this WL all the SQL statements which send result sets which represents a 
data row will be modified to send OK packet in place of EOF packet and hence
OK packet will be considered as a terminator to result sets.
Note: Even in case of multiple result sets or for prepared statements with
binary result sets the above is same.

In case of embedded server there will be no change.

Client side changes:
--------------------
Clients which expect EOF packet as a marker to terminate the results sets will
be modified to check for OK packet as a terminator. Client will read the needed
information like server status and warnings count from OK packet and set it in	
the MYSQL structure accordingly. In case of huge data packet with length greater
than 16777216L client will treat it as a data packet and process accordingly.


Code changes description:
=========================
With this WL the server will always send OK packet(based on client capability)
as part of result sets (data) as an indication to end of data.

Server side changes include:
1. In Protocol::send_eof() check for client compatibility and call net_send_ok() 
   so that OK packet is sent instead of EOF packet.
2. Based on client capability skip call to write_eof_packet in                                            
   Protocol::send_result_set_metadata().
3. In net_send_ok() check if the OK packet being sent is part of result sets
   then set buff[0]=254.

Client side changes include:
1. Since OK packet is sent as part of result sets, in client once all the 
   data rows are processed read the server_status and warning_count 
   appropriately from the OK packet.


Code changes in include/mysql_com.h, libmysql/client_settings.h,
sql/client_settings.h
================================================================

Introduce capability flag to ensure backward compatibility.
#define CLIENT_DEPRECATE_EOF (1UL << 24)
Add this capability to CLIENT_CAPABILITIES and CLIENT_ALL_FLAGS

Code changes in sql-common/client.c
===================================

This below function will read the result set sent from server

MYSQL_DATA *cli_read_rows(MYSQL *mysql,MYSQL_FIELD *mysql_fields,
                          unsigned int fields)
{

...
..
This below loop reads all the result set till it reaches EOF packet.
With this WL since OK packet is sent its length will be 8 and hence the 
condition has to be changed so that the loop is terminated correctly when OK 
packet is read.

-  while (*(cp=net->read_pos) != 254 || pkt_len >= 8)
+  while (*(cp=net->read_pos) != 254)


Once the terminating packet is received since its OK packet, read the 
server_status and warning_count correctly from the packet.

+  if (net->read_pos[0] == 254)
   {
     if (pkt_len > 1)				/* MySQL 4.1 protocol */
     {
-      mysql->warning_count= uint2korr(net->read_pos+1);
+      if(mysql->server_capabilities & CLIENT_DEPRECATE_EOF)
+        mysql->warning_count= uint2korr(net->read_pos+5);
+      else
+        mysql->warning_count= uint2korr(net->read_pos+1);
       mysql->server_status= uint2korr(net->read_pos+3);
     }

Code changes in libmysql/libmysql.c
===================================

Below code changes are made to ensure that when processing 
result sets if OK packet is read terminate the loop.

int cli_read_binary_rows(MYSQL_STMT *stmt)
{
   while ((pkt_len= cli_safe_read(mysql)) != packet_error)
   {
   ..
-    if (cp[0] != 254 || pkt_len >= 8)
+    if (cp[0] != 254)
     {
..
}

Code changes in sql/protocol.cc
===============================

Below changes are done in net_send_ok() in order to reuse the EOF packet tag
in OK packet only if this packet is sent as part of result sets. 

eof_identifier represents when to use EOF identifier in OK packet.

 bool
 net_send_ok(THD *thd,
             uint server_status, uint statement_warn_count,
-            ulonglong affected_rows, ulonglong id, const char *message)
+            ulonglong affected_rows, ulonglong id, const char *message,
+            bool eof_identifier)

{
.... 
+  /* reuse EOF packet identifier only in case of result sets */
+  if(eof_identifier && thd->client_capabilities & CLIENT_DEPRECATE_EOF)
+    buff[0]= 254;
+

Below changes are done to send OK packet instead of EOF packet based on
capability flag.

bool Protocol::send_eof(uint server_status, uint statement_warn_count)
 {
   DBUG_ENTER("Protocol::send_eof");
-  const bool retval= net_send_eof(thd, server_status, statement_warn_count);
+  bool retval;
+  if(thd->client_capabilities & CLIENT_DEPRECATE_EOF &&
+     (thd->get_command() != COM_BINLOG_DUMP &&
+      thd->get_command() != COM_BINLOG_DUMP_GTID))
+    retval= net_send_ok(thd, server_status, statement_warn_count, 0, 0, NULL, 
TRUE);
+  else
+    retval= net_send_eof(thd, server_status, statement_warn_count);
   DBUG_RETURN(retval);
 }


This below function will send result set metadata along with EOF packet as
a terminator. Code changes are done to send OK packet after checking for 
CLIENT_DEPRECATE_EOF flag.

bool Protocol::send_result_set_metadata(List<Item> *list, uint flags)
{
..
   if (flags & SEND_EOF)
   {
+    /* if it is new client do not send EOF packet */
+    if(thd->client_capabilities & CLIENT_DEPRECATE_EOF);
     /*
       Mark the end of meta-data result set, and store thd->server_status,
       to show that there is no cursor.
       Send no warning information, as it will be sent at statement end.
     */
-    if (write_eof_packet(thd, &thd->net, thd->server_status,
+    else if (write_eof_packet(thd, &thd->net, thd->server_status,
                          thd->get_stmt_da()->current_statement_cond_count()))
       DBUG_RETURN(1);

}

Below changes are done to ensure that OUT parameters which are sent as a result
set are terminated with EOF.

bool Protocol_binary::send_out_parameters(List<Item_param> *sp_params)
{
....
-  /* Restore THD::server_status. */
-  thd->server_status&= ~SERVER_PS_OUT_PARAMS;
-
   /*
-    Reset SERVER_MORE_RESULTS_EXISTS bit, because this is the last packet
-    for sure.
+    OUT params of SP are passed to client as a result set and hence
+    terminate this result set with an OK packet.
   */
-  thd->server_status&= ~SERVER_MORE_RESULTS_EXISTS;
-
-  /* Send EOF-packet. */
-  net_send_eof(thd, thd->server_status, 0);
-
+  if (thd->client_capabilities & CLIENT_DEPRECATE_EOF)
+  {
+    if(net_send_ok(thd, thd->server_status,
+                   thd->get_stmt_da()->current_statement_cond_count(),
+                   0, 0, NULL, TRUE))
+      return FALSE;
+    /* Restore THD::server_status. */
+    thd->server_status&= ~SERVER_PS_OUT_PARAMS;
+    thd->server_status&= ~SERVER_MORE_RESULTS_EXISTS;
+  }
+  else
+  {
+    /* Restore THD::server_status. */
+    thd->server_status&= ~SERVER_PS_OUT_PARAMS;
+
+    /*
+      Reset SERVER_MORE_RESULTS_EXISTS bit, because this is the last packet
+      for sure.
+    */
+    thd->server_status&= ~SERVER_MORE_RESULTS_EXISTS;
+    /* Send EOF-packet. */
+    net_send_eof(thd, thd->server_status, 0);
+  }
   return FALSE;
 }