mysql数据库协议..分析【mysql protocol 】



The topic is: the contents of logical packets in MySQL version 5.0 client/server communication.

The description is of logical packets. There will be only passing mention of non-logical considerations, such as physical packets, transport, buffering, and compression. If you are interested in those topics, you may wish to consult another document: "MySQL Client - Server Protocol Documentation" in the file net_doc.txt in the internals directory of the mysqldoc MySQL documentation repository.

The description is of the version-5.0 protocol at the time of writing. Most of the examples show version-4.1 tests, which is okay because the changes from version-4.1 to version-5.0 were small.

A typical description of a packet will include:

"Bytes and Names". This is intended as a quick summary of the lengths and identifiers for every field in the packet, in order of appearance. The "Bytes" column contains the length in bytes. The Names column contains names which are taken from the MySQL source code whenever possible. If the version-4.0 and version-4.1 formats differ significantly, we will show both formats.

Descriptions for each field. This contains text notes about the usage and possible contents.

(If necessary) notes about alternative terms. Naming in this document is not authoritative and you will often see different words used for the same things, in other documents.

(If necessary) references to program or header files in the MySQL source code. An example of such a reference is: sql/ net_store_length() which means "in the sql subdirectory, in the file, the function named net_store_length".

An Example. All examples have three columns:

-- the field name
-- a hexadecimal dump
-- an ascii dump, if the field has character data

All spaces and carriage returns in the hexadecimal dump are there for formatting purposes only.

In the later sections, related to prepared statements, the notes should be considered unreliable and there are no examples.


Null-Terminated String: used for some variable-length character strings. The value '/0' (sometimes written 0x00) denotes the end of the string.

Length Coded Binary: a variable-length number. To compute the value of a Length Coded Binary, one must examine the value of its first byte.

  Value Of     # Of Bytes  Description
  First Byte   Following
  ----------   ----------- -----------
  0-250        0           = value of first byte
  251          0           column value = NULL
                           only appropriate in a Row Data Packet
  252          2           = value of following 16-bit word
  253          3           = value of following 24-bit word
  254          8           = value of following 64-bit word

Thus the length of a Length Coded Binary, including the first byte, will vary from 1 to 9 bytes. The relevant MySQL source program is sql/ net_store_length().

All numbers are stored with the least significant byte first. All numbers are unsigned.

Length Coded String: a variable-length string. Used instead of Null-Terminated String, especially for character strings which might contain '/0' or might be very long. The first part of a Length Coded String is a Length Coded Binary number (the length); the second part of a Length Coded String is the actual data. An example of a short Length Coded String is these three hexadecimal bytes: 02 61 62, which means "length = 2, contents = 'ab'".

The Packet Header

Bytes                 Name
 -----                 ----
 3                     Packet Length
 1                     Packet Number
 Packet Length: The length, in bytes, of the packet
                that follows the Packet Header. There
                may be some special values in the most
                significant byte. Since 2**24 = MB,
                the maximum packet length is 16MB.
 Packet Number: A serial number which can be used to
                ensure that all packets are present
                and in order. The first packet of a
                client query will have Packet Number = 0
                Thus, when a new SQL statement starts, 
                the packet number is re-initialised.

The Packet Header will not be shown in the descriptions of packets that follow this section. Think of it as always there. But logically, it "precedes the packet" rather than "is included in the packet".

Alternative terms: Packet Length is also called "packetsize". Packet Number is also called "Packet no".

Relevant MySQL Source Code:
include/my_global.h int3store()
sql/ my_net_write(), net_flush(), net_write_command(), my_net_read()

Packet Types

This is what happens in a typical session:

The Handshake (when client connects):

  Server Sends To Client: Handshake Initialisation Packet

  Client Sends To Server: Client Authentication Packet

  Server Sends To Client: OK Packet, or Error Packet

The Commands (for every action the client wants the server to do):

  Client Sends To Server: Command Packet

  Server Sends To Client: OK Packet, or Error Packet, or Result Set Packet

In the rest of this chapter, you will find a description for each packet type, in separate sections.

Alternative terms: The Handshake is also called "client login" or "login procedure" or "connecting".

Handshake Initialization Packet

From server to client during initial handshake.

