WL#8338: X Plugin

Status: Complete   —   Priority: Medium

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.

Contents


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.

Contents


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

Mysqlx_stmt_create_collection

number of the named commands received

Mysqlx_stmt_create_collection_index

number of the named command received

Mysqlx_stmt_drop_collection

number of the named command received

Mysqlx_stmt_drop_collection_index

number of the named command received

Mysqlx_stmt_list_objects

number of the named command received

Mysqlx_stmt_enable_notices

number of the named command received

Mysqlx_stmt_disable_notices

number of the named command received

Mysqlx_stmt_list_notices

number of the named command received

Mysqlx_stmt_list_clients

number of the named command received

Mysqlx_stmt_kill_client

number of the named command received

Mysqlx_stmt_ping

number of the named command received

Mysqlx_crud_insert

number of Insert requests received

Mysqlx_crud_update

number of Update requests received

Mysqlx_crud_delete

number of Delete requests received

Mysqlx_crud_find

number of Find requests received

Mysqlx_expect_open

number of expectation blocks opened

Mysqlx_expect_close

number of expectation blocks closed

Mysqlx_errors_sent

number of errors that were sent to clients

Mysqlx_rows_sent

number of rows sent back to clients

Mysqlx_notice_warning_sent

number of warning notices sent back to clients

Mysqlx_notice_other_sent

number of other types of notices sent back to clients

Mysqlx_ssl_cipher

current SSL cipher (empty for non-SSL connections)

Mysqlx_ssl_cipher_list

list of possible SSL ciphers (empty for non-SSL connections)

Mysqlx_ssl_verify_depth

the certificate verification depth for SSL connection

Mysqlx_ssl_verify_mode

the certificate verification mode for SSL connection

Mysqlx_ssl_version

name of the protocol used for the connection ssl

Mysqlx_ssl_sessions_reused

query whether a reused session was negotiated during handshake

Global Only Status Variables

Mysqlx_sessions

number of currently active sessions

Mysqlx_sessions_closed

number of sessions that were closed

Mysqlx_sessions_fatal_error

number of sessions that were closed with a fatal error

Mysqlx_worker_threads

number of worker threads

Mysqlx_worker_threads_active

number of worker threads currently executing client requests

Mysqlx_connections_closed

number of network connections that were closed on the network layer

Mysqlx_connections_accepted

number of network connections that were accepted on the network layer

Mysqlx_connections_rejected

number of network connections that were rejected because of mysqlx_max_connections limit was reached

Mysqlx_connection_errors

number of network connection errors that happened

Mysqlx_connection_accept_errors

number of network connection errors that happened during connection accept phase

Mysqlx_sessions_accepted

number of sessions that were properly authenticated

Mysqlx_sessions_closed

number of sessions that were closed

Mysqlx_sessions_rejected

number of sessions that were rejected before proper authentication finished

Mysqlx_sessions_killed

number of sessions that were killed

Mysqlx_ssl_accept_renegotiates

number of negotiates needed to establish the connection

Mysqlx_ssl_accepts

number of accepted SSL connections

Mysqlx_ssl_ctx_verify_depth

the certificate verification depth limit currently set in ctx

Mysqlx_ssl_ctx_verify_mode

the certificate verification mode currently set in ctx

Mysqlx_ssl_finished_accepts

number of successful SSL connections to the server

Mysqlx_ssl_server_not_after

last date for which the SSL certificate is valid

Mysqlx_ssl_server_not_before

first date for which the SSL certificate is valid

Mysqlx_ssl_session_cache_hits

number of successfully reused sessions

Mysqlx_ssl_session_cache_misses

number of sessions proposed by clients that were not found in the internal session cache in server mode

Mysqlx_ssl_session_cache_mode

mode of session cache

Mysqlx_ssl_session_cache_overflows

number of sessions that were removed because the maximum session cache size was exceeded

Mysqlx_ssl_session_cache_size

size of internal session cache

Mysqlx_ssl_session_cache_timeouts

number of sessions proposed by clients and either found in the internal or external session cache in server mode, but that were invalid due to timeout.

Mysqlx_ssl_used_session_cache_entries

number of sessions held in the internal session cache


Security

Client Sessions, Security Context and Access Control

