MySQL 8.0 – Welcome to the DevAPI!

 

By now you’ve read Mike Frank’s excellent introduction to the MySQL 8.0 release and it’s Document Store.  In that post Mike laid out the benefits of the new Document Store model and briefly outlined the different components involved.  While it’s fair to consider the Document Store as the first pillar of the new MySQL 8 Document story, we must identify the X DevAPI to be the second.  The post covers our motivation, goals, and overall design principles of this new API.

Motivation

Most of our connectors have not implemented their own API.  Rather, most of them implement externally defined interfaces.   Examples include our Connector/J that implements the JDBC standard, Connector/Net that implements the ADO.Net standard, and our Connector/ODBC.  None of these standards work well for a document-oriented database.  In fact, there really are no defined standard APIs for document databases.  Therefore we knew we needed to develop a new API for our users.

Another reason why we needed a new API is because we are doing something that has not been done before.  Document databases exist.  Relational databases exist.  We even see databases that support relational and document querying over the same data set.  However we have yet to see a relational database include a document model so that a user can use document objects alongside their existing relational data.

We are only beginning to bring forth the unifying power of MySQL 8.   In the coming releases you’ll see exciting developments like being able to link your existing relational data with your document data in a single query using simple API patterns.

Goals

We have some very clear goals we follow as we develop the X DevAPI.  They are:

  • Simplicity — No one likes a complicated API.  We wanted the API to be easy to understand no matter what language you are using.   It’s important to us that if you write X DevAPI code using Node.JS then a Python developer, for example, can read it and know what is going on.
  • Bridge the gap between relational and document data — MySQL runs some of the world’s largest web properties and they have petabytes of relational data.  Asking them to add tables or columns to their data stores can be very challenging.  The new document store allows connecting these different types of data in the same queries.  We want to provide a powerful API for this.
  • Expressive — We wanted our API to be very expressive and follow a fluent interface style of development.  We wanted this to enable writing and executing the same X DevAPI code in the shell as in your application.
  • Seamless support for InnoDB Cluster farms — We want the API to seamlessly and transparently allow simple development of applications that span from one machine to hundreds.

Please note that these are goals. Some of them may not be fully realized in the initial release of the X DevAPI but these goals outline what we are thinking about during this development.

Design

No matter what connector you use the design of the X DevAPI core components remains the same.  The API involves some central objects outlined below. You’ll hear more about these objects and more in the individual product announcements and in our documentation found at https://dev.mysql.com/doc/.

Session Represents a logical connection to a server or farm
Schema Represents a MySQL database/schema
Collection Represents a collection of JSON documents
Result There’s a series of Result objects that handle different scenarios

Our goal is that each connector will include a core API (set of objects, properties and methods) that are the same across all connectors.  Outside of the core API, each connector can and will provide additional syntax or support additional methods.

Of course we also realize that each language and framework has it’s own style and it’s important to us to be respectful of that.  An example might be a count property that might appear as .Count  in one language but should be represented as .getCount()  in another.  Again, please recognize they are the same concept just different syntax.

Looking at the New X DevAPI Syntax

You’ll learn much more about the X DevAPI and our different languages when you read our announcement blogs and our documentation but I wanted to take just a minute to give quick examples of how you can use the X DevAPI in the shell to do quick ad-hoc prototyping.  First let’s look at opening a session to server.  In this example we open a session  to localhost and get a reference to the test schema so we can work with it.

Next we’ll create a collection and add a document to it.

We can modify the document very easily.  This example finds all documents that have a length of 250 and changes that length to 125.

We can find those documents if we want to work with them

And finally we can remove the documents and drop our collection

These have just been some quick examples but we hope that it has shown some of the basic ideas we have in the X DevAPI and you are interested in learning more and trying it out.

Availability

