WL#8338: X Plugin

Status: Complete

The MySQL X Plugin implements a feature set and an extended protocol that aims to expose the ACID compliant data storage capabilities of MySQL through a simple to use CRUD style programming interfaces (in addition to the traditional SQL language) for use with both Document Store and Relational data.

The document model interfaces will be implemented as a layer on top of the existing SQL functionality of MySQL. The protocol and client libraries will expose a high-level interface which will be implemented by the plugin. The plugin will be responsible for implementing everything necessary to map between document model requests to SQL.

Functional Requirements

Functional Requirements

General Requirements

F-A1: Must implement a documented oriented CRUD interface for storing and retrieving JSON data

F-A2: User should be able to create document tables through a high-level API, hiding the underlying SQL layer implementation details

F-A3: User should be able to store and perform basic queries in documents tables without writing SQL

F-A4: Must implement the extended X protocol that provides equivalent functionality to plain MySQL connections

Connection

F-B1: Must advertise its capabilities via the getCapabilities message

F-B2: Must allow the client to advertise its own capabilities via the setCapabilities message

F-B3: Must allow users to force close (kill) other sessions

F-B4: Must allow privileged users to view sessions connected to the plugin

F-B5: MySQL clients that do not speak the extended protocol attempting to connect to the X plugin by mistake should be handled in a reasonable way

F-B6: Must support SSL connections

Authentication

F-C1: Must perform client authentication through a challenge/response type authentication method which shall not transfer passwords in clear text over the network

F-C2: Must use and follow the MySQL user system (mysql.user)

Query / DML Interface

F-D1: Must allow the user to execute traditional SQL commands, as has been possible until now

F-D2: Must allow the user to execute document model CRUD commands

F-D3: All operations must return an OK message if successful, containing number of rows affected, GTID (if enabled) and other metadata

SQL Interface

F-E1: Must allow executing SQL statements

Resultset Interface

F-F1: Must allow retrieving the metadata and rows from an executed statement

F-F2: User must be able to request the server to return a simplified version of the metadata, omitting information such as names

Document Table Interface

F-G1: Must allow creation of tables suitable for storage of documents, without requiring the user to write SQL with the necessary form

F-G2: Must allow creation of indexes on document fields, without requiring knowledge of the SQL underpinnings

F-G3: Must allow dropping indexes

F-G4: Must allow listing existing indexes

F-G5: Must allow listing existing document tables

F-G6: Must allow dropping tables

CRUD Interface - Insert

F-G7: Must allow insertion of JSON documents on a previously created document table

F-G8: Must use a predefined field named _id as an object identifier or primary key for the document

F-G9: The object identifier must be unique in the table

F-G10: A unique object identifier must be generated by the plugin if one is not provided by the client

CRUD Interface - Find

F-H1: Must allow querying JSON documents by matching their contents to a user specified expression

F-H2: Must return a resultset with 0 or more rows, with metadata

F-H3: Must allow SELECTing a relational table through the CRUD interface

CRUD Interface - Update

F-I1: Must allow updating stored JSON documents, identified by object id or by a query expression

F-I2: Must allow updating relational table columns through the CRUD interface

CRUD Interface - Delete

F-J1: Must allow deleting stored JSON documents, identified by object id or by a query expression

F-J2: Must allow deleting rows from a relational table through the CRUD interface

Non-Functional Requirements

NF-1: Must authenticate against MySQL user accounts

NF-2: Must allow basic status monitoring of the plugin

NF-3: Must confirm to MySQL user account restrictions (such as expired passwords and rejecting logins to locked accounts)

NF-4: It is NOT a requirement that the Document CRUD interface work with collections created manually by the user, unless they follow the exact same table layout as the one used by the plugin. The plugin will use a specific table layout for Collections and if users attempt to mimic them incorrectly, the results would be undefined when using the Document CRUD interface. However, such tables will still be fully supported by the Relational CRUD interface.

NF-5: It should be possible to backup and restore a schema and its data using traditional tools such as mysqldump and mysql

NF-6: From a performance point of view, the general expectation is that operations executed through the plugin will have similar performance as when executed through the current client connection pipeline, using the classic MySQL protocol. The plugin will thus attempt to be as close as possible to that for common operations under common conditions.