The plugin uses the Session and SQL Service APIs (WL7947 and WL8177) provided by the core server to perform database operations. When a client connection is accepted by the server, a MySQL Session object is allocated for it, which is then used exclusively by that client until the end of the session.

The session object has a security context associated to it, which maps to a MySQL user account in the mysql.user table. Any database operations executed through the SQL Service will trigger internal access control checks in the core server, in the same way as classic MySQL connections. Thus, all the usual database access control checks and privileges will be honored.

Authentication

Authentication is handled by the plugin, independently from the pluggable authentication framework present in the server, which cannot be used by plugins.

The X protocol supports a generic authentication framework based on SASL, which can be used as the foundation for specific authentication methods.

Initially, a challenge/response based method, similar to the mysql_native_password method from MySQL4.1 will be implemented. The password hashing will be performed using the same algorithm as the "new" MySQL4.1 authentication method.

Additionally, a simple plain text authentication mechanism is also enabled for TLS encrypted connections (unavailable without TLS).

During authentication, the plugin will use a limited privilege security context, mapped to the specially created mysqlxsys@localhost account, to perform queries on the mysql.user table. Once the session is authenticated, that security context will have the final user assigned to it.

Plugin Account

When the plugin is installed, a check is performed to see if the mysqlxsys@localhost account already exists and has the required privileges. If not, the account will be created. If the account already exists but has different privileges, the plugin will proceed normally, unless the account does not have the required rights, in which case the plugin will not start.

The account has only SELECT privilege on the mysql.user table and is created in the LOCKED state, so that it cannot be used externally. A fixed password is assigned for the purpose of not triggering the validate_password plugin, but will never be actually usable as the account is locked.

During creation of that account, a temporary session is used, authenticated as the root@localhost account. The root account is expected to be valid and not expired. The session is destroyed after the necessary checks.

The account is automatically dropped when the plugin is uninstalled.

Network Communications

The plugin supports TLS encryption of TCP connections according to server configuration (YaSSL or OpenSSL, depending on server build).

There are plugin specific SSL configuration options, which can be used to override the default server configuration. But if these are not provided, the default server SSL options will be used.

Although it is possible for clients to communicate with the plugin through an unencrypted channel, client libraries are expected to enable TLS by default, unless explicitly disabled by the user.

If TLS is not enabled, authentication methods that rely on transmission of unencrypted passwords are disabled.

Protocol messages are specified using the Protobuf language. The Protobuf library, when performing serialization and deserialization to/from network performs various validations (including boundary checks, conformance to the message specification etc), ensuring that invalid or corrupted messages are detected and handled appropriately (client connection immediately aborted).

Auditing Integration

Audit log support is handled internally by the SQL session service. The following events triggered through the plugin are logged:

  • pre-auth (client connected but not authenticated)
  • connect (authentication performed)
  • command (SQL command executed)
  • disconnect

SQL Execution

SQL execution is performed through the SQL command service, which performs all authorization checks necessary.

To minimize or eliminate the risk of SQL injection issues, several mechanisms are used:

  • single SQL statements only - multiple statements per request are not supported
  • placeholder binding - it is possible to insert placeholders in the SQL statement, which will be replaced with literal values provided by the client (with proper escaping). In conjunction with client library support, this makes it easy for developers to build SQL statements without string concatenation.
  • granular protocol level representation - for CRUD operations, requests are broken down into individual components at the protocol level and assembled back into a full SQL statement by the plugin, where any strings are properly escaped before assemblage into SQL.

Prepared statements would be another mechanism that would help here, but is not implemented initially.

Session Killing

As of now, the KILL CONNECTION MySQL command will be partially honored. Sessions that are flagged as killed will be disconnected by the plugin when the opportunity arises (before or after a client command is handled), but not if it's idle.

A X plugin specific kill_client command is also available, which can be used to kill X sessions in any state, through an X plugin specific client Id. This is important as in the future, it may be possible for a single client connection to have multiple MySQL sessions associated.

Session Reset

The Mysqlx::Session::Reset message can used to reset the session while preserving the connection. A session Reset will leave the session in the same state as it was before it was authenticated. Furthermore, it will also recreate the MySQL SQL Session associated to it and all other session state, to ensure that no state is not preserved across a Reset. After a Reset, the client is expected to authenticate it again.


Protocol

The X Plugin implements protocol extensions as described in WL#8639.

