Client/Server Communication in MySQL - Relationship Between MySQL Protocol and OS Layer
(Page 3 of 4 )
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
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
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
4
Internal MySQL ID of the thread that is handling this connection. Low byte first.
ver_len + 5
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 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
2
Server capabilities bit mask with the low byte first. See Table 4-5 later for the meaning of different bits.
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 .
/ 7 