Chapter 4. Client/Server Communication
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 Table 4-1.
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 Table 4-2.
Offset in the body | Length | Description |
0 | 1 | Protocol 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 | | 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. |
| 4 | Internal MySQL ID of the thread that is handling this connection. Low byte first. |
| 9 | In 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 |
| 2 | Server capabilities bit mask with the low byte first. See Table 4-5 later for the meaning of different bits. |
| 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 |
| 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 |
| 13 | Reserved for future use. Currently zeroed out. |
| 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 |
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.
Offset in the body | Length | Description |
0 | 2 | Protocol capabilities bit mask of the client, low byte first. |
2 | 3 | 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. |
5 | Varies; 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. |
Offset in the body | Length | Description |
0 | 4 | Protocol capabilities bit mask of the client, low-byte first. |
4 | 4 | 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. |
8 | 1 | Default character set (or more precisely, collation) code of the client. |
9 | 23 | Reserved space; currently zeroed out. |
32 | Varies; 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
usertable in themysqldatabase, 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.
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.
Offset in the body | Length | Description |
0 | 1 | Command 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.
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.
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, especially 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_PINGCOM_QUERYif the query does not need to return a result set; for example,INSERT, UPDATE, orALTER TABLECOM_REFRESHCOM_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.
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.
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:
End-of-field information data in a result set
End-of-row data in a result set
Server acknowledgment of
COM_SHUTDOWNServer reporting success in response to
COM_SET_OPTIONandCOM_DEBUGRequest 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.
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:
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
EOFpacket.
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.
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
|
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. |
Length | Description |
4 | Data field (see the section “Data Field,” earlier
in this chapter) containing the ASCII string |
Varies | Database 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. |
2 | Character set code of the field (low byte first). |
4 | Field length (low byte first). |
1 | Type code of the field according to enum |
2 | Bit mask of field option flags (low byte first). See Table 4-13 for the explanation of the bits. |
1 | Decimal-point precision of field values. |
2 | Reserved. |
Varies | Optional element. If present, contains the default value of the field in the standard field data format. |
Bit macro | Hexadecimal bit value | Description |
| 0x0001 | The field value cannot be |
| 0x0002 | The field is a part of the primary key. |
| 0x0004 | The field is a part of a unique key. |
| 0x0008 | The field is a part of some non-unique key. |
| 0x0010 | The field is a |
| 0x0020 | The field was declared with the |
| 0x0040 | The field has been declared with the |
| 0x0080 | The field has been declared with the |
| 0x0100 | The field is an |
| 0x0200 | The field has been declared with the |
| 0x0400 | The field is a timestamp. |
| 0x0800 | The field is a |
| 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.
Get Understanding MySQL Internals now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