Commands

GetCapabilities

Returns the capabilities of the current version of the plugin. This command may only be executed during the handshake step of the connection. Some of the currently supported capabilities:

  • authentication.mechanisms = [PLAIN,MYSQL41] (read only)
  • document.formats = [PLAIN] (read only)
  • tls = bool (read/write)
  • client.pwd_expire_ok = bool (read/write)
  • plugin.version = string (read only)

Rationale: Server capabilities announcement is an optional step, so that the extra message can be omitted in environments where roundtrip minimization is desired and clients already know about server capabilities beforehand.

Errors

None

SetCapabilities

Notifies the server about capabilities that the client supports for read/write capabilities.

Errors

5001 - "Capability prepare failed for '%s'" 5002 - "Capability '%s' doesn't exist"

StmtExecute

Executes a client statement. StmtExecute is a generic command execution interface. It has a namespace field, which is used to specify the type of command to be executed. Currently, 2 namespace are understood by the plugin:

  • sql
  • xplugin

The 1st allows clients to execute arbitrary MySQL SQL statements, which are forwarded directly to the MySQL SQL parser. The 2nd is used for executing X plugin specific commands.

Arguments

  • namespace - the namespace that stmt belongs
  • stmt - string representing the statement to be executed
  • args - array of optional arguments to the statement or variables to be replaced for placeholders in stmt
  • compact_metadata - if true, the resultset metadata (if present) will omit all information except for type

Results

StmtExecute will generate 0, 1 or more resultsets, followed by optional local Notices and a closing StmtExecuteOk message.

Local scope Notice messages are used to report WARNINGs that were generated during the execution of the statement. They can be enabled/disabled with the enable_notices or disable_notices commands.

Commands understood in the xplugin namespace are listed below:

create_collection

Creates a table for document storage.

Document tables are created with the format described earlier. They are to be treated as a opaque document bucket and is not intended to have its structure changed by the user.

Arguments
  • schema: string
  • name: string
Pre Conditions
  • User must have CREATE privileges on the schema
Errors
  • 5015 - "Insufficient number of arguments"
  • 5015 - "Invalid number of arguments, expected %i but got %i"
  • 5112 - "Invalid schema"
  • 5113 - "Invalid collection name"

create_collection_index

Creates an index on a given document field. Indexes on documents are created by first creating a virtual generated column that extracts the field from the document to an indexable column and then creating the index on that column. JSN_EXTRACT() is used to extract the field value and JSN_UNQUOTE() to remove the quoting from strings. User must specify the datatype that the field must be extracted as. The default is VARCHAR(64).

If an index of the requested type already exists for the field, the request will be ignored. If an index of a different type for the same field exists, an error will be returned.

