Ulf Wendel

MySQL 5.7 – HTTP Plugin for MySQL

| 8 Comments

It is official: MySQL listens to HTTP and speaks JSON. MySQL got a new plugin that lets HTTP clients and JavaScript users connect to MySQL using HTTP. The development preview brings three APIs: key-document for nested JSON documents, CRUD for JSON mapped SQL tables and plain SQL with JSON replies. More so: MySQL 5.7.4 has SQL functions for modifying JSON, for searching documents and new indexing methods! The download and documentation (also here) is on http://labs.mysql.com/, the slides are below:

What a buzzword bingo! The HTTP Plugin is just a HTTP proxy. One that you can load into MySQL, if you want, to avoid having to write your own one. It simplifies the setup, it is a convenience feature that you should consider if you don’t need the power of scaling the database and the web server independently.

Without the HTTP Plugin   With the HTTP Plugin
HTTP Client   HTTP Client
|   |
Web Service (e.g. PHP script)   |
Web server   |
|   HTTP Plugin
MySQL   MySQL

Did we forget about the developer?

The HTTP Plugin just makes MySQL a bit more web developer friendly. Hello frontend developers, we heard about you ;-). MySQL was born in a world without the web. MySQL grew up with backend developers. MySQL somewhat missed the growing role of frontend development, the developers and their ideas. NoSQL happily listened: be it in terms of on built-in JavaScript, the choice of JSON as a serialization format for document stores, asynchronous APIs or just HTTP interfaces.

The three APIs

There are three APIs: plain SQL over HTTP, CRUD and DOCUMENT. All three return JSON. Technically, JSON is just a different data serialization format for MySQL. If you are a frontend JavaScript developer and life in a world of HTTP and JavaScript, you likely prefer that over some binary format.

To be totally frank: at this point the integration of JSON into the APIs is only so-so. That is on purpose. None of the APIs is finalized. We translated some “what if” questions into code so that one has something play with. Now we fish for input.

Let’s get the fishing started, let’s cause some laughter. Here’s the SQL endpoint executing SELECT 1:

shell> curl ... --url "http://127.0.0.1:8080/sql/db/SELECT+1"
[
{
"meta":[
  {"type":8,"catalog":"def",
  "database":"","table":"",
  "org_table":"","column":"1","org_column":"",
  "charset":63,"length":1,
  "flags":129,"decimals":0}
],
"data":[ 
        ["1"]
],
"status":[{"server_status":2,"warning_count":0}]
}
]

The SQL endpoint replies 1:1 what a standard MySQL client gets a MySQL Connector may hide from the developers eyes. This is all information the MySQL Client/Server Protocol has to offer. We included everything to see if there is anybody who wants to write its own “driver” atop of it. If so, that self-made driver can expose similar features.

Ignoring the chatty reply, the SQL endpoint is needed for rich queries. The CRUD and DOCUMENT endpoint currently support key-document semantics only.

The CRUD endpoint a single row from a MySQL table identified by its primary key value. The row is mapped to JSON in the most simple way. No meta data is included: a very lightweight reply.

shell> curl ...  --url "http://127.0.0.1:8080/crud/db/simple/1"
{"id":"1","col_a":"Ahoy"}

The SQL endpoint supports HTTP GET requests only. CRUD and DOCUMENT endpoints accept GET, PUT and DELETE requests. A better mapping is much desired. To be frank, once more, we had the choice between writing documentation and attempting to look a bit more REST-like. You know REST, what’s there to demo?

The DOCUMENT endpoint takes any valid JSON and stores it. The access pattern is key-document:

shell> # curl -i -X PUT --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/doc/db/another_table"
HTTP/1.1 201 Created
...
{"info": "Table created"}
shell> # curl -i -X PUT -d '{"words": ["Hello", "world"]}' --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/doc/db/another_table/key"
HTTP/1.1 200 OK
...
{"info": "Document added"}
shell> # curl -X DELETE -i --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/doc/db/another_table/"
HTTP/1.1 200 OK
...
{"info": "Table dropped"}