Concepts
Administration
- Installation
- Backwards Compatibility
Monitoring
Security
Protocol
Commands
- GetCapabilities
  - Errors
- SetCapabilities
  - Errors
- StmtExecute
  - Arguments
  - Results
  - create_collection
  - create_collection_index
  - list_objects
    - Arguments
    - Errors
  - drop_collection
    - Arguments
    - Errors
  - drop_collection_index
    - Arguments
    - Errors
  - enable_notices
    - Arguments
    - Errors
  - disable_notices
    - Arguments
    - Errors
  - list_notices
  - kill_client
  - list_clients
    - Errors
  - Errors
- Insert
- Delete
  - Arguments
  - Results
  - Errors
- Update
  - Arguments
  - Results
  - set
    - Arguments
  - remove
    - Arguments
  - replace
    - Arguments
  - merge
    - Arguments
  - array_insert
    - Arguments
  - array_append
    - Arguments
  - Errors
- Find
  - Arguments
  - Results
  - Errors
- Open Expectation Block
  - Arguments
  - Errors
- Close Expectation Block
  - Errors
Notices
- warning
- SessionStateChanged

Concepts

The Plugin

The X plugin is shipped as a daemon type MySQL plugin named mysqlx.so

Document

A set of key/value pairs that represent objects provided by the user, such as a JSON object. A document is represented internally in MySQL using the MySQL binary JSON object, through the JSON MySQL datatype.

For transfer between server and client and client side manipulation of documents, the standard plain text, UTF-8 encoded representation is used.

Document Id

A document id is a value that uniquely identifies a document inside a document table, identified by the _id key name. A generated column that extracts its value into a column named _id and a UNIQUE NOT NULL index is automatically created for it.

If the field is missing, the insert will be rejected.

To allow distributed, uncoordinated generation of Document Ids, the Document Id is specified as a client generated UUID value.

Additionally, Document Ids are to be considered immutable. Once a document is inserted into a collection, the _id may not be changed. That is to prevent references/foreign keys to not get out of sync, specially for the case where cascading updates may be expensive, such as in sharded environments.

UUID Format

The UUID format is to be decided by the client DevAPI group.

Requirements:

Strong global uniqueness guarantee
Be (mostly) monotonically incremental, at least within the same client connection, considering that globally sequential UUIDs would be difficult if not impossible

Document Table/Collections

A document table is an ordinary MySQL table that has a pre-defined layout which is used to store Documents. The table is defined as follows:

doc JSON NOT NULL, _id VARCHAR(32) NOT NULL GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) VIRTUAL UNIQUE KEY

doc is defined to be NOT NULL, because for a document to be valid it must have at least the _id field. It also provides some protection against accidental document deletions, such as when using UPDATE operations with JSON functions with NULL parameters.
_id is defined as virtual column, that maps to the _id field inside the JSON document.

The table may also contain additional columns, which are automatically created by the plugin to allow creation of indexes on the contents of the document.

Document tables are created through the create_collection command.

It is expected that the DDL of a collection does not change outside the control of the plugin.

There is no additional X plugin specific metadata for indexes at this time.

Data Model

CRUD API commands specify the data model over which they operate on. Currently, relational and document are supported.

The basic premise is that traditional relational tables and documents can be mapped between each other (ie. a table column corresponds to a document field) and thus, the same CRUD operations can be applied to either. To explicitly define whether we're dealing with tables or collections, CRUD protocol requests contain a data_model field, which specify that to the plugin beforehand.

When operating on a table in relational "mode", any SQL table definition is supported. Table columns can be of any number and any type and JSON object is an optional component, just like any other datatype. There is syntax support for operating on JSON objects (ie for examining the contents of a JSON document), but otherwise, these are treated as any other column.

Example relational table:

| id | product    | price  | manufacturer |  nutritional_facts (JSON) |
| 1  | chocolate  | 1.5    | ACME         | {"calories": 100, "carbs": 50 ...}
| 2  | banana     | 1.0    | NULL         | {"calories": 30, "carbs": 20 ...}