With the release of MySQL 8.0 we are also making available 8.0 GA versions of our connectors.  Each of them, save ODBC, provide an initial implementation of X DevAPI.  These connectors should be installed from their respective package stores.  You can also find them at our website at https://www.mysql.com/downloads/.

You can read much more about each of the products on their announcement blogs:

We know that we have not covered every language available.  If you are using MySQL 8 with a connector in a different language please let us know.  We want to work with the community to bring X DevAPI to more and more languages.

Thank you!

I want to thank you for your time to read this and learn about our new products.  I hope that you will take the time to get to know what we’ve created and let us know where we fell down.  We have much more planned and we want to hear from you!

Preparing your Community Connector for MySQL 8 – part 2 – SHA256

In part 1 of this series we looked at implementing support for the caching_sha2_password authentication plugin using the libmyqlclient C library.  This is possible for C based connectors or connectors that can make use of an external C library.  For some, this is not possible.  For example, Java and C# both function better if they don’t have to thunk out to an external library.  For these, implementing the MySQL client/server protocol natively is required.  This part 2 is about implementing support for the caching_sha2_password plugin natively.  It is expected that you already have a working connector and understand the client/server protocol.  For reference please internal documentation here.  This post will not attempt to always give you precise byte positions or counts.  For that please see the above linked internal documentation.

This blog post will not attempt to explain every aspect of implementing the connection phase of the client server protocol.   For help in implementing it please refer to https://dev.mysql.com/doc/internals/en/connection-phase.html.

Initial Handshake

When initially connected, the server sends an initial handshake packet with lots of information.  Part of that information is the default authentication plugin that is set for the server.  Many connectors give the user the option of specifying an authentication plugin via a configuration or connection string option.  Doing this can eliminate a round trip during authentication.  However if you don’t do this or the user has not specified an authentication method, then attempting authentication using the default method is the way to go.

So a handshake response packet is now sent.  Part of this packet is the username, the “auth response”, and the plugin name.   Username is simple and the plugin name will be the name of the authentication plugin you received in the initial handshake packet.   By default starting with 8.0.4, that will be “caching_sha2_password” (assuming we are not switching to a different method).  The “auth response” part of the packet is the SHA256 scramble of the password.  This scramble uses the format

XOR(SHA256(PASSWORD), SHA256(SHA256(SHA256(PASSWORD)), seed_bytes))

Here the seed_bytes are the bytes originally passed to the plugin.

After sending the handshake response packet to the server, the server will respond with a packet.  There are four possible responses from the server:

  • Server sends a “More data” packet (first byte == 0x01) with the second byte = 0x03.  This will be followed by a normal OK packet.  This is the case when the user’s password is already in the server cache and authentication has succeeded.  We call this the “fast” authentication.
  • Server sends back an “error” packet.  This means the user’s password is in the cache but it doesn’t match what was given.
  • Server sends back an “auth switch” packet (first byte == 0xFE).  This means that the user who is trying to authenticate is using a different authentication plugin than the one specified and an auth switch cycle needs to happen.
  • Server sends back a “More data” packet (first byte == 0x01) with the second byte = 0x04.  This means that more data is needed to complete the authentication.  In the example of using “caching_sha2_password” this means that the users password is not in the server cache and the server is asking the client to send the user’s full password.  This is called the “full” authentication.

Full Authentication

When the server cache does not contain the password hash, the server asks for the full password so the cache can be populated.  This can be done in one of two ways:

  • Passing it in plain text if the connection is already secure using SSL/TLS.
  • Encrypting it with the servers public key if the connection is not secure.

Full Authentication via SSL/TLS

If the connection is already secure then all that is necessary is to pass the users password in clear text.  This is as simple as simply sending a single packet containing only the password as a zero terminated array of bytes.  The server will then respond with either an “Ok” packet if authentication was successful or an “Error” packet if not.

Full Authentication via RSA Key Exchange