Indexes are created as follows:

  • ALTER TABLE table ADD COLUMN (v_field_name GENERATED ALWAYS AS (JSN_EXTRACT(document, '$.field_name ')) VIRTUAL, ADD INDEX table_field ON table.v_field_name
Arguments
  • schema: string
  • table: string
  • name: string - name of the index, optional
  • field_path: string - the document path to the field to be indexed
  • unique: bool - whether the index should be a unique index
  • not_null: bool - whether the generated column will be created as NOT NULL
  • datatype: string - datatype of the index
Pre Conditions
  • User must have Index privileges on the schema
  • User must have Alter privileges on the table
  • The indexed field must be of a simple type. Multi-value indexes are currently not supported.
Errors
  • 5015 - "Insufficient number of arguments"
  • 5015 - "Invalid number of arguments, expected %i but got %i"
  • 5015 - "Too few arguments";
  • 5016 - "Invalid type for argument '%s' at #%i (should be %s)"
  • 5017 - "Argument value '%s' for document_path is invalid"
  • 5017 - "Argument value '%s' for index_name is invalid"
  • 5017 - "Invalid document path value for argument %s"
  • 5017 - "Invalid or unsupported type specification '%s'"
  • 5112 - "Invalid schema '%s'"
  • 5113 - "Invalid collection name '%s'"
  • 5113 - "Invalid collection name: %s.%s"
  • 5117 - "Collection contains document missing required field"

list_objects

Creates a resultset containing the list of document tables and other objects in the specified schema.

The list of tables will contain all tables of the schema, regardless of where they were created. A flag will indicate whether the table is a document collection or not, which is determined from its definition.

Arguments
  • schema: string
  • filter: string - a filter to use for matching object names to be returned
Errors
  • 5015 - "Insufficient number of arguments"
  • 5015 - "Invalid number of arguments, expected %i but got %i"

drop_collection

Drops the named table and its indexes.

Arguments
  • schema: string
  • table_or_collection: string
Errors
  • 5015 - "Insufficient number of arguments"
  • 5015 - "Invalid number of arguments, expected %i but got %i"
  • 5112 - "Invalid schema"
  • 5113 - "Invalid collection name"

drop_collection_index

Drops the named index and virtual column for the table.

Arguments
  • schema: string
  • table_or_collection: string
  • index_name: string
Errors
  • 5013 - "Invalid index name"
  • 5015 - "Insufficient number of arguments"
  • 5015 - "Invalid number of arguments, expected %i but got %i"
  • 5112 - "Invalid schema"
  • 5113 - "Invalid collection name"
  • 5113 - "Invalid collection name: %s.%s"

enable_notices

Receives a list of notice names to be enabled for the session.

Arguments
  • notice*: string (one or more)
Errors
  • 5015 - "Insufficient number of arguments"
  • 5015 - "Invalid number of arguments, expected %i but got %i"
  • 5163 - "Invalid notice name %s"

disable_notices

Receives a list of notice names to be disabled for the session.

Arguments
  • notice*: string (one or more)
Errors
  • 5015 - "Insufficient number of arguments"
  • 5015 - "Invalid number of arguments, expected %i but got %i"
  • 5163 - "Invalid notice name %s"
  • 5164 - "Cannot disable notice %s"

list_notices

Returns a resultset containing a list of supported notices and their enable state.

Argumetns

None

Resultset

Resultset has 2 columns, the name of the notice and the enable state.

Errors
  • 5015 - "Insufficient number of arguments"

kill_client

Kills another X client session.

Arguments
  • client_id: bigint - the client id
Pre Conditions
  • User must have SUPER privileges or own the session being terminated
Errors
  • 5015 - "Insufficient number of arguments"
  • 5015 - "Invalid number of arguments, expected %i but got %i"

list_clients

Lists X plugin client connections

Errors
  • 5015 - "Insufficient number of arguments"
  • 5015 - "Invalid number of arguments, expected %i but got %i"

Errors

  • 5157 - "Invalid xplugin command %s"

Insert

Inserts a row or set of rows in the specified collection or table.

Arguments

  • schema and table
  • data_model - relational or document
  • projection - list of columns being inserted (for TABLE inserts)
  • rows - 1 or more rows with the data to be inserted

Results

  • rows_affected - number of rows that were affected by the operation
  • last_insert_id - last value used for the auto-increment field in a TABLE INSERT operation (not set for DOCUMENT inserts)

Mapping

When inserting into a document collection, the generated INSERT statement will look like:

INSERT INTO schema.table (doc, _id) VALUES (?, JSN_UNQUOTE(JSN_EXTRACT(doc, '$._id')));

When inserting into a relational table, the generated INSERT will be as:

INSERT INTO schema.table (projection, ...) VALUES (?, ...);

Errors

  • 5013 - "Missing row data for Insert"
  • 5014 - "Wrong number of fields in row being inserted"
  • 5112 - "Invalid schema"
  • 5113 - "Invalid collection"
  • 5113 - "Invalid table"
  • 5114 - "Invalid projection for document operation"
  • 5115 - "Document is missing a required field"
  • 5116 - "Document contains a field value that is not unique but required to be"
  • 5117 - "Table '%s' is not a document collection"
Common CRUD errors (expressions related)
  • 5150 - "Invalid operator %s"
  • 5151 - "Asterisk operator require zero or two operands in expression"
  • 5151 - "BETWEEN expression requires exactly three parameters."
  • 5151 - "Binary operations require exactly two operands in expression."
  • 5151 - "Binary operations require exactly two operands in expression."
  • 5151 - "CAST expression requires exactly two parameters."
  • 5151 - "DATE expression requires exactly three parameters."
  • 5151 - "IN expression requires at least two parameters."
  • 5151 - "LIKE expression requires exactly two or three parameters."
  • 5151 - "Nullary operator require no operands in expression"
  • 5151 - "Unary operations require exactly one operand in expression."
  • 5152 - "Column name is required if table name is specified in ColumnIdentifier.
  • 5152 - "Table name is required if schema name is specified in ColumnIdentifier.
  • 5153 - "Invalid content type for Mysqlx::Datatypes::Scalar::Octets %s"
  • 5153 - "Invalid value for Mysqlx::Datatypes::Any::Type %s"
  • 5153 - "Invalid value for Mysqlx::Datatypes::Scalar::Type %s"
  • 5153 - "Invalid value for Mysqlx::Expr::DocumentPathItem::Type %s"
  • 5153 - "Invalid value for Mysqlx::Expr::Expr_Type %s"
  • 5153 - "Mysqlx::Expr::Expr::VARIABLE is not supported yet"
  • 5154 - "CAST type invalid."
  • 5154 - "DATE interval unit invalid."
  • 5154 - "Invalid empty value for Mysqlx::Expr::DocumentPathItem::MEMBER"
  • 5154 - "Invalid key for Mysqlx::Expr::Object"
  • 5154 - "Invalid value for Mysqlx::Expr::Object on key '%s'"
  • 5154 - "Invalid value of placeholder"

Delete

Deletes one or more rows matching a filter expression from the given collection or table.

Arguments

  • schema and table
  • data_model - relational or document
  • criteria - a logical expression used to filter rows from the table to be deleted
  • limit - max number of rows to delete
  • order - optional ordering of the rows to be deleted. Relevant when a limit is specified

Results

  • rows_affected - number of rows that were affected by the operation

Errors

  • 5012 - "Invalid parameter: non-zero offset value not allowed for this operation");
  • 5112 - "Invalid schema"
  • 5113 - "Invalid collection"
  • 5113 - "Invalid table"
  • common CRUD errors related to expressions (see Insert command)