When operating on a document table, a few restrictions are enforced and a few simplifications occur. First, the document collection table must have a specific definition (or be created by the plugin through the create_collection command) and the data inserted must be valid JSON objects with an _id field containing a unique Object identifier. In this case, there is no explicit database level schema for the table which needs to be defined by the user. Operations on the table will use document paths only and column level identifiers are made transparent, such that from the point of view of the developer, a collection will look like nothing more than a document object container, with no concept of columns.

Example document collection (as exposed by the client side APIs):

| doc |
| {"_id": xxxxx, "product": "chocolate", "price":1.5, "manufacturer": "ACME", "nutrifacts" : {"calories": 100, "carbs": 50 ...}} |
| {"_id": xxxxy, "product": "banana", "price":1.0, "nutrifacts" : {"calories": 30, "carbs": 20 ...}} |

Index

A document table can have any number of user defined indexes. User defined indexes are implemented as stored generated columns that use the JSON_EXTRACT() function, or virtual generated columns if support for those are present in the server in time.

Indexes are created through the create_collection_index command.

Because indexing is fully based on core server functionality, it is also possible to create indexes manually via SQL.

There is no additional X plugin specific metadata for indexes at this time.

Index Typing

Collection indexes support the following constraints:

unique - whether duplicate entries for the indexed document path are allowed
required - whether the document path is required to be present in all documents in the collection

required is implemented by making the generated column NOT NULL and relies on JSON_EXTRACT() returning NULL for docpaths that don't exist. Note that this is not the same as NOT NULL from the point of view of a document collection, since existing paths that contain the null value would satisfy the required constraint; that is: json_extract('{"a" : null}', '$.a') is not the same as json_extract('{}', '$.a')

Because JSON documents lack detailed typing information, the user is required to explicitly provide the datatype to be used for the index. The following types are allowed:

TEXT(n)
INT [UNSIGNED]
FLOAT
DOUBLE
DECIMAL
TIMESTAMP
DATETIME(n)
DATE
TIME(n)

Note that for the TEXT type, the index will be created using the supplied prefix length.

CRUD Commands and the Developer API

Traditionally, MySQL clients will craft and send SQL statements in the form of a string to the server, which will then parse and execute them. These SQL statements are opaque to everything inbetween the application and the MySQL parser, they can express any statement, but extracting information from them requires parsing the SQL grammar.

Similar to RPC servers, in the X plugin, each type of command is encoded through different messages. The protocol, not the MySQL grammar, defines the list of accepted commands and their syntax.

This allows for the introduction of intermediate layer components that can act on the contents of the commands at a higher level, without expensive and complex SQL parsing.

At the client side, a Connector will provide a high-level developer API (DevAPI), that will assemble the messages according to what is defined in the protocol, from the programmers native code.

Example:

var res = db.select('name', 'age')

       .from('employee')
       .where('name like :name and age > 30')
       .orderBy('name')
       .bind({name: 'm%'})
       .returnAll();

The conversion from native code to protocol messages is done by the Connector, including the parsing of logical expressions from SQL string into a parse tree format defined by the protocol.

The plugin will then build back a SQL WHERE expression string, which can be passed to the MySQL parser.

The client side Developer API is defined in a separate document.

Session

A MySQL X session, which corresponds to a MySQL session or an instance of the THD structure. Although a Session currently also corresponds to a physical connection, they are not strictly tied to each other. A Connection could potentially be used as a transport by more than one session.

Transactions

Because CRUD operations map directly to SQL statements, the usual MySQL transaction semantics apply. In other words, by default commands are executed in auto-commit mode. If a START TRANSACTION SQL statement is issued by the client, all subsequent commands, both CRUD and SQL will be executed in that transaction.

Lifetime of a transaction is controlled via SQL transaction statements (START TRANSACTION, COMMIT, ROLLBACK), or the usual implicit commits.

Pipelining

Because the protocol is designed in a way to minimize dependencies of request messages on a previous reply, it is possible to decouple reading requests from network, unserialization, executing requests and sending responses. This allows multiple network roundtrips to be aggregated together and reduce the effect of network latency.

It also becomes possible for clients to send a batch of commands to the server all at once and read their responses also all at once, minimizing the number of network roundtrips and allowing for better performance, specially when network latency is a bottleneck.

For example, a PHP script rendering a web page can connect, authenticate, perform multiple selects, request their results and close the session all in a single step and then read back all responses at once, potentially reducing the whole operation into a single network round trip.

