The X Protocol represents the first major deviation from the existing client/server protocol in MySQL. The X Protocol is designed to be extensible, maximize security, and ensure good performance. All three of these categories were at the top of the list for must-have features and requirements when the X Protocol was designed.
Although the X Protocol is mainly hidden behind an abstraction layer by the clients that wrapper (implement it) such as the X Plugin and database connectors, it is important to learn how it works if you ever plan to implement your own application using the X Protocol. We will do so in Chapters 8 and 9. Even if you never intend to develop a MySQL client, a closer look at the X Protocol will reveal and further emphasize an example of the leap in technology under the hood in MySQL 8.
In this chapter, we explore the X Protocol and discover how it works. We also look at how to get started working with the X Protocol through a database connector. We see some examples of writing small scripts to interact with the X Protocol in Python via the Connector/Python library. Let’s get started with a detailed overview of the X Protocol and its origins.
Note
I present a lot of the concepts we discovered in the previous chapters with minimal explanation for brevity only duplicating information where clarity is needed.
Overview
If you have ever written a communication protocol either designed from scratch or if you have had to write code to implement a communication protocol, then you are aware of the complexities and strict need to handle data exchange with unwavering precision. There simply isn’t a quality of “good enough” when it comes to exchanging messages from one system to another. The arrangement of the data sent to or received from another system must be arranged in an agreed format—both data alignment (what goes first) and how it is represented (encoding). Failure to get it right can lead to disaster.
The older client/server MySQL protocol is an excellent example of a communication protocol designed from scratch. Although it has been used for decades with only relatively minor changes, for some time it limited the MySQL engineers. They have struggled repeatedly when trying to implement new features because the old client/server protocol is not extensible.
However, adding new features isn’t the only issue one must deal with during the evolution of the protocol. In the case of the client/server protocol in MySQL, security is a major concern. Although SSL extensions were added to the protocol, security was not enforced by default. That is, except for the exchange of the login password, the client/server messages are not required to be encrypted. Thus, it is possible for someone to discover the data being sent to/from the server if SSL or other form of encryption is not enabled.
Performance is another area where existing protocols designed for a specific, limited set of commands and messages can suffer. That is, newer technologies have shown it is possible to achieve better performance if one were to design the protocol exchanges using techniques like pipelining.
Adding these qualities to the existing client/server protocol isn’t feasible. More specific, the engineers knew that to extend the client/server protocol, every system (client, application, server, etc.) that uses the protocol must be updated or modified to work with the new extensions. This is serious because you simply cannot expect every user of MySQL to suddenly update every version of their MySQL tools, custom applications, scripts, and so forth to comply with a new extension of the protocol. For this reason and many similar reasons, changing the client/server protocol in the past had been forbidden and limited to only those changes that ensure existing clients can continue to work despite the changes.
Despite this mandate, there have been a few minor changes along the way to the client/server protocol. The most recent occurred during the version 5.7 development releases concerning the returning of the Ok message. But even this minor change was built to ensure backward compatibility. To date, the client/server protocol continues to support pre- and post-Ok message protocol changes. Such is the bane of long-lived communication protocols: always having to maintain some level of backward compatibility at the expense of progress.
When the engineers began designing what is now the document store in MySQL including the new MySQL Shell, X Plugin, and X DevAPI, it became very clear that it was time to implement a new protocol that could enhance the new features. More specific, it was clear that the existing client/server protocol wasn’t going to be sufficient to meet all the goals for MySQL 8 features and products. Hence, we needed a new protocol, which was dubbed the X Protocol to follow the new naming conventions.1
X Plugin: integrated in the MySQL server (https://dev.mysql.com/downloads/mysql/)
Shell: version 8.0.4 or later ( https://dev.mysql.com/downloads/shell/ )
Connector/J: version 8.0.8 and later ( https://dev.mysql.com/downloads/connector/j/ )
Connector/Net: version 8.0.8 and later ( https://dev.mysql.com/downloads/connector/net/ )
Connector/Node.js: version 8.0.8 and later ( https://dev.mysql.com/downloads/connector/nodejs/ )
Connector/Python: version 8.0.5 and later ( https://dev.mysql.com/downloads/connector/python/ )
Note
Connector products are often abbreviated such as C/J, C/Net, C/Node.js, and C/Py.
We will see an example of how the Connector/Python connector implements and exposes the X Protocol in a later section. Now let’s look at the goals and their motivations for developing and implement the X Protocol.
Goals for the X Protocol
As mentioned, the three main areas (called design constraints or simply goals) that the X Protocol was designed to address include extensibility, security, and performance. The next few sections present some of the driving motivations for the three major design constraints for the X Protocol.
Tip
If you want to see some of the actual engineering documents used to design the X Protocol, see the worklog2 for the project at http://dev.mysql.com/worklog/task/?id=8639 .
What About the Client/Server Protocol?
You may be wondering if the X Protocol works only for all things X. That is, it doesn’t work with the old protocol. The answer is the X Protocol also supports the client/server protocol. This is how the MySQL Shell can connect to older servers without the need for using an intermediate library. More specific, the X Protocol includes an option to communicate using the older client/server protocol.
Extensibility
When software is said to have the goal of extensibility, it means the software must be capable of being modified to add new features without requiring major rework or retooling. Although organizations may have slightly different definitions or examples of what rework means, in the case of the client/server protocol, it is not extensible because there is very little room for extending the protocol to include new messages, commands, and data without major changes to the code and the potential incompatibility with older products.
The engineers wanted to ensure the new protocol would be built from the start with extensibility in mind. In this case, extensibility includes the ability to add capabilities and features without causing existing products to fail or be reworked to comply with the changes.
Some of the areas where the X Protocol needed extensibility includes being able to add new messages, add new features (e.g., ensuring the protocol supports things such as pipelining to reduce round trips), permit the addition of new authentication mechanisms, change or add new encryption and compression facilities, and more.
Security
In this modern world of the Internet of Things and the rapid escalation of the population of modern civilizations becoming continuously connected, it has never been more important for systems to be as secure as possible. That is, to provide the very best options to permit data and users to be protected against accidental or deliberate exploitation.
Tip
For more information about the Internet of Things and MySQL, see my book, MySQL for the Internet of Things, Charles Bell (Apress 2016) https://www.apress.com/us/book/9781484212943 .
The engineers at Oracle take security very seriously. Indeed, it is a key aspect in almost every design, review, and quality control mechanism. At Oracle, security is paramount. Thus, when it came time to develop a new protocol, the security mechanisms were vastly improved from the client/server protocol. In particular, security defaults in the X Protocol use only trusted, proven standards such as transport layer security (TLS)3 and simple authentication and security layer (SASL).4
Performance
As with security, performance is another key area that Oracle uses to evaluate the quality of products. In this case, performance must be such that the system can perform its tasks appropriately without unnecessary wait times, lag, or long running tasks. Unlike security, performance is often evaluated subjectively and anecdotally. That is, newer releases must run no slower than the previous release.
In the case of the X Protocol, performance goals are ensured by using sound foundational technologies and by leveraging features such as pipelining, which allows more than one message to be passed at a time, reducing the number of round trips (to/from the server and back to the client), and not waiting for a response from the server when sending multiple commands thereby not tying up a client to wait for a response.
In the next section, we look at the underpinnings of the X Protocol by studying the foundation of the design.
X Protocol and Protocol Buffers
One of the biggest things that the MySQL engineers wanted to overcome is the lengthy time required to develop the various aspects of a protocol mechanism from scratch. In particular, the engineers wanted to take advantage of established, well-documented, and superior technologies. After all, the problems of creating an extensible, secure, and high performance communication protocol have been solved by a lot of people to varying degrees of success.
Although several options were evaluated and discussed, it was important that the technology be well established and open source. Furthermore, the technology must support rapid implementation with little or no third-party dependencies, be language and platform independent,5 and not require retooling of the development tools and processes to use it.
The technology that was chosen is called Protocol Buffers from Google ( https://developers.google.com/protocol-buffers/ ). Google Protocol Buffers, affectionately named protobuf, is an extensible, language and platform independent mechanism for serializing structured data. It is designed for speed, compactness, and simplicity. Protobuf permits you to define a message exchange protocol quickly and easily. In that respect, protobuf is loosely similar to XML and other variants. Protobuf is available for several languages including C++, C#, Go, Java, and Python. The latest version of protobuf (version 3) supports additional languages such as Ruby.
However, language support in this sense means there is a compiler option available to translate the protobuf definition files into language-specific code that can be used by that language. For example, to use protobuf in C++, you must compile the protobuf definition files from their native, protobuf definition to files that can be read and compiled by the C++ compiler.
Protobuf is essentially a way to organize data so that it can be defined in a structured manner (called a message). That is, we can define a precise assembly of how the data is to be represented. This allows you to transmit and receive the data in an agreed on structure. This may not sound like a big deal until you consider the extensibility aspect where older messages are still valid even though there are newer versions of the message. Structured data mechanisms are supported in most languages with various degrees of type strictness. However, these are rarely extensible and any change to the structure renders the format incompatible (well, mostly). Protobuf is designed to allow you to extend the data organization without having to rebuild.
To understand the power of protobuf , let’s look at a short example. In this case, we will use a variation of the rolodex of contacts example from earlier chapters. We need two messages (data structures); a way to store contact name and phone numbers (there may be more than one for each contact), and a message to store all the contacts. As you will see, this allows us to write some very simple code to read and write data.
Note
Although a complete tutorial of protobuf is beyond the scope of this book, the following will give you a bird’s eye view of protobuf. However, Google has provided ample documentation should you need to know more about protobuf.
Installing the Protobuf Compiler
There two things we need to install. We must have the protobuf compiler and the protobuf libraries installed.
You can download the protobuf compiler from https://github.com/google/protobuf/releases/tag/v3.0.0 . Scroll down to the bottom of the page and download the file that matches your platform. Most are in the form of a compressed file that you can download and uncompress. For most platforms, no installation is required. You can run the protobuf compiler (named protoc) from the bin folder of the download. For example, I downloaded the file for macOS named protoc-3.0.2-osx-x86_64.zip and thus can run the protobuf compiler as ./protoc-3.0.2-osx-x86_64/bin/protoc. Or, you can place the location of protoc in your path.
You can install the protobuf libraries in several ways. See the runtime installation instructions for your language at https://github.com/google/protobuf/#protobuf-runtime-installation for instructions on how to install protobuf. For Linux and macOS platforms, you can use PyPi (pip) to install protobuf libraries as shown in the following. Note that if you installed pip using elevated privileges (e.g. sudo), you may need to specify sudo to install protobuf.
Note
You also must have Python installed on your system. See https://www.python.org/ for downloading and installing Python on your system. The example scripts in this chapter were written for and execute correctly for Python version 2.7. If you are using Python 3.0 or later, you may need to make minor changes to the code.
Protobuf Example
Let’s begin with a look at the protobuf definition file. Protobuf files are named with a .proto extension. We will name our protobuf definition file contacts.proto. Listing 7-1 shows the contents of the protobuf file contacts.proto. Place this file in a folder as we will be adding additional files to compile and test the protobuf definition. This is a standard example pattern that you will see in other documentation—a data item definition followed by an array (or list) containing the data items.
Contacts Protobuf Definition
Here we see code that looks a lot like C++. That is no accident and was chosen because several languages use similar syntax making this familiar to most developers. The first line we see is a directive for the protobuf compiler to use version 2 of the language (version 3 is the current version). MySQL uses version 2 as well.
In the first message, named Contact, we define two required fields, an id and a first name. The Id is an integer and the first name is a string. We can also define an optional field for the last name. Within that message is another message, named PhoneNumber, that stores a required field for the phone number. However, because this is a message, we add another field named phones to store 0 or more phone numbers. That is, the repeated declaration indicates it can contain 0 or more messages. Note the = N for each data item. This is a required tag that must be unique. Most people just use a number starting from 1. Finally, we see a message named Contacts that we store 0 or more contacts named list.
To use the new protobuf definition, we must compile it. For this example, I will compile it for use with Python. The command to use is as follows. This generates a file named contacts_pb2.py, which we can import in our Python script. We use the option --python_out to tell the compiler two things: 1) that we want to compile for Python; and 2) that we want the output of the compiler to appear in the current folder (.). You will not see any additional output from this command—it is all written to the file. Be sure you have the protoc executable location on your path or call it directly using the location (path) as shown in the following.
C++: --cpp_out=<out_dir>
C# --csharp_out=<out_dir>
Java: --java_out=<out_dir>
Java Nano --javanano_out=<out_dir>
JavaScript: --js_out=<out_dir>
Objective C: --objc_out=<out_dir>
Python: --python_out=<out_dir>
Ruby: --ruby_out=<out_dir>
The contents of the contacts_pb2.py file isn’t very interesting. In fact, it’s quite complex. What is more interesting is how we use the new protocol. Because this is a data structure for storing contacts, let’s write a script that is to write a couple of contacts to a file using the new messages. Listing 7-2 shows a simple Python script to write two contacts to a binary file. Why binary? Because protobuf is designed to allow us to serialize data quickly and easily while preserving typed (binary) data. As with the previous examples in the book, don’t worry too much if you don’t know Python. It’s a very easy scripting language (see the side bar later in the chapter for more details).
Writing Contacts to a File (Protobuf Example)
I used an inline coding style here rather than a loop to show you how to add new messages using the add() method from protobuf. However, note first that we must import the file we created with the protobuf compiler (contacts_pbs2). Then we create an instance to the Contacts class generated by the protobuf compiler. Recall this is an array (list) of type Contact. When calling the add() method, we get an instance to a Contact structure, which we can assign values using the field names. Thus, I set the id, first name, and then add phone numbers by creating a new phone number structure by referencing the nested message named phones and then populating it. Note that you must call add() each time you want to add a new message. Finally, I use the SerializeToString() method to serialize all the messages I’ve built in memory and write that to a file named my_contacts. Take a few moments to read through the code until you understand how it works.
Tip
Don’t worry too much about the minor details or ways you can improve the code. I include the example code to demonstrate protobuf rather than demonstrate using Python. We’ll see more about Python in a later section.
If you’re following along and want to run the code, create a file named write_contacts.py, enter the code, save it, and then execute it with a command as in the following. You won’t see any output here either because it creates the file my_contacts.
If you’re wondering what this data looks like in the file, the following shows a hex dump of the file, my_contacts. Note that it is indeed a binary file.
Now, let’s see how we can read the contacts from the file. This code is considerably shorter and easier to read. Once again, we import the contacts_pb2 file and then open the file for reading. However, in this case, we create a new instance of the Contacts class and then read from the file using the ParseFromString() method. This creates the contact list in memory, which we can then iterate through and print the data. The following shows the complete code for reading the contact lists.
As in the write example, we can execute this code but in this case, we will see the contact list printed out. Listing 7-3 shows the output. Note that we see a nicely formatted output that resembles C++ (and JSON a bit).
Reading the Contact List (protobuf example)
Of course, you could write the code to access individual fields with dotted syntax. For example, you could print out just the first and last name with the following sample code.
When you execute this file, you see output like the following.
As you can see, working with protobuf makes reading and writing structured data easier with far less complexity than if we wrote our own structures. If this example is intriguing, I encourage you to play around with it and embellish it to your whim. If you want to know more about protobuf including how to get started building your own messages and protocol, see the online documentation at https://developers.google.com/protocol-buffers/docs/overview .
So, what is the MySQL protobuf called X Protocol then? Shouldn’t it have been named, “MySQL Protocol Buffer”? Recall protobuf is a technology that can be leveraged to design protocols. The X Protocol therefore is a product of using the protobuf to define the messages, commands, and so forth that make up the new protocol. Thus, the X Protocol is a definition of a communication protocol using the language of protobuf. Cool, eh?
Now that we know more about the X Protocol, how (and why) it was designed, let’s take a closer look at how it works at the code and protobuf level.
X Protocol: Under the Hood
Although it is true that developers are unlikely to have a need to write such low-level code that interfaces directly with the X Protocol, it is helpful to take a tour of how the X Protocol is implemented. In the interest of brevity, we will only see a few parts of the X Protocol before embarking on a detailed look at how one of the database connectors implements the X Protocol. If you’re a code junky, you can assume your best coding posture now.6
Let’s begin with a look at the protobuf definition files that define the MySQL X Protocol.
Protobuf Implementation
The MySQL protobuf definition files can be found in the source code download of any product that implements the X Protocol. For example, you can find them in the source code for the MySQL server in the rapid/plugin/x/protocol folder named with a prefix of mysqlx and a file extension of .proto. You can also see and download the X Protocol protobuf definition files from GitHub at https://github.com/mysql/mysql-server/blob/5.7/rapid/plugin/x/protocol .
The mysqlx.proto file (Github)
However, if you prefer to download the server code, you can. Just visit https://dev.mysql.com/downloads/mysql/ , choose the source code entry in the Select Operating System dropdown list, choose a file for your platform, and download it. Once you unzip (untar) the file, you can explore the server source code on your own PC.
Protobuf Definition Files (X Protocol)
File | Description |
|---|---|
mysqlx.proto | Defines messages for client, server, and general Ok and error messages; this is the main file that imports all the other files. |
mysqlx_connection.proto | Defines messages for determining capabilities of the server during the connection negotiation process (see later) |
mysqlx_crud.proto | Defines messages for handling the CRUD operations |
mysqlx_datatypes.proto | Defines messages for working with scalar data types |
mysqlx_expect.proto | Defines messages for working with pipelined messages |
mysqlx_expr.proto | Defines messages for working with expressions |
mysqlx_notice.proto | Defines messages for posting notices such as session and variable state changes |
mysqlx_resultset.proto | Defines messages for result sets including rows and columns; this file is a key component of the X Protocol and demonstrates the power of protobuf. |
mysqlx_sql.proto | Defines messages for executing statements |
mysqlx_session.proto | Defines messages to manage sessions |
To give you a glimpse at what the files contain, Listing 7-4 shows the error message from the mysqlx.proto file.
Generic Error Message (mysqlx.proto)
Note that the message is very well defined and includes what you would expect to see if you’ve looked at the client/server protocol. In particular, we see an optional severity setting, error code, SQL state code (string), and an error message (string). Severity is an enumerated value and currently can be set to ERROR (0) or FAIL (1). Cool, eh?
You may be wondering what the protobuf compiler does to this code when compiled. Let’s look at the resulting Python code. Listing 7-5 shows the compiled code for the generic error message. I omit some of the code for brevity.
Python Generic Error Message (mysqlx_pb2.proto)
Gah! That’s not even remotely as simple nor is easy to read. This is an excellent example to show how much protobuf can do for us. Clearly, defining the messages in protobuf is orders of magnitude (relatively speaking) than doing it in Python. In case you’re curious, compiling the protobuf definition files in other languages creates equally as complex and seemingly incomprehensible code. But don’t worry; we don’t need to read the compiled files directly! That’s good, isn't it?
To get a sense of the complexity (and completeness) of the X Protocol, let’s look at how Connector/Python implements the X Protocol. In the next section, we will see how the X Protocol works using a few simple examples including the connection procedure.
Tip
I encourage you to explore the other *.proto files to see the messages they define.
X Protocol Examples
We explore two examples of the X Protocol in action: 1) an overview of how a connection is established starting with negotiation, authentication, and then commands; and 2) how SQL inserts are handled. These examples are easy to understand and, if you’re curious, can be found easily in the protobuf definition files.
Example 1: Authentication
Let’s assume we want to connect to a server using the older authentication for simplicity. This will give you a good idea of how a communication protocol works without the heavy lifting we see in the newer mechanisms. The goal is to understand how a typical communication protocol works by way of example. After all, it is not likely that you will build your own authentication protocol (but you can by building your own authentication plugin).
The life cycle of the procedure begins with a negotiation phase where the client requests from the server the authentication (and other) capabilities using the CapabilitiesGet() method. The server responds with the CapabilitiesGet message (defined in the mysqlx_connection.proto file). The client then sets the capabilities (such as setting the authentication extensions like TLS) sending the completed message back via the CapabilitiesSet() method. Assuming the data is correct, the server replies with the Ok message.
X Protocol connection procedure (Courtesy of Oracle)
Let’s look at the CapabilitiesSet message. Listing 7-6 shows an excerpt from the mysqlx_connection.proto file.
CapabilitiesSet Message (mysqlx_connection.proto)
Note that we see the CapabilitiesSet message has one field named capabilities of the type Capabilities message. This is used as a placeholder for the client to complete the message with data and send it back to the server. The other values include SCALAR (1), OBJECT (2), or ARRAY (3) and can be found in the mysqlx_datatypes.proto file.
Example 2: Simple Inserts
In this example, we are going to examine what happens when an SQL statement is issued. In particular, two INSERT statements are executed against a simple table. At this point, we are working with an SQL object and the StmtExecute messages located in the however strangely named mysqlx_sql.proto file.
X Protocol simple inserts (Courtesy of Oracle)
Let’s look at the Sql.StmtExecute message. Listing 7-7 shows an excerpt from the mysqlx_sql.proto file.
Sql.StmtExecute Message (mysqlx_sql.proto)
Note that we have fields for the namespace (set to SQL by default), the SQL statement stored in stmt. Note that it is of type byte so we can handle any character set including binary data. We then can have zero or more arguments (args) to allow for parameterized queries. Finally, we can have an optional compact_metadata setting to allow the server to only send the type information back to the client.
As you can see, the X Protocol has a lot going on under the hood. However, we don’t have to know all there is to know about the X Protocol to use it. In fact, the best way to use the X Protocol is through the MySQL Shell, which we saw in detail in Chapter 4 or through the database connectors that support the X Protocol. Let’s look at how one database connector implements the X Protocol.
Wait! Where’s the Rest of the Code?
If you take time to examine the protobuf definition files, you may notice two major things that are missing. Protobuf is a protocol definition language (API) but it does not include any support for direct transport of messages over the wire nor is there any direct support for encryption, compression, and other techniques for transmitting data.
The X Protocol therefore is where all this code exists. Now you can see why the X Protocol is more than just a protobuf implementation. There are other facilities that the X Protocol implements that isn’t part of the protobuf message definitions. These include handshaking with the server, error message definitions, and much more.
X Protocol Walkthrough
To better understand the power and elegance of the X Protocol, we will examine how one of the database connectors implements the X Protocol. This presents an abstraction layer over the protobuf definition files, which given what we learned about how the protobuf, is a very good thing. As you will see, the connectors make working with the X Protocol very easy thus continuing the goals of protobuf to make communication protocols easy to create and use.
The database connector we use in this section and in the next is Connector/Python, C/Py. Once again, I chose C/Py for its simplicity and readability. If you want to follow along and see the code in context, you can download the source code for Connector/Python version 8.0.5 or later at http://dev.mysql.com/downloads/connector/python/ . Note that you may need to click on the Development Releases tab then select the Platform Independent entry from the dropdown list.
We look at the C/Py code for each of the examples in the previous section. Thus, we will see the code for connecting to the server and executing an SQL INSERT statement.
Example 1: Authentication
We find the code for the authentication process in the C/Py source code file named connection.py found in the /lib/mysqlx folder. Listing 7-8 shows an excerpt of the source code (methods) that implements the procedure. I omit the specifics of collecting and passing connection information for brevity. The starting point to focus on is the connect() method in the Connection class.
Connection Methods for Authenticate Procedure (C/Py)
Note that in the connect() method, we see a couple of things occur. First, we see the C/Py opens a stream connection to the server (via the _connection_params() method that returns the data set previously) then the code creates an instance to a reader/writer. This is how the connector transports the messages to/from the server.
Next, the code instantiates an instance of the Protocol class, which is the abstraction of the X Protocol. We’ll see more details of that code later.
Now, focus on the last two statements in the connect() method. Here we see method calls for the CapabilitiesGet/Set methods in _handle_capabilities() and the authenticate phase in _authenticate(). Take a few moments to read through the code so you can see that all the steps from Figure 7-1 are shown.
The CapabilitiesGet/Set methods of the Protocol class can be found in the protocol.py file found in the /lib/mysqlx folder of the C/Py source code and are shown in Listing 7-9.
CapabilitiesGet/Set Methods for Authenticate Procedure (C/Py)
It is at this point where we can see calls to the protobuf code by way of the MySQLx* classes that are generated by the protobuf compiler.
Example 2: Simple Inserts
This example is a bit easier to view so we’ll go a bit deeper than the last example. We find the code for the authentication process in the C/Py source code file named statement.py in the /lib/mysqlx folder of the C/Py source code. Listing 7-10 shows an excerpt of the source code that implements a class for executing INSERT SQL statements.
SQL INSERT Class (C/Py)
As you can see, the code is easy to read. The first thing to notice is the class is derived from a base class named WriteStatement (also in statement.py). That base class has an abstract (virtual) method named execute(), which this derived class implements. However, in this case, it calls the send_insert() method from the connection class (in connection.py). The following shows the send_insert() method.
As you can see, this calls the Protocol class method send_insert() in the protocol.py file with the statement as shown in Listing 7-11.
The send_insert() Method in the Protocol Class (C/Py)
As in the previous example, we can now see the protobuf interface and follow along in the code to see the steps outlined in Figure 7-2 in the code.
Tip
If you want to learn more about how the X Protocol works, see the MySQL Internals documentation at https://dev.mysql.com/doc/internals/en/x-protocol.html .
Now that we know a lot more about the X Protocol and can appreciate the abstraction provided by the X Plugin and Shell as well as the database connectors, let’s look at how we can write client applications that take advantage of the X Protocol as provided by the MySQL connectors. In this case, we’ll continue our quest to master the X Protocol by using the Connector/Python.
Creating X Clients
Creating MySQL client applications that use the X Protocol is best executed using either the MySQL Shell or ultimately one of the database connectors together with installing the X Plugin on the server. In this section, we will see two examples of standalone clients. One that was written using MySQL as a document store and another using only the relational data model.
The programming language we will use is a very easy scripting language called Python. As you will see, the commands are quite intuitive and very expressive. For the purposes of this demonstration, you do not need to be an expert with the language. I will provide all the code and commands you need as we go along.
Python? Isn’t That a Snake?
The Python programming language is a high-level language designed to be as close to reading English as possible while being simple, easy to learn, and very powerful. Pythonistas will tell you the designers have indeed met these goals.
Python does not require a compilation step prior to being used. Rather, Python applications (whose file names end in .py) are interpreted on the fly. This is very powerful; but unless you use a Python development environment, some syntax errors (such as incorrect indentation) will not be discovered until the application is executed. Fortunately, Python provides a robust exception-handling mechanism.
Programming the Raspberry Pi, by Simon Monk (McGraw-Hill, 2013).
Beginning Python from Novice to Professional, 2nd Ed., by Magnus Lie Hetland (Apress, 2008).
Python Cookbook, by David Beazley and Brian K. Jones (O’Reilly Media, 2013).
Interestingly, Python was named after the British comedy troupe Monty Python and not the reptile. As you learn Python, you may encounter campy references to Monty Python episodes. Having a fondness for Monty Python, I find these references entertaining. Of course, your mileage may vary.
To get started, you can either enter the code as shown in the examples or download the source code from the Apress site for this book. You can use any code editor you want when writing Python scripts. We begin with a short description of how to setup the environment to run the examples.
Tip
There are many available including a very powerful IDE from JetBrains named PyCharm ( http://www.jetbrains.com/pycharm/ ). If you want a great open source for Python, check out PyCharm Community Edition.
Setup for the Examples
There are a couple of things you need to have installed to work with the examples in this section. You must download the Google Protocol Buffers Python library and install the programming language runtime. You must also have the source code for C/Py downloaded.
Recall, we need to have the protobuf compiler and protobuf libraries installed. If you have not already done this, please refer the previous section, “Installing the Protobuf Compiler.”
The language-specific runtime libraries can be downloaded from https://github.com/google/protobuf . You should download the entire package by clicking on the Clone or Download button. Once the download is complete, you will see a file named protobuf-master.zip that you can uncompress. To install the library for your chosen language, navigate to the folder named for the language and read the README.md file for specific installation instructions. For example, we will be using Python in this chapter. The folder is named /protobuf-master/python. To install the Python on macOS, you run the following commands.
Installing the Python libraries on other systems is similar. The only difference for installing it on Windows is you do not need to use sudo (super user). However, on my system there was an issue with locating the protobuf compiler. I received an error similar to the following.
Once I placed the protobuf compiler executable (protoc) in the specified directory (../src), I could install the Python protobuf libraries with the previous commands. You may encounter similar issues on other platforms.
Tip
Scroll down to the bottom of the page on https://github.com/google/protobuf and click on the links in the table to see instructions for installing the protobuf libraries for other languages.
If you haven’t already, you must download the source code for the C/Py version 8.0.5 or later from http://dev.mysql.com/downloads/connector/python/ . Be sure to download the Platform Independent option from the dropdown list. We will be using some of the source files from the C/Py source tree in our example.
I chose to do it this way to help you see the details of how protobuf works with Python and how C/Py implements the X Protocol. Although the examples will show the X Protocol abstraction layer in C/Py, you can use your favorite debugger or Python IDE to drill down into the code and see how things work. Therefore, I have setup this example for the curious among us.7 However, you need not go that far if you do not want to. Rather, you can concentrate on how the examples work to give you a better understanding of how to work with the new X Protocol via a database connector.
Perhaps more important, because the C/Py example we are using is a development milestone release (think beta), copying the source code will not affect any other installation of C/Py on your system thereby allowing you to run these examples and not have to install the development milestone release of the connector.
The files we need are in the /lib/mysqlx folder. But first, create a new folder on your system. Name it whatever you like such as xclient. Next, copy the mysqlx folder from the C/Py archive into the xclient folder. When you create the files for the following examples, save them in the xclient folder. For example, I named the document store example xclient_json.py and the relational data example xclient_sql.py.
Tip
If you get an error that one or more libraries cannot be found, ensure you have copied the mysqlx folder into the same folder as the xclient_json.py and xclient_sql.py files.
Document Store Example
This example creates a simple client to demonstrate how to use the X Protocol abstraction available in C/Py. The example uses the concept of the rolodex of contacts we encountered in Chapter 1. In this case, the code will connect to the server, create a schema and collection in the schema and populate the collection with documents. The code will then retrieve all the documents and print them. But we don’t just print the raw document. The code demonstrates how to do a find operation on the collection and iterate over the documents printing the phone numbers for each contact document found.
The following briefly describes the code portions. I highlight the pertinent code statements to draw your attention to the X Protocol abstraction methods. Most of the calls will be familiar to you because we encountered them in Chapter 5 and elsewhere in the book. Thus, I keep the explanations brief. Refer to Chapter 5 if you need more information about the classes and methods used in the example.
The first thing that we need to do is import the mysqlx library. Recall this is the set of files from the C/Py download. It contains the C/Py abstraction for the X Protocol files that we saw earlier. If you examine that folder, you will notice the .proto files are missing. This is because we only need the .py files that were generated when the protobuf compiler was run. Fortunately, all those files exist in the mysqlx folder.
Next, we ask the user to provide the logon credentials (user id, password, host, and port). We use this information to open a session (a connection) to the server. For this, we use the get_session() method and assign the resulting instance of the session object to a variable mysqlx_session. If something should happen that we cannot connect, we check the status of the session and if it is not open, exit. Note that we are using an X Session in this example because we are only going to execute CRUD operations and do not need any SQL support.
Next, we use the mysqlx_session object instance and attempt to get the schema with the get_schema() method.8 This sets the default schema so that when we create a collection (or other objects perhaps); they will be contained in the schema. I use a constant to store the schema name and the collection name. If the schema is not on the server, we create it with the create_schema() method. Either way, we get a schema object instance, which we can use to create the collection with the create_collection() method, which gives us an object instance to the collection. Note that I use the remove() method to empty the collection. This permits us to rerun the code without duplicating data (I am not checking document Ids).
Let’s look at the code before we continue. Listing 7-12 shows the completed code. Take some time to read through the code so that you can see all the methods and actions described thus far. All of the code up to the contacts.remove() call should be familiar to you. If you want to execute this code to see what it does, you can place this code in a file named xclient_json.py.
X Client Source Code (JSON)
Tip
If you are using Python 3.0 or later, you will need to change the raw_input() calls to input() and the iteritems() to items(). This is because raw_input() and iteritems() are no longer supported in later releases of Python.
Next, we can add some contacts. We do this using the add() method for the collection object instance. In this case, we add a couple of documents; one for a person that we only know their first name and a phone number, and another for someone that we know their full name, several phone numbers, and some notes we’ve made about them. This illustrates the power of using a document store: store what you need and don’t force the data to comply with a strict structure or the storage mechanism!
Once the documents are added, we use the find() method on the collection without any expressions. We chain the find operation with the execute() method. This simply returns all the documents in the collection in the form of a document result object instance. We can then use that object to iterate over the documents with the fetch_one() method. Note that this returns a document object instance, which we can use to get the data elements directly using named attributes (a powerful expression). Take a moment to read through the code for fetching the documents. Note that when the collection is at the end, the fetch_one() returns None and the while loop terminates.
Finally, we drop the collection with the drop_collection() method and drop the schema with the drop_schema() method so that we can rerun the code and avoid duplication. However, you may notice I’ve added code to protect against accidental execution. For example, if you use the debugger and terminate the code before the end, the statements at the top of the code will use the schema if it already exists and empty the collection.
Now let’s see the script in action. In this case, we expect to see only the first name and a list of phone numbers for the people in our rolodex (in this case only two entries).
In case you’re wondering if this is all an elaborate ruse and that the collection and documents we created are somehow stored elsewhere in MySQL, if you disable the drop_*() calls and run the program again, you can log into the server and see the construction of the underlying tables as shown in Listing 7-13.
Definition of the Contacts Collection
If you run a SELECT statement to get all the data from that table, you will see the results similar to those shown in Listing 7-14. The order of the results may differ but you should see the same data in the results. Note that the document ids are added to each of the JSON documents.
Results of SELECT Statement for Contacts Collection
That’s cool, isn’t it? We will see more code like this in Chapter 8 when we explore a full document store application example. But first, let’s see an example of Connector/Python using the X Protocol for executing SQL commands.
Relational Data Example
Now let’s look at a relational data example using the X Protocol. We will use the same code from C/Py as the last example only this time we’re going to execute an SQL statement rather than work with data. I chose this simple example because, if not at first, eventually your MySQL document store applications will use less and less SQL operations. Even so, you may need to execute an SQL statement now and again if you want to check variables, status, or similar operations with the server.
This example connects to the server using a Session and executes the SQL statement, SHOW VARIABLES LIKE, to retrieve all the system variables for the X Plugin. This is the same SQL statement we saw in Chapter 6. Although we aren’t accessing any data, the result set returned from the SHOW VARIABLES statement is the same as would be returned if querying a table. So, we will see how to handle a result set from a SQL command without the need to create any sample data.
As in the last example, we begin by importing the mysqlx library and prompt the user for the logon credentials. Note that I demonstrate how to use defaults for user input. Next, we get a Session with the get_session() method. This returns a Session object instance. We then check to see if the connection is open and if it is not (e.g., the connection failed), we exit. Listing 7-15 shows the complete code for this example. Take a moment to read through it so that you can see all the concepts discussed thus far.
X Client Source Code (SQL)
Tip
If you are using Python 3.0 or later, you may need to change the raw_input() calls to input(). This is because raw_input() is no longer supported in later releases of Python.
To execute a SQL statement, we need to ask the session for a SqlStatement object instance by passing in the SQL statement we want to execute. We do that by calling the sql() method for the session object instance. We can use that object to execute the statement and get a result object instance in return.
Next, we can iterate over the columns in the result set printing their names. This illustrates how to capture the column names in a result set.
Next, we use the fetch_all() method to get all the rows in a list, loop through them in a for loop and print the value for each column found. Note we use “row” and “column” here because this is not a document being returned—it’s an old-fashioned SQL result set (well, via the X Protocol). Finally, we close the session. Listing 7-16 shows an example of the script running. You should be able to equate the output with the print() statements in the source code. Note that later versions of MySQL may have additional variables and some default values may differ.
X Client Results (SQL)
Note here that we see all the system variables for the X Plugin (those that start with mysqlx_). We also see the values for each system variable. The SSL entries do not have any values because the connection used in the example is not connecting via a secure connection.
As you can see, even with a language like Python, it is very easy to write clients that take advantage of the X Protocol and the X DevAPI. Of course, this is all possible with Connector/Python, which implements the X Protocol. For more information about the X Protocol, see the “X Protocol” section in the online MySQL internals reference manual at https://dev.mysql.com/doc/internals/en/ . For specific information about writing clients with the connectors, see the individual connector online documentation at https://dev.mysql.com/doc . You can find information about using the X DevAPI with Connector/Python at https://dev.mysql.com/doc/dev/connector-python/ .
Summary
The X Protocol is a revolutionary new feature in MySQL that overcomes a lot of the limitations of the older client/server protocol. The X Protocol is designed for extensibility so it can be extended without affecting the clients that rely on it. The X Protocol is also designed with a greater level of security and greater performance. For the first time in decades, MySQL clients can connect and interact with the server using modern, reliable technologies and promises to be the catalyst for many more new features in the future.
In this chapter, we examined the X Protocol starting with the motivations for why it was created, the chief tenets or goals of the design, and how it was implemented using protobuf as the foundation. We also saw a walkthrough of how portions of the X Protocol work for simple use cases. We then looked at how to use protobuf in our applications for moving data (messages) around in the code (on disk, over the wire, etc.), which illustrates the power of protobuf.
We also took a short tour of how C/Py implements the X Protocol by examining portions of the actual C/Py source code. We then used the X Protocol abstraction layer in C/Py in standalone Python scripts to demonstrate how well the X Protocol works—its ease of implementation as well as a concrete example of the technologies presented this far in the book.
As with the X Plugin, we also discovered that the X Protocol is much more than a feature, it’s a carefully crafted and well abstracted mechanism that is one of the underpinnings for the future of MySQL. Even though we know we’re using the X Protocol when using those connectors that support it, the X Protocol, it just works and works very well.
In Chapter 8, I provide a tutorial on writing applications using the X DevAPI, which we now know is enabled through the X Plugin and X Protocol. The project will use the MySQL document store to build a Python web-based solution for storing information about books.