MySQL Protocol Packet Basics for PurePerl
Writing MySQL Protocol Packets in PurePerl - Part 3
Writing a Perl Module
Foreword: In this part of the series, I explain the basics of the MySQL protocol packets.
By: Chrysanthus Date Published: 28 Jan 2015
Introduction
There are two types of packets: compressed and noncompressed. The decision on which type will be used for the session is made during the handshake stage, and depends on the capabilities and settings of both the client software and the server software. In this series I explain only the uncompressed type.
Whether you are dealing with the compressed or uncompressed type, the packets are divided into two categories: commands sent by the client, and responses returned by the server. In the query phase the user’s requests are in command packets and resultsets or error or OK packets are response packets.
Response or 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 the table below.
“low byte first” means that the least significant byte is on the left and as you move to the right of the bit string, you have the more and more significant bytes. The bit ordering in a byte is not changed; it is MSB first. It is the byte ordering that is changed. In the table, Offset means the starting index of a byte sequence, in the whole bit string considered. Length in the table is the number of bytes.
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.
As you may have noticed, the 3-byte field would limit the body length to 16 MB, i.e. 224–1. If you want to transmit a bigger packet, you will have to split the bigger packet 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.
| Offset | Length | Description |
| 0 | 3 | Packet body length stored with the low byte first. |
| 3 | 1 | Packet sequence number. The sequence numbers are reset with each new command. |
As mentioned above, the server response packets are: data packets, end-of-data-stream packets, success report (OK) packets, and error message packets.
Data Packets
The SQL SELECT query returns a result set. This result set consists of one or more packets called data packets.
End-of-Data-Stream Packet
The End-of-Data-Stream or end-of-file (EOF) packet can communicate one of the following:
- End-of-field information data in a result set
- End-of-row data in a result set
- Server acknowledgment of COM_SHUTDOWN
- Server reporting success in response to COM_SET_OPTION and COM_DEBUG (see later)
The Success Report Packet
The success report or OK packet is sent to indicate that the server successfully completed the command.
The Error Packet
The error packet is sent by the server, to indicate that something went wrong with the processing of a command.
Protocol Capability Bits
The two-byte sequence, 0000000010000000 means that the initial default database can be specified during authentication by the client software. Note that all the bits in this sequence are zero except one. This two-byte sequence can be written in hexadecimal form as, 0x0080 where 0x means hexadecimal.
The two-byte sequence, 0000000000100000 means that the client or the server (software) is capable of using compressed packet. Note that all the bits in this sequence are zero except one. This two-byte sequence can be written in hexadecimal form as, 0x0020. The 1 here is in a different position from the 1 in the previous byte sequence.
With the presence of the single 1, we say the bit in that position is set.
Each of such a byte sequence is called a Bit Mask. You can bitwise OR the above two byte-sequences to have, 0000000010100000. This result can be written in hexadecimal form as, 0x00A0. You can bitwise OR a number of bit masks and the result will still be known as a bit mask.
A Bit Mask usually written in hexadecimal, shows the capabilities of the client or server (software).
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Related Links
Internet Sockets and PerlPerl pack and unpack Functions
Writing MySQL Protocol Packets in PurePerl
Developing a PurePerl MySQL API
Using the PurePerl MySQL API
More Related Links
Perl Mailsend
PurePerl MySQL API
Perl Course - Professional and Advanced
Major in Website Design
Web Development Course
Producing a Pure Perl Library
MySQL Course
BACK NEXT