Understand server response packets in MariaDB's client/server protocol. This section details the various types of packets sent by the server, including OK, Error, and Result Set packets.
MariaDB Server sends the following packets as part of a result set.
A result set consists of different packets:
If not (MARIADB_CLIENT_CACHE_METADATA
capability set) OR
(send metadata == 1):
For each column (for instance, column_count
times):
If not (CLIENT_DEPRECATE_EOF
capability set) EOF_Packet.
If error:
Else:
If CLIENT_DEPRECATE_EOF
capability:
OK_Packet with a 0xFE
header.
Else EOF_Packet.
It would be unsafe to assume that any packet with a 0xFE header is an OK packet (OK_Packet) or an EOF packet (EOF_Packet), because result-set row packets (ResultsetRow) can also begin with 0xFE when using the text protocol with a field length greater than 0xFFFFFF. To safely confirm that a packet with a 0xFE header is an OK packet (OK_Packet) or an EOF packet (EOF_Packet), you must also check that the packet length is less than 0xFFFFFF.
The column count packet describes the number of columns in the result set. It uses the following format:
int column count.
If (MARIADB_CLIENT_CACHE_METADATA
capability set):
int<1> metadata follows (0 / 1).
The metadata indicator byte is only present if both the client and the server declare the MARIADB_CLIENT_CACHE_METADATA
capability.
If the metadata byte is set to 1
, the normal metadata follows the column definitions. If the metadata byte is set to 0
, the Column Count Packet is immediately followed by the second EOF packet (EOF_Packet) or the result set rows if the CLIENT_DEPRECATE_EOF
capability is set.
A column definition packet describes a column in the result set. It uses the following format:
string catalog (always 'def').
string schema.
string table alias.
string table.
string column alias.
string column.
If extended type supported (see MARIADB_CLIENT_EXTENDED_METADATA
):
int length of fixed fields (=0xC).
int<2> character set number.
int<4> max. column size.
int<1> decimals.
int<2> - unused -
The column type field in the column definition packet describes the base type of the column. It also indicates how the values are encoded for COM_STMT_EXECUTE
parameters and binary result set rows.
6
MYSQL_TYPE_NULL
Not used, nullness is indicated by the NULL-bitmap in the result
17
MYSQL_TYPE_TIMESTAMP2
Used only in the replication protocol
18
MYSQL_TYPE_DATETIME2
Used only in the replication protocol
19
MYSQL_TYPE_TIME2
Used only in the replication protocol
The column details flag describes certain column attributes and whether certain column options are set.
It is a bitmask with the following flags:
1
NOT_NULL
field cannot be null
2
PRIMARY_KEY
field is a primary key
4
UNIQUE_KEY
field is unique
8
MULTIPLE_KEY
field is in a multiple key
16
BLOB
is this field a Blob
32
UNSIGNED
is this field unsigned
64
ZEROFILL_FLAG
is this field a zerofill
128
BINARY_COLLATION
whether this field has a binary collation
256
ENUM
Field is an enumeration
512
AUTO_INCREMENT
field auto-increment
1024
TIMESTAMP
field is a timestamp value
2048
SET
field is a SET
4096
NO_DEFAULT_VALUE_FLAG
field doesn't have default value
8192
ON_UPDATE_NOW_FLAG
field is set to NOW on UPDATE
32768
NUM_FLAG
field is num
This extended column type information can be used to find out more specific details about the column type:
For a POINT column, the column type field is MYSQL_TYPE_GEOMETRY
, but the extended type indicates 'point'.
For a JSON column, the column type field is MYSQL_TYPE_STRING
, but the extended type indicates 'json'.
This page is licensed: CC BY-SA / Gnu FDL
A result set row represents a database result set unit, which is usually generated by executing a statement that queries the database. Using COM_STMT_EXECUTE, the result set row is in binary format, otherswise in text format.
For each column:
string column data.
The byte representation of the string according to client character collation.
byte<1> 0x00
header.
byte<(number_of_columns + 7) / 8> NULL-Bitmap.
For each column:
If column value is not null:
If MYSQL_TYPE_DOUBLE
type : DOUBLE Binary encoding
If MYSQL_TYPE_LONGLONG
type : BIGINT Binary encoding
If MYSQL_TYPE_INTEGER
type : INTEGER Binary encoding
If MYSQL_TYPE_MEDIUMINT
type : MEDIUMINT Binary encoding
If MYSQL_TYPE_FLOAT
type : FLOAT Binary encoding
If MYSQL_TYPE_SMALLINT
type : SMALLINTBinary encoding
If MYSQL_TYPE_YEAR
type : YEAR Binary encoding
If MYSQL_TYPE_TINYINT
type : TINYINT Binary encoding
If MYSQL_TYPE_DATE
type : DATE Binary encoding
If MYSQL_TYPE_TIMESTAMP
type : TIMESTAMP Binary encoding
If MYSQL_TYPE_DATETIME
type : TIMESTAMP Binary encoding
If MYSQL_TYPE_TIME
type : TIME Binary encoding
If MYSQL_TYPE_NEWDECIMAL
type : DECIMAL Binary encoding
If MYSQL_TYPE_TINY_BLOB
, MYSQL_TYPE_MEDIUM_BLOB
, MYSQL_TYPE_LONG_BLOB
, MYSQL_TYPE_BLOB
, MYSQL_TYPE_GEOMETRY
, MYSQL_TYPE_STRING
, MYSQL_TYPE_VARCHAR
, MYSQL_TYPE_VAR_STRING
): byte value
The NULL
-Bitmap indicates if a parameter for a column is null (one bit per parameter) beginning with the 3rd bit. NULL
-bitmap size is (number_of_columns + 7) / 8.
DECIMAL
has no fixed size, so will be encoded as string. A DECIMAL(10,2)
with a value of -15.5
is stored as:
06 45 49 53 46 53 48 . - 1 5 . 5 0
DOUBLE
is the IEEE 754 floating-point value in Little-endian format on 8 bytes.
BIGINT
is the value in Little-endian format on 8 bytes. Signed is defined by the Column field detail flag.
INTEGER
is the value in Little-endian format on 4 bytes. Signed is defined by the Column field detail flag.
MEDIUMINT
is similar to INTEGER
binary encoding, even if MEDIUM
int is 3-bytes encoded server side. (Last byte will always be 0x00).
FLOAT
is the IEEE 754 floating-point value in Little-endian format on 4 bytes.
SMALLINT
is the value in Little-endian format on 2 bytes. Signed is defined by the Column field detail flag.
YEAR
uses the same format as SMALLINT
.
TINYINT
is the value of 1 byte. Signed is defined by the Column field detail flag.
DATE
uses the same format as TIMESTAMP
binary encoding, with a data length of 0
for the special '0000-00-00
' value and 4
for the standard year/month/day format.
Data is encoded in 8 bytes without fractional seconds, 12 bytes with fractional seconds.
1
data length : 0 for special '0000-00-00 00:00:00' value. 4 with year + month + day of month only 7 for timestamps without fractional seconds 11 with fractional seconds
2-3
year on 2 bytes little-endian format
4
Month ( 1=january)
5
days of month
6
hour of day (0 if DATE type)
7
minutes (0 if DATE type)
8
seconds (0 if DATE type)
9-12
micro-second on 4 bytes little-endian format (only if data-length is > 7)
Data is encoded in 9 bytes without fractional seconds, 13 bytes with fractional seconds.
int<1> data length: 0 for special '00:00:00' value, 8 without fractional seconds, 12 with fractional seconds.
This page is licensed: CC BY-SA / Gnu FDL
OK_Packet
is sent by the server to the client and indicates a successful completion of a command sent by the client before.
Server to client.
int<1> 0x00 : OK_Packet
header or (0xFE
if CLIENT_DEPRECATE_EOF
is set).
int affected rows.
int last insert id.
int<2> warning count.
If packet has more data:
string info.
If (status flags & SERVER_SESSION_STATE_CHANGED
) and session_tracking_supported
(see CLIENT_SESSION_TRACK):
The length-encoded info string is not always included in the packet. Check the length of the packet to detect if there is data after the warning count. For the first OK_Packet
in the connection it contains (if present) the SSL certificate verification signature. For the following OK_Packets
it contains (if present) various human-readable information.
Values of server status flags:
SERVER_STATUS_IN_TRANS
1
A transaction is currently active.
SERVER_STATUS_AUTOCOMMIT
2
Autocommit mode is set.
SERVER_MORE_RESULTS_EXISTS
8
More results exists (more packets will follow).
SERVER_QUERY_NO_INDEX_USED
32
The query did not use an index.
SERVER_STATUS_CURSOR_EXISTS
64
When using COM_STMT_FETCH
, indicate that current cursor still has result.
SERVER_STATUS_LAST_ROW_SENT
128
When using COM_STMT_FETCH
, indicate that current cursor has finished to send results.
SERVER_STATUS_DB_DROPPED
1<<8
Database has been dropped.
SERVER_STATUS_NO_BACKSLASH_ESCAPES
1<<9
Current escape mode is "no backslash escape".
SERVER_STATUS_METADATA_CHANGED
1<<10
A DDL change did have an impact on an existing PREPARE
(an automatic re-prepare has been executed).
SERVER_PS_OUT_PARAMS
1<<12
This result set contains stored procedure output parameter.
SERVER_STATUS_IN_TRANS_READONLY
1<<13
Current transaction is a read-only transaction.
SERVER_SESSION_STATE_CHANGED
1<<14
Session state change. See session change type for more information.
While packet has remaining data:
0
SESSION_TRACK_SYSTEM_VARIABLES
1
SESSION_TRACK_SCHEMA
2
SESSION_TRACK_STATE_CHANGE
3
SESSION_TRACK_GTIDS
4
SESSION_TRACK_TRANSACTION_CHARACTERISTICS
5
SESSION_TRACK_TRANSACTION_STATE
Each type of data has its own kind of format:
string new current schema.
While there is remaining data:
string variable data.
For each variable data:
Indicates if session state changes occurred. The value is represented as 1
.
string 1
if session state tracking was enabled.
This tracker is not implemented by MariaDB.
string Transaction characteristics.
Transaction characteristics is the set of SQL statements that reproduce the type and state of the current transaction. It can consist of the following SQL statements:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY;
START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE;
SET TRANSACTION READ ONLY;
SET TRANSACTION READ WRITE;
XA START <XA specification>;
string Transaction state string.
The transaction state string is always 8 characters long. The characters, in order, are:
No transaction: _
Explicit transaction: T
Implicit transaction: I
Transaction read safe: _
Transaction read unsafe: r
Unknown transaction type: _
Read-only transaction: R
Transaction write safe: _
Transaction write unsafe: w
Unknown transaction type: _
Read-write transaction: W
Transaction statement safe: _
Transaction statement unsafe: s
Transaction does not have resultsets: _
Transaction with result sets: S
No locked tables: _
Tables have been locked: L
To determine if session tracking is enabled, check if the CLIENT_SESSION_TRACK flag is set in server_capabilities
.
This page is licensed: CC BY-SA / Gnu FDL
The EOF_Packet
marks the end of a result set, and returns status and warnings.
When testing for an EOF packet, the packet size must be less than 9 bytes in length. The result set can send data that begin with a 0xfe
byte, but then the packet length will be greater than 9.
This page is licensed: CC BY-SA / Gnu FDL
ERR_Packet
indicates that an error occurred.
int<1> ERR_Packet
header = 0xFF
.
int<2> error code. see error list.
Note that the ERR_Packet
is supposed to send a server error to the client. In particular, all error codes in the range 2000 to 2999 and 5000 to 5999 (inclusive) are reserved for client errors and an ERR packet with such an error code will be considered malformed.
This page is licensed: CC BY-SA / Gnu FDL
If the client sends a LOAD DATA LOCAL INFILE
statement via com_query, the server responds with LOCAL_INFILE_Packet
to tell the client to send a specified file to the server.
The client sends the file as the packet body. If the file is large, the contents are sent in multiple separate packets. After the file is sent, the client must send an empty packet to indicate that no more data will follow.
Once the client has finished sending the file, the server will respond with an OK_packet or an ERR_packet.
This page is licensed: CC BY-SA / Gnu FDL