If the connection is not already secure then passing the password in clear text is obviously not going to work.  This case is handled by the client encrypting the user’s password with the server’s public key and sending that back.  Here are the steps to accomplishing that.

  • In response to receiving the 0x01 0x04 packet from the server, the client has two choices.  The client could respond with a packet containing the single byte 0x02.  This requests the server send it’s public key.  This is considered somewhat insecure.  A better option if available would be for the client to have the server public key locally.  This would be stored in some implementation defined way.
  • If the client requested the server send the public key, the server will then respond with a “More data” packet containing the servers public key
  • The client then responds with the password encrypted with the servers public key. (see below for more details)
  • The server would then respond with either the Ok or Error packet.

Password Encryption (a closer look)

The password is “obfuscated” first by employing a rotating “xor” against the seed bytes that were given to the authentication plugin upon initial handshake.   Pseudocode for this might look like this:

Buffer would then be encrypted using the RSA public key the server passed to the client.  The resulting buffer would then be passed back to the server.

RSA Padding Change

It’s important to note that a incompatible change happened in server 8.0.5.  Prior to server 8.0.5 the encryption was done using RSA_PKCS1_PADDING.  With 8.0.5 it is done with RSA_PKCS1_OAEP_PADDING.  This means that if you have implemented support for this authentication scheme for servers prior to 8.0.5 you will need to update your connector to make this change.

Conclusion

I hope this blog has helped you understand a bit better how to implement this new security protocol into your product.  We continually strive to find new and better ways to help protect your data.  Please let us know if you have any questions or find any errors with this post!

Welcome to Slack!

Open source is at the foundation of MySQL and the biggest and best part of open source is the legion of developers and users who use and contribute to our great product.  It has always been of incredible importance to us to interact with our friends in MySQL space and one of the great ways of doing that is via IRC (Internet Relay Chat) on #freenode.  While that still remains a great option many other systems have been developed that offer other advantages.  One of those is slack.

We wanted to create a slack space where our users could hang out, help each other, and interact with MySQL developers.  We’re in no way wanting to replace IRC but just wanting to make it even easier to solve your MySQL problems and learn about the many great things we are working on.

Head on over to http://mysqlcommunity.slack.com to join in on the fun!

Preparing your Community Connector for MySQL 8 – part 1 – SHA256

As some of you are by now aware we have shipped MySQL version 8.0.4 and with it delivered a change to the default authentication plugin that is used by the server when new users are created and is announced by the server to the client.  This was done to further tighten the security of MySQL.  Please refer to this article for a good explanation of this new authentication plugin and why it is important.

If you are an application user or application developer and you want to use MySQL 8.0.3 or 8.0.4 and make use of this new authentication plugin then you need to make sure that connector you use supports it.  As this is a new plugin, support for it in community connectors is still being developed.  I would encourage you to reach out to the communities that create the connector you use and let them  know you need this support.

This article (and the one coming after it) is for the connector developers.  There are two types of connectors out there.  The first type uses the libmysqlclient C library to implement the protocol.  The second type implements the MySQL client server protocol natively.  This article gives you important information for using libmysqclient in your connector.  A followup article will include relevant information for native implementations.

Which Version Should You Use?

Even though your application may currently link against the libmysqlclient library that comes with MySQL 5.7, this one will only work for authenticating users who are using other plugins such as mysql_native_password or sha256_password.  For all situations where new users would be created using the caching_sha2_password plugin, an updated connector that supports this plugin is required.  MySQL 8.0.4-rc contains libmysqlclient version 21 which fully understands this new plugin and can work with any user accounts.  We are exploring the possibility of backporting support for caching_sha2_password to previous versions of libmysqlclient.

It’s important to understand that the libmysqlclient library that comes with MySQL 8 is backward compatible and can connect to previous versions of the server so there is no significant need to support building against 5.7 and 8.0 versions at the same time.

How Do I Use It?

Exactly the way you have been using it.  The only change to the API related to this new authentication plugin is a new connection option to retrieve the server public key.  Here are the two scenarios and how they might be coded.

