Retrieved from "http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol"
The topic is: the contents of logical packets in MySQL version 5.0 client/server communication.
The description is of logical packets. There will be only passing mention of non-logical considerations, such as physical packets, transport, buffering, and compression. If you are interested in those topics, you may wish to consult another document: "MySQL Client - Server Protocol Documentation" in the file
net_doc.txt in the
internals directory of the
mysqldoc MySQL documentation repository.
The description is of the version-5.0 protocol at the time of writing. Most of the examples show version-4.1 tests, which is okay because the changes from version-4.1 to version-5.0 were small.
A typical description of a packet will include:
"Bytes and Names". This is intended as a quick summary of the lengths and identifiers for every field in the packet, in order of appearance. The "Bytes" column contains the length in bytes. The Names column contains names which are taken from the MySQL source code whenever possible. If the version-4.0 and version-4.1 formats differ significantly, we will show both formats.
Descriptions for each field. This contains text notes about the usage and possible contents.
(If necessary) notes about alternative terms. Naming in this document is not authoritative and you will often see different words used for the same things, in other documents.
(If necessary) references to program or header files in the MySQL source code. An example of such a reference is: sql/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.
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'".
The Packet Header
Bytes Name ----- ---- 3 Packet Length 1 Packet Number Packet Length: The length, in bytes, of the packet that follows the Packet Header. There may be some special values in the most significant byte. Since 2**24 = MB, the maximum packet length is 16MB. Packet Number: A serial number which can be used to ensure that all packets are present and in order. The first packet of a client query will have Packet Number = 0 Thus, when a new SQL statement starts, the packet number is re-initialised.
The Packet Header will not be shown in the descriptions of packets that follow this section. Think of it as always there. But logically, it "precedes the packet" rather than "is included in the packet".
Alternative terms: Packet Length is also called "packetsize". Packet Number is also called "Packet no".
Relevant MySQL Source Code: include/my_global.h int3store() sql/net_serv.cc my_net_write(), net_flush(), net_write_command(), my_net_read()
This is what happens in a typical session:
The Handshake (when client connects): Server Sends To Client: Handshake Initialisation Packet Client Sends To Server: Client Authentication Packet Server Sends To Client: OK Packet, or Error Packet The Commands (for every action the client wants the server to do): Client Sends To Server: Command Packet Server Sends To Client: OK Packet, or Error Packet, or Result Set Packet
In the rest of this chapter, you will find a description for each packet type, in separate sections.
Alternative terms: The Handshake is also called "client login" or "login procedure" or "connecting".
Handshake Initialization Packet
From server to client during initial handshake.
Bytes Name ----- ---- 1 protocol_version n (Null-Terminated String) server_version 4 thread_id 8 scramble_buff 1 (filler) always 0x00 2 server_capabilities 1 server_language 2 server_status 13 (filler) always 0x00 ... 13 rest of scramble_buff (4.1) protocol_version: The server takes this from PROTOCOL_VERSION in /include/mysql_version.h. Example value = 10. server_version: The server takes this from MYSQL_SERVER_VERSION in /include/mysql_version.h. Example value = "4.1.1-alpha". thread_number: ID of the server thread for this connection. scramble_buff: The password mechanism uses this. The second part are the last 13 bytes. (See "Password functions" section elsewhere in this document.) server_capabilities: CLIENT_XXX options. The possible flag values at time of writing (taken from include/mysql_com.h): CLIENT_LONG_PASSWORD 1 /* new more secure passwords */ CLIENT_FOUND_ROWS 2 /* Found instead of affected rows */ CLIENT_LONG_FLAG 4 /* Get all column flags */ CLIENT_CONNECT_WITH_DB 8 /* One can specify db on connect */ CLIENT_NO_SCHEMA 16 /* Don't allow database.table.column */ CLIENT_COMPRESS 32 /* Can use compression protocol */ CLIENT_ODBC 64 /* Odbc client */ CLIENT_LOCAL_FILES 128 /* Can use LOAD DATA LOCAL */ CLIENT_IGNORE_SPACE 256 /* Ignore spaces before '(' */ CLIENT_PROTOCOL_41 512 /* New 4.1 protocol */ CLIENT_INTERACTIVE 1024 /* This is an interactive client */ CLIENT_SSL 2048 /* Switch to SSL after handshake */ CLIENT_IGNORE_SIGPIPE 4096 /* IGNORE sigpipes */ CLIENT_TRANSACTIONS 8192 /* Client knows about transactions */ CLIENT_RESERVED 16384 /* Old flag for 4.1 protocol */ CLIENT_SECURE_CONNECTION 32768 /* New 4.1 authentication */ CLIENT_MULTI_STATEMENTS 65536 /* Enable/disable multi-stmt support */ CLIENT_MULTI_RESULTS 131072 /* Enable/disable multi-results */ server_language: current server character set number server_status: SERVER_STATUS_xxx flags: e.g. SERVER_STATUS_AUTOCOMMIT
Alternative terms: Handshake Initialization Packet is also called "greeting packet". Protocol version is also called "Prot. version". server_version is also called "Server Version String". thread_number is also called "Thread Number". current server charset number is also called "charset_no". scramble_buff is also called "crypt seed". server_status is also called "SERVER_STATUS_xxx flags" or "Server status variables".
Example Handshake Initialization Packet Hexadecimal ASCII ----------- ----- protocol_version 0a . server_version 34 2e 31 2e 31 2d 71 6c 4.1.1-al 70 68 61 2d 64 65 62 75 pha-debu 67 00 g. thread_number 01 00 00 00 .... scramble_buff 3a 23 3d 4b 43 4a 2e 43 ........ (filler) 00 . server_capabilities 2c 82 .. server_language 08 . server_status 02 00 .. (filler) 00 00 00 00 00 00 00 00 ........ 00 00 00 00 00
In the example, the server is telling the client that its server_capabilities include CLIENT_MULTI_RESULTS, CLIENT_SSL, CLIENT_COMPRESS, CLIENT_CONNECT_WITH_DB, CLIENT_FOUND_ROWS.
The "server_language" (or "charset") corresponds to the character_set_server variable in the MySQL server. This number also contains the collation used. Technically this number determines the collation and the character set is implicit for the collation. You can use the following SQL statement to get the cleartext information:
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)
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.
The Server Initialization Packet and the Client Authentication Packet both have an 8-byte field, scramble_buff. The value in this field is used for password authentication.
Relevant MySQL Source Code: libmysql/password.c, see also comments at start of file. It works thus:
4.0 and before
- The server sends a random string to the client, in scramble_buff.
- The client encrypts the scramble_buff value using the hash of a password that the user has entered. This happens in sql/password.c:scramble() function.
- The client sends the encrypted scramble_buff value to the server.
- The server encrypts the original random string using a value in the mysql database, mysql.user.Password.
- The server compares its encrypted random string to what the client sent in scramble_buff.
- If they are the same, the password is okay.
In this protocol, snooping on the wire doesn't reveal the password. But note the problem - if the client doesn't know the password, but knows a hash of it (as stored in mysql.user.Password) it can connect to the server. In other words, the hash of a password is the real password; if one can get the value of mysql.user.Password - he can connect to the server.
4.1 and later
remember that mysql.user.Password stores SHA1(SHA1(password))
- The server sends a random string (scramble) to the client
- the client calculates:
- stage1_hash = SHA1(password), using the password that the user has entered.
- token = SHA1(SHA1(stage1_hash), scramble) XOR stage1_hash
- the client sends the token to the server
- the server calculates
- stage1_hash' = token XOR SHA1(mysql.user.Password, scramble)
- the server compares SHA1(stage1_hash') and mysql.user.Password
- If they are the same, the password is okay.
This protocol fixes the flaw of the old one, neither snooping on the wire nor mysql.user.Password are sufficient for a successful connection. But when one has both mysql.user.Password and the intercepted data on the wire, he has enough information to connect.
Command Packet (Overview)
From client to server whenever the client wants the server to do something.
Bytes Name ----- ---- 1 command n arg command: The most common value is 03 COM_QUERY, because INSERT UPDATE DELETE SELECT etc. have this code. The possible values at time of writing (taken from /include/mysql_com.h for enum_server_command) are: # Name Associated client function - ---- -------------------------- 0x00 COM_SLEEP (none, this is an internal thread state) 0x01 COM_QUIT mysql_close 0x02 COM_INIT_DB mysql_select_db 0x03 COM_QUERY mysql_real_query 0x04 COM_FIELD_LIST mysql_list_fields 0x05 COM_CREATE_DB mysql_create_db (deprecated) 0x06 COM_DROP_DB mysql_drop_db (deprecated) 0x07 COM_REFRESH mysql_refresh 0x08 COM_SHUTDOWN mysql_shutdown 0x09 COM_STATISTICS mysql_stat 0x0a COM_PROCESS_INFO mysql_list_processes 0x0b COM_CONNECT (none, this is an internal thread state) 0x0c COM_PROCESS_KILL mysql_kill 0x0d COM_DEBUG mysql_dump_debug_info 0x0e COM_PING mysql_ping 0x0f COM_TIME (none, this is an internal thread state) 0x10 COM_DELAYED_INSERT (none, this is an internal thread state) 0x11 COM_CHANGE_USER mysql_change_user 0x12 COM_BINLOG_DUMP sent by the slave IO thread to request a binlog 0x13 COM_TABLE_DUMP LOAD TABLE ... FROM MASTER (deprecated) 0x14 COM_CONNECT_OUT (none, this is an internal thread state) 0x15 COM_REGISTER_SLAVE sent by the slave to register with the master (optional) 0x16 COM_STMT_PREPARE mysql_stmt_prepare 0x17 COM_STMT_EXECUTE mysql_stmt_execute 0x18 COM_STMT_SEND_LONG_DATA mysql_stmt_send_long_data 0x19 COM_STMT_CLOSE mysql_stmt_close 0x1a COM_STMT_RESET mysql_stmt_reset 0x1b COM_SET_OPTION mysql_set_server_option 0x1c COM_STMT_FETCH mysql_stmt_fetch arg: The text of the command is just the way the user typed it, there is no processing by the client (except removal of the final ';'). This field is not a null-terminated string; however, the size can be calculated from the packet size, and the MySQL client appends '/0' when receiving.
The command byte is stored in the thd structure for the MySQL worker threads and is shown in the Command column for SHOW PROCESSLIST. An inactive thread gets 0x00 (Sleep). The dedicated thread to execute INSERT DELAYED gets 0x10.
The replication requests (0x12 .. 0x15) cannot be send from regular clients, only from another server or from the mysqlbinlog program.
Relevant MySQL source code: sql-common/client.c cli_advanced_command(), mysql_send_query(). libmysql/libmysql.c mysql_real_query(), simple_command(), net_field_length().
Example Command Packet Hexadecimal ASCII ----------- ----- command 02 . arg 74 65 73 74 test
In the example, the value 02 in the command field stands for COM_INIT_DB. This is the packet that the client puts together for "use test;".
Command Packet (detailed description)
Closes the current connection. No arguments.
Functional equivalent to the SQL statement USE <database>. Exported by many clients, i.e. in PHP as mysqli::select_db()
Bytes Name ----- ---- n database name (up to end of packet, no termination character)
The most common request type. Used to execute nonprepared SQL statements.
Bytes Name ----- ---- n SQL statement (up to end of packet, no termination character)
Functional equivalent to SHOW [FULL] FIELDS FROM ...
Bytes Name ----- ---- n table name (null terminated) n column name or wildcard (optional)
results from a call of the C-API function mysql_create_db(). This function is marked deprecated; the recommended way to create a database is to use the SQL statement CREATE DATABASE.
Bytes Name ----- ---- n database name (up to end of packet, no termination character)
results from a call of the C-API function mysql_drop_db(). This function is marked deprecated; the recommended way to drop a database is to use the SQL statement DROP DATABASE.
Bytes Name ----- ---- n database name (up to end of packet, no termination character)
Parameter is one byte, evaluated as bitmap. Some (but not all) options are available via the FLUSH statement or via mysqladmin flush-foo
Bytes Name ----- ---- 1 bitmap of refresh options 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
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
Asks the MySQL server to compile a text message with some server statistics (uptime, queries per second, etc.). This packet can be sent with mysqladmin status. No arguments.
Functional equivalent to the SQL statement SHOW PROCESSLIST. This packet can be sent by mysqladmin processlist. No arguments.
Functional equivalent to the SQL statement KILL <id>.
Bytes Name ----- ---- 4 Process ID (little endian)
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.
This packet can be used to test the connection and to reset the connection inactivity counter in the MySQL server (wait_timeout). This packet can be sent with mysqladmin ping. Also exported by almost any client API. No arguments.
This packet is effectively a re-login without closing/opening the connection. Important side effect: this packet destroys the session context (temporary tables, session variables, etc.) in the MySQL server.
Some connection pool implementations use this to clean up the session context.
Bytes Name ----- ---- n user name (Null-terminated string) n password 3.23 scramble - Null-terminated string (9 bytes) 4.1 scramble - Length (1 byte) coded string (21 byte) n database name (Null-terminated string) 2 character set number (since 5.1.23?)
This request is the last request sent from slave to master when a replication connection is established. The master answers with a stream of response packets, each containing one binlog event. If the master goes down, it sends an EOF packet.
Bytes Name ----- ---- 4 binlog position to start at (little endian) 2 binlog flags (currently not used; always 0) 4 server_id of the slave (little endian) n binlog file name (optional)
If the binlog file name is not given, it defaults to the first binlog available on the master.
This request is sent from slave to master for a LOAD TABLE ... FROM MASTER statement. This feature is marked deprecated. Do not use!
Bytes Name ----- ---- n schema name (length coded string) n table name (length coded string)
If the report_host variable is set on the slave, it sends this packet when it establishs the replication connection.
Bytes Name ----- ---- 4 server_id on the slave (little endian) n report_host (length coded string) n report_user (length coded string) n report_password (length coded string) 2 report_port 4 rpl_recovery_rank 4 server_id on the master (always 0)
The rpl_recovery_rank is a MySQL server variable that can be set, but is not yet used. In the future this will be used for replication failover.
Prepare a SQL statement. This request is answered with a special OK packet (documented elsewhere), sending the statement handle. All the other request packets for prepared statements use this statement handle.
Bytes Name ----- ---- n query string with '?' place holders (up to end of packet, no termination character)
this is documented elsewhere
result of a call to mysql_stmt_send_long_data() to send a BLOB in pieces.
Bytes Name ----- ---- 4 Statement ID (little endian) 2 Parameter number (little endian) n payload (up to end of packet, no termination character)
Destroy a prepared statement. The statement handle becomes invalid.
Bytes Name ----- ---- 4 Statement ID (little endian)
Reset (empty) the parameter buffers for a prepared statement. Mostly used in connection with COM_LONG_DATA.
Bytes Name ----- ---- 4 Statement ID (little endian)
The parameter is a 16-bit integer. There is an ENUM type enum_mysql_set_option defined in mysql_com.h:
Bytes Name ----- ---- 2 option to be set (little endian)
Fetch result rows from a prepared statement. Can fetch a variable amount of rows.
Bytes Name ----- ---- 4 Statement ID (little endian) 4 number of rows to fetch (little endian)
 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
 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, it is returned here. Otherwise this field contains 0. Note: when using for example a multiple row INSERT the insert_id will be from the first row inserted, not from 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.
 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'.
 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.
 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).
 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 ..
 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.
 Row Data Packet: Binary (Tentative Description)