Bytes                        Name
 -----                        ----
 1                            protocol_version
 n (Null-Terminated String)   server_version
 4                            thread_id
 8                            scramble_buff
 1                            (filler) always 0x00
 2                            server_capabilities
 1                            server_language
 2                            server_status
 13                           (filler) always 0x00 ...
 13                           rest of scramble_buff (4.1)
 protocol_version:    The server takes this from PROTOCOL_VERSION
                      in /include/mysql_version.h. Example value = 10.
 server_version:      The server takes this from MYSQL_SERVER_VERSION
                      in /include/mysql_version.h. Example value = "4.1.1-alpha".
 thread_number:       ID of the server thread for this connection.
 scramble_buff:       The password mechanism uses this. The second part are the
                      last 13 bytes.
                      (See "Password functions" section elsewhere in this document.)
 server_capabilities: CLIENT_XXX options. The possible flag values at time of
 writing (taken from  include/mysql_com.h):
  CLIENT_LONG_PASSWORD	1	/* new more secure passwords */
  CLIENT_FOUND_ROWS	2	/* Found instead of affected rows */
  CLIENT_LONG_FLAG	4	/* Get all column flags */
  CLIENT_CONNECT_WITH_DB	8	/* One can specify db on connect */
  CLIENT_NO_SCHEMA	16	/* Don't allow database.table.column */
  CLIENT_COMPRESS		32	/* Can use compression protocol */
  CLIENT_ODBC		64	/* Odbc client */
  CLIENT_IGNORE_SPACE	256	/* Ignore spaces before '(' */
  CLIENT_PROTOCOL_41	512	/* New 4.1 protocol */
  CLIENT_INTERACTIVE	1024	/* This is an interactive client */
  CLIENT_SSL              2048	/* Switch to SSL after handshake */
  CLIENT_IGNORE_SIGPIPE   4096    /* IGNORE sigpipes */
  CLIENT_TRANSACTIONS	8192	/* Client knows about transactions */
  CLIENT_RESERVED         16384   /* Old flag for 4.1 protocol  */
  CLIENT_SECURE_CONNECTION 32768  /* New 4.1 authentication */
  CLIENT_MULTI_STATEMENTS 65536   /* Enable/disable multi-stmt support */
  CLIENT_MULTI_RESULTS    131072  /* Enable/disable multi-results */
 server_language:     current server character set number
 server_status:       SERVER_STATUS_xxx flags: e.g. SERVER_STATUS_AUTOCOMMIT

Alternative terms: Handshake Initialization Packet is also called "greeting packet". Protocol version is also called "Prot. version". server_version is also called "Server Version String". thread_number is also called "Thread Number". current server charset number is also called "charset_no". scramble_buff is also called "crypt seed". server_status is also called "SERVER_STATUS_xxx flags" or "Server status variables".


Example Handshake Initialization Packet
                    Hexadecimal                ASCII
                    -----------                -----
protocol_version    0a                         .
server_version      34 2e 31 2e 31 2d 71 6c    4.1.1-al
                    70 68 61 2d 64 65 62 75    pha-debu
                    67 00                      g.
thread_number       01 00 00 00                ....
scramble_buff       3a 23 3d 4b 43 4a 2e 43    ........
(filler)            00                         .
server_capabilities 2c 82                      ..
server_language     08                         .
server_status       02 00                      ..
(filler)            00 00 00 00 00 00 00 00    ........
                    00 00 00 00 00

In the example, the server is telling the client that its server_capabilities include CLIENT_MULTI_RESULTS, CLIENT_SSL, CLIENT_COMPRESS, CLIENT_CONNECT_WITH_DB, CLIENT_FOUND_ROWS.

The "server_language" (or "charset") corresponds to the character_set_server variable in the MySQL server. This number also contains the collation used. Technically this number determines the collation and the character set is implicit for the collation. You can use the following SQL statement to get the cleartext information:

    -> WHERE ID=8;
| latin1             | latin1_swedish_ci | 
1 row in set (0,00 sec)

Client Authentication Packet

From client to server during initial handshake.

 Bytes                        Name
 -----                        ----
 2                            client_flags
 3                            max_packet_size
 n  (Null-Terminated String)  user
 8                            scramble_buff
 1                            (filler) always 0x00
 Bytes                        Name
 -----                        ----
 4                            client_flags
 4                            max_packet_size
 1                            charset_number
 23                           (filler) always 0x00...
 n (Null-Terminated String)   user
 n (Length Coded Binary)      scramble_buff (1 + x bytes)
 n (Null-Terminated String)   databasename (optional)
 client_flags:            CLIENT_xxx options. The list of possible flag
                          values is in the description of the Handshake
                          Initialisation Packet, for server_capabilities.
                          For some of the bits, the server passed "what
                          it's capable of". The client leaves some of the
                          bits on, adds others, and passes back to the server.
                          One important flag is: whether compression is desired.
                          Another interesting one is CLIENT_CONNECT_WITH_DB,
                          which shows the presence of the optional databasename.
 max_packet_size:         the maximum number of bytes in a packet for the client
 charset_number:          in the same domain as the server_language field that
                          the server passes in the Handshake Initialization packet.
 user:                    identification
 scramble_buff:           the password, after encrypting using the scramble_buff
                          contents passed by the server (see "Password functions"
                          section elsewhere in this document)
                          if length is zero, no password was given
 databasename:            name of schema to use initially

The scramble_buff and databasename fields are optional. The length-coding byte for the scramble_buff will always be given, even if it's zero.

Alternative terms: "Client authentication packet" is sometimes called "client auth response" or "client auth packet" or "login packet". "Scramble_buff" is sometimes called "crypted password".

Relevant MySQL Source Code:
- On the client side: libmysql/libmysql.c::mysql_real_connect().
- On the server side: sql/
Example Client Authentication Packet
                    Hexadecimal                ASCII
                    -----------                -----
client_flags        85 a6 03 00                ....
max_packet_size     00 00 00 01                ....
charset_number      08                         .
(filler)            00 00 00 00 00 00 00 00    ........
                    00 00 00 00 00 00 00 00    ........
                    00 00 00 00 00 00 00       .......