Update

Updates one or more rows matching a filter expression with the given operations. When operating on TABLEs, only full updates (SET) are allowed. When working with DOCUMENTs, partial updates are allowed through the use of the supported partial update operations.

The list of Update operatios are described further below.

Arguments

  • schema and table
  • data_model - relational or document
  • criteria - a logical expression used to filter rows from the table to be updated
  • limit - max number of rows to update
  • order - optional ordering of the rows to be updated. Relevant when a limit is specified
  • operations - list of operations to perform on the document

Results

  • rows_affected - number of rows that were affected by the operation
    • Update Operations**

Update operations depend on the data_model. For relational table updates, only the SET operation is supported. For document updates a richer set of partial update operations are supported:

set

Sets the value for an item identified by a document path in a document.

Arguments
  • document_path
  • value

remove

Deletes an item from the document, idenfied by a document path.

Arguments
  • document_path

replace

Change the value of a document item, if the document_path exists.

Arguments
  • document_path
  • value

merge

Merges the content of a document object with another.

Arguments
  • column (when operating on TABLE)
  • document - the document to be merged

array_insert

Inserts an item at the specified array path, with index.

Arguments
  • document_path with array index
  • item to insert

array_append

Appends an item at the specified array path, after the last element.

Arguments
  • document_path
  • item to insert

Errors

  • 5012 - "Invalid parameter: non-zero offset value not allowed for this operation"
  • 5050 - "Invalid data for update operation on document collection table"
  • 5050 - "Invalid update expression list"
  • 5050 - "Unexpected value argument for ITEM_REMOVE operation"
  • 5051 - "Invalid type of update operation for document"
  • 5051 - "Invalid type of update operation for table"
  • 5052 - "Invalid column name to update"
  • 5053 - "Forbidden update operation on '$._id' member"
  • 5053 - "Invalid document member location"
  • 5053 - "Invalid member location"
  • 5112 - "Invalid schema"
  • 5113 - "Invalid collection"
  • 5113 - "Invalid table"
  • common CRUD errors related to expressions (see Insert command)

Find

Performs a search on a document table, through a SELECT SQL statement.

Arguments

  • schema and table
  • data_model - relational or document
  • projection - list of fields to return as results of the query
  • criteria - a logical expression used to filter rows from the table to be updated
  • limit - max number of rows to update
  • order - optional ordering of the rows to be updated. Relevant when a limit is specified

