Client/Server Communication DO NOT USE

MOVED TO CODEWALKERS

Understanding MySQL; Written by Sasha Pachev; note – fix tables & empty spaces; Published by O’Reilly; Chapter 4



In this chapter we will discuss the details of the client/server communication in MySQL. The goal is to give you the ability to look at a binary dump of the client/server communication and be able to understand what happened. This chapter can also be helpful if you are trying to write a MySQL proxy server, a security application to audit MySQL traffic on your network, or some other program that for some reason needs to understand the low-level details of the MySQL client/server protocol.

Protocol Overview

The server listens for connections on a TCP/IP port or a local socket. When a client connects, a handshake and authentication are performed. If successful, the session begins. The client sends a command, and the server responds with a data set or a message appropriate for the type of command that was sent. When the client is finished, it sends a special command telling the server it is done, and the session is terminated.

The basic unit of communication is the application-layer packet. Commands consist of one packet. Responses may include several.

Packet Format

There are two types of packets: compressed and noncompressed. The decision on which one will be used for the session is made during the handshake stage, and depends on the capabilities and settings of both the client and the server.

Additionally, regardless of the compression option, the packets are divided into two categories: commands sent by the client, and responses returned by the server.

Server response packets are divided into four categories: data packets, end-of-data-stream packets, success report (OK) packets, and error message packets.

All packets share the common 4-byte header, documented in Table4-1 .

Table 4-1. Common 4-byte header for uncompressed packets

Offset LengthDescription
03Packet body length stored with the low byte first.
31

Packet sequence number. The sequence numbers are reset with each new command.

 

While the correct packet sequencing is ensured by the underlying transmission protocol, this field is used for the sanity checks of the application logic.

   

A compressed packet will have an additional 3-byte field, low byte first, containing the length of the compressed packet body part that follows. An uncompressed packet will have the body immediately after the header.