user                70 67 75 6c 75 74 7a 61    pgulutza
                    6e 00                      n.

Password functions

The Server Initialization Packet and the Client Authentication Packet both have an 8-byte field, scramble_buff. The value in this field is used for password authentication.

Relevant MySQL Source Code: libmysql/password.c, see also comments at start of file. It works thus:

4.0 and before

  • The server sends a random string to the client, in scramble_buff.
  • The client encrypts the scramble_buff value using the hash of a password that the user has entered. This happens in sql/password.c:scramble() function.
  • The client sends the encrypted scramble_buff value to the server.
  • The server encrypts the original random string using a value in the mysql database, mysql.user.Password.
  • The server compares its encrypted random string to what the client sent in scramble_buff.
  • If they are the same, the password is okay.

In this protocol, snooping on the wire doesn't reveal the password. But note the problem - if the client doesn't know the password, but knows a hash of it (as stored in mysql.user.Password) it can connect to the server. In other words, the hash of a password is the real password; if one can get the value of mysql.user.Password - he can connect to the server.

4.1 and later

remember that mysql.user.Password stores SHA1(SHA1(password))

  • The server sends a random string (scramble) to the client
  • the client calculates:
    • stage1_hash = SHA1(password), using the password that the user has entered.
    • token = SHA1(SHA1(stage1_hash), scramble) XOR stage1_hash
  • the client sends the token to the server
  • the server calculates
    • stage1_hash' = token XOR SHA1(mysql.user.Password, scramble)
  • the server compares SHA1(stage1_hash') and mysql.user.Password
  • If they are the same, the password is okay.

This protocol fixes the flaw of the old one, neither snooping on the wire nor mysql.user.Password are sufficient for a successful connection. But when one has both mysql.user.Password and the intercepted data on the wire, he has enough information to connect.

Command Packet (Overview)

From client to server whenever the client wants the server to do something.

Bytes                        Name
 -----                        ----
 1                            command
 n                            arg
 command:      The most common value is 03 COM_QUERY, because
               INSERT UPDATE DELETE SELECT etc. have this code.
               The possible values at time of writing (taken
               from /include/mysql_com.h for enum_server_command) are:
               #      Name                Associated client function
               -      ----                --------------------------
               0x00   COM_SLEEP           (none, this is an internal thread state)
               0x01   COM_QUIT            mysql_close
               0x02   COM_INIT_DB         mysql_select_db 
               0x03   COM_QUERY           mysql_real_query
               0x04   COM_FIELD_LIST      mysql_list_fields
               0x05   COM_CREATE_DB       mysql_create_db (deprecated)
               0x06   COM_DROP_DB         mysql_drop_db (deprecated)
               0x07   COM_REFRESH         mysql_refresh
               0x08   COM_SHUTDOWN        mysql_shutdown
               0x09   COM_STATISTICS      mysql_stat
               0x0a   COM_PROCESS_INFO    mysql_list_processes
               0x0b   COM_CONNECT         (none, this is an internal thread state)
               0x0c   COM_PROCESS_KILL    mysql_kill
               0x0d   COM_DEBUG           mysql_dump_debug_info
               0x0e   COM_PING            mysql_ping
               0x0f   COM_TIME            (none, this is an internal thread state)
               0x10   COM_DELAYED_INSERT  (none, this is an internal thread state)
               0x11   COM_CHANGE_USER     mysql_change_user
               0x12   COM_BINLOG_DUMP     sent by the slave IO thread to request a binlog
               0x13   COM_TABLE_DUMP      LOAD TABLE ... FROM MASTER (deprecated)
               0x14   COM_CONNECT_OUT     (none, this is an internal thread state)
               0x15   COM_REGISTER_SLAVE  sent by the slave to register with the master (optional)
               0x16   COM_STMT_PREPARE    mysql_stmt_prepare
               0x17   COM_STMT_EXECUTE    mysql_stmt_execute
               0x18   COM_STMT_SEND_LONG_DATA mysql_stmt_send_long_data
               0x19   COM_STMT_CLOSE      mysql_stmt_close
               0x1a   COM_STMT_RESET      mysql_stmt_reset
               0x1b   COM_SET_OPTION      mysql_set_server_option
               0x1c   COM_STMT_FETCH      mysql_stmt_fetch
 arg:           The text of the command is just the way the user typed it, there is no processing
                by the client (except removal of the final ';').
                This field is not a null-terminated string; however,
                the size can be calculated from the packet size,
                and the MySQL client appends '/0' when receiving.

The command byte is stored in the thd structure for the MySQL worker threads and is shown in the Command column for SHOW PROCESSLIST. An inactive thread gets 0x00 (Sleep). The dedicated thread to execute INSERT DELAYED gets 0x10.

The replication requests (0x12 .. 0x15) cannot be send from regular clients, only from another server or from the mysqlbinlog program.

Relevant MySQL source code:
sql-common/client.c cli_advanced_command(), mysql_send_query().
libmysql/libmysql.c mysql_real_query(), simple_command(), net_field_length().
Example Command Packet
                    Hexadecimal                ASCII
                    -----------                -----
command             02                         .
arg                 74 65 73 74                test

In the example, the value 02 in the command field stands for COM_INIT_DB. This is the packet that the client puts together for "use test;".

Command Packet (detailed description)


Closes the current connection. No arguments.


Functional equivalent to the SQL statement USE <database>. Exported by many clients, i.e. in PHP as mysqli::select_db()

 Bytes                        Name
 -----                        ----
 n                            database name 
                              (up to end of packet, no termination character)

[edit] COM_QUERY

The most common request type. Used to execute nonprepared SQL statements.

 Bytes                        Name
 -----                        ----
 n                            SQL statement 
                              (up to end of packet, no termination character)


Functional equivalent to SHOW [FULL] FIELDS FROM ...

 Bytes                        Name
 -----                        ----
 n                            table name (null terminated) 
 n                            column name or wildcard (optional)


results from a call of the C-API function mysql_create_db(). This function is marked deprecated; the recommended way to create a database is to use the SQL statement CREATE DATABASE.

 Bytes                        Name
 -----                        ----
 n                            database name 
                              (up to end of packet, no termination character)

[edit] COM_DROP_DB

results from a call of the C-API function mysql_drop_db(). This function is marked deprecated; the recommended way to drop a database is to use the SQL statement DROP DATABASE.

 Bytes                        Name
 -----                        ----
 n                            database name 
                              (up to end of packet, no termination character)


Parameter is one byte, evaluated as bitmap. Some (but not all) options are available via the FLUSH statement or via mysqladmin flush-foo

 Bytes                        Name
 -----                        ----
 1                            bitmap of refresh options
                              defined in mysql_com.h


Asks the MySQL server to shutdown. Parameter is one byte, optional. This packet can be sent with mysqladmin shutdown.

 Bytes                        Name
 -----                        ----
 1                            shutdown option: 
                              0xFF... KILL_CONNECTION
                              defined in mysql_com.h


Asks the MySQL server to compile a text message with some server statistics (uptime, queries per second, etc.). This packet can be sent with mysqladmin status. No arguments.


Functional equivalent to the SQL statement SHOW PROCESSLIST. This packet can be sent by mysqladmin processlist. No arguments.


Functional equivalent to the SQL statement KILL <id>.

 Bytes                        Name
 -----                        ----
 4                            Process ID (little endian)

[edit] COM_DEBUG

Asks the MySQL server to dump some debug information. The amount of data depends on compile time options (debug=no|yes|full). This packet can be sent with mysqladmin debug. No arguments.

[edit] COM_PING

This packet can be used to test the connection and to reset the connection inactivity counter in the MySQL server (wait_timeout). This packet can be sent with mysqladmin ping. Also exported by almost any client API. No arguments.


This packet is effectively a re-login without closing/opening the connection. Important side effect: this packet destroys the session context (temporary tables, session variables, etc.) in the MySQL server.

Some connection pool implementations use this to clean up the session context.

 Bytes                        Name
 -----                        ----
 n                            user name (Null-terminated string)
 n                            password
                              3.23 scramble - Null-terminated string (9 bytes)
                              4.1 scramble - Length (1 byte) coded string (21 byte)
 n                            database name (Null-terminated string)
 2                            character set number (since 5.1.23?)


This request is the last request sent from slave to master when a replication connection is established. The master answers with a stream of response packets, each containing one binlog event. If the master goes down, it sends an EOF packet.

 Bytes                        Name
 -----                        ----
 4                            binlog position to start at (little endian)
 2                            binlog flags (currently not used; always 0)
 4                            server_id of the slave (little endian)
 n                            binlog file name (optional)

If the binlog file name is not given, it defaults to the first binlog available on the master.


This request is sent from slave to master for a LOAD TABLE ... FROM MASTER statement. This feature is marked deprecated. Do not use!

 Bytes                        Name
 -----                        ----
 n                            schema name (length coded string)
 n                            table name (length coded string)



If the report_host variable is set on the slave, it sends this packet when it establishs the replication connection.

 Bytes                        Name
 -----                        ----
 4                            server_id on the slave (little endian)
 n                            report_host (length coded string)
 n                            report_user (length coded string)
 n                            report_password (length coded string)
 2                            report_port
 4                            rpl_recovery_rank 
 4                            server_id on the master (always 0)

The rpl_recovery_rank is a MySQL server variable that can be set, but is not yet used. In the future this will be used for replication failover.


Prepare a SQL statement. This request is answered with a special OK packet (documented elsewhere), sending the statement handle. All the other request packets for prepared statements use this statement handle.

 Bytes                        Name
 -----                        ----
 n                            query string with '?' place holders 
                              (up to end of packet, no termination character)


this is documented elsewhere


result of a call to mysql_stmt_send_long_data() to send a BLOB in pieces.

 Bytes                        Name
 -----                        ----
 4                            Statement ID (little endian)
 2                            Parameter number (little endian)
 n                            payload
                              (up to end of packet, no termination character)


Destroy a prepared statement. The statement handle becomes invalid.

 Bytes                        Name
 -----                        ----
 4                            Statement ID (little endian)


Reset (empty) the parameter buffers for a prepared statement. Mostly used in connection with COM_LONG_DATA.

 Bytes                        Name
 -----                        ----
 4                            Statement ID (little endian)


The parameter is a 16-bit integer. There is an ENUM type enum_mysql_set_option defined in mysql_com.h:

 Bytes                        Name
 -----                        ----
 2                            option to be set (little endian)


Fetch result rows from a prepared statement. Can fetch a variable amount of rows.

 Bytes                        Name
 -----                        ----
 4                            Statement ID (little endian)
 4                            number of rows to fetch (little endian)

[edit] Types Of Result Packets

A "result packet" is a packet that goes from the server to the client in response to a Client Authentication Packet or Command Packet. To distinguish between the types of result packets, a client must look at the first byte in the packet. We will call this byte "field_count" in the description of each individual package, although it goes by several names.

Type Of Result Packet       Hexadecimal Value Of First Byte (field_count)
 ---------------------       ---------------------------------------------
 OK Packet                   00
 Error Packet                ff
 Result Set Packet           1-250 (first byte of Length-Coded Binary)
 Field Packet                1-250 ("")
 Row Data Packet             1-250 ("")
 EOF Packet                  fe

[edit] OK Packet

From server to client in response to command, if no error and no result set.

 Bytes                       Name
 -----                       ----
 1   (Length Coded Binary)   field_count, always = 0
 1-9 (Length Coded Binary)   affected_rows
 1-9 (Length Coded Binary)   insert_id
 2                           server_status
 n   (until end of packet)   message
 Bytes                       Name
 -----                       ----
 1   (Length Coded Binary)   field_count, always = 0
 1-9 (Length Coded Binary)   affected_rows
 1-9 (Length Coded Binary)   insert_id
 2                           server_status
 2                           warning_count
 n   (until end of packet)   message
 field_count:     always = 0
 affected_rows:   = number of rows affected by INSERT/UPDATE/DELETE
 insert_id:       If the statement generated any AUTO_INCREMENT number, 
                  it is returned here. Otherwise this field contains 0.
                  Note: when using for example a multiple row INSERT the
                  insert_id will be from the first row inserted, not from
 server_status:   = The client can use this to check if the
                  command was inside a transaction.
 warning_count:   number of warnings
 message:         For example, after a multi-line INSERT, message might be
                  "Records: 3 Duplicates: 0 Warnings: 0"

The message field is optional.

Alternative terms: OK Packet is also known as "okay packet" or "ok packet" or "OK-Packet". field_count is also known as "number of rows" or "marker for ok packet". message is also known as "Messagetext". OK Packets (and result set packets) are also called "Result packets".

Relevant files in MySQL source:
(client) sql/client.c mysql_read_query_result()
(server) sql/ send_ok()
Example OK Packet
                    Hexadecimal                ASCII
                    -----------                -----
field_count         00                         .
affected_rows       01                         .
insert_id           00                         .
server_status       02 00                      ..
warning_count       00 00                      ..

In the example, the optional message field is missing (the client can determine this by examining the packet length). This is a packet that the server returns after a successful INSERT of a single row that contains no auto_increment columns.

[edit] Error Packet

From server to client in response to command, if error.

 Bytes                       Name
 -----                       ----
 1                           field_count, always = 0xff
 2                           errno (little endian)
 n                           message
 Bytes                       Name
 -----                       ----
 1                           field_count, always = 0xff
 2                           errno
 1                           (sqlstate marker), always '#'
 5                           sqlstate (5 characters)
 n                           message
 field_count:       Always 0xff (255 decimal).
 errno:             The possible values are listed in the manual, and in
                    the MySQL source code file /include/mysqld_error.h.
 sqlstate marker:   This is always '#'. It is necessary for distinguishing
                    version-4.1 messages.
 sqlstate:          The server translates errno values to sqlstate values
                    with a function named mysql_errno_to_sqlstate(). The
                    possible values are listed in the manual, and in the
                    MySQL source code file /include/sql_state.h.
 message:           The error message is a string which ends at the end of
                    the packet, that is, its length can be determined from
                    the packet header. The MySQL client (in the my_net_read()
                    function) always adds '/0' to a packet, so the message
                    may appear to be a Null-Terminated String.
                    Expect the message to be between 0 and 512 bytes long.

Alternative terms: field_count is also known as "Status code" or "Error Packet marker". errno is also known as "Error Number" or "Error Code".

Relevant files in MySQL source: (client) client.c net_safe_read() (server) sql/ send_error()

Example of Error Packet
                    Hexadecimal                ASCII
                    -----------                -----
field_count         ff                         .
errno               1b 04                      ..
(sqlstate marker)   23                         #
sqlstate            34 32 53 30 32             42S02
message             55 63 6b 6e 6f 77 6e 20    Unknown
                    74 61 62 6c 6c 65 20 27    table '
                    71 27                      q'

Note that some error messages past MySQL 4.1 are still returned without SQLState. For example, error 1043 'Bad handshake'.

[edit] Result Set Header Packet

From server to client after command, if no error and result set -- that is, if the command was a query which returned a result set.

The Result Set Header Packet is the first of several, possibly many, packets that the server sends for result sets. The order of packets for a result set is:

  (Result Set Header Packet)  the number of columns
  (Field Packets)             column descriptors
  (EOF Packet)                marker: end of Field Packets
  (Row Data Packets)          row contents
  (EOF Packet)                marker: end of Data Packets
Bytes                        Name
 -----                        ----
 1-9   (Length-Coded-Binary)  field_count
 1-9   (Length-Coded-Binary)  extra
 field_count: See the section "Types Of Result Packets"
              to see how one can distinguish the
              first byte of field_count from the first
              byte of an OK Packet, or other packet types.
 extra:       For example, SHOW COLUMNS uses this to send
              the number of rows in the table.

The "extra" field is optional and never appears for ordinary result sets.

Alternative terms: a Result Set Packet is also called "a result packet for a command returning rows" or "a field description packet".

Relevant MySQL source code:
libmysql/libmysql.c (client):
  mysql_store_result() Read a result set from the server to memory
  mysql_use_result()   Read a result set row by row from the server.
See also my_net_write() which describes local data loading.
Example of Result Set Header Packet
                    Hexadecimal                ASCII
                    -----------                -----
field_count         03                         .

In the example, we se what the packet would contain after "SELECT * FROM t7" if table t7 has 3 columns.

[edit] Field Packet

From Server To Client, part of Result Set Packets. One for each column in the result set. Thus, if the value of field_columns in the Result Set Header Packet is 3, then the Field Packet occurs 3 times.

 Bytes                      Name
 -----                      ----
 n (Length Coded String)    table
 n (Length Coded String)    name
 4 (Length Coded Binary)    length
 2 (Length Coded Binary)    type
 2 (Length Coded Binary)    flags
 1                          decimals
 n (Length Coded Binary)    default
 Bytes                      Name
 -----                      ----
 n (Length Coded String)    catalog
 n (Length Coded String)    db
 n (Length Coded String)    table
 n (Length Coded String)    org_table
 n (Length Coded String)    name
 n (Length Coded String)    org_name
 1                          (filler)
 2                          charsetnr
 4                          length
 1                          type
 2                          flags
 1                          decimals
 2                          (filler), always 0x00
 n (Length Coded Binary)    default

In practice, since identifiers are almost always 250 bytes or shorter, the Length Coded Strings look like: (1 byte for length of data) (data)

catalog:                 Catalog. For 4.1, 5.0 and 5.1 the value is "def".
db:                      Database identifier, also known as schema name.
table:                   Table identifier, after AS clause (if any).
org_table:               Original table identifier, before AS clause (if any).
name:                    Column identifier, after AS clause (if any).
org_name:                Column identifier, before AS clause (if any).
charsetnr:               Character set number.
length:                  Length of column, according to the definition.
                         Also known as "display length". The value given
                         here may be larger than the actual length, for
                         example an instance of a VARCHAR(2) column may
                         have only 1 character in it.
type:                    The code for the column's data type. Also known as
                         "enum_field_type". The possible values at time of
                         writing (taken from  include/mysql_com.h), in hexadecimal:
                         0x00   FIELD_TYPE_DECIMAL
                         0x01   FIELD_TYPE_TINY
                         0x02   FIELD_TYPE_SHORT
                         0x03   FIELD_TYPE_LONG
                         0x04   FIELD_TYPE_FLOAT
                         0x05   FIELD_TYPE_DOUBLE
                         0x06   FIELD_TYPE_NULL
                         0x07   FIELD_TYPE_TIMESTAMP
                         0x08   FIELD_TYPE_LONGLONG
                         0x09   FIELD_TYPE_INT24
                         0x0a   FIELD_TYPE_DATE
                         0x0b   FIELD_TYPE_TIME
                         0x0c   FIELD_TYPE_DATETIME
                         0x0d   FIELD_TYPE_YEAR
                         0x0e   FIELD_TYPE_NEWDATE
                         0x0f   FIELD_TYPE_VARCHAR (new in MySQL 5.0)
                         0x10   FIELD_TYPE_BIT (new in MySQL 5.0)
                         0xf6   FIELD_TYPE_NEWDECIMAL (new in MYSQL 5.0)
                         0xf7   FIELD_TYPE_ENUM
                         0xf8   FIELD_TYPE_SET
                         0xf9   FIELD_TYPE_TINY_BLOB
                         0xfa   FIELD_TYPE_MEDIUM_BLOB
                         0xfb   FIELD_TYPE_LONG_BLOB
                         0xfc   FIELD_TYPE_BLOB
                         0xfd   FIELD_TYPE_VAR_STRING
                         0xfe   FIELD_TYPE_STRING
                         0xff   FIELD_TYPE_GEOMETRY

flags:                   The possible flag values at time of
                         writing (taken from  include/mysql_com.h), in hexadecimal:
                         0001 NOT_NULL_FLAG
                         0002 PRI_KEY_FLAG
                         0004 UNIQUE_KEY_FLAG
                         0008 MULTIPLE_KEY_FLAG
                         0010 BLOB_FLAG
                         0020 UNSIGNED_FLAG
                         0040 ZEROFILL_FLAG
                         0080 BINARY_FLAG
                         0100 ENUM_FLAG
                         0200 AUTO_INCREMENT_FLAG
                         0400 TIMESTAMP_FLAG
                         0800 SET_FLAG

decimals:                The number of positions after the decimal
                         point if the type is DECIMAL or NUMERIC.
                         Also known as "scale".
default:                 For table definitions. Doesn't occur for
                         normal result sets. See mysql_list_fields().

Alternative Terms: Field Packets are also called "Header Info Packets" or "field descriptor packets" (that's a better term but it's rarely used). In non-MySQL contexts Field Packets are more commonly known as "Result Set Metadata".

Relevant MySQL source code:
(client) client/client.c unpack_fields().
(server) sql/ send_fields().
Example of Field Packet
                    Hexadecimal                ASCII
                    -----------                -----
catalog             03 73 74 64                .std
db                  03 64 62 31                .db1
table               02 54 37                   .T7
org_table           02 74 37                   .t7
name                02 53 31                   .S1
org_name            02 73 31                   .s1
(filler)            0c                         .
charsetnr           08 00                      ..
length              01 00 00 00                ....
type                fe                         .
flags               00 00                      ..
decimals            00                         .
(filler)            00 00                      ..

In the example, we see what the server returns for "SELECT s1 AS S1 FROM t7 AS T7" where column s1 is defined as CHAR(1).

[edit] EOF Packet

From Server To Client, at the end of a series of Field Packets, and at the end of a series of Data Packets. With prepared statements, EOF Packet can also end parameter information, which we'll describe later.

 Bytes                 Name
 -----                 ----
 1                     field_count, always = 0xfe
 Bytes                 Name
 -----                 ----
 1                     field_count, always = 0xfe
 2                     warning_count
 2                     Status Flags
 field_count:          The value is always 0xfe (decimal 254).
                       However ... recall (from the
                       section "Elements", above) that the value 254 can begin
                       a Length-Encoded-Binary value which contains an 8-byte
                       integer. So, to ensure that a packet is really an EOF
                       Packet: (a) check that first byte in packet = 0xfe, (b)
                       check that size of packet < 9.
 warning_count:        Number of warnings. Sent after all data has been sent
                       to the client.
 server_status:        Contains flags like SERVER_MORE_RESULTS_EXISTS

Alternative terms: EOF Packet is also known as "Last Data Packet" or "End Packet".

Relevant MySQL source code:
(server) send_eof()
Example of EOF Packet
                    Hexadecimal                ASCII
                    -----------                -----
field_count         fe                         .
warning_count       00 00                      ..
server_status       00 00                      ..

[edit] Row Data Packet

From server to client. One packet for each row in the result set.

Bytes                   Name
 -----                   ----
 n (Length Coded String) (column value)
 (column value):       The data in the column, as a character string.
                       If a column is defined as non-character, the
                       server converts the value into a character
                       before sending it. Since the value is a Length
                       Coded String, a NULL can be represented with a
                       single byte containing 251(see the description
                       of Length Coded Strings in section "Elements" above).

The (column value) fields occur multiple times. All (column value) fields are in one packet. There is no space between each (column value).

Alternative Terms: Row Data Packets are also called "Row Packets" or "Data Packets".

Relevant MySQL source code:
(client) client/client.c read_rows
Example of Row Data Packet
                    Hexadecimal                ASCII
                    -----------                -----
(first column)      01 58                      .X
(second column)     02 35 35                   .55

In the example, we see what the packet contains after a SELECT from a table defined as "(s1 CHAR, s2 INTEGER)" and containing one row where s1='X' and s2=55.

[edit] Row Data Packet: Binary (Tentative Description)

From server to client, or from client to server (if the client has a prepared statement, the "result set" packet format is used for transferring parameter descriptors and parameter data).

Recall that in the description of Row Data we said that: "If a column is defined as non-character, the server converts the value into a character before sending it." That doesn't have to be true. If it isn't true, it's a Row Data Packet: Binary.

Bytes                   Name
 -----                   ----
 1                       Null Bit Map with first two bits = 01
 n (Length Coded String) (column value)
 Bytes                   Name
 -----                   ----
 ?                       Packet Header
 1                       Null Bit Map with first two bits = 01
 Null Bit Map: The most significant 2 bits are reserved. Since
               there is always one bit on and one bit off, this can't be
               confused with the first byte of an Error Packet (255), the
               first byte of a Last Data Packet (254), or the first byte of
               an OK Packet (0).
 (column value): The column order and organization are the same as for
                 conventional Row Data Packets. The difference is that
                 each column value is sent just as it is stored. It's now up
                 to the client to convert numbers to strings if that's desirable.
                 For a description of column storage, see "Physical Attributes Of
                 Columns" elsewhere in this document.

Only non-zero parameters are passed.

Because no conversion takes place, fixed-length data items are as described in the "Physical Attributes of Columns" section: one byte for TINYINT, two bytes for FLOAT, four bytes for FLOAT, etc. Strings will appear as packed-string-length plus string value. DATETIME, DATE and TIME will be as follows:

Type             Size        Comment
 ----             ----        -------
 date             1 + 0-11    Length + 2 byte year, 1 byte MMDDHHMMSS,
                              4 byte billionth of a second
 datetime         1 + 0-11    Length + 2 byte year, 1 byte MMDDHHMMSS,
                              4 byte billionth of a second
 time             1 + 0-11    Length + sign (0 = pos, 1= neg), 4 byte days,
                              1 byte HHMMDD, 4 byte billionth of a second

Alternative Terms: Row Data Packet: Binary is also called "Binary result set packet".

Except for the different way of signalling NULLs, the server/client parameter interaction here proceeds the say way that the server sends result set data to the client. Since the data is not sent as a string, the length and meaning depend on the data type. The client must make appropriate conversions given its knowledge of the data type.


[edit] OK for Prepared Statement Initialization Packet

From server to client, in response to prepared statement initialization packet.

Bytes              Name
 -----              ----
 1                  0 - marker for OK packet
 4                  statement_handler_id
 2                  number of columns in result set
 2                  number of parameters in query
 1                  filler (always 0)
 2                  warning count
 (Field Packets)    column descriptors, as in a Result Set Header Packet
 (EOF Packet)       marker: end of Data Packets

Alternative terms: statement_handler_id is called "statement handle" or "hstmt" everywhere but at MySQL. Prepared statement initialization packet is also called "prepared statement init packet".

[edit] Parameter Packet (Tentative Description)

From server to client, for prepared statements which contain parameters.

The Parameter Packets follow a Prepared Statement Initialization Packet which has a positive value in the parameters field.

Bytes                   Name
 -----                   ----
 2                       type
 2                       flags
 1                       decimals
 4                       length
 type:                Same as for type field in a Field Packet.
 flags:               Same as for flags field in a Field Packet.
 decimals:            Same as for decimals field in a Field Packet.
 length:              Same as for length field in a Field Packet.

Notice the similarity to a Field Packet.

The parameter data will be sent in a packet with the same format as Row Data Packet: Binary.

[edit] Long Data Packet (Tentative Description)

From client to server, for long parameter values.

Bytes                   Name
 -----                   ----
 4                       statement_handler_id
 2                       parameter_number
 2                       type
 n                       data
 statement_handler_id:     ID of statement handler
 parameter_number:         Parameter number.
 type:                     Parameter data type. Not used at time of writing.
 data:                     Value of parameter, as binary string. The length
                           of data is implicit from the packet length.

This is used by mysql_send_long_data() to set any parameter to a string value. One can call mysql_send_long_data() multiple times for the same parameter; The server will concatenate the results to one big string.

The server will not send an ok or error packet in response to this. If there is an error (for example the string is too big), one will see the error when calling "execute".

Relevant MySQL Source Code:
(server) mysql_send_long_data

[edit] Execute Packet (Tentative Description)

From client to server, to execute a prepared statement.

Bytes                Name
 -----                ----
 1                    code
 4                    statement_id
 1                    flags
 4                    iteration_count
 (param_count+7)/8    null_bit_map
 1                    new_parameter_bound_flag
 n*2                  type of parameters (only if new_params_bound = 1)
 code:          always COM_EXECUTE
 statement_id:  statement identifier
 flags:         reserved for future use. In MySQL 4.0, always 0.
                In MySQL 5.0: 
                  0: CURSOR_TYPE_NO_CURSOR
                  1: CURSOR_TYPE_READ_ONLY
                  2: CURSOR_TYPE_FOR_UPDATE
                  4: CURSOR_TYPE_SCROLLABLE
 iteration_count: reserved for future use. Currently always 1.
 null_bit_map:  A bitmap indicating parameters that are NULL.
                Bits are counted from LSB, using as many bytes
                as necessary ((param_count+7)/8)
                i.e. if the first parameter (parameter 0) is NULL, then
                the least significant bit in the first byte will be 1.
 new_parameter_bound_flag:   Contains 1 if this is the first time
                             that "execute" has been called, or if
                             the parameters have been rebound.
 type:          Occurs once for each parameter that is not NULL.
                The highest significant bit of this 16-bit value
                encodes the unsigned property. The other 15 bits
                are reserved for the type (only 8 currently used).
                This block is sent when parameters have been rebound
                or when a prepared statement is executed for the 
                first time.

The Execute Packet is also known as "COM_EXECUTE Packet".

In response to an Execute Packet, the server should send back one of: an OK Packet, an Error Packet, or a series of Result Set Packets in which all the Row Data Packets are binary.

Relevant MySQL Source Code: libmysql/libmysql.c cli_read_prepare_result()


[edit] Compression

This chapter does not discuss compression, but you should be aware of its existence.

Compression is of one or more logical packets. The packet_number field that is in each packet header is an aid for keeping track.

The opposite of "compressed" is "raw".

Compression is used if both client and server support zlib compression, and the client requests compression.

A compressed packet header is: packet length (3 bytes), packet number (1 byte), and Uncompressed Packet Length (3 bytes). The Uncompressed Packet Length is the number of bytes in the original, uncompressed packet. If this is zero then the data is not compressed.

When the compressed protocol is in use (that is, when the client has requested it by setting the flag bit in the Client Authentication Packet and the server has accepted it), either the client or the server may compress packets. However, compression will not occur if the compressed length is greater than the original length. Thus, some packets will be compressed while other packets are not compressed.