From server to client, or from client to server (if the client has a prepared statement, the "result set" packet format is used for transferring parameter descriptors and parameter data).
Recall that in the description of Row Data we said that: "If a column is defined as non-character, the server converts the value into a character before sending it." That doesn't have to be true. If it isn't true, it's a Row Data Packet: Binary.
Bytes Name ----- ---- 1 Null Bit Map with first two bits = 01 n (Length Coded String) (column value) ... Bytes Name ----- ---- ? Packet Header 1 Null Bit Map with first two bits = 01 Null Bit Map: The most significant 2 bits are reserved. Since there is always one bit on and one bit off, this can't be confused with the first byte of an Error Packet (255), the first byte of a Last Data Packet (254), or the first byte of an OK Packet (0). (column value): The column order and organization are the same as for conventional Row Data Packets. The difference is that each column value is sent just as it is stored. It's now up to the client to convert numbers to strings if that's desirable. For a description of column storage, see "Physical Attributes Of Columns" elsewhere in this document.
Only non-zero parameters are passed.
Because no conversion takes place, fixed-length data items are as described in the "Physical Attributes of Columns" section: one byte for TINYINT, two bytes for FLOAT, four bytes for FLOAT, etc. Strings will appear as packed-string-length plus string value. DATETIME, DATE and TIME will be as follows:
Type Size Comment ---- ---- ------- date 1 + 0-11 Length + 2 byte year, 1 byte MMDDHHMMSS, 4 byte billionth of a second datetime 1 + 0-11 Length + 2 byte year, 1 byte MMDDHHMMSS, 4 byte billionth of a second time 1 + 0-11 Length + sign (0 = pos, 1= neg), 4 byte days, 1 byte HHMMDD, 4 byte billionth of a second
Alternative Terms: Row Data Packet: Binary is also called "Binary result set packet".
Except for the different way of signalling NULLs, the server/client parameter interaction here proceeds the say way that the server sends result set data to the client. Since the data is not sent as a string, the length and meaning depend on the data type. The client must make appropriate conversions given its knowledge of the data type.
 OK for Prepared Statement Initialization Packet