The compression is done with the use of ZLIB (see http://www.zlib.net). The body of the compressed packet is exactly what a call to compress() with the uncompressed body as argument would return. It is, however, possible for the body to be stored without compression when the compressed body would turn out no smaller than the uncompressed one, or when compress() fails for some reason—e.g., due to the lack of available memory. If this happens, the uncompressed length field will contain 0.

It is important to remember, though, that even in that case, the compressed format is still used, which unfortunately results in the waste of 3 bytes per packet. Therefore, a session that predominately uses small or poorly compressible packets goes faster if the compression is turned off.

As you may have noticed, the 3-byte field would limit the body length to 16 MB. What if you need to send a bigger packet? In version 3.23 and earlier, it is not possible. Version 4.0 added a compatible improvement to the protocol that overcame this limitation. If the length of the packet is greater than the value of MAX_PACKET_LENGTH , which is defined to be 224–1 in sql/net_serv.cc, the packet gets split into smaller packets with bodies of MAX_PACKET_LENGTH plus the last packet with a body that is shorter than MAX_PACKET_LENGTH . The last short packet will always be present even if it must have a zero-length body. It serves as an indicator that there are no more packet parts left in the stream for this large packet.

Relationship Between MySQL Protocol and OS Layer

If you try to run a network sniffer on the MySQL port, you will notice that sometimes several MySQL protocol packets are contained in one TCP/IP packet, and sometimes a MySQL packet spans several TCP/IP layer packets, while some fit into exactly one TCP/IP packet. If you somehow manage to intercept the local socket traffic, you will observe a similar effect. Some buffer writes will have exactly one packet, while others may contain several. If the lower-level socket-buffer write operation has a limit on the maximum number of bytes it can handle in one chunk, you may also see one MySQL packet being transferred in several buffer writes.

To understand the mechanics of this phenomenon, let’s examine the API the server or the client uses to send packets. Packets are put in the network buffer with a call to my_net_write() , defined in sql/net_serv.cc. When the buffer has reached capacity, its contents will be flushed, which results in an operating system write() call on the socket—or possibly a sequence of them if the contents of the buffer cannot be written into the socket in one operation. On the operating system level this may result in sending one or more packets, depending on how much it takes to accommodate the data volume under the operating system protocol constraints.

In some cases, the data in the network buffer needs to be sent to the client immediately. In that case, net_flush() , defined in sql/net_serv.cc, is called.

Authenticating Handshake

The session between a client and a server begins with an authenticating handshake. Before it can begin, the server checks whether the host that the client is connecting from is even allowed to connect to this server. If it is not, an error message packet is sent to the client notifying it that the host is not allowed to connect.

In the case of successful host verification, the server sends a greeting packet with the standard 4-byte header, the packet sequence number set to 0, and the body in the format shown in Table4-2.

Table 4-2. Fields of the server’s greeting packet

Offset in the body LengthDescription
01Protocol version number. Decimal 10 (0x0A) in recent versions. Although some changes were made in the protocol in versions 4.0 and 4.1, the protocol version number remained the same because the changes were fully backward-compatible.
1 ver_len = strlen (server_version) + 1 Zero-terminated server version string. The length is variable, and is calculated according to the formula in the Length column. The subsequent offsets are a function of the length of this field.
ver_len + 1 4Internal MySQL ID of the thread that is handling this connection. Low byte first.
ver_len + 5 9In version 4.0 and earlier, the random seed string in its entirety. In 4.1 and later, the first 8 bytes of the 20-byte random seed string. At the end is a terminating zero. Starting in version 4.1, the length of this field is controlled by the value of SCRAMBLE_LENGTH_323 , defined in include/mysql_com.h. In the earlier versions, the macro is SCRAMBLE_LENGTH , defined in sql/sql_parse.cc. With the terminating zero byte, the length of the string is one greater than the value of the macro.
ver_len + 14 2Server capabilities bit mask with the low byte first. See Table 4-5 later for the meaning of different bits.

Table 4-2. Fields of the server’s greeting packet (continued)

Offset in the body LengthDescription
ver_len + 16 1

Default character set code, or more precisely, the code of the default collation. A character set collation is a set of rules that defines a sequential order among characters. A list of available collations and their codes can be obtained by executing SHOW COLLATION LIKE ‘%’ in version 4.1.

ver_len + 17 2

The server status bit mask with the low byte first. Reports whether the server is in transaction or autocommit mode, if there are additional results from a multistatement query, or if a good index (or some index) was used for query optimization. For details, see the SERVER_* values in include/mysql_com.h.

ver_len + 19 13

Reserved for future use. Currently zeroed out.

ver_len + 32 13

Present only in version 4.1 and later. The rest of the random seed string terminated with a zero byte. The length is equal to the value of SCRAMBLE_LENGTH – SCRAMBLE_LENGTH_323 + 1 .

The client responds with a credentials packet. The format differs between versions up to and including 4.0, and versions 4.1 and later. Table 4-3 shows the format for the pre-4.1 era. Table 4-4 shows the format for versions 4.1 and later, if the client understands and is willing to use the 4.1 protocol.

Table 4-3. Fields of the client’s credentials packet, up to MySQL version 4.0

Offset in the body LengthDescription
02Protocol capabilities bit mask of the client, low byte first.
23Maximum packet length that the client is willing to send or receive. Zero values means the client imposes no restrictions of its own in addition to what is already there in the protocol.
5Varies; see description

Credentials string in following format: zero-terminated MySQL username, then if the password is not empty, scrambled password (8 bytes). This can be optionally followed by the initial database name, in which case a zero byte terminator is added immediately after the XOR encrypted password, followed by the database name string without a terminating zero byte.

Table 4-4. Fields of the client’s credentials packet, MySQL version 4.1 and later

Offset in the body LengthDescription
04Protocol capabilities bit mask of the client, low-byte first.
44

Maximum packet length that the client is willing to send or receive. Zero values means the client imposes no restrictions of its own in addition to what is already there in the protocol.

81Default character set (or more precisely, collation) code of the client.
923Reserved space; currently zeroed out.
32Varies; see description

Credentials string in the following format: zero-terminated username, then the length of the SHA1 encrypted password (decimal 20), followed by its value (20 bytes), which is optionally followed by the zero-terminated initial database name.

If the SSL capability option is enabled both on the client and on the server, the client will first send the initial part of the response packet without the credentials string. When the server receives it, it will see the SSL capability bit enabled in the capabilities mask, and know that it should expect the rest of the communication in SSL. The client switches to the SSL layer, and resends the entire response packet securely this time. It would be more efficient, of course, to not resend the initial part of the response, but, for historical reasons, this small overhead allowed the code on the server to stay fairly clean without thorough rework.

Once the server receives the credentials packet, it verifies the information. From this point, it can respond in three different ways:

  • If the check succeeds, the standard OK response packet is sent (for details, see the "Server Responses" section, later in the chapter).

  • If the credentials did not meet the expectations of the server, the standard error message response is sent.
  • The third possibility comes from the need to support the transition from 4.0 to 4.1. In some cases, the DBA may have upgraded both the client and the server to 4.1, but forgot or chose not to upgrade the user table in the mysql database, which contains user names and their respective password hashes. If the entry for that user has the old-style password hash, it is impossible to authenticate with the new authentication protocol.

    In that event, the server sends a special packet with the 1-byte-long body containing decimal 254, which means: “please send the authentication credentials in the old format.” The client responds with a packet whose body contains a zero-terminated encrypted password string. The server responds with either OK or a standard error message.

At this point the handshake is complete, and the client begins to issue commands.

Authentication Protocol Security

Neither the old nor the new protocol ever sends the user password across the connection in plain text. However, there are a number of weaknesses in the old protocol. First, knowing the value of the password hash allows the attacker to perform authentication without actually knowing the password. This is possible due to a flaw in the way the expected response to the challenge is computed—it is uniquely determined by the value of the password hash and the value of the challenge (for details, see scramble_323() and check_scramble_323() in sql/password.c). Therefore, if the attacker can get read access to the user table in the mysql database, or obtain the value of the stored password hash some other way, she will be able to authenticate with a specially modified version of the MySQL client library.

Second, even without having access to the hash, the correct password can be guessed in a small number of attempts if the attacker can intercept the authentication traffic between the client and the server on a few occasions. This is possible due to the weakness in the encryption method of the old protocol. The encryption is done using a home-cooked XOR procedure (see the scramble_323() function mentioned earlier), which lacks true cryptographic strength.

These weaknesses have been addressed in version 4.1. The authentication method now uses SHA1 hashes for encryption, which are much more resistant to cracking. Also, the changed challenge-verification algorithm removed the ability to authenticate by knowing just the value of the password hash rather than the actual password.

Despite the added improvements, do not feel complacent about the security of the new protocol. It is still recommended to block access to the MySQL port on the firewall, and if this is not possible, require the clients to use SSL.

Protocol Capabilities Bit Mask

During the authentication handshake, the client and the server exchange information on what the other is able or willing to do. This enables them to adjust their expectations of their peer and not send the data in some unsupported format. The exchange of information is accomplished through fields containing the bit mask of protocol capabilities.

The bit mask can be either 4 or 2 bytes long, depending on the context. The newer (4.1 and later) clients and servers understand 4-byte masks as well as 2-byte ones. The older (4.0 and earlier) ones can handle only 2-byte masks.

The server, regardless of the version, always announces its capabilities with a 2-byte bit mask. Although both newer clients and servers understand the 4-byte mask, the first packet in the dialog must be understood by any client regardless of the version. For this reason, even the newer clients expect the greeting packet to contain a 2-byte mask.

Once the client knows that it is talking to a newer server, it can announce its capabilities with a 4-byte mask. However, if the newer client detects that it is talking to an older server, it will announce the capabilities with only a 2-byte mask. Naturally, the older clients can only send a 2-byte mask; they are not aware of 4-byte ones.

Table 4-5 explains the meaning of the bits used in the capabilities’ bit mask. The values are defined in include/mysql_com.h.

Table 4-5. Protocol capability bits

Bit macro symbol Hex valueDescription
CLIENT_LONG_PASSWORD 0x0001

Apparently was used in the early development of 4.1 to indicate that the server is able to use the new password format.

CLIENT_FOUND_ROWS 0x0002Normally, in reporting the results of an UPDATE query, the server returns the number of records that were actually modified. If this flag is set, the server is being asked to report the number of records that were matched by the WHERE clause. Not all of those will necessarily be updated, as some may already contain the desired values.

Authenticating Handshake

Table 4-5. Protocol capability bits (continued)

Bit macro symbol

Hex value

Description

CLIENT_LONG_FLAG

0x0004

This flag will be set for all modern clients. Some old clients expect to receive only 1 byte of flags in the field definition record, while the newer ones expect 2 bytes. If this flag is cleared, the client is old and wants only 1 byte for field flags. This flag will also be set by the modern server to indicate that it is capable of sending the field definition in the new format with 2 bytes for field flags. Old servers (pre-3.23) will not report having this capability.

CLIENT_CONNECT_WITH_DB

0x0008

This flag is also set for all modern clients and servers. It indicates that the initial default database can be specified during authentication.

CLIENT_NO_SCHEMA

0x0010

If set, the client is asking the server to consider the syntax db_name.table_name.col_namean error. This syntax is normally accepted.

CLIENT_COMPRESS

0x0020

When set, indicates that the client or the server is capable of using the compressed protocol.

CLIENT_ODBC

0x0040

Apparently was created to indicate that the client is an ODBC client. At this point, it does not appear to be used.

CLIENT_LOCAL_FILES

0x0080

When set, indicates that the client is capable of uploading local files with LOAD DATA LOCALINFILE.

CLIENT_IGNORE_SPACE

0x0100

When set, communicates to the server that the parser should ignore the space characters between identifiers and subsequent ‘.’ or ‘(’ characters. This flag enables syntax such as: db_name .table_name or length (str) which would normally be illegal.

CLIENT_PROTOCOL_41

0x0200

When set, indicates that the client or the server is capable of using the new protocol that was introduced in version 4.1.

CLIENT_INTERACTIVE

0x0400

When set, the client is communicating to the server that it is accepting commands directly from a human. For the server, this means that a different inactivity timeout value should be applied. The server has two settings: wait_timout and interactive_timeout. The former is for regular clients, while the latter is for the interactive ones. This distinction was created to deal with applications using buggy persistent connection pools that would lose track of established connections without closing them first, keep creating new ones, and eventually overflow the server max_connectionslimit. The workaround was to set wait_timeoutto a low value that would disconnect the lost connections sooner. This, unfortunately, had a side effect of disconnecting interactive clients too soon, which was solved by giving them a separate timeout.

Table 4-5. Protocol capability bits (continued)

Bit macro symbol

Hex value

Description

CLIENT_SSL

0x0800

When set, indicates the capability of the client or the server to use SSL.

CLIENT_IGNORE_SIGPIPE

0x1000

Used internally in the client code in versions 3.23 and 4.0. SIGPIPEis a Unix signal sent to a process when the socket or the pipe it is writing to has already been closed by the peer. However, a thread in a threaded application on some platforms may get a SIGPIPEsignal spuriously under some circumstances. Versions 3.23 and 4.0 permit the client pro-grammer to choose whether SIGPIPEshould be ignored. Version 4.1 just blocks it during the client initialization and does not worry about the issue from that point on.

CLIENT_TRANSACTIONS

0x2000

When set in the packet coming from the server, indicates that the server supports transactions and is capable of report-ing transaction status. When present in the client packet, indicates that the client is aware of servers that support transactions.

CLIENT_RESERVED

0x4000

Not used.

CLIENT_SECURE_CONNECTION

0x8000

When set, indicates that the client or the server can authenti-cate using the new SHA1 method introduced in 4.1.

CLIENT_MULTI_STATEMENTS

0x10000

When set, indicates that the client can send more than one statement in one query, for example:

res = mysql_query(con,"SELECT a FROM t1 WHERE id =1; SELECT b FROM t1 WHERE id=3");

  

CLIENT_MULTI_RESULTS

0x20000

When set, indicates that the client can receive results from multiple queries in the same statement.

CLIENT_REMEMBER_OPTIONS

0x80000000

Internal flag used inside the client routines. Never sent to the server.

Command Packet

Once the authentication is complete, the client begins sending commands to the server using command packets. The body of a command packet is documented in Table 4-6.

Table 4-6. Format of client command packet

Offset in the body LengthDescription
01Command code.
1

For the noncompressed packet, total packet length from the header – 1. For the compressed packet, the compressed body length – 1.

The argument of the command, if present.

The command codes are contained in enum server_command , defined in include/mysql_com.h. The command-handling logic can be found in the switch statement of dispatch_command() in sql/sql_parse.cc.

Table 4-7 documents different types of commands with their codes and arguments.

Table 4-7. Client commands

Command code enum valueCode numeric valueArgument descriptionCommand description
COM_SLEEP 0No argument.Never sent by a client. Reserved for internal use.
COM_QUIT 1No argument.Tells the server to end the session. Issued by the client API call mysql_close() .
COM_INIT_DB 2A string containing the name of the database.Tells the server to change the default database for the session to the one specified by the argument. Issued by the client API call mysql_select_db() .
COM_QUERY 3A string containing the query.Tells the server to run the query. Issued by the client API call mysql_query() .
COM_FIELD_LIST 4A string containing the name of the table.Tells the server to return a list of fields for the specified table. This is an obsolete command still supported on the server for compatibility with old clients. Newer clients use the SHOW FIELDS query.
COM_CREATE_DB 5A string containing the name of the database.Tells the server to create a database with the specified name. This is an obsolete command still supported on the server for compatibility with old clients. Newer clients use the CREATE DATABASE query.
COM_DROP_DB 6A string containing the name of the database.Tells the server to drop the database with the specified name. This is an obsolete command still supported on the server for compatibility with old clients. Newer clients use the DROP DATABASE query.

Table 4-7. Client commands (continued) 

Command code enum valueCode numeric valueArgument descriptionCommand description
COM_REFRESH 7A byte containing the bit mask of reloading operations.Tells the server to refresh the table cache, rotate the logs, reread the access control tables, clear the host name lookup cache, reset the status variables to 0, clear the replication master logs, or reset the replication slave depending on the options in the bit mask. Issued by the client API call mysql_refresh() .
COM_SHUTDOWN 8No argument.Tells the server to shut down. Issued by the client API call mysql_shutdown() . 
COM_STATISTICS 9No argument.Tells the server to send back a string containing a brief status report. Issued by the client API call mysql_stat() .
COM_PROCESS_INFO 10No argument.Tells the server to send back a report on the status of all running threads. This is an obsolete command still supported on the server for compatibility with old clients. Newer clients use the SHOWPROCESSLIST query.
COM_CONNECT 11No argument.

Never sent by a client. Used for internal purposes.

COM_PROCESS_KILL  12A 4-byte integer with the low byte first containing the MySQL ID of the thread to be terminated.

Tells the server to terminate the thread identified by the argument. Issued by the client API call mysql_kill() . This is an obsolete command still supported on the server for compatibility with old clients. Newer clients use the KILL query.

COM_DEBUG 13No argument.

Tells the server to dump some debugging information into its error log. Issued by the client API call mysql_dump_ debug_info() .

COM_PING 14No argument.

Tells the server to respond with an OK packet. If the server is alive and reachable, it will. Issued by the client API call mysql_ping() .

Table 4-7. Client commands (continued)

Command code

 

 

 

enum value

Code numeric value

Argument description

Command description

COM_TIME

15

No argument.

Never sent by a client. Used for internal purposes.

COM_DELAYED_

INSERT

16

No argument.

Never sent by a client. Used for internal purposes.

COM_CHANGE_USER

17

A byte sequence in the following format: zero-terminated user name, encrypted password, zero-terminated default database name.

Tells the server the client wants to change the user associated with this session. Issued by the client API call mysql_change_user().

COM_BINLOG_DUMP

18

A byte sequence in the following format: 4-byte integer for the offset, 2-byte integer for the flags, 4-byte integer for the slave server ID, and a string for the log name. All integers are formatted with the low byte first.

Tells the server to send a continuous feed of the replication master log events starting at the specified offset in the specified log. Used by the replication slave, and in the mysqlbinlog command-line utility.

COM_TABLE_DUMP

19

A byte sequence in the following format: 1 byte for database name length, database name, 1 byte for table name length, table name.

Tells the server to send the table definition and data to the client in raw format. Used when a replication slave receives a LOAD DATAFROMMASTER query.

COM_CONNECT_OUT

20

No argument.

Never sent by a client. Used for internal purposes.

COM_REGISTER_SLAVE

21

A byte sequence in the following format: a 4-byte integer for the server ID, then a sequence of 1 byte-length pre-fixed strings in the following order: slave host name, slave user to connect as, slave user password. Then a 2-byte slave user port, 4-byte replication recovery rank, and another 4-byte field that is currently unused. All integers have the low byte first.

Tells the replication master server to register the slave using the information supplied in the argument. This command is a remnant of the started fail-safe replication project. It was introduced in the early version 4.0, but not much has changed since. It is possible that this command might get removed in the future versions.

COM_PREPARE

22

A string containing the statement.

Tells the server to prepare the statement specified by the argument. Issued by the client API call mysql_stmt_prepare(). New in version 4.1.

COM_EXECUTE

23

A byte sequence in the following format: 4-byte statement ID, 1 byte for flags, and 4-byte iteration count. All integers have the low byte first.

Tells the server to execute the statement referenced by the statement ID. Issued by the client API call mysql_stmt_execute(). New in version 4.1.

Table 4-7. Client commands (continued)

Command code

 

 

 

enum value

Code numeric value

Argument description

Command description

COM_LONG_DATA

24

A byte sequence in the following format: 4 byte statement ID, 2 byte parameter number, parameter string. Both integers have the low byte first.

Tells the server the packet contains the data for one bound parameter in a prepared statement. Used to avoid unnecessary copying of a large amount of data when the value of the bound parameter is very long. Issued by the client API call mysql_stmt_send_long_data(). New in version 4.1.

COM_CLOSE_STMT

25

4-byte statement ID with the low byte first.

Tells the server to close the prepared statement specified by the statement ID. Issued by the client API call mysql_stmt_ close(). New in version 4.1.

COM_RESET_STMT

26

4-byte statement ID with the low byte first.

Tells the server to discard the current parameter values in the prepared statement specified by the statement ID that may have been set with COM_LONG_DATA. Issued by the client API call mysql_stmt_reset(). New in version 4.1.

COM_SET_OPTION

27

2-byte code for the option, low byte first.

Tells the server to enable or disable the option specified by the code. At this point, seems to be used only to enable or disable the support of multiple state-ments in one query string. Issued by the client API call mysql_set_server_option(). New in version 4.1.

COM_END

28

No argument.

Never sent by a client. Used for internal purposes.

When MySQL developers add a new command, to keep the backward compatibility for the older clients, all new commands are added immediately before COM_END in the enum server_command . Adding it anywhere else would alter the numeric codes of the commands and thus break all of the commands after the point of the insertion in older clients. This requirement allows us to easily track the history of features to a certain extent. For example, we can tell that prepared statements were added after replication because COM_PREPARE follows COM_BINLOG_DUMP .

Command Packet

Server Responses

Once the server receives a command, it processes it and sends one or more response packets. Several types of responses are discussed in this section.

Data Field

Data fields are critical components in many of the server response packets. A data field consists of a length specifier sequence followed by the actual data value. The length specifier sequence can be understood by studying the definition of net_store_ length() from sql/pack.c:

  char *
  net_store_length(char *pkg, ulonglong length)
  {
   
uchar *packet=(uchar*) pkg;
   
if (length < (ulonglong) LL(251))
   
{
     
*packet=(uchar) length;
     
return (char*) packet+1;
    }
    /* 251 is reserved for NULL */
    if (length < (ulonglong) LL(65536))
    {
     
*packet++=252;
     
int2store(packet,(uint) length);
     
return (char*) packet+2;
   
}
   
if (length < (ulonglong) LL(16777216))
   
{
     
*packet++=253;
     
int3store(packet,(ulong) length);
     
return (char*) packet+3;
   
}
   
*packet++=254;
   
int8store(packet,length);
   
return (char*) packet+8;
 
}

As you can see, if the value of length does not exceed 251 (i.e., if it can fit into 1 byte without a conflict with the reserved values), the code just stores it in a byte. If it is 251 and higher but fits into 2 bytes, the code prefixes it with the value of 252 and then writes it out in the following 2 bytes. If 2 bytes is not enough, but 4 would do, the code uses 253 for the code, and then occupies the next 4 bytes with the length value. If 4 bytes is not enough, the code uses 254 for the code, and stores it in 8 bytes. It must be noted that all length values following the code are stored with the low byte first.

One may ask why the 1 byte length is limited to 251, when the first reserved value in the net_store_length() is 252. The code 251 has a special meaning. It indicates that there is no length value or data following the code, and the value of the field is the SQL NULL .

Why such a complexity? Most of the time the data field is fairly short, and, espe cially if a query returns a lot of records and/or selects a lot of columns, there could be many of them in the response. Wasting even a byte per field in this situation would add up to a large overhead. The probability of a field length being greater than 250 is relatively low, but even in that case, wasting a byte is barely noticeable since the server is already sending at least 253 bytes: at least 2 for the length, and at least 251 for the field value.

Immediately after the length sequences is the actual data value, which is converted to a string representation.

In the pre-4.1 versions, the standard server API call for storing a data field in a buffer is net_store_data() , which exists in several variants, one for each possible data argument type. The net_store_data() family is found in sql/net_pkg.cc in those older version. Versions 4.1 and higher use Protocol::store() , which in the case of the simple protocol, just wraps around net_store_data() . Both are implemented in sql/protocol.cc.

Note that in version 4.1, when returning the data for prepared statements fields and when the data value is not a string, the data is sent in the raw binary format with the low byte first without a length specifier.

OK Packet

An OK packet is sent to indicate that the server successfully completed the command. It is sent in response to the following commands:

  • COM_PING 
  • COM_QUERY if the query does not need to return a result set; for example, INSERT , UPDATE , or ALTER TABLE
  • COM_REFRESH
  • COM_REGISTER_SLAVE

This type of packet is appropriate for commands that do not return a result set. Its format, however, permits sending some extra status information, such as the number of modified records, the value of the automatically generated primary key, or a custom status message in a string format. The structure of the packet body is documented in Table 4-8 .

Table 4-8. Format of server’s OK packet

 

Offset in the body

Length

Description

0

1

A byte with the value of 0, indicating that the packet has no fields.

1

rows_len

The number of records that the query has changed in the field length format described in the "Data Field" section, earlier in this chapter. Its length varies depending on the value. I will refer to its length as rows_lento express the subsequent offsets.

Server Responses

Table 4-8. Format of server’s OK packet (continued) 

Offset in the body

Length

Description

1 + rows_len

id_len

The value of the generated auto-increment ID for the primary key. Set to 0 if not applicable in the context. The value is stored in the field length format of a data field. I will refer to the length of this value as id_len.

1 + rows_len + id_len

2

Server status bit mask, low byte first. For details on different values, see the macros starting with STATUS_in include/mysql_com.h. In the protocol of version 4.0 and earlier, the status field is present only if it is a nonzero value. In the protocol of version 4.1 and later, it is reported unconditionally.

3 + rows_len + id_len

2

Present only in the protocol of version 4.1 and later. Contains the number of warnings the last command has generated. For example, if the command was COM_QUERYwith LOAD DATA INFILE, and some of the fields or lines could not be properly imported, a number of warnings will be generated. The number is stored with the low byte first.

5 + rows_len + id_lenin

msg_len

An optional field for the status message if one is present in the standard data field format with the field length followed by field value, which in this case is a character string. 

 

 

 

1 +rows_len + id_lenor

 

version 4.1 and later protocol.

3 +rows_len + id_lenin

 

 the older protocol, depending on whether the server status bit mask was included.

To send an OK packet from inside the server, you must call send_ok() . In version 4.1 and later, the function is declared in sql/protocol.h, and defined in sql/protocol.cc. In the earlier versions, it is declared in sql/mysql_priv.h and defined in sql/net_pkg.cc.

Error Packet

When something goes wrong with the processing of a command, the server responds with an error packet. The format is documented in Table 4-9.

Table 4-9. Format of server’s error packet

Offset in the body LengthDescription
01A byte containing 255. The client will always treat a response packet starting with a byte containing 255 as an error message.
12

The error code. Low byte first. The field will not be included if the server is talking to a very ancient pre-3.23 client, and the subsequent offsets should be adjusted accordingly in that case.

32Character ‘ # ’ followed by the byte containing the value of the ODBC/JDBC SQL state. Present only in version 4.1 and later.
5 in version 4.1 and later, 3 in 4.0 and earlierVariesZero-terminated text of the error message.

To send an error packet from inside the server, call send_error() , which is defined in sql/protocol.cc in version 4.1 and later, and in sql/net_pkg.cc in version 4.0 and earlier.

EOF Packet

The end-of-file (EOF) packet is used to communicate a number of messages:

EOF) packet is used to communicate a number of messages: The end-of-file (EOF) packet is used to communicate a number of messages:
  1. End-of-field information data in a result set
  2. End-of-row data in a result set
  3. Server acknowledgment of COM_SHUTDOWN
  4. Server reporting success in response to COM_SET_OPTION and COM_DEBUG
  5. Request for the old-style credentials during authentication

The body of an EOF packet always starts with a byte containing decimal 254. In the pre-4.1 era, there was nothing else in the body in addition to this byte. Version 4.1 added another 4 bytes of status fields with the potential of going up to 7 bytes. The format of the version 4.1 EOF body is outlined in Table 4-10.

Table 4-10. Format of server’s EOF packet

Offset in the body

Length

Description

0

1

Byte with the decimal 254

1

2

Number of warnings

3

2

Server status bit mask

The reason for the 7 byte limit in the status bytes area is that the decimal 254 byte followed by an 8 byte string at the beginning of a packet body can have a different meaning: it can specify the number of fields in a result set using the field length format described in the “Data Field ” section, earlier in this chapter.

To send an EOF packet, the server uses send_eof() , which is defined in sql/protocol.cc in 4.1 and later, and in sql/net_pkg.cc in the earlier versions.

Result Set Packets

A large number of queries produce a result set. Some examples are SELECT, SHOW, CHECK , REPAIR , and EXPLAIN . Any time the expected information from a query is more than a simple status report, a result set is returned.

The result set consists of a sequence of packets. First, the server sends information about the fields with a call to Protocol::send_fields() in sql/protocol.cc in version 4.1 and later. In the older versions, the function is called send_fields() and is found in sql/net_pkg.cc. This stage produces the following sequence of packets:

Server Responses

  • A packet with the body consisting of the standard field-length specifier sequence. However, this time, the meaning of the number is different. It indicates the number of fields in the result set.
  • A group of field description packets (see the upcoming explanation for the format description), one for each field, in the field order of the result set.
  • A terminating EOF packet.

The format of the field description packet body is shown in Tables 4-11 and 4-12. Table 4-11 shows the format for versions 4.0 and earlier, while Table 4-12 shows the format for versions 4.1 and later. Because most of the packet elements have variable lengths, the offsets are dependent on the content of the previous fields. I will, therefore, omit the offset column in the format descriptions. Finally, Table 4-13 explains the different field option flags.

Table 4-11. Format of server’s result set sequence, versions 4.0 and earlier

 

Length

Description

Varies

Table name of the field in the data field format. If the table was aliased in the query, contains the name of the alias.

Varies

Column name of the field in the data field format. If the column was aliased in the query, contains the name of the alias.

4

Data field-formatted value of field length, low byte first.

2

Data field-formatted field-type code according to enum field_typesin include/mysql_com.h.

1

Decimal value 3, meaning the next 3 bytes contain data. The idea is to make the sequence look like a standard data field.

2

Bit mask of field option flags (low byte first). See Table 4-12 for the explanation of the bits.

1

Decimal point precision of the field.

Varies

Optional element. If present, contains the default value of the field in the standard field data format.

Table 4-12. Format of server’s result set sequence, versions 4.1 and later

Length Description
4

Data field (see the section “Data Field,” earlier in this chapter) containing the ASCII string def .

VariesDatabase name of the field in the data field format.
Varies

Table name of the field in the data field format. If the table was aliased in the query, contains the name of the alias.

Varies

Table name of the field in the data field format. If the table was aliased in the query, contains the original name of the table.

Varies

Column name of the field in the data field format. If the column was aliased in the query, contains the name of the alias.

Varies

Column name of the field in the data field format. If the column was aliased in the query, contains the original name of the table.

1

Byte containing decimal 12, meaning that 12 bytes of data follow. The idea is to make the sequence look like a standard data field.

Table 4-12. Format of server’s result set sequence, versions 4.1 and later (continued)

Length Description
2Character set code of the field (low byte first).
4Field length (low byte first).
1Type code of the field according to enum field_types in include/mysql_com.h.
2Bit mask of field option flags (low byte first). See Table 4-13 for the explanation of the bits.
1Decimal-point precision of field values.
2Reserved.
Varies

Optional element. If present, contains the default value of the field in the standard field data format.

Table 4-13. Option flags in server’s result set packets

 

Bit macro

Hexadecimal bit value

Description

NOT_NULL_FLAG

0x0001

The field value cannot be NULL(it is declared with the NOT NULL attribute).

PRI_KEY_FLAG

0x0002

The field is a part of the primary key.

UNIQUE_KEY_FLAG

0x0004

The field is a part of a unique key.

MULTIPLE_KEY_ FLAG

0x0008

The field is a part of some non-unique key.

BLOB_FLAG

0x0010

The field is a BLOBor TEXT.

UNSIGNED_FLAG

0x0020

The field was declared with the UNSIGNEDattribute, which has the same meaning as the unsignedkeyword in C.

ZEROFILL_FLAG

0x0040

The field has been declared with the ZEROFILLattribute, which tells the server to pad the numeric types with leading zeros in the output to fit the specified field length.

BINARY_FLAG

0x0080

The field has been declared with the BINARYattribute, which tells the server to compare strings byte-for-byte in a case-sensitive manner.

ENUM_FLAG

0x0100

The field is an ENUM.

AUTO_INCREMENT_ FLAG

0x0200

The field has been declared with the AUTO_INCREMENTattribute, which enables the automatic generation of primary key values when a new record is inserted.

TIMESTAMP_FLAG

0x0400

The field is a timestamp.

SET_FLAG

0x0800

The field is a SET.

NUM_FLAG

0x8000

Used with cursors in version 4.1 to indicate that the field is numeric.

Following the field definition sequence of packets, the server sends the actual rows of data, one packet per row. Each row data packet consists of a sequence of values stored in the standard field data format. When reporting the result of a regular query (sent with COM_QUERY ), the field data is converted to the string format. When using a prepared statement ( COM_PREPARE ), the field data is sent in its native format with the low byte first.

After all of the data rows have been sent, the packet sequence is terminated with an EOF packet.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye