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:

res_1 = conn.query("DO 1")

1	res_1 = conn.query("DO 1")

you want:

hndl_1 = conn.query_send("DO 1")

# do something else and wait for completion
res_1 = conn.query_recv(hndl_1)

hndl_1 = conn.query_send("DO 1")

# do something else and wait for completion

res_1 = conn.query_recv(hndl_1)

On the network this translates into:

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

Pipelining

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

  res_1 = conn.query("DO 1");
  res_2 = conn.query("DO 2");

1 2	res_1 = conn.query("DO 1"); res_2 = conn.query("DO 2");

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:

  hndl_1 = conn.query_send("DO 1");
  hndl_2 = conn.query_send("DO 2");

  # wait for completion
  res_1 = conn.query_recv(hndl_1);
  res_2 = conn.query_recv(hndl_2);

hndl_1 = conn.query_send("DO 1");

hndl_2 = conn.query_send("DO 2");

# wait for completion

res_1 = conn.query_recv(hndl_1);

res_2 = conn.query_recv(hndl_2);

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

Expectations

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:

  try:
    # trigger a duplicate key exception
    conn.query("INSERT INTO tbl VALUES (1)")
    conn.query("INSERT INTO tbl VALUES (1)")
    # not executed
    conn.query("INSERT INTO tbl VALUES (1)")
  except Exception:
    pass

try:

# trigger a duplicate key exception

conn.query("INSERT INTO tbl VALUES (1)")

# not executed

conn.query("INSERT INTO tbl VALUES (1)")

except Exception:

pass

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

Check https://github.com/mysql/mysql-server/tree/5.7/rapid/plugin/x/protocol
for the low-level message description.

Negotiation

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.

Authentication

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:

PLAIN over TLS (see RFC 4616)
MYSQL41

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

Notices

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:

Server is going down

1	Server is going down

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.

SQL

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.

CRUD

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

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.

  -- table
  SELECT * FROM tbl WHERE id = 1;

  -- collection
  SELECT * FROM tbl WHERE JSON_EXTRACT(doc, "$.id") = 1;

-- table

SELECT * FROM tbl WHERE id = 1;

-- collection

SELECT * FROM tbl WHERE JSON_EXTRACT(doc, "$.id") = 1;

More Details

The X Protocol is documented in more detail in http://dev.mysql.com/doc/internals/en/x-protocol.html and in WL#8639

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