From server to client, in response to prepared statement initialization packet.
Bytes Name ----- ---- 1 0 - marker for OK packet 4 statement_handler_id 2 number of columns in result set 2 number of parameters in query 1 filler (always 0) 2 warning count (Field Packets) column descriptors, as in a Result Set Header Packet (EOF Packet) marker: end of Data Packets
Alternative terms: statement_handler_id is called "statement handle" or "hstmt" everywhere but at MySQL. Prepared statement initialization packet is also called "prepared statement init packet".
 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.
 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
 Execute Packet (Tentative Description)
From client to server, to execute a prepared statement.
Bytes Name ----- ---- 1 code 4 statement_id 1 flags 4 iteration_count (param_count+7)/8 null_bit_map 1 new_parameter_bound_flag n*2 type of parameters (only if new_params_bound = 1) code: always COM_EXECUTE statement_id: statement identifier flags: reserved for future use. In MySQL 4.0, always 0. In MySQL 5.0: 0: CURSOR_TYPE_NO_CURSOR 1: CURSOR_TYPE_READ_ONLY 2: CURSOR_TYPE_FOR_UPDATE 4: CURSOR_TYPE_SCROLLABLE iteration_count: reserved for future use. Currently always 1. null_bit_map: A bitmap indicating parameters that are NULL. Bits are counted from LSB, using as many bytes as necessary ((param_count+7)/8) i.e. if the first parameter (parameter 0) is NULL, then the least significant bit in the first byte will be 1. new_parameter_bound_flag: Contains 1 if this is the first time that "execute" has been called, or if the parameters have been rebound. type: Occurs once for each parameter that is not NULL. The highest significant bit of this 16-bit value encodes the unsigned property. The other 15 bits are reserved for the type (only 8 currently used). This block is sent when parameters have been rebound or when a prepared statement is executed for the first time.
The Execute Packet is also known as "COM_EXECUTE Packet".
In response to an Execute Packet, the server should send back one of: an OK Packet, an Error Packet, or a series of Result Set Packets in which all the Row Data Packets are binary.
Relevant MySQL Source Code: libmysql/libmysql.c cli_read_prepare_result()
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.