转载自：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 theinternals 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 PacketThe 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 scramble10                            (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 passwordis 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 asmysqli::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 viamysqladmin 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 withmysqladmin 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 withmysqladmin status. No arguments.

[edit]COM_PROCESS_INFO

Functional equivalent to the SQL statement SHOW PROCESSLIST. This packet can be sent bymysqladmin 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 withmysqladmin 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 withmysqladmin 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 inmysql_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             42S02message             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_GEOMETRYflags:                   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_FLAGdecimals:                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                .stddb                  03 64 62 31                .db1table               02 54 37                   .T7org_table           02 74 37                   .t7name                02 53 31                   .S1org_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 theTLS 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"