MySQL Internals ClientServer Protocol

crespo09

转载自：http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol
　　


MySQL Internals ClientServer Protocol


[edit]
MySQL Client/Server Protocol



[edit]
Organization

　　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/protocol.cc net_store_length() which means "in the sql subdirectory, in the protocol.cc 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.



[edit]
Elements

　　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/protocol.cc 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'".



[edit]
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. The maximum packet
length is (2**24 -1),about 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".

　　Indeed,if the packet length is equal or greater than (2**24 -1) Bytes, this packet must be split into two or more packets.

　　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/net_serv.cc my_net_write(), net_flush(), net_write_command(), my_net_read()


[edit]
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".



[edit]
Handshake Initialization Packet

　　From server to client during initial handshake. The follow is taken from sql/sql_acl.cc:


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
2                            server capabilities (two upper bytes)
1                            length of the scramble
10                            (filler)  always 0
n                            rest of the plugin provided data (at least 12 bytes)
1                            \0 byte, terminating the second part of a scramble
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_PASSWORD1/* new more secure passwords */
CLIENT_FOUND_ROWS2/* Found instead of affected rows */
CLIENT_LONG_FLAG4/* Get all column flags */
CLIENT_CONNECT_WITH_DB8/* One can specify db on connect */
CLIENT_NO_SCHEMA16/* Don't allow database.table.column */
CLIENT_COMPRESS32/* Can use compression protocol */
CLIENT_ODBC64/* Odbc client */
CLIENT_LOCAL_FILES128/* Can use LOAD DATA LOCAL */
CLIENT_IGNORE_SPACE256/* Ignore spaces before '(' */
CLIENT_PROTOCOL_41512/* New 4.1 protocol */
CLIENT_INTERACTIVE1024/* This is an interactive client */
CLIENT_SSL              2048/* Switch to SSL after handshake */
CLIENT_IGNORE_SIGPIPE   4096    /* IGNORE sigpipes */
CLIENT_TRANSACTIONS8192/* 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:

mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME
-> FROM INFORMATION_SCHEMA.COLLATIONS
-> WHERE ID=8;
+--------------------+-------------------+
| CHARACTER_SET_NAME | COLLATION_NAME    |
+--------------------+-------------------+
| latin1             | latin1_swedish_ci |
+--------------------+-------------------+
1 row in set (0,00 sec)


[edit]
Client Authentication Packet

　　From client to server during initial handshake.

VERSION 4.0
Bytes                        Name
-----                        ----
2                            client_flags
3                            max_packet_size
n  (Null-Terminated String)  user
8                            scramble_buff
1                            (filler) always 0x00
VERSION 4.1
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/sql_parse.cc::check_connections()

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.


[edit]
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:



[edit]
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.



[edit]
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(scramble + SHA1(stage1_hash)) XOR stage1_hash
    
  
  
  
• the client sends the token to the server
  
• the server calculates
  
  
  
  • stage1_hash' = token XOR SHA1(scramble + mysql.user.Password)
    
  
  
  
• the server compares SHA1(stage1_hash') and mysql.user.Password
  
• If they are the same, the password is okay.
  

　　(Note SHA1(A+B) is the SHA1 of the concatenation of A with B.)
　　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.



[edit]
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;".



[edit]
Command Packet (detailed description)



[edit]
COM_QUIT

　　Closes the current connection. No arguments.


[edit]
COM_INIT_DB

　　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)


[edit]
COM_FIELD_LIST

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

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


[edit]
COM_CREATE_DB

　　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)


[edit]
COM_REFRESH

　　Parameter is one byte, evaluated as bitmap. Some (but not all) options are available via the FLUSH statement or via
mysqladmin flush-foo. This is used by the C-API call mysql_refresh().

Bytes                        Name
-----                        ----
1                            bitmap of refresh options
0x01...REFRESH_GRANT
0x02...REFRESH_LOG
0x04...REFRESH_TABLES
0x08...REFRESH_HOSTS
0x10...REFRESH_STATUS
0x20...REFRESH_THREADS
0x40...REFRESH_SLAVE
0x80...REFRESH_MASTER
defined in mysql_com.h


[edit]
COM_SHUTDOWN

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

Bytes                        Name
-----                        ----
1                            shutdown option:
0x00...SHUTDOWN_DEFAULT
0x01...SHUTDOWN_WAIT_CONNECTIONS
0x02...SHUTDOWN_WAIT_TRANSACTIONS
0x08...SHUTDOWN_WAIT_UPDATES
0x10...SHUTDOWN_WAIT_ALL_BUFFERS
0x11...SHUTDOWN_WAIT_CRITICAL_BUFFERS
0xFE...KILL_QUERY
0xFF... KILL_CONNECTION
defined in mysql_com.h


[edit]
COM_STATISTICS

　　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.


[edit]
COM_PROCESS_INFO

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


[edit]
COM_PROCESS_KILL

　　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.


[edit]
COM_CHANGE_USER

　　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?)


[edit]
COM_BINLOG_DUMP

　　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.



[edit]
COM_TABLE_DUMP

　　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)

　　



[edit]
COM_REGISTER_SLAVE

　　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.



[edit]
COM_PREPARE

　　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)


[edit]
COM_EXECUTE

　　this is documented
elsewhere


[edit]
COM_LONG_DATA

　　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)


[edit]
COM_CLOSE_STMT

　　Destroy a prepared statement. The statement handle becomes invalid.

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


[edit]
COM_RESET_STMT

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


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


[edit]
COM_SET_OPTION

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

• MYSQL_OPTION_MULTI_STATEMENTS_ON
  
• MYSQL_OPTION_MULTI_STATEMENTS_OFF
  

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


[edit]
COM_FETCH_STMT

　　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.

VERSION 4.0
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
VERSION 4.1
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,
the number 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
last.
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/protocol.cc 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.

VERSION 4.0
Bytes                       Name
-----                       ----
1                           field_count, always = 0xff
2                           errno (little endian)
n                           message
VERSION 4.1
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/protocol.cc 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.


VERSION 4.0
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
VERSION 4.1
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/sql_base.cc 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.


VERSION 4.0
Bytes                 Name
-----                 ----
1                     field_count, always = 0xfe
VERSION 4.1
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) protocol.cc 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                       0 (packet header)
(col_count+7+2)/8       Null Bit Map with first two bits = 01
n                       column values
Null Bit Map: The first 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).  
NOTE: MySQL 5.x these 2 bits are always 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-NULL 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
If the sub-second part is 0, it isn't sent.
If the time-part is 00:00:00 too, it isnt' sent either.
If all fields are 0, nothing is sent, but the length byte.

　　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.

　　It is made up of:

• a PREPARE_OK packet
  
• if "number of parameters" > 0
  
  
  
  • (field packets) as in a Result Set Header Packet
    
  • (EOF packet)
    
  
  
  
• if "number of columns" > 0
  
  
  
  • (field packets) as in a Result Set Header Packet
    
  • (EOF packet)
    
  
  
  

　　The PREPARE_OK packet is:

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

　　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
if param_count > 0:
(param_count+7)/8    null_bit_map
1                    new_parameter_bound_flag
if new_params_bound == 1:
n*2                  type of parameters
n                    values for the parameters
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;
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.
values:        for all non-NULL values, each parameters appends its value
as described in Row Data Packet: Binary (column values)


　　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.



[edit]
Encryption

　　If the server advertises SSL (aka TLS) capability by setting the CLIENT_SSL flag in the initial greeting packet, then the client can request that the connection be encrypted.

　　For an encrypted connection, the client first sends an abbreviated client authentication packet containing only the flags word, with the CLIENT_SSL bit set. Without waiting for a server response, the client then begins the
TLS handshake (starting with the ClientHello message). Once the SSL/TLS session has been established, the client sends the full client authentication packet over the newly established channel (including the flags field again, as well as the remaining authentication
fields). The server responds to this client authentication packet as usual.
　　Retrieved from "http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol"