2. mysql
  3. MySQL Protocol

MySQL Protocol

In my recent work I had to dive into the Internals of the MySQL protocol.

Most of the protocol is described in Chapter 8. MySQL Client/Server Protocol. "Most" as it documents the state of MySQL 4.1.x before it was stable. It is interesting for all developers who want to extend the mysql-server or want to write a native connector to MySQL which isn't using libmysqlclient.

Note: the protocol definition and the derived work at released under the terms of the GPL. See 8.1. Licensing Notice

Connection States

The MySQL protocol has two phases: auth and query.

Connection States

The auth-phase is:

  1. client connects to the server
  2. servers sends handshake packet
  3. client sends a auth packet
  4. servers sends either OK or ERR

The afterwards the client and server enter the query-phase.

  1. Client sends a COM_* packet
  2. Server sends OK, ERR, EOF or a result-set

Packets

All the packets start with a 4-byte packet-header. It is made up of a 3-byte packet-length and a 1-byte sequence number. Whenever the client sends a new Query it resets the sequence-number to 0. Keep in mind that some statements might overflow the sequence-number (result-set with more than 255 fields)

If a packet is larger than 16MByte (3-byte length header) it is indicated by a packet length of 0x00ffffff which indicates that the packet is finished by another packet until the packet-len is less than 16MByte. You will see this when you handle BLOBs.

If you are sending 32MByte of data you will see 2 packets with 16MByte and a empty packet which is closing it:

\377\377\377\1
  ....
\377\377\377\2
  ....
\0\0\0\3

EOF packet

The EOF packet is pretty simple and usually used to close different sections in a result-set. 

\5\0\0\5  [5 byte, packet 5]
  \376\0\0\2\0  [EOF, flags: autocommit]

note: I splitted the stream into the logic pieces. It is using the octal notation used by strace.

The flags are taken from include/mysql_com.h:

#define SERVER_STATUS_IN_TRANS     1    /* Transaction has started */
#define SERVER_STATUS_AUTOCOMMIT   2    /* Server in auto_commit mode */
#define SERVER_MORE_RESULTS_EXISTS 8    /* Multi query - next query exists */
#define SERVER_QUERY_NO_GOOD_INDEX_USED 16
#define SERVER_QUERY_NO_INDEX_USED      32
/*
  The server was able to fulfill the clients request and opened a
  read-only non-scrollable cursor for a query. This flag comes
  in reply to COM_STMT_EXECUTE and COM_STMT_FETCH commands.
*/
#define SERVER_STATUS_CURSOR_EXISTS 64
/*
  This flag is sent when a read-only cursor is exhausted, in reply to
  COM_STMT_FETCH command.
*/
#define SERVER_STATUS_LAST_ROW_SENT 128
#define SERVER_STATUS_DB_DROPPED        256 /* A database was dropped */
#define SERVER_STATUS_NO_BACKSLASH_ESCAPES 512

SERVER_QUERY_NO_GOOD_INDEX_USED and SERVER_QUERY_NO_INDEX_USED are nice hints for the application.

Result Sets

For most other statements like INSERT, UPDATE, DELETE you'll just get a OK packet.

For SELECT queries and FETCH you can have a result-set. For the query like:

SELECT @@version_comment LIMIT 1;

... the result-set is splitted into 2 parts:

  • the field description
  • the rows

Both sections are finished with an EOF packet. If the result-set is empty you might either have no result-set or just a field-description:

 SELECT 1 FROM dual LIMIT 0;

will give you 

\1\0\0\1 [1 byte, packet 1]
  \1  [1 field]
\27\0\0\2 [23 bytes, packet 2]
  \3def\0\0\0\0011\0\f?\0\1\0\0\0\10\201\0\0\0\0
\5\0\0\3 [5 bytes, packet 3]
  \376\0\0\2\0 [EOF for fields]
\5\0\0\4 [5 bytes, packet 4]
  \376\0\0\2\0 [EOF for rows]

For a real result-set you have:

\1\0\0\1
  \1 [1 field]
\'\0\0\2
  \3def         - always \3def
  \0
  \0
  \0
  \21@@version_comment
  \0            - org-name
  \f            - filler
  \10\0         - charset
  \34\0\0\0     - length (28 byte, max length of the field)
  \375          - type (MYSQL_TYPE_VAR_STRING, see include/mysql_com.h)
  \1\0          - flags (NOT_NULL_FLAG, see include/mysql_com.h)
  \37           - decimals
  \0\0          - filler
\5\0\0\3
  \376\0\0\2\0 [EOF]
\35\0\0\4
  \34MySQL Community Server (GPL)
\5\0\0\5
  \376\0\0\2\0 [EOF]

Keep an eye on the EOF packet as they might have flags which result in more packets to be fetched before the query is really finished.

  • SERVER_MORE_RESULTS_EXISTS is used in multi-query result-sets to flag that there is another result-set coming up.
  • SERVER_STATUS_CURSOR_EXISTS is used to mark a short result-set for COM_STMT_EXECUTE and COM_STMT_FETCH
  • SERVER_STATUS_LAST_ROW_SENT is used to mark the end of the result-set on COM_STMT_FETCH

Some SELECT statements get OK instead:

SELECT 1 FROM dual INTO OUTFILE 'example.txt'

returns:

\7\0\0\1
  \0\0\0\2\0\0\0

Replication Client

The documentation for the replication client protocol is a bit out-dated as it only covers MySQL 4.1 and older. To get a log-event-stream you have to send a [COM_BINLOG_DUMP] command:

- COM_BINLOG_DUMP
  - 4byte binlog-pos
  - 2byte flags (BINLOG_DUMP_NON_BLOCK see sql/mysql_priv.h)
  - 4byte slave-server-id
  - nul-term binlog name

You can get the binlog-pos and the binlog-name with SHOW MASTER STATUS:

> SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| hostname-bin.000009 | 25075226 |              |                  |
+---------------------+----------+--------------+------------------+

The slave-server-id should be unique across the replication-tree. The flags can only be BINLOG_DUMP_NON_BLOCK for now which causes are EOF packet to be sent if no new is available. Pretty much like a EAGAIN in for a read() syscall.

Log Events

Each log-event has the same header. After the error-code field you might have a variable-size option field which is only used in 5.0 and later.

When you execute:

jan@localhost[world]> DROP TABLE t1;

... you get:

N\0\0\3
  \0
    g\255\7F   - timestamp
    \2         - QUERY_EVENT
    \1\0\0\0   - server-id
    M\0\0\0    - event-size
    \263\0\0\0 - log-pos
    \20\0      - flags
      \2\0\0\0 - thread-id
      \0\0\0\0 - query-time
      \5       - str-len of default-db (world)
      \0\0     - error-code on master-side
        \32\0  - var-size-len (5.0 and later)
          \0   - Q_FLAGS2_CODE
            \0@\0\0 - flags (4byte)
          \1   - Q_SQL_MODE_CODE
            \0\0\0\0\0\0\0\0 (8byte)
          \6   - Q_CATALOG_NZ_CODE
            \3std (4byte)
          \4   - Q_CHARSET_CODE
            \10\0\10\0\10\0 (6byte)
          world\0 -
          drop table t1

The flags are defined in sql/log_event.h.

Comments

Enable javascript to load comments.