WL#8639: X Protocol
Status: Complete
X Protocol ========== The X Protocol focuses on: * extensibility * performance * security Extensibility includes flexibility to add capabilities for example ensuring the protocol supports things like pipelining to reduce round trips and more. Performance is achieved by ensuring that the protocol can utilize pipelining and therefore reduce the number of round-trips Security comes with using trusted, proven standards for like TLS and SASL.
Requirements
------------
The X Protocol has to allow:
F1) sending multiple commands to the server in order without
waiting for a response
F2) transmitting the information that is transmitted in the existing MySQL C/S
Protocol for non-deprecated commands
F3) optionally enabling encryption of the connection via SSL
F4) optionally enabling compression
F5) executing SQL commands
F6) executing plugin internal commands
F7) extensible authentication
F8) extending itself to add new features in a backward compatible way:
* prepared statements
* cursors
* compression
* .. and other features
Lifecycle
=========
Transport
transport layer which exchanges data: TCP sockets, Unix Sockets,
Named Pipes, TLS, ...
Connection
a lower-level connection between two Endpoints
Session
the session maintains the state. User-Variables, Temporary Tables, ...
Messages
Messages are exchanged between Endpoints. On a higher level they build
a sequence of Messages with a initial and final Message.
Endpoints
a Client or a Server
Connection
----------
A connection
* supports connection capability negotiation via
Mysqlx.Connection::CapabilitiesGet and
Mysqlx.Connection::CapabilitiesSet
* must support at least one of
* TLS Extension and SASL PLAIN authentication or
* TLS Extension and SASL EXTERNAL authentication or
* a challenge-response authentication like MySQL41 or SCRAM
Session
-------
A session owns state like:
* current schema
* current character set
* temporary tables
* user variables
* open transactions
* ...
and it is used by the server and the protocol to manage state.
Sessions are:
* opened with Mysqlx.Session::AuthenticateStart
* reset with Mysqlx.Session::Reset
* closed with Mysqlx.Session::Close
Closing a session releases all session related data.
Stages of Session Setup
-----------------------
After a client connects to the server it:
* may ask for the servers capabilities with Mysqlx.Connection::CapabilitiesGet
* may ask the server to use optional protocol features with
Mysqlx.Connection::CapabilitiesSet
* MUST authenticate
* may send commands
.. uml::
== Negotiation ==
Client -> Server: CapabilitiesGet()
Server --> Client: { "tls": 0, ... }
Client -> Server: CapabilitiesSet({"tls" : 1})
Server --> Client: Ok
== Authentication ==
Client -> Server: AuthenticateStart(mech="MYSQL41", ...)
Server --> Client: AuthenticateContinue(auth_data="...")
Client -> Server: AuthenticateContinue(auth_data="...")
Server --> Client: AuthenticateOk()
== Commands ==
...
In the **Negotiation** step the client checks which features the
server supports on the protocol side.
After a successful finish of the **Authentication** step the previous
Session is discarded and a new Session is created.
Further **Command** Messages run within a Session.
Authentication
--------------
Authentication supports several authentication mechanisms
which can be discovered with Mysqlx.Connection::CapabilitiesGet
``authentication.mechanisms``
:protobuf:msg:`Mysqlx.Connection::CapabilitiesGet`
server side supported SASL mechanism
* before TLS connection established: ``[ ]``
* after TLS connection established: ``[ "EXTERNAL", "PLAIN" ]``
required mechanisms if TLS is supported.
* EXTERNAL (X.509 client certificates) :rfc:`4422#appendix-A` (required)
* PLAIN (over SSL) :rfc:`4616` (required)
other known mechanisms
* MYSQL41 (MySQL 4.1 auth mechanism)
.. uml::
Client -> Server: AuthenticateStart()
loop
Server --> Client: AuthenticateContinue()
Client -> Server: AuthenticateContinue()
end
alt
Server --> Client: Error()
else
Server --> Client: AuthenticateOk()
end
Extensions
----------
If the result of Mysqlx.Connection::CapabilitiesGet contains a extension key
from the table below it supports the feature.
=============== ========================
name extension
=============== ========================
``tls`` `TLS extension`_
=============== ========================
.. note::
More extensions can be added in further iterations as long as:
* they are announced in CapabilitiesGet() and documented
TLS extension
.............
The client may assume that the server supports a set of features by default
and skip the Mysqlx.Connection::CapabilitiesGet step:
* if the TLS extension isn't supported the
Mysqlx.Connection::CapabilitiesSet will fail
* if it is supported, it will succeed.
.. uml::
== Negotiation ==
Client -> Server: CapabilitiesSet({"tls" : 1})
Server --> Client: Ok
note over Client, Server: TLS handshake
== Authentication ==
Client -> Server: AuthenticateStart(mech="EXTERNAL")
Server --> Client: AuthenticateOk()
== Commands ==
...
Mysqlx.Connection::CapabilitiesGet
* ``0``: supported, not in use
* ``1``: supported, in use
Mysqlx.Connection::CapabilitiesSet
* ``1``: switch to TLS connection after server-side Ok
If the server doesn't support the capability, it will return an Error.
.. note::
disabling TLS on a connection may not be supported by the
server and should result in an Error.
Pipelining
----------
The client may send several messages to the server without waiting for
a response for each message first.
Instead of waiting after each message if for the servers response:
.. uml::
c->s: Sql::StmtExecute("DO 1")
c<=s: Resultset::StmtExecuteOk()
...
c->s: Sql::StmtExecute("DO 2")
c<=s: Resultset::StmtExecuteOk()
the client can pipeline the messages and let the TCP layer combine the messages
into one TCP packet:
.. uml::
c->s: Sql::StmtExecute("DO 1")
c->s: Sql::StmtExecute("DO 2")
...
c<=s: Resultset::StmtExecuteOk()
c<=s: Resultset::StmtExecuteOk()
Message sequences must be built in a way that pipelining can be utilized.
Expectations
------------
With the use of pipelining in the Mysqlx Protocol (sending messages
without waiting for successful response) only so many messages
can be pipelined without causing havoc if one of the pipelined, dependent
messages fails.
Setting Expectations
....................
Expectations let statements fail reliably until the end of the block::
Mysqlx.Expect::Open([+no_error])
... // may fail
... // expectation(no_error) failed
Mysqlx.Expect::Close()
Expect blocks can be nested::
Mysqlx.Expect::Open([+no_error])
... // may fail
Mysqlx.Expect::Open([+no_error])
... // expectation(no_error) failed
Mysqlx.Expect::Close()
... // expectation(no_error) failed
Mysqlx.Expect::Close()
With these expectations pipelined, the server will handle errors
in a consistent, reliable way.
It also allows to express how a streaming insert would behave if one
of the inserts fails (e.g. duplicate key error, disk full, ...):
Either fail at first error::
Mysqlx.Expect::Open([+no_error])
Mysqlx.Crud::Insert(...) // ok
Mysqlx.Crud::Insert(...) // duplicate_key error
Mysqlx.Crud::Insert(...) // expectation(no_error) failed
Mysqlx.Crud::Insert(...) // expectation(no_error) failed
Mysqlx.Expect::Close()
Or ignore error and continue::
Mysqlx.Expect::Open([-no_error])
Mysqlx.Crud::Insert(...) // ok
Mysqlx.Crud::Insert(...) // duplicate_key error
Mysqlx.Crud::Insert(...) // ok
Mysqlx.Crud::Insert(...) // ok
Mysqlx.Expect::Close()
Behaviour
.........
A Expectation Block
* encloses client messages
* has a Condition Set
* has a parent Expectation Block
* can inherit a Condition Set from the parent
Expectation Block or start with a empty Condition Set
* fails if one of the Conditions fails while the Block is started or active
* fails if one of the Conditions isn't recognized or not valid
A Condition Set
* has a set of Conditions
* allows to set/unset Conditions
A Condition
* has a key and value
* key is integer
* value format depends on the key
If a Expectation Block fails, all following messages of the Expectation block
are failing with:
* error-msg: ``Expectation failed: %s``
* error-code: ...
.. todo:: define error-code and error-msg for messages in a failed block.
Conditions
..........
========================= ==========
Condition Key
========================= ==========
`no_error`_ 1
========================= ==========
no_error
........
Fail all messages of the block after the first message returning
an error.
Example::
Mysqlx.Expect::Open([+no_error])
Mysqlx.Expect::Close()
Message Sequence
----------------
Messages usually appear in a sequence. Each initial message (one referenced by
Mysqlx::ClientMessages) is associated with a set of possible following messages.
A message sequence either
* finishes successfully if it reaches its end-state or
* is aborted with a `Error Message`
At any time in between local `Notices` may be sent by the server as part of the
message sequence.
Global `Notices` may be sent by the server at any time.
Common Messages
---------------
Error Message
.............
After the client sent the initial message, the server may send a
Mysqlx::Error message at any time to terminate the current message sequence.
Notices
.......
The server may send a Mysqlx.Notice::Frame to the client at any time.
A notice can be
* global (``.scope == GLOBAL``) or
* belong to the currently executed `Message Sequence`_ (``.scope == LOCAL +
message sequence is active``):
.. note::
if the Server sends a ``LOCAL`` notice while no message sequence
is active, the Notice should be ignored.
Messages
========
Message Structure
-----------------
Messages have a
* 4 byte *length* (little endian)
* 1 byte *message type*
* a ``message_payload`` of length ``.length - 1``
.. c:type:: Mysqlx.Message
Container of all messages that are exchanged between client and server.
:param length: length of the whole message
:param message_type: type of the ``message_payload``
:param message_payload: the message's payload encoded using
`Google Protobuf `_
if not otherwise noted.
.. code-block:: c
struct {
uint32 length;
uint8 message_type;
opaque message_payload[Message.length - 1];
} Message;
Client Messages
---------------
All client Messages use a .message_type of ClientMessages.Type:
message ClientMessages {
enum Type {
CON_CAPABILITIES_GET = 1;
CON_CAPABILITIES_SET = 2;
CON_CLOSE = 3;
SESS_AUTHENTICATE_START = 4;
SESS_AUTHENTICATE_CONTINUE = 5;
SESS_RESET = 6;
SESS_CLOSE = 7;
SQL_STMT_EXECUTE = 12;
CRUD_FIND = 17;
CRUD_INSERT = 18;
CRUD_UPDATE = 19;
CRUD_DELETE = 20;
EXPECT_OPEN = 24;
EXPECT_CLOSE = 25;
}
}
Server Messages
---------------
All client Messages use a .message_type of ServerMessages.Type:
message ServerMessages {
enum Type {
OK = 0;
ERROR = 1;
CONN_CAPABILITIES = 2;
SESS_AUTHENTICATE_CONTINUE = 3;
SESS_AUTHENTICATE_OK = 4;
// NOTICE has to stay at 11 forever
NOTICE = 11;
RESULTSET_COLUMN_META_DATA = 12;
RESULTSET_ROW = 13;
RESULTSET_FETCH_DONE = 14;
RESULTSET_FETCH_SUSPENDED = 15;
RESULTSET_FETCH_DONE_MORE_RESULTSETS = 16;
SQL_STMT_EXECUTE_OK = 17;
RESULTSET_FETCH_DONE_MORE_OUT_PARAMS = 18;
};
}
Common Messages
---------------
// generic Ok message
message Ok {
optional string msg = 1;
}
// generic Error message
//
// A ``severity`` of ``ERROR`` indicates the current message sequence is
// aborted for the given error and the session is ready for more.
//
// In case of a ``FATAL`` error message the client should not expect
// the server to continue handling any further messages and should
// close the connection.
//
// :param severity: severity of the error message
// :param code: error-code
// :param sql_state: SQL state
// :param msg: human readable error message
message Error {
optional Severity severity = 1 [ default = ERROR ];
required uint32 code = 2;
required string sql_state = 4;
required string msg = 3;
enum Severity {
ERROR = 0;
FATAL = 1;
};
}
Connection
----------
// a Capability
//
// a tuple of a ``name`` and a Mysqlx.Datatypes::Any
message Capability {
required string name = 1;
required Mysqlx.Datatypes.Any value = 2;
}
// Capabilities
message Capabilities {
repeated Capability capabilities = 1;
}
// get supported connection capabilities and their current state
//
// :returns: Mysqlx.Connection::Capabilities orMysqlx::Error
//
message CapabilitiesGet {
};
// sets connection capabilities atomically
//
// only provided values are changed, other values are left unchanged.
// If any of the changes fails, all changes are discarded.
//
// :precond: active sessions == 0
// :returns: Mysqlx::Ok or Mysqlx::Error
message CapabilitiesSet {
required Capabilities capabilities = 1;
};
// announce to the server that the client wants to close the connection
//
// it discards any session state of the server
//
// :Returns: Mysqlx::Ok
message Close {
};
Sessions
--------
// Messages to manage Sessions
//
// .. uml::
//
// == session start ==
// Client -> Server: AuthenticateStart
// opt
// Server --> Client: AuthenticateContinue
// Client --> Server: AuthenticateContinue
// end
// alt
// Server --> Client: AuthenticateOk
// else
// Server --> Client: Error
// end
// ...
// == session reset ==
// Client -> Server: Reset
// Server --> Client: Ok
// == session end ==
// Client -> Server: Close
// Server --> Client: Ok
//
// the initial message send from the client to the server to start the
// authentication process
//
// :param mech_name: authentication mechanism name
// :param auth_data: authentication data
// :param initial_response: initial response
// :Returns: Mysqlx.Session::AuthenticateContinue
message AuthenticateStart {
required string mech_name = 1;
optional bytes auth_data = 2;
optional bytes initial_response = 3;
}
// send by client or server after a Mysqlx.Session::AuthenticateStart to
// exchange more auth data
//
// :Returns: Mysqlx.Session::AuthenticateContinue
message AuthenticateContinue {
required bytes auth_data = 1;
}
// sent by the server after successful authentication
message AuthenticateOk {
optional bytes auth_data = 1;
}
// reset the current session
//
// :Returns: Mysqlx::Ok
message Reset {
}
// close the current session
//
// :Returns: Mysqlx::Ok
message Close {
}
Notices
-------
// Common Frame for all Notices
//
// ===================================================== =====
// .type value
// ===================================================== =====
// :protobuf:msg:`Mysqlx.Notice::Warning` 1
// :protobuf:msg:`Mysqlx.Notice::SessionVariableChanged` 2
// :protobuf:msg:`Mysqlx.Notice::SessionStateChanged` 3
// ===================================================== =====
//
// :param type: the type of the payload
// :param payload: the payload of the notification
// :param scope: global or local notification
//
message Frame {
enum Scope {
GLOBAL = 1;
LOCAL = 2;
};
required uint32 type = 1;
optional Scope scope = 2 [ default = GLOBAL ];
optional bytes payload = 3;
}
// Server-side warnings and notes
//
// ``.scope`` == ``local``
// ``.level``, ``.code`` and ``.msg`` map the content of
//
// .. code-block:: sql
//
// SHOW WARNINGS
//
// ``.scope`` == ``global``
// (undefined) will be used for global, unstructured messages like:
//
// * server is shutting down
// * a node disconnected from group
// * schema or table dropped
//
// ========================================== =======================
// :protobuf:msg:`Mysqlx.Notice::Frame` field value
// ========================================== =======================
// ``.type`` 1
// ``.scope`` ``local`` or ``global``
// ========================================== =======================
//
// :param level: warning level: Note or Warning
// :param code: warning code
// :param msg: warning message
message Warning {
enum Level {
NOTE = 1;
WARNING = 2;
};
optional Level level = 1 [ default = WARNING ];
required uint32 code = 2;
required string msg = 3;
}
// Notify clients about changes to the current session variables
//
// Every change to a variable that is accessible through:
//
// .. code-block:: sql
//
// SHOW SESSION VARIABLES
//
// ========================================== =========
// :protobuf:msg:`Mysqlx.Notice::Frame` field value
// ========================================== =========
// ``.type`` 2
// ``.scope`` ``local``
// ========================================== =========
//
// :param namespace: namespace that param belongs to
// :param param: name of the variable
// :param value: the changed value of param
message SessionVariableChanged {
required string param = 1;
optional Mysqlx.Datatypes.Scalar value = 2;
}
// Notify clients about changes to the internal session state
//
// ========================================== =========
// :protobuf:msg:`Mysqlx.Notice::Frame` field value
// ========================================== =========
// ``.type`` 3
// ``.scope`` ``local``
// ========================================== =========
//
// :param param: parameter key
// :param value: updated value
message SessionStateChanged {
enum Parameter {
CURRENT_SCHEMA = 1;
ACCOUNT_EXPIRED = 2;
GENERATED_INSERT_ID = 3;
ROWS_AFFECTED = 4;
ROWS_FOUND = 5;
ROWS_MATCHED = 6;
TRX_COMMITTED = 7;
TRX_ROLLEDBACK = 9;
// .. more to be added
}
required Parameter param = 1;
optional Mysqlx.Datatypes.Scalar value = 2;
}
Expectations
------------
// Pipelining messages is a core feature of the Mysqlx Protocol. It
// sends messages to the server without waiting for a response to
// save latency.
//
// * in case of success the time to wait and check the result as been saved
// and the latency is reduced.
//
// * in the case of an error a mechanism is need to ensure that the following
// messages are not executed, but skipped with an error instead.
//
// This basic mechanism is extended to carry a arbitrary set of conditions
// that are checked before executing message::
//
// Mysqlx.Expect::Open([+no_error,
// +gtid_executed_contains = "...",
// +max_stmt_exec_time_ms = 10])
//
// Mysqlx.Expect::Close()
//
// Expect blocks can be nested to increase/change the restrictions
// for a subset of the messages. At the end of the Expect block the
// previous restrictions are restored.
// open an Expect block and set/unset the conditions that have to be fulfilled
//
// if any of the conditions fail, all enclosed messages will fail with
// a Mysqlx.Error message.
//
// :returns: :protobuf:msg:`Mysqlx::Ok` on success,
// :protobuf:msg:`Mysqlx::Error` on error
//
message Open {
message Condition {
enum ConditionOperation {
// set the condition
//
// set, if not set
// overwrite, if set
EXPECT_OP_SET = 0;
// unset the condition
EXPECT_OP_UNSET = 1;
};
required uint32 condition_key = 1;
optional bytes condition_value = 2;
optional ConditionOperation op = 3 [ default = EXPECT_OP_SET ];
};
enum CtxOperation {
// copy the operations from the parent Expect-block
EXPECT_CTX_COPY_PREV = 0;
// start with a empty set of operations
EXPECT_CTX_EMPTY = 1;
};
optional CtxOperation op = 1 [ default = EXPECT_CTX_COPY_PREV ];
repeated Condition cond = 2;
}
// close a Expect block
//
// closing a Expect block restores the state of the previous Expect block
// for the following messages
//
// :returns: :protobuf:msg:`Mysqlx::Ok` on success,
// :protobuf:msg:`Mysqlx::Error` on error
message Close {
}
Statement Execution
-------------------
// execute a statement in the given namespace
//
// .. uml::
//
// client -> server: StmtExecute
// ... zero or more Resultsets ...
// server --> client: StmtExecuteOk
//
// Notices:
// This message may generate a notice containing WARNINGs
// generated by its execution.
// This message may generate a notice containing INFO messages generated by
// its execution.
//
// :param namespace: namespace of the statement to be executed
// :param stmt: statement that shall be executed.
// :param args: values for wildcard replacements
// :param compact_metadata: send only type information for
// :protobuf:msg:`Mysqlx.Resultset::ColumnMetadata`,
// skipping names and others
// :returns:
// * zero or one :protobuf:msg:`Mysqlx.Resultset::` followed
// by :protobuf:msg:`Mysqlx.Sql::StmtExecuteOk`
message StmtExecute {
optional string namespace = 3 [ default = "sql" ];
required bytes stmt = 1;
repeated Mysqlx.Datatypes.Any args = 2;
optional bool compact_metadata = 4 [ default = false ];
}
// statement executed successful
message StmtExecuteOk {
}
Resultsets
----------
// Executing a statement against the server may result in zero or more
// Resultsets followed by zero or one Resultset of the ``OUT`` parameters.
//
// A Resultset consists of:
//
// * one or more :protobuf:msg:`Mysqlx.Resultset::ColumnMetaData`
// * zero or more :protobuf:msg:`Mysqlx.Resultset::Row`
//
// It is followed by:
//
// * a :protobuf:msg:`Mysqlx.Resultset::FetchDoneMoreResultsets` if more
// resultsets are following
// * a :protobuf:msg:`Mysqlx.Resultset::FetchDoneMoreOutParams` if more
// Resultset of ``OUT`` parameters is following
// * a :protobuf:msg:`Mysqlx.Resultset::FetchDone` if the last resultset
// was sent
//
// .. uml::
//
// ...
// loop has more resultsets
// group resultset
// loop has more columns
// server --> client: ColumnMetaData
// end
// loop has more rows
// server --> client: Row
// end
// end
// alt has more resultsets
// server --> client: FetchDoneMoreResultsets
// end
// end
// loop has more OUT-paramsets
// server --> client: FetchDoneMoreOutParams
// group resultset
// loop has more columns
// server --> client: ColumnMetaData
// end
// loop has more rows
// server --> client: Row
// end
// end
// end
// server --> client: FetchDone
// ...
//
// Examples
// ````````
//
// .. rubric:: No Resultset
//
// A ``INSERT`` statement usually doesn't send any resultset which results
// in only a ``FetchDone``.
//
// .. uml::
//
// server --> client: FetchDone
//
// .. rubric:: Empty Resultset
//
// ``SELECT 1 LIMIT 0`` results in a empty resultset:
//
// .. uml::
//
// server --> client: ColumnMetaData(.name = "1", .type = INT)
// server --> client: FetchDone
//
// .. rubric:: Multi Resultset
//
// ``CALL`` may result in multiple resultsets.
//
// .. uml::
//
// server --> client: ColumnMetaData(.name = "1", .type = INT)
// server --> client: Row
// server --> client: FetchDoneMoreResultsets
// server --> client: ColumnMetaData(.name = "1", .type = INT)
// server --> client: Row
// server --> client: FetchDone
//
// .. rubric:: OUT params
//
// ``CALL`` may result OUT parameters only
//
// .. uml::
//
// server --> client: FetchDoneMoreOutParams
// server --> client: ColumnMetaData(.name = "1", .type = INT)
// server --> client: Row
// server --> client: FetchDone
// resultsets are finished, OUT paramset is next
message FetchDoneMoreOutParams {
}
// resultset and out-params are finished, but more resultsets available
message FetchDoneMoreResultsets {
}
// all resultsets are finished
message FetchDone {
}
// meta data of a Column
//
// .. note:: the encoding used for the different ``bytes`` fields in the meta
// data is externally controlled.
// .. seealso:: https://dev.mysql.com/doc/refman/5.0/en/charset-
connection.html
//
// .. note::
// The server may not set the ``original_{table|name}`` fields if they are
// equal to the plain ``{table|name}`` field.
//
// A client has to reconstruct it like::
//
// if .original_name is empty and .name is not empty:
// .original_name = .name
//
// if .original_table is empty and .table is not empty:
// .original_table = .table
//
// .. note::
// ``compact metadata format`` can be requested by the client. In that case
// only ``.type`` is set and all other fields are empty.
//
// :param type:
// .. table:: Expected Datatype of Mysqlx.Resultset.Row per SQL Type for non
NULL values
//
// ================= ============ ======= ========== ====== ========
// SQL Type .type .length .frac_dig .flags .charset
// ================= ============ ======= ========== ====== ========
// TINY SINT x
// TINY UNSIGNED UINT x x
// SHORT SINT x
// SHORT UNSIGNED UINT x x
// INT24 SINT x
// INT24 UNSIGNED UINT x x
// INT SINT x
// INT UNSIGNED UINT x x
// LONGLONG SINT x
// LONGLONG UNSIGNED UINT x x
// DOUBLE DOUBLE x x x
// FLOAT FLOAT x x x
// DECIMAL DECIMAL x x x
// VARCHAR,CHAR,... BYTES x x x
// GEOMETRY BYTES
// TIME TIME x
// DATE DATETIME x
// DATETIME DATETIME x
// YEAR UINT x x
// TIMESTAMP DATETIME x
// SET SET x
// ENUM ENUM x
// NULL BYTES
// BIT BIT x
// ================= ============ ======= ========== ====== ========
//
// .. note:: the SQL "NULL" value is sent as an empty field value in
:protobuf:msg:`Mysqlx.Resultset::Row`
// .. seealso:: protobuf encoding of primitive datatypes are decribed in
https://developers.google.com/protocol-buffers/docs/encoding
// SINT
//
// ``.length``
// maximum number of displayable decimal digits (including minus sign) of
the type
//
// .. note::
// valid range is 0-255, but usually you'll see 1-20
//
// =============== ==
// SQL Type max digits per type
// =============== ==
// TINY SIGNED 4
// SHORT SIGNED 6
// INT24 SIGNED 8
// INT SIGNED 11
// LONGLONG SIGNED 20
// =============== ==
//
// .. seealso:: definition of ``M`` in
https://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html
//
// ``value``
// variable length encoded signed 64 integer
//
// UINT
//
// ``.flags & 1`` (zerofill)
// the client has to left pad with 0's up to .length
//
// ``.length``
// maximum number of displayable decimal digits of the type
//
// .. note::
// valid range is 0-255, but usually you'll see 1-20
//
// ================= ==
// SQL Type max digits per type
// ================= ==
// TINY UNSIGNED 3
// SHORT UNSIGNED 5
// INT24 UNSIGNED 8
// INT UNSIGNED 10
// LONGLONG UNSIGNED 20
// ================= ==
//
// .. seealso:: definition of ``M`` in
https://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html
//
// ``value``
// variable length encoded unsigned 64 integer
// BIT
//
// ``.length``
// maximum number of displayable binary digits
//
// .. note:: valid range for M of the ``BIT`` type is 1 - 64
// .. seealso:: https://dev.mysql.com/doc/refman/5.5/en/numeric-type-
overview.html
//
// ``value``
// variable length encoded unsigned 64 integer
//
// DOUBLE
//
// ``.length``
// maximum number of displayable decimal digits (including the decimal
point and ``.fractional_digits``)
//
// ``.fractional_digits``
// maximum number of displayable decimal digits following the decimal
point
//
// ``value``
// encoded as Protobuf's 'double'
//
// FLOAT
//
// ``.length``
// maximum number of displayable decimal digits (including the decimal
point and ``.fractional_digits``)
//
// ``.fractional_digits``
// maximum number of displayable decimal digits following the decimal
point
//
// ``value``
// encoded as Protobuf's 'float'
// BYTES, ENUM
// BYTES is used for all opaque byte strings that may have a charset
//
// * TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
// * TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
// * VARCHAR, VARBINARY
// * CHAR, BINARY
// * ENUM
//
// ``.length``
// the maximum length of characters of the underlying type
//
// ``.flags & 1`` (rightpad)
// if the length of the field is less than ``.length``, the receiver is
// supposed to add padding characters to the right end of the string.
// If the ``.charset`` is "binary", the padding character is ``0x00``,
// otherwise it is a space character as defined by that character set.
//
// ============= ======= ======== =======
// SQL Type .length .charset .flags
// ============= ======= ======== =======
// TINYBLOB 256 binary
// BLOB 65535 binary
// VARCHAR(32) 32 utf8
// VARBINARY(32) 32 utf8_bin
// BINARY(32) 32 binary rightpad
// CHAR(32) 32 utf8 rightpad
// ============= ======= ======== =======
//
// ``value``
// sequence of bytes with added one extra '\0' byte at the end. To obtain
the
// original string, the extra '\0' should be removed.
// .. note:: the length of the string can be acquired with protobuf's
field length() method
// length of sequence-of-bytes = length-of-field - 1
// .. note:: the extra byte allows to distinguish between a NULL and empty
byte sequence
// TIME
// A time value.
//
// ``value``
// the following bytes sequence:
//
// ``| negate [ | hour | [ | minutes | [ | seconds | [ | useconds |
]]]]``
//
// * negate - one byte, should be one of: 0x00 for "+", 0x01 for "-"
// * hour - optional variable length encoded unsigned64 value for the hour
// * minutes - optional variable length encoded unsigned64 value for the
minutes
// * seconds - optional variable length encoded unsigned64 value for the
seconds
// * useconds - optional variable length encoded unsigned64 value for the
microseconds
//
// .. seealso:: protobuf encoding in
https://developers.google.com/protocol-buffers/docs/encoding
// .. note:: hour, minutes, seconds, useconds are optional if all the
values to the right are 0
//
// Example: 0x00 -> +00:00:00.000000
//
// DATETIME
// A date or date and time value.
//
// ``value``
// a sequence of variants, arranged as follows:
//
// ``| year | month | day | [ | hour | [ | minutes | [ | seconds | [ |
useconds | ]]]]``
//
// * year - variable length encoded unsigned64 value for the year
// * month - variable length encoded unsigned64 value for the month
// * day - variable length encoded unsigned64 value for the day
// * hour - optional variable length encoded unsigned64 value for the hour
// * minutes - optional variable length encoded unsigned64 value for the
minutes
// * seconds - optional variable length encoded unsigned64 value for the
seconds
// * useconds - optional variable length encoded unsigned64 value for the
microseconds
//
// .. note:: hour, minutes, seconds, useconds are optional if all the
values to the right are 0
//
// ``.flags & 1`` (timestamp)
//
// ============= =======
// SQL Type .flags
// ============= =======
// DATETIME
// TIMESTAMP 1
// ============= =======
// DECIMAL
// An arbitrary length number. The number is encoded as a single byte
// indicating the position of the decimal point followed by the Packed BCD
// encoded number. Packed BCD is used to simplify conversion to and
// from strings and other native arbitrary precision math datatypes.
// .. seealso:: packed BCD in https://en.wikipedia.org/wiki/Binary-
coded_decimal
//
// ``.length``
// maximum number of displayable decimal digits (*excluding* the decimal
point and sign, but including ``.fractional_digits``)
//
// .. note:: should be in the range of 1 - 65
//
// ``.fractional_digits``
// is the decimal digits to display out of length
//
// .. note:: should be in the range of 0 - 30
//
// ``value``
// the following bytes sequence:
//
// ``| scale | BCD | sign | [0x0] |``
//
// * scale - 8bit scale value (number of decimal digit after the '.')
// * BCD - BCD encoded digits (4 bits for each digit)
// * sign - sign encoded on 4 bits (0xc = "+", 0xd = "-")
// * 0x0 - last 4bits if length(digits) % 2 == 0
//
// Example: x04 0x12 0x34 0x01 0xd0 -> -12.3401
//
// SET
// A list of strings representing a SET of values.
//
// ``value``
// A sequence of 0 or more of protobuf's bytes (length prepended octets)
or one of
// the special sequences with a predefined meaning listed below.
//
// Example (length of the bytes array shown in brackets):
// * ``[0]`` - the NULL value
// * ``[1] 0x00`` - a set containing a blank string ''
// * ``[1] 0x01`` - this would be an invalid value, but is to be treated
as the empty set
// * ``[2] 0x01 0x00`` - a set with a single item, which is the '\0'
character
// * ``[8] 0x03 F O O 0x03 B A R`` - a set with 2 items: FOO,BAR
//
// :param name: name of the column
// :param original_name: name of the column before an alias was applied
// :param table: name of the table the column orginates from
// :param original_table: name of the table the column orginates from before an
alias was applied
// :param schema: schema the column originates from
// :param catalog:
// catalog the schema originates from
//
// .. note::
// as there is current no support for catalogs in MySQL, don't expect this
field to be set.
// In the MySQL C/S protocol the field had the value ``def`` all the time.
//
// :param fractional_digits: displayed factional decimal digits for floating
point and fixed point numbers
// :param length: maximum count of displayable characters of .type
// :param flags:
// ``.type`` specific flags
//
// ======= ====== ===========
// type value description
// ======= ====== ===========
// UINT 0x0001 zerofill
// DOUBLE 0x0001 unsigned
// FLOAT 0x0001 unsigned
// DECIMAL 0x0001 unsigned
// BYTES 0x0001 rightpad
// ======= ====== ===========
//
// ====== ================
// value description
// ====== ================
// 0x0010 NOT_NULL
// 0x0020 PRIMARY_KEY
// 0x0040 UNIQUE_KEY
// 0x0080 MULTIPLE_KEY
// 0x0100 AUTO_INCREMENT
// ====== ================
//
// default: 0
// :param content_type:
// a hint about the higher-level encoding of a BYTES field
//
// ====== ====== ===========
// type value description
// ====== ====== ===========
// BYTES 0x0001 GEOMETRY (WKB encoding)
// BYTES 0x0002 JSON (text encoding)
// BYTES 0x0003 XML (text encoding)
// ====== ====== ===========
//
// .. note::
// this list isn't comprehensive. As guideline: the field's value is
expected
// to pass a validator check on client and server if this field is set.
// If the server adds more internal datatypes that rely on BLOB storage
// like image manipulation, seeking into complex types in BLOBs, ... more
// types will be added.
//
message ColumnMetaData {
enum FieldType {
SINT = 1;
UINT = 2;
DOUBLE = 5;
FLOAT = 6;
BYTES = 7;
TIME = 10;
DATETIME = 12;
SET = 15;
ENUM = 16;
BIT = 17;
DECIMAL = 18;
}
// datatype of the field in a row
required FieldType type = 1;
optional bytes name = 2;
optional bytes original_name = 3;
optional bytes table = 4;
optional bytes original_table = 5;
optional bytes schema = 6;
optional bytes catalog = 7;
optional uint64 collation = 8;
optional uint32 fractional_digits = 9;
optional uint32 length = 10;
optional uint32 flags = 11;
optional uint32 content_type = 12;
}
// Row in a Resultset
//
// a row is a list of fields encoded as byte sequences.
// The type of the fields is provided by the ColumnMetadata and
// can be used to determine the appropriate message to use to
// decode the contents of the field (one of the RowField* messages)
//
// If a field length is 0, it is to be interpreted as NULL,
// otherwise the value can be extracted by protobuf decoding the bytearray.
message Row {
repeated bytes field = 1;
}
CRUD messages
-------------
// column definition
message Column {
optional string name = 1;
optional string alias = 2;
repeated Mysqlx.Expr.DocumentPathItem document_path = 3;
}
// a projection
//
// :param source: the expression identifying an element from the source data
// which can include a column identifier or any expression
// :param alias: optional alias
message Projection {
required Mysqlx.Expr.Expr source = 1;
optional string alias = 2;
}
// DataModel to use for filters, names, ...
enum DataModel {
DOCUMENT = 1;
TABLE = 2;
};
// collection
message Collection {
required string name = 1;
optional string schema = 2;
}
// limit
//
// :param row_count: maximum rows to filter
// :param offset: maximum rows to skip before applying the row_count
message Limit {
required uint64 row_count = 1;
optional uint64 offset = 2;
}
// sort order
message Order {
enum Direction {
ASC = 1;
DESC = 2;
};
required Mysqlx.Expr.Expr expr = 1;
optional Direction direction = 2 [ default=ASC ];
}
// update operations
//
// :param source: specification of the value to be updated
// if data_model is TABLE, a column name may be specified and also a
document path, if the column has type JSON
// if data_model is DOCUMENT, only document paths are allowed
// in both cases, schema and table must be not set
// :param operation: the type of operation to be performed
// :param value: an expression to be computed as the new value for the operation
message UpdateOperation {
enum UpdateType {
SET = 1; // only allowed for TABLE
ITEM_REMOVE = 2; // no value (removes the identified path from a object
or array)
ITEM_SET = 3; // sets the new value on the identified path
ITEM_REPLACE = 4; // replaces a value if the path exists
ITEM_MERGE = 5; // source and value must be documents
ARRAY_INSERT = 6; // insert the value in the array at the index identified
in the source path
ARRAY_APPEND = 7; // append the value on the array at the identified path
}
required Mysqlx.Expr.ColumnIdentifier source = 1;
required UpdateType operation = 2;
optional Mysqlx.Expr.Expr value = 3;
}
// Find Documents/Rows in a Collection/Table
//
// .. uml::
//
// client -> server: Find
// ... one or more Resultset ...
//
// :param collection: collection to insert into
// :param data_model: datamodel that the operations refer to
// :param projection: list of column projections that shall be returned
// :param criteria: filter criteria
// :param limit: numbers of rows that shall be skipped and returned
// :param order: sort-order in which the rows/document shall be returned in
// :param grouping: column expression list for aggregation (GROUP BY)
// :param grouping_criteria: filter criteria for aggregated groups
// :Returns: :protobuf:msg:`Mysqlx.Resultset::`
message Find {
required Collection collection = 2;
optional DataModel data_model = 3;
repeated Projection projection = 4;
optional Mysqlx.Expr.Expr criteria = 5;
optional Limit limit = 6;
repeated Order order = 7;
repeated Mysqlx.Expr.Expr grouping = 8;
optional Mysqlx.Expr.Expr grouping_criteria = 9;
};
// Insert documents/rows into a collection/table
//
// :param collection: collection to insert into
// :param data_model: datamodel that the operations refer to
// :param projection: name of the columns to insert data into
// :param row: set of rows to insert into the collection/table
// :param args: values for parameters used in row expressions
// :Returns: :protobuf:msg:`Mysqlx.Resultset::`
message Insert {
required Collection collection = 1;
optional DataModel data_model = 2;
repeated Column projection = 3;
message TypedRow {
repeated Mysqlx.Datatypes.Any field = 1;
};
repeated TypedRow row = 4;
repeated Mysqlx.Datatypes.Scalar args = 5;
};
// Update documents/rows in a collection/table
//
// :param collection: collection to change
// :param data_model: datamodel that the operations refer to
// :param criteria: filter expression to match rows that the operations will
apply on
// :param args: values for parameters used in filter expression
// :param limit: limits the number of rows to match
// :param order: specifies order of matched rows
// :param operation: list of operations to be applied. Valid operations will
depend on the data_model.
// :Returns: :protobuf:msg:`Mysqlx.Resultset::`
message Update {
required Collection collection = 2;
optional DataModel data_model = 3;
optional Mysqlx.Expr.Expr criteria = 4;
repeated Mysqlx.Datatypes.Scalar args = 8;
optional Limit limit = 5;
repeated Order order = 6;
repeated UpdateOperation operation = 7;
};
// Delete documents/rows from a Collection/Table
//
// :param collection: collection to change
// :param data_model: datamodel that the operations refer to
// :param criteria: filter expression to match rows that the operations will
apply on
// :param limit: limits the number of rows to match
// :param order: specifies order of matched rows
// :param args: values for parameters used in filter expression
// :Returns: :protobuf:msg:`Mysqlx.Resultset::`
message Delete {
required Collection collection = 1;
optional DataModel data_model = 2;
optional Mysqlx.Expr.Expr criteria = 3;
repeated Mysqlx.Datatypes.Scalar args = 6;
optional Limit limit = 4;
repeated Order order = 5;
};
Expressions
-----------
// Expressions
//
// the "root" of the expression tree
//
// .. productionlist::
// expr: `operator` |
// : `identifier` |
// : `function_call` |
// : variable |
// : `literal` |
// : placeholder
//
// In certain commands, `placeholder` are substituded with a value from the row.
// given in `ExecuteStmt`, in order of appearance.
//
message Expr {
enum Type {
IDENT = 1;
LITERAL = 2;
VARIABLE = 3;
FUNC_CALL = 4;
OPERATOR = 5;
PLACEHOLDER = 6;
OBJECT = 7;
ARRAY = 8;
};
required Type type = 1;
optional ColumnIdentifier identifier = 2;
optional string variable = 3;
optional Mysqlx.Datatypes.Any constant = 4;
optional FunctionCall function_call = 5;
optional Operator operator = 6;
optional uint32 position = 7;
optional Object object = 8;
optional Array array = 9;
}
// identifier: name, schame.name
//
// .. productionlist::
// identifier: string "." string |
// : string
message Identifier {
required string name = 1;
optional string schema_name = 2;
}
// DocumentPathItem
//
// .. productionlist::
// document_path: path_item | path_item document_path
// path_item : member | array_index | "**"
// member : "." string | "." "*"
// array_index : "[" number "]" | "[" "*" "]"
//
message DocumentPathItem {
enum Type {
MEMBER = 1; // .member
MEMBER_ASTERISK = 2; // .*
ARRAY_INDEX = 3; // [index]
ARRAY_INDEX_ASTERISK = 4; // [*]
DOUBLE_ASTERISK = 5; // **
};
required Type type = 1;
optional string value = 2;
optional uint32 index = 3;
}
// col_identifier (table): col@doc_path, tbl.col@doc_path col, tbl.col,
schema.tbl.col
// col_identifier (document): doc_path
//
// .. productionlist::
// col_identifier: string "." string "." string |
// : string "." string |
// : string |
// : string "." string "." string "@" document_path |
// : string "." string "@" document_path |
// : string "@" document_path |
// : document_path
// document_path: member | arrayLocation | doubleAsterisk
// member = "." string | "." "*"
// arrayLocation = "[" index "]" | "[" "*" "]"
// doubleAsterisk = "**"
//
message ColumnIdentifier {
repeated Mysqlx.Expr.DocumentPathItem document_path = 1;
optional string name = 2;
optional string table_name = 3;
optional string schema_name = 4;
}
// function call: ``func(a, b, "1", 3)``
//
// .. productionlist::
// function_call: `identifier` "(" [ `expr` ["," `expr` ]* ] ")"
message FunctionCall {
required Identifier name = 1;
repeated Expr param = 2;
}
// operator: ``<<(a, b)``
//
// .. note::
//
// Non-authoritative list of operators implemented (case sensitive):
//
// Unary
// * ``!``
// * ``sign_plus``
// * ``sign_minus``
// * ``~``
//
// Binary
// * ``&&``
// * ``||``
// * ``xor``
// * ``==``
// * ``!=``
// * ``>``
// * ``>=``
// * ``<``
// * ``<=``
// * ``&``
// * ``|``
// * ``^``
// * ``<<``
// * ``>>``
// * ``+``
// * ``-``
// * ``*``
// * ``/``
// * ``div``
// * ``%``
// * ``is``
// * ``is_not``
// * ``regexp``
// * ``not_regexp``
// * ``like``
// * ``not_like``
//
// Using special representation, with more than 2 params
// * ``in`` (param[0] IN (param[1], param[2], ...))
// * ``not_in`` (param[0] NOT IN (param[1], param[2], ...))
//
// Ternary
// * ``between``
// * ``between_not``
// * ``date_add``
// * ``date_sub``
//
// Units for date_add/date_sub
// * ``MICROSECOND``
// * ``SECOND``
// * ``MINUTE``
// * ``HOUR``
// * ``DAY``
// * ``WEEK``
// * ``MONTH``
// * ``QUARTER``
// * ``YEAR``
// * ``SECOND_MICROSECOND``
// * ``MINUTE_MICROSECOND``
// * ``MINUTE_SECOND``
// * ``HOUR_MICROSECOND``
// * ``HOUR_SECOND``
// * ``HOUR_MINUTE``
// * ``DAY_MICROSECOND``
// * ``DAY_SECOND``
// * ``DAY_MINUTE``
// * ``DAY_HOUR``
//
// .. productionlist::
// operator: `name` "(" [ `expr` ["," `expr` ]* ] ")"
message Operator {
required string name = 1;
repeated Expr param = 2;
}
Datatypes
---------
// a scalar
message Scalar {
// a string with a charset/collation
message String {
required bytes value = 1;
optional uint64 collation = 2;
};
enum Type {
V_SINT = 1;
V_UINT = 2;
V_NULL = 3;
V_OCTETS = 4;
V_DOUBLE = 5;
V_FLOAT = 6;
V_BOOL = 7;
V_STRING = 8;
};
required Type type = 1;
optional sint64 v_signed_int = 2;
optional uint64 v_unsigned_int = 3;
// 4 is unused, was Null which doesn't have a storage anymore
optional bytes v_opaque = 5;
optional double v_double = 6;
optional float v_float = 7;
optional bool v_bool = 8;
optional String v_string = 9;
}
// a object
message Object {
message ObjectField {
required string key = 1;
required Any value = 2;
}
repeated ObjectField fld = 1;
}
// a Array
message Array {
repeated Any value = 1;
}
// a helper to allow all field types
message Any {
enum Type {
SCALAR = 1;
OBJECT = 2;
ARRAY = 3;
};
required Type type = 1;
optional Scalar scalar = 2;
optional Object obj = 3;
optional Array array = 4;
}
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.