Expectations

Expectations and Batch Error Handling

With the introduction of pipelining and batch support, it becomes necessary to specify the behaviour of the session with respect to error conditions.

In a traditional, non-batch (interactive) client session, the client will send a request, read back its results and can decide whether to send the next request depending on that result. Dependencies between statements are handled explicitly by the client.

With batching, all requests are sent at once before reading any results. The normal behaviour would be that any errors that occur in a batch do not affect the flow of execution. For cases where the execution of a batch must be interrupted in case of errors, a new concept of Expectations is introduced. Using expectations, the user can define blocks of commands, where an error will cause the remaining statements in the block to fail until its end (like an exception block).

Notices

Notices are messages that may interleave the normal message sequence, used by the server to report certain out-of-band information to the client.

There are 2 scopes of notices: Local and Global.

Local scope notices may be generated by a specific command and will be sent between the moment the command is issued and before the final Ok packet for the command is returned. For example, SELECT 1/0 will return a resultset, a Notice with a divide by 0 warning and a StmtExecuteOk message.

Global scope notices are asynchronous in nature and may be sent by the server at any time. Global notices may be used for purposes such as configuration changes, connection timeouts and others. At the moment, there are no Global notices defined.

Administration

Installation

Plugin loading and unloading is done through the standard INSTALL and UNINSTALL PLUGIN commands and can be done through any MySQL client.

There are no additional steps required by the user.

Backwards Compatibility

Because the plugin is entirely new and implements new APIs and interfaces, there are no predicted backwards compatibility issues. The plugin should not have any effects on users upgrading from a MySQL 5.6 server to an X enabled MySQL 5.7.

Downgrading from MySQL 5.7 to 5.6 will likewise not be impacted by the X plugin itself, other than loss of functionality provided by the plugin.

At the protocol level, the X plugin does not intend to provide the full feature set of the classic MySQL protocol and certain commands, such as COM_SHUTDOWN, COM_BINLOG_DUMP, COM_FIELD_LIST will not be supported.

The plugin will listen for TCP connections on port 33060 and will not interfere with connections to port 3306 using the classic protocol.

Note: Classic MySQL clients that do not support the new protocol will not be recognized by the X plugin and will be disconnected after a timeout. Because of a difference in the side that makes the 1st step during the handshake, a client would otherwise wait forever for an initial message from the server side to arrive.

Monitoring

Performance Schema

Integration with the performance_schema will be performed whenever appropriate, including:

threads - network thread
mutexes
rwlocks
condition variables

Server Variables

mysqlx_connect_timeout

Number of seconds to wait for the first packet to be received from newly connected clients.

This option is dynamically changeable.

mysqlx_port

specifies the port where the X Plugin will listen for connections. Default value 33060

This option is only read at startup time.

mysqlx_max_connections

max number of concurrent client connections the X plugin can accept. Default value 100

This option is dynamically changeable. If the new value is smaller than the current number of connections, the new limit will only be taken into account for new connections.

mysqlx_max_allowed_packet

max size of a network packet

This option is dynamically changeable.

mysqlx_min_worker_threads

minimum number of worker threads the X plugin will use for handling client requests. Default value 100. The maximum is limited implicitly by Mysqlx_max_connections

This option is dynamically changeable.

mysqlx_idle_worker_thread_timeout

Number of seconds after which an idle worker thread is terminated.

This option is dynamically changeable.

SSL related options

The following options are used from the server settings to configure SSL:

ssl_key
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_crl
ssl_crlpath

There are also mysqlx_ prefixed versions of the same variables from the plugin, that will override the server options if any of them are set.

These options are not dynamically changeable.

Status Variables

When querying status variables from outside of mysqlx session (e.g. from regular mysql client), only global variables are accessible. When querying status variables from inside of mysqlx session, global variables are always shadowed by their session counterparts. GLOBAL and SESSION modifiers of SHOW STATUS command are ignored in every case.

Global and Session Status Variables

Mysqlx_bytes_sent

number of bytes sent through the network

Mysqlx_bytes_received

number of bytes received through the network

Mysqlx_stmt_execute_sql

number of StmtExecute requests received for the SQL namespace

Mysqlx_stmt_execute_xplugin

number of StmtExecute requests received for the xplugin namespace