Understanding MySQL X (All Flavors)

what is MySQL X Protocol

what is MySQL X ProtocolSince 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)


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:

MySQL  localhost:33060+ ssl  JS > var test = require('mysqlx');
 MySQL  localhost:33060+ ssl  JS > var session = mysqlx.getSession({host: 'localhost', user: 'root', password: 'root', port: 3306});

So what’s new here? How does it help, and how I can make use of it? First let’s try to illustrate the architecture:


MySQL X the components

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:

[root@data1 ~]# mysqlsh
MySQL Shell 8.0.13
Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > var test_conn = require('mysqlx');
 MySQL  JS > var session = mysqlx.getSession({host: 'localhost', user: 'root', password: 'root', port: 33060});   #creating session, notice X protocol listen port 33060 by default
 MySQL  JS > test_collection = session.getSchema('test').createCollection("people");
 MySQL  JS > test_collection.add({birth:"1988-06-12", Name: "Francisco"});
Query OK, 1 item affected (0.0456 sec)
 MySQL  JS > test_collection.add({birth:"2001-11-03", Name: "Maria", Nickname: "Mary"});
Query OK, 1 item affected (0.0255 sec)
 MySQL  JS > test_collection.find();
        "Name": "Francisco",
        "_id": "00005c19099f0000000000000004",
        "birth": "1988-06-12"
        "Name": "Maria",
        "Nickname": "Mary",
        "_id": "00005c19099f0000000000000005",
        "birth": "2001-11-03"
2 documents in set (0.0005 sec)
 MySQL  JS > \sql 									#simple command to switch between modes
Switching to SQL mode... Commands end with ;
 MySQL  SQL > \connect root@localhost
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 36 (X protocol)
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  SQL > use test
Default schema set to `test`.
Fetching table and column names from `test` for auto-completion... Press ^C to stop.
 MySQL  localhost:33060+ ssl  test  SQL >  CREATE TABLE `people2` (
                                       ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                                       ->   `birth` datetime NOT NULL,
                                       ->   `name` varchar(45) NOT NULL DEFAULT '',
                                       ->   `nickname` varchar(45) NULL DEFAULT '',
                                       ->   PRIMARY KEY (`id`)
                                       -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.1056 sec)
 MySQL  localhost:33060+ ssl  test  SQL > insert into people2(birth, name, nickname) values('2010-05-01', 'Peter', null), ('1999-10-14','Joseph', 'Joe');
Query OK, 2 rows affected (0.0326 sec)
 MySQL  localhost:33060+ ssl  test  SQL > select * from people2;
| id | birth               | name   | nickname |
|  1 | 2010-05-01 00:00:00 | Peter  | NULL     |
|  2 | 1999-10-14 00:00:00 | Joseph | Joe      |
2 rows in set (0.0004 sec)
 MySQL  localhost:33060+ ssl  test  SQL > select * from people;
| doc                                                                                                 | _id                          |
| {"_id": "00005c19099f0000000000000004", "Name": "Francisco", "birth": "1988-06-12"}                 | 00005c19099f0000000000000004 |
| {"_id": "00005c19099f0000000000000005", "Name": "Maria", "birth": "2001-11-03", "Nickname": "Mary"} | 00005c19099f0000000000000005 |
2 rows in set (0.0028 sec)

Interesting right? Within the same shell, I’ve created session to run over X protocol, and handled both document and relational objects, all without quitting from shell.

Is this all? Of course not! We are just scratching the surface, we haven’t used asynchronous calls nor CRUD operations. In fact, these topics are enough for a blog post each. Hopefully, though, the What’s are answered for now – at least a little –and if that’s the case, I’ll be very happy!

Photo by Deva Darshan on Unsplash

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com