O'Reilly logo

Understanding MySQL Internals by Sasha Pachev

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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_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_len to express the subsequent offsets.

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_QUERY with 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_len in version 4.1 and later protocol., 1 + rows_len + id_len or 3 + rows_len + id_len in the older protocol, depending on whether the server status bit mask was included.

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.

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

Length

Description

0

1

A byte containing 255. The client will always treat a response packet starting with a byte containing 255 as an error message.

1

2

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.

3

2

Character '#' 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 earlier

Varies

Zero-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:

  • 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

  • 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:

  • 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_types in 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.

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 field_types in include/mysql_com.h.

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.

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 BLOB or TEXT.

UNSIGNED_FLAG

0x0020

The field was declared with the UNSIGNED attribute, which has the same meaning as the unsigned keyword in C.

ZEROFILL_FLAG

0x0040

The field has been declared with the ZEROFILL attribute, 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 BINARY attribute, 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_INCREMENT attribute, 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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required