Using an SSL connection (same as for 5.7)
In this scenario the users credentials are passed to the server in plain text however they are passed via the SSL connection and are therefore passed securely.

Not using an SSL connection
In this scenario we are not using an SSL connection and so it is vital that the users credentials not get passed “in the clear”.  To facilitate this the servers public key is retrieved and is used for an RSA key exchange.

For for information on creating encrypted connections to the server, please see this page.

Conclusion

Connectors based on libmysqlclient can be updated very easily by simply updating the version of libmysqlclient they link against.

Simplified Client and Connector Versioning

Here at MySQL we are constantly improving both our servers and our client software by making things simple to use and understand.  An area where we want to simplify things is around the version number we use for various MySQL client, connector, and other applications like Workbench, MySQL Enterprise Backup, etc.

Basically:

  • We want to make sure our users easily know which version of a connector or client works with which server version(s).
  • We want to deliver support for new server features during the server DMR phase without disrupting the GA versions of the connectors.

Which Connector Version To Use?

MySQL connectors (our client libraries — Connector/C, Connector/J, Connector/Net, etc) and other clients (tools and applications — MySQL Shell, MySQL Enterprise Backup, etc) will generally release more often than the server.  Relative to upgrading a set of servers in an enterprise, upgrading a client is normally quite simple.

Because of this rapid versioning of clients we have historically not tied the client version to the server version.  In addition to that we’ve changed the server versioning as of MySQL 8.0 to increment just the first digit for each major release.  After 8 comes 9, etc.

So you may see that the current versions of some client libraries are all over the place – 6.9 for Connector/Net, 5.1 for Connector/J, 2.1 for Connector/Python, or 4.1 for MySQL Enterprise Backup.  As you can see these version numbers do not relate to the server version at all.  We think this is confusing and with the recent server version change we could simplify things for clients, connectors, and applications.

Note:  In some cases we tried to make this easier by having a policy that all our support client libraries must support all servers that are still supported, but then there are other clients like MySQL Enterprise Manager where that’s not the case.

Challenges – DMRs and Testing Out New Server Features

The server team provides pre-release versions of the server available for testing out new features.  These releases are called DMR or “Development Milestone Release”.  While many of the new server features are transparent to client libraries, some require client library updates like new collations or new security measures.

When server changes affect clients users ask how can they try out the new features?   What connector or client version should they use?

And, features appearing in server DMR releases can change.  They are, after all, in development so adding support for pre-release features that can change into a GA connector is really a no-go.  So we need a  way to make new server features available to connectors and clients without disrupting the normal feature cycle of the clients.

Solving – New Simplified Versioning Methods for Clients

To address these issues beginning with the 8.0 server GA release, all of our connectors will synchronize the first digit of their version number with the server.   This means that MySQL Server 8.0 will go GA alongside 8.0 versions of each of our connectors.

Now, it’s easy to know which version of a client you should use – the first version number should match (starting with 8.x.x releases).

  • For Connectors – Using MySQL 8.0 then any of our MySQL connectors 8.0 or later will fit the bill!  Does this mean that older connectors will not work with MySQL 8.0?  Not necessarily!  However, older connectors might not be quite as compatible and will certainly not support many of the new features.
  • Other client tools and application – Will also go to 8.0 versions – MySQL Shell 8.0, MySQL Workbench 8.0, MySQL Enterprise Backup 8.0, etc.

As new MySQL Server DMR releases come out you can expect to see corresponding clients to help you try out the new features in both the client and the server.

We see the change as an opportunity to really clarify our product offerings using the version number and deliver a much more unified experience.  Clients will still version frequently so you’ll see some clients move ahead with 8.1 and 8.2 versions but that first digit 8 will still tell  you that it will work perfectly with MySQL Server 8.0

We’ll have more to say on this in the coming weeks!  Please let us know if you have any questions!