Results

  • Column Metadata and row messages, followed by an Ok message

Errors

  • 5112 - "Invalid schema"
  • 5113 - "Invalid collection"
  • 5113 - "Invalid table"
  • 5120 - "Invalid projection target name"
  • 5121 - "`%s` is not a member of collection"
  • 5156 - "`%s` is not a collection"
  • common CRUD errors related to expressions (see Insert command)

Open Expectation Block

Starts an expectation block with the provided conditions. Expectations may be nested and must be followed by a Close statement.

Currently supported conditions are:

  • no_error - Fail all messages of the block after the first message returning an error.

Arguments

  • operation - whether the condition should be inherited from the parent block or started from scratch
  • condition - list of conditions that the block must wait

Errors

  • 5161 - "Invalid value '%s' for expectation no_error"
  • 5160 - "Unknown condition key"
  • 5159 - "Expectation failed: %s"

Close Expectation Block

Closes an expectation block.

Errors

  • 5158 - "Expect block currently not open"
  • 5159 - "Expectation failed: %s"

Notices

warning

Warning notices are sent to the client if the last command/statement executed generated Warnings. One notice message is sent to the client per warning (as in the output of the SHOW WARNINGS SQL command). Notices are sent before the StmtExecuteOk message for that command.

Warning notices can be enabled/disabled through the enable_notices/disable_notices xplugin namespace command.

By default, they are enabled.

SessionStateChanged

Simple notices for specific for sessions that are generated by client requests. They will be sent before the StmtExecuteOk message for the request.

  • GENERATED_INSERT_ID - if an AUTO_INCREMENT value is generated for an Insert statement, this will contain the value that was generated
  • ROWS_AFFECTED - number of rows affected, such as for update and delete operations
  • PRODUCED_MESSAGE - informational text message that can be generated by some SQL commands
  • ACCOUNT_EXPIRED - sent after authentication, if the account password is expired and the client has set the client.pwd_expired_ok capability. The session will be put into sandbox mode until the password is reset.
  • CLIENT_ID_ASSIGNED - sent after authentication, with the client id that can be used to kill it

Contents


Server Dependencies

This section lists dependencies on the MySQL server core at the C++ code level (not functionality accessed via SQL).

Core Functionality

Authentication

Authentication will be performed by the plugin, using services provided by the product of WL#7254.

SQL Service API

WL#8177

SQL Session Service

WL#7947

SQL Session Info Service

WL#8733

Security Context Service

WL#7254 , See New_security_context_plugin_service

JSON Functions

WL#7909

JSON Datatype

WL#8132

JSON Comparator

WL#8249

Resultset Processing

  • protocol.h

Misc Code

  • my_thread.h
  • thr_mutex.h

Plugin registration

  • mysql/plugin.h
  • sql_plugin.h

Logging

  • mysql/service_my_plugin_log.h

Performance Schema

  • mysql/psi/psi.h
  • mysql/psi/mysql_thread.h

External Dependencies

protobuf 2.6

Protobuf is composed of several components:

  • A protocol definition language
  • A generic binary wire format, with support for various datatypes including variable length encoded numeric values (varints)
  • A runtime library to encode/decode messages into its wire format
  • A compiler that translates a protocol described in the protobuf language into code that uses the runtime library to represent messages and perform validation (ex. required fields are all present, values of enum fields are in the allowed range etc)

The 1st two are not code and are rather just a specification, while the other 2 items are an actual C++ library and tool. The protobuf package source code will be included in the server source and the runtime library will be linked to the plugin.

rapidjson

For parsing/looking inside JSON objects sent by user. Code already present in server is used.

Deliverables

This worklog will result in the following deliverables:

  • xplugin.so/.dll dynamically loadable plugin for the MySQL server, which implements the client connection and request handling functionality, through a new TCP server port (33060).
  • libmysqlxtest, a static C++ library that implements the client side of the protocol, which can be used to write tests that connect to the plugin, performs authentication, sends requests and receives responses.
  • Protocol level Python test suite, a set of Python unit-tests that test the core plugin functionality by sending protocol messages to a running instance of an X enabled MySQL server and checking that the server response matches the expected. Located in tests/xplugin
  • Low-level C++/GTest unit-test suite, to test C++ routines used in the X Plugin. Located in unittest/gunit/xplugin