Understanding MySQL X (All Flavors)

Here is the article. 

Since 5.7.12 MySQL includes what is called the X plugin, but also it includes X protocol and X DevApi. But what is all this and how does it work? Let me share a personal short story on how I found myself investigating this feature. In a previous post I wrote about the MySQL Router tool, and our colleague Mr. Lefred pointed out that I was wrong about X protocol, because I mentioned it was created to be used with JSON docs. Given this input, I wanted to investigate in a little bit more depth about what all this “X” means and how it can be used in our day to day operations.

First problem I found is that the documentation is pretty extensive in the how’s but it was really hard to find the what’s. This is a bit strange, because for people trying to research about this new feature the documentation is not very helpful. In fact, I had to go to different websites to get a sense of what X means, how it works, and what it was created for.

Let’s start from the very beginning: what does the X stand for? Basically, it’s a way to name the crossover between relational and document models with extended capabilities, and the X is used for naming the three components we are describing: the plugin, the protocol and the DevApi.

X Plugin

This is the actual interface between MySQL server and the clients. By clients we can consider a variety of clients, not only the MySQL shell. It has to be installed in MySQL 5.7 versions via the INSTALL PLUGIN command but comes installed by default in MySQL 8. The plugin adds all the functionality, configuration variables, and status counters we need to use it.

It has the ability to work with both traditional SQL and Document objects, and also supports CRUD (Create, Read, Update, Delete) operations,  asynchronous query execution and so on – this provides a great capacity to extend the current way we work with MySQL.

X Protocol

This is a new client protocol created to ‘talk’ between the X Plugin and Clients.  I think it is fair to say this is an eXtended version of the MySQL protocol.
It was designed with the idea of having the capacity for asynchronous calls, meaning that you can send more than one query to server from same client without the need of waiting for first query to finish before sending the second and so. This improves the overall execution time by saving network round trips between clients and server.

Additionally, the protocol accepts CRUD operations and, of course, the handling of JSON documents and plain SQL. The protocol is fully implemented in MySQLShell and has several connectors for popular languages (Java and .Net for example)

X DevAPI

The last piece of this package is the X DevAPI protocol. Probably the best documented of these pieces is the API implemented on the MySQL Shell and connectors that supports the X Protocol. This API is designed to easily write programs from a given client using some popular languages. For example, we can easily create/test a program from MySQL Shell using Python or JavaScript.

The API defines few interesting concepts to handle sessions. These sessions can handle several connections to a server so in a specific session we can encapsulate more than one MySQL connection. You can define a basic session connection as follows (in JavaScript) using the MySQL Shell:

As you may notice, the X plugin adds a new interface that talks to X protocol, then this protocol is able to interact with connectors that supports the protocol (as mentioned above). The classic functionality is still present, so we just extended its functionality. The good part of this is that the protocol is capable of operating with both relational data and document store.

So now let’s check the funny part by putting all pieces together using a simple example using MySQL Shell: