MySQL 5.7.12 – Part 2: Improving the MySQL Protocol

The X Protocol

For the MySQL Document Store we developed a new protocol to lay out a solid foundation for the features we’ll implement over the next years.

Building a Protocol with Async APIs in mind

Asynchronous APIs are all about executing other work while a task gets blocked. Instead of waiting in the client until the server finished executing of the query:

you want:

On the network this translates into:

Client -[#red]> Server: Sql::StmtExecute("DO 1") ... a RTT + ExecTime later... Server -[#red]-> Client: Sql::StmtExecuteOk


Let’s assume we actually want to send multiple, independent queries to the server:

Client -[#red]> Server: Sql::StmtExecute("DO 1") ... a RTT + ExecTime later... Server -[#red]-> Client: Sql::StmtExecuteOk Client -[#blue]> Server: Sql::StmtExecute("DO 2") ... a RTT + ExecTime later... Server -[#blue]-> Client: Sql::StmtExecuteOk

As the two statements are independent the client could also send the two statements first and then wait for completion:

Client -[#red]> Server: Sql::StmtExecute("DO 1") Client -[#blue]> Server: Sql::StmtExecute("DO 2") ... a RTT + 3x ExecTime later... Server -[#red]-> Client: Sql::StmtExecuteOk Server -[#blue]-> Client: Sql::StmtExecuteOk

The server will not notice a difference between the two scenarios. In both cases the statements are received in the same order and are executed in the same way. But as the client didn’t wait for the response of the first command before sending the second command and a whole Round Trip Time (RTT) is saved.

The faster the statement execution time becomes compared to the networks RTT the more visible the effect of pipelining becomes:

  • mostly SELECTs on Primary Key on one side
  • congested networks on the other


As the client doesn’t wait for responses from the server it doesn’t have a good way to handle errors.

In programming languages one can use Exceptions to handle unexpected situations in a higher layer:

Handling it in the application requires to wait for the response and therefore destroys the idea of pipelining. It can be solved by letting the server know about the client’s expectations:

Client -> Server: Expect::Open([+no_error]) Client -> Server: Sql::StmtExecute("INSERT ...") Client -> Server: Sql::StmtExecute("INSERT ...") Client -> Server: Sql::StmtExecute("INSERT ...") Client -> Server: Expect::Close Server --> Client: Ok Server --> Client: Ok Server --> Client: Error("duplicate key") Server --> Client: Error("expectation failed") Server --> Client: Ok

The server will let all statements fail with an distinct error as soon as the first statement of the block fails.

Designing the X Protocol

The X Protocol is built around a few core ideas:

  • reuse existing concepts that are proven
  • allow to generate most of the code to simplify community adoption
  • be extensible to allow protocol evolution

Taking the Good Parts

While designing the X Protocol we checked the MySQL Client/Server protocol for concepts and ideas we would like to see in the X Protocol too:

  • negotiation of protocol capabilities to allow evolve
    the protocol over the years
  • compression via zlib’s DEFLATE method
  • enabling encryption of the connection via TLS after negotiation
  • a space efficient variable length integer encoding
  • multiple authentication methods

Message Description

To generate most of the code we looked for a solid, already existing message serialization language and picked Google Protobuf.


  • is compact on the wire
  • allows adding new fields to messages
  • has basic datatypes for numbers, strings and messages
  • has great language support

for the low-level message description.


The basic, optional protocol features like TLS can be negotiated at connection setup:

== Negotiation == Client -> Server: CapabilitiesGet() Server --> Client: { "tls": 0, ... } Client -> Server: CapabilitiesSet({"tls" : 1}) Server --> Client: Ok

This negotiation step is optional and can be skipped.


On the authentication layer the X Protocol uses SASL which defines a very basic protocol of four messages:

  • starting authentication from the client
  • continuing by client or server
  • final Ok or Error

For the MYSQL41 authentication mechanism it may look like:
== Authentication == Client -> Server: Session::AuthenticateStart(mech="MYSQL41", ...) Server --> Client: Session::AuthenticateContinue(auth_data="...") Client --> Server: Session::AuthenticateContinue(auth_data="...") Server --> Client: Session::AuthenticateOk()

SASL has a growing list of SASL mechanisms to perform the actual authentication.

The X Protocol currently supports:

In the backend the PLAIN mechanism can authenticate against mysql_native_password and various other authentication plugins.


Notices are sent from the server to the client and can carry:

  • warnings
  • session variable changes
  • global state changes

and can be either local or global.

Local notices belong to the currently executed message like:

  • warnings
  • last insert id
  • affected rows

Global notices on the other side are independent of the currently execute message and could be:

The list of notices can be extended and will allow to implement nice features where a channel from the server to the client is needed.


The concepts around SQL execution are taking over from the MySQL Client/Server Protocol:

  • send statement in plaintext
  • receive one or more resultsets with
    • column definitions
    • zero or more rows

As we use Protobuf as the serialization in the X Protocol we get the behaviour of COM_QUERY with the nice compact encoding of the resultset of COM_STMT_EXECUTE.


Next the plaintext SQL statements the X Protocol also supports a set of CRUD

  • Crud::Find
  • Crud::Insert
  • Crud::Update
  • Crud::Delete

which provide a subset of the SQL in binary form and allows to query and modify

  • tables
  • collections (tables with a JSON column)

The server will “do the right thing” and translate the CRUD messages into the queries for the given data model.

More Details

The X Protocol is documented in more detail in and in WL#8639

If you have questions or feedback feel free to leave a comment below or join us in the Forum

14 thoughts on “MySQL 5.7.12 – Part 2: Improving the MySQL Protocol

  1. Sound great. But how about a quick guide and examples like a tutorial, for example, how to setup, how client connects to the server and how to communicate?

  2. Sounds excellent, but please allow for something other than zlib. We need something with less latency and should either sacrifice some compression (lz4, snappy, quicklz) or lets support zstd

    1. Thanks Mark. Good suggestion. We agree totally. I think Jan was referring to the compression support in the classic MySQL protocol when he mentioned zlib. When compression is implemented in the X plugin, we will indeed use one of the faster, newer compression libs.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.