That’s inside

The HTTP Plugin for MySQL works exactly like any of todays client-side proxies/web services. A multi-threaded web server library featuring asynchronous I/O accepts the web request. Then, the plugin translates it to SQL statements to compute an answer. A server plugin alone does not make MySQL NoSQL or a document store over night.

MySQL 5.7.4 brings some improvements that help with the proxy job: updated SQL JSON functions, virtual columns and stay tuned on that [functional indexes] topic!.

Acting exactly like any of todays client-side proxies and storing JSON documents in plain BLOB columns has the advantage of acting fast. Improving the HTTP API alone takes about as much effort as it takes improving a web service! The MySQL Connectors team can act fast and independent of any other team on this.

Starting simple with the CRUD and DOCUMENT endpoint, offering key-document semantics only leaves room for future optimization. There are faster ways than mapping HTTP requests to SQL. For MySQL 5.7 the InnoDB team claims 1.100.000 QPS through the memcache protocol and 625.000 QPS through SQL. But optimizing for speed would be a premature optimization. After all, we don’t have finalized APIs yet.

The other stories

Since 2009, since the time when the equation NoSQL = Not Only SQL appeared, MySQL expert users have attempted to create key-value access methods for MySQL. This ultimately resulted in the development of the Memcache interfaces for MySQL. There is a dual access method for data inside MySQL: fast key/value and rich SQL.

PHP, … any standard client
Memcache driver MySQL Connector
| |
Fast Key/Value Rich SQL
| |
InnoDB Memcache Plugin Standard C/S interface
MySQL

This story has one disadvantage: clients need two drivers. The protocol changes, two libraries are needed that implemenent the network protocol and data serialization. Assuming the SQL CRUD and DOCUMENT endpoints would be optimized for performance, clients could choose between fast access and rich query language by just changing the URL.

How about SQL over Protocol X? The SQL endpoint has given you a quick impression how much information the standard MYSQL Client/Server protocol must carry to handle all possible SQL and every possible client. If you need only a subset of the SQL features offered by MySQL, you could try to write a plugin that has a leaner protocol. There are major users of MySQL that restrict themselves to certain SQL features. Some even developed their own programming languages. To get back to the ground: how about Websocket for JavaScript users?

HTTP Client HTTP Client Your Client
| | |
HTTP (JSON) Websocket (JSON) Your protocol (your serialization)
| | |
HTTP Plugin Websocket Plugin Your Plugin
MySQL

These and many more stories, some of which I hinted towards the end of the presentation, become possible if MySQL had just two plugin services:

HTTP Client Your Client
| |
HTTP (JSON) Your protocol (your serialization)
| |
HTTP Plugin Your Plugin
Plugin SQL Execution Service Plugin Authentication Service
MySQL

But that’s another story for another audience. And, no, the development preview release only contains some proof-of-concept hacks here, no proper plugin services…

Happy hacking!

@Ulf_Wendel Follow me on Twitter

8 Comments

  1. Pingback: MySQL 5.7 – HTTP Plugin for MySQL | InsideMySQL

  2. How can this plugin resolve the sql injections and the unwanted bad sql-s?

    • The SQL endpoint runs any SQL you want and allow for the MySQL user used to execute it.

      • I think you mean to say: “There is nothing built in to resolve sql injections or unwanted bad sql queries”

        • I mean what I say: the SQL endpoint runs any SQL you want. It runs SQL as a certain MySQL user. Restrict the user to whateer you want.

        • I’m taking a rest today. Let me give a more complete view in an extra blog posting, deal?

  3. “Restrict the user to whateer you want.” – yes, this is the only way, BUT
    this is not enough to avoid for example list out from a table payment_transaction user_id = Y meanwhile the user is X. -> you allowed sql user to list table payment_transaction

    So the PHP was a wrapper to handle it with log in sessions etc.

  4. Pingback: HTTP Plugin for MySQL | Joseph Scott

Leave a Reply

Required fields are marked *.