WL#9270: X Protocol Prepared Statement execution

Affects: Server-8.0   —   Status: Complete

Motivation

Applications that repeatedly execute the same kind of statements with changed values, currently have to go through same parse-execute cycle for every execution.

In the case of the Document Store a large part of the queries are simple _id based lookups which can be accelerated by preparing the Crud once and executing it often.

Goals

Move SQL prepare functionality to X Protocol. Give user a possibility to prepared and execute DevAPI CRUD (X Protocol CRUD).

Optional Goals

Performance boost should be visible when the client-application is going to switch from SQL/CRUD execution to prepared statement execution.

Notes

Pipelining

Prepared Statements allow to split off the parse phase from the execute into two steps and allow to repeatedly execute a statement with different values without reparsing the statement all the time.

In the classic protocol this is solved by:

C->S: COM_STMT_PREPARE(sql)
C<-S: (stmt_id, arg_count)
C->S: COM_STMT_EXECUTE(stmt_id, args, flags, ...)
C<-S: (resultset)

As the EXECUTE has to wait for the response of the PREPARE to get access to the server assigned statement-id using prepared-statements results in two round-trips.

If prepared-statements are used as a performance feature the user has decide upfront if round-trip time of PREPARE + EXECUTE is less than the time spent to send just pipeline the statements and let the server parse them again and again.

As the xprotocol supports pipelining statements already, the prepared statements MUST support pipelining too.

Design requirements

DR1
user MUST be able to prepare and execute a SQL statement
DR2
user MUST be able to prepare and execute a CRUD statement
DR3
user MUST be able to pipeline a prepare and execute statement
DR4
user MUST be able to execute a earlier prepared statement
DR5
user MUST be able to close a prepared statement
DR6
resultset of execute MUST be the same as the resultset of a directly executed statement (Sql::StmtExecute, Crud::Find, ...)
DR7
user MUST be able to fetch a part of the resultset generated by prepared-statement execution
DR8
user MUST be able to cleanup the resourced allocated prepare

Function requirements - Server

FR_S1
SQL accepted via the classic protocol for COM_STMT_PREPARE MUST be accepted by the xprotocol implementation too
FR_S2
arguments specified in the execute MUST replace wild-cards specified the prepare
FR_S3
execute MUST accept arguments
FR_S4
execute MUST return out-parameters of stored procedures
FR_S5
close MUST close a prepared statement and release its resources
FR_S6
closing a prepared-statement that has cursor assigned MUST close the cursor
FR_S7
cursor MUST be able to open/execute a prepared statement
FR_S8
cursor MUST be able to fetch all resultsets referenced by a cursor
FR_S9
cursor MUST be able to fetch a part of resultset/resultsets referenced by a cursor
FR_S10
execute after a execute with a cursor MUST implicitly close the cursor
FR_S11
preparing a statement with a statement-id already assigned MUST close the old prepared statement
FR_S12
closing X session should close all prepared-statements and cursors
FR_S13
reseting X session should close all prepared-statements and cursors
FR_S14
user should be able to use prepared-statements and cursors with expectations (for example open expect block with NO_ERROR)

error-cases

FR_SE1
executing a statement with a unassigned prepare-statement-id MUST fail
FR_SE2
closing a prepare-statement-id that's not in use MUST fail
FR_SE3
fetching from a cursor with unassigned cursor-id MUST fail
FR_SE4
closing a cursor with unassigned cursor-id MUST fail
FR_SE5
fetching from a cursor that reached end of data MUST fail
FR_SE6
preparing non SQL and non CRUD statement MUST fail

Function requirements - Client

FR_C1
libmysqlxclient MUST support prepare, execute, close prepare statement operations
FR_C2
libmysqlxclient MUST be able to fetch a subset of rows
FR_C3
libmysqlxclient MUST be able to operate on X Protocol messages directly
FR_C4
libmysqlxclient MUST be have API at session level to operate on prepared statements and cursors

Protocol

This worklog introduces two types of objects, prepared-statements and cursors. Both are managed by X Protocol messages and referred by numerical IDs. IDs are chosen by the client. Client must ensure that assigned IDs are reused only when ID is not needed or deallocated explicitly before reuse. Cursors and prepared-statement object in classic protocol are the same object, still to make X Protocol more flexible it was separated.

Prepared-statements

Multiple executions of same SQL statement is one of the expensive operations that can be optimized in classic-protocol using prepared statements. Adding prepared-statements to X Protocol gives additional gain in case of CRUD operations. When a CRUD operation is execute, X Plugin generates SQL from protobuf messages and the SQL is parsed by the server, thus the query is created and parsed each time before execution.

Mysqlx.Prepare.Prepare

Instead of generating the query and parsing it on the server side multiple times, user can do it just once and cache the result. Later on the cached query can be executed just by referring to its ID. SQL or CRUD statement can be prepared on X Protocol using following message:

message Prepare {
  required uint32 stmt_id = 1;

  // Determine which of optional fields was set by the client
  // (Workaround for missing "oneof" keyword in pb2.5)
  enum OneOfMessageType {
    FIND = 0;
    INSERT = 1;
    UPDATE = 2;
    DELETE = 4;
    STMT = 5;
  }

  message OneOfMessage {
    required OneOfMessageType type = 1 /* OneOfMessageType */;

    optional Mysqlx.Crud.Find find = 2;
    optional Mysqlx.Crud.Insert insert = 3;
    optional Mysqlx.Crud.Update update = 4;
    optional Mysqlx.Crud.Delete delete = 5;
    optional Mysqlx.Sql.StmtExecute stmt_execute = 6;
  }

  required OneOfMessage stmt = 2;

  option (client_message_id) = PREPARE_PREPARE; // comment_out_if PROTOBUF_LITE
}
  • stmt_id - ID which is going to represent the result of preparation, its value is chosen by client and its his responsibility to guarantee its uniqueness in current session. Using same value on already prepared statement deallocates old one.
  • stmt - Statement which should be prepared, currently client can prepare SQL, Crud.Insert, Crud.Update, Crud.Find, Crud.Delete.
  • stmt.type - Value defines which filed from OneOfMessage message (stmt field) is valid. Possible values are define by Prepare.OneOfMessageType.

Flow with this message looks following:

client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=...)
alt Success
  client<-server: Mysqlx.Ok
else
  client<-server: Mysqlx.Error(
    Error codes generated by the server|
    Error codes generated by X Plugin query generator)
end

When preparing SQL, the rules are the same as in classic protocol, question mark is a placeholder, which is going to be replaced by argument supplied in args of Mysqlx.Prepare.Execute message. Example of placeholders:

SELECT SLEEP(?);

X Plugin generates SQL from CRUD messages, this means that user can't use question marks like with SQL execution (StmtExecute). X Protocol already supports placeholder inside CRUD. To use those placeholders, user must specify and expression of type Mysqlx.Expr.Expr.PLACEHOLDER, with index into the args field of:

  • Mysqlx.Crud.Find, args
  • Mysqlx.Crud.Insert, args
  • Mysqlx.Crud.Update, args
  • Mysqlx.Crud.Delete, args

When preparing one of those messages, placeholders with indexes that reach outside the args field, must be translated question mark (SQL placeholder).

Those not resolved placeholder indexes must be translated in a way, that they point to array elements supplied by user in args field of Mysqlx.Prepare.Execute message.

For example, lets assume that user prepared following:

Mysqlx.Prepare.Prepare {
  stmt_id:1
  stmt {
    type: FIND
    find {
      collection {name:"coll"}

      projection { type: PLACEHOLDER postion: 3 }
      projection { type: PLACEHOLDER postion: 1 }
      projection { type: PLACEHOLDER postion: 1 }
      projection { type: PLACEHOLDER postion: 2 }
      projection { type: PLACEHOLDER postion: 0 }
      projection { type: PLACEHOLDER postion: 3 }

      args {string:"A"}
      args {string:"B"}
    }
  }
}

Which is going to generate following SQL:

SELECT ?, "B", "B", ?, "A", ? FROM coll;

X Plugin must remember the indexes that user specified for those question marks:

SELECT ?, /* placeholder_index:3 */,
      "B",
      "B",
      ?, /* placeholder_index:2 */,
      "A",
      ?, /* placeholder_index:3 */,
      FROM coll;

Missing data will be filled using binded data from field args in Mysqlx.Prepare.Execute message. X Plugin will assume that data from Execute/args are appedned to the end of Find/args. This can be interpreted other way, by referencing Execute/args directly. To do so each index must be substracted by number of elements in Find.args field:

SELECT ?, /* placeholder_index:1 */,
      "B",
      "B",
      ?, /* placeholder_index:0 */,
      "A",
      ?, /* placeholder_index:1 */,
      FROM coll;

When user will execute the prepared Find (SQL above), using following message:

message Execute {
  stmt_id: 1;

  args { string:"X"}
  args { string:"Y"}
}

X Plugin must build following array of binded arguments from the prepared statement:

["Y", "X", "Y"]

SQL allows to use placeholders inside the LIMIT statement, still X Protocol CRUD messages use following definition which doesn't allow to do that:

message Limit {
  required uint64 row_count = 1;
  optional uint64 offset = 2;
}

To workaround the problem, another message must be added as limit_expr field to following message: Mysqlx.Crud.Find, Mysqlx.Crud.Delete, Mysqlx.Crud.Update:

message LimitExpr {
  required Mysqlx.Expr.Expr row_count = 1;
  optional Mysqlx.Expr.Expr offset = 2;
}

The use of limit and limit_expr has following constraints:

  • only of those fields can be set, in case when X Plugin received two field set it must return ER_X_BAD_MESSAGE.

  • the Expr type, can describe a lot of different values. In case of limit_expr, X Plugin must only allow a Scalar/UINT value and Expr/PLACEHOLDER.

Mysqlx.Prepare.Execute

Following message is responsible for execution of already prepared SQL or CRUD statement:

message Execute {
  required uint32 stmt_id = 1;

  repeated Mysqlx.Datatypes.Any args = 2;
  optional bool compact_metadata = 3 [ default = false ];

  option (client_message_id) = PREPARE_EXECUTE; // comment_out_if PROTOBUF_LITE
}
  • stmt_id - ID representing statement prepared with Mysqlx.Prepare.Prepare.
  • args - Arguments to insert in place of placeholders defined at preparation. X Plugin must support only scalar arguments. Not all arguments must be used when executing, still all dependencies from Mysqlx.Expr.Expr.PLACEHOLDER must be satisfied.
  • compact_metadata - Defines if that only type information should be send in Mysqlx.Resultset::ColumnMetadata, skipping names.

Flow with this message looks following:

client->server: Mysqlx.Prepare.Execute(stmt_id=1, args=...)
alt Success
  client<-server: ...Resultset...
  client<-server: Mysqlx.Sql.StmtExecuteOk
else
  alt
    client<-server: ...Part of Resultset...
  end
  client<-server: Mysqlx.Error(
    ER_X_BAD_STATEMENT_ID|
    ER_X_PREPARED_EXECUTE_ARGUMENT_NOT_SUPPORTED|
    ER_X_PREPARED_EXECUTE_ARGUMENT_CONSISTENCY|
    Error codes generated by the server)
end

Error cases:

  • return ER_X_BAD_STATEMENT_ID whenstmt_id is not assigned to prepared-statement in context of current session.
  • return ER_X_PREPARED_EXECUTE_ARGUMENT_NOT_SUPPORTED when value in args field is not supported by X Plugin, only Scalar types must be supported.
  • return ER_X_PREPARED_EXECUTE_ARGUMENT_CONSISTENCY when placeholder index points outside args field boundaries.

In case when user executes a prepared Mysqlx.Curd.Insert, then all generated document ID must be returned to the client in notices.

Executing a prepared-statement that is in use by a cursor, must close the cursor:

client->server: Cursor.Open(cursor_id=1, stmt_id=10)
client<-server: Ok
client->server: Prepared.Execute(stmt_id=10)
.. resultsets ..
client<-server: StmtExecuteOk
client->server: Cursor.Fetch(cursor_id=1)
client<-server: Error (ER_X_BAD_CURSOR_ID)

Mysqlx.Prepare.Deallocate

Following message is responsible for deleting prepared statement and it marks the stmt_id as free:

message Deallocate {
  required uint32 stmt_id = 1;

  option (client_message_id) = PREPARE_DEALLOCATE; // comment_out_if PROTOBUF_LITE
}
  • stmt_id - ID representing statement prepared with Mysqlx.Prepare.Prepare.

Flow with this message look following:

client->server: Mysqlx.Prepare.Deallocate(stmt_id=1)
alt Success
  client<-server: Mysqlx.Ok
else
  client<-server: Mysqlx.Error(
    ER_X_BAD_STATEMENT_ID|
    Error codes generated by the server)
end

Error cases:

  • return ER_X_BAD_STATEMENT_ID whenstmt_id is not assigned to prepared-statement in context of current session.

Deallocating a prepared-statement that is in use by a cursor, must close then cursor:

client->server: Cursor.Open(cursor_id=1, stmt_id=10)
client<-server: Ok
client->server: Prepared.Deallocate(stmt_id=10)
client<-server: Ok
client->server: Cursor.Fetch(cursor_id=1)
client<-server: Error (ER_X_BAD_CURSOR_ID)

Cursors

Cursors were added to X Protocol, to give user possibility to fetch resultsets in small chunks using iterator like behavior.

Client application can manage cursors using:

  • create/open cursor by sending Mysqlx.Cursor.Open
  • fetch data from cursor by sending Mysqlx.Cursor.Fetch
  • close cursor by sending Mysqlx.Cursor.Close

Cursors divide the flow of resultsets on multiple parts, each part is transfered after execution of open, fetch. Information that the resultsets were broken on parts can be determined by looking at the last message of the result, which can be either FetchSuspended or FetchDone. The first fetched resultset (when opening) must contain meta-data.

Not all statements may be fetched using cursors, an example of this case is a call to "dynamic SQL" (stored procedure call). open instead of returning meta-data, it is going to return full resultset/resultsets. Further attempts to fetch such cursor are going to end with error pointing that there are no more data inside the cursor.

Mysqlx.Cursor.Open

Following message is responsible for opening a cursor for a given statement. The cursor executes the statement and instead of streaming the result to client it suspends it until client asks for more data. Currently cursor can execute only prepared statements, still the message is open for future changes. Client must provide an ID (cursor_id) which the X Plugin is going to use as cursor identification:

message Open {
  required uint32 cursor_id = 1;

  message OneOfMessage {
    enum Type {
      PREPARE_EXECUTE = 0;
    }
    required Type type = 1 /* OneOfMessageType */;

    optional Mysqlx.Prepare.Execute prepare_execute = 2;
  }

  required OneOfMessage stmt = 4;
  optional uint64 fetch_rows = 5;

  option (client_message_id) = CURSOR_OPEN; // comment_out_if PROTOBUF_LITE
}
  • cursor_id - ID which is going to represent the newly created cursor, its value is chosen by client and its his responsibility to guarantee its uniqueness in current session. If client specified already assigned ID then old cursor is going to be closed.
  • stmt - Statement which is going to be executed and its resultset is going to be streamed to client application using cursor. In case when stmt_id is already opened by other cursor, it is going to be closed and reopened in new cursor.
  • fetch_rows - defines how many rows of the resultset should be send to the client. For dynamic SQL this argument is going to be ignored and whole resultset/resultsets are going to be returned. In case when client didn't set it, its equivalent to setting it to zero (fetch nothing).

Flow with this message looks following:

client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
alt
  client<-server: Resultsets
end
alt Success
  client<-server: StmtExecuteOk
else
  client<-server: Mysqlx.Error(
    ER_X_BAD_STATEMENT_ID|
    ER_X_PREPARED_EXECUTE_ARGUMENT_NOT_SUPPORTED|
    ER_X_PREPARED_EXECUTE_ARGUMENT_CONSISTENCY|
    Error codes generated by the server)
end

Error cases:

  • return ER_X_BAD_STATEMENT_ID when stmt_id does not refer to an existing prepared statement.
  • return ER_X_PREPARED_EXECUTE_ARGUMENT_NOT_SUPPORTED when value in Cursor.stmt.prepare_execute.args field is not supported by X Plugin, only Scalar types must be supported.

The flow for opening a cursor is quite generic, there are several different scenarios that can occur:

Case 1: Cursor was created and user didn't request any data to be fetched

 client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
 client<-server: Mysqlx.Resultset.ColumnMetaData...
 client<-server: Mysqlx.Resultset.FetchSuspended
 client<-server: Mysqlx.Sql.StmtExecuteOk

User can interpret FetchSuspended messages as information that cursor is open and valid.

Case 2: Cursor was created and user requested more data than the resultset has:

client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1}, fetch_row=10)
client<-server: Mysqlx.Resultset.ColumnMetaData...
client<-server: Mysqlx.Resultset.Row
client<-server: Mysqlx.Resultset.Row
client<-server: Mysqlx.Resultset.FetchDone
client<-server: Mysqlx.Sql.StmtExecuteOk

User can interpret FetchDone messages as information that cursor is open still there are no more data in it.

Case 3: Cursor was created and user requested less data than the resultset has:

client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1}, fetch_row=1)
client<-server: Mysqlx.Resultset.ColumnMetaData...
client<-server: Mysqlx.Resultset.Row
client<-server: Mysqlx.Resultset.FetchSuspended
client<-server: Mysqlx.Sql.StmtExecuteOk

Case 4: Cursor was created, still its doesn't contains any data. Stmt was executed right away and doesn't have any resultset:

client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
client<-server: Mysqlx.Resultset.FetchDone
client<-server: Mysqlx.Sql.StmtExecuteOk

Case 5: Cursor was created, still fetching is not possible because stmt executed right away (probably because its was dynamic SQL):

client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
client<-server: Mysqlx.Resultset.ColumnMetaData...
client<-server: Mysqlx.Resultset.Row
client<-server: Mysqlx.Resultset.Row
client<-server: Mysqlx.Resultset.FetchDone
client<-server: Mysqlx.Sql.StmtExecuteOk

There are even more combinations possible, still all are in align with resultset flow.

Mysqlx.Cursor.Fetch

Following message is responsible for fetching a part or resultset from a cursor:

message Fetch {
  required uint32 cursor_id = 1;
  optional uint64 fetch_rows = 2;

  option (client_message_id) = CURSOR_FETCH; // comment_out_if PROTOBUF_LITE
}
  • cursor_id - ID which represents opened cursor
  • fetch_rows - number of rows which should be fetched from the resultset, in case when it was not set then rest of resultsets is going to be returned.

Flow with this message looks following:

client -> server: Fetch
alt Success
  ... none or partial Resultsets or full Resultsets ...
  client <- server: StmtExecuteOk
else
  client <- server: Error(ER_X_BAD_CURSOR_ID|..Error generated by server..)
end

User can check if only a part of resultset/resultsets was transfered by looking at message before StmtExecuteOk. In case when there is no data to be transfered in the cursor then the message is going to be Mysqlx.Resultset.FetchDone or Mysqlx.Resultset.FetchDoneMoreResultsets (old behavior), in other case the message is going to be Mysqlx.Resultset.FetchSuspended. Flow for transferring resultsets, looks following:

loop has more resultsets or not at end of fetch
  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 or not at end of fetch
  server --> client: FetchDoneMoreOutParams
  group resultset
    loop has more columns
      server --> client: ColumnMetaData
    end
    loop has more rows
      server --> client: Row
    end
  end
end
alt at end of all resultsets
  server --> client: FetchDone
else cursor is opened
  server --> client: FetchSuspended
end

After fetching whole resultset (reception of FetchDone), a cursor should be marked that it has not data. Thus next Mysqlx.Cursor.Fetch must fail with an error.

Error cases:

  • return ER_X_BAD_CURSOR_ID when client used cursor-id that was not opened.
  • return ER_X_CURSOR_REACHED_EOF when all data were already fetched from cursor represented by cursor-id (server send FetchDone to mark the EOF).

Mysqlx.Cursor.Close

Following message is responsible for closing the cursor, which deallocates all data associated with the cursor:

message Close {
  required uint32 cursor_id = 1;

  option (client_message_id) = CURSOR_CLOSE; // comment_out_if PROTOBUF_LITE
}
  • cursor_id - ID which represents opened cursor

Flow with this message look following:

client -> server: Close
alt Success
  client <- server: Ok
else
  client <- server: Error(ER_X_BAD_CURSOR_ID|..Error generated by server..)
end

Error cases:

  • return ER_X_BAD_CURSOR_ID when client used cursor-id that was not opened.

Stored procedure output parameters

X Protocol already defines a way of transferring stored procedures OUT and INTOUT parameters in following description:

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
alt at end of all resultsets
  server --> client: FetchDone
else cursor is opened
  server --> client: FetchSuspended
end

Where the output parameters are transfered as a last resultset with single row defining the values. The 'out-param' resultset begins after reception of FetchDoneMoreOutParams followed by ColumnMetaDatawhich defines types for the row.

Assuming that user defined following procedure:

CREATE PROCEDURE my_inc(INOUT inout_number INT)
BEGIN
    SET inout_number=inout_number + 1;
END$$

After preparing following statement:

CALL my_inc(?)

and executing it with argument set to "1", it will generate following resultset:

client->server: Mysqlx.Prepare.Execute(stmt_id=1, args=1)
server->client: Mysqlx.Resultset.FetchDoneMoreOutParams
server->client: Mysqlx.Resultset.ColumnMetaData(type=UINT)
server->client: Mysqlx.Resultset.Row(field:2)
server->client: Mysqlx.Resultset.FetchDone

Same behavior must be implemented in aspect of:

  • Mysqlx.Prepare.Execute
  • Mysqlx.Sql.StmtExecute
  • Mysqlx.Cursor.Fetch

Pipelining

Current protocol design doesn't allow to open a cursor or execute a prepared statement from Mysqlx.Prepare.Prepare message. Still user can achieve the same effect by pipelining two message, for example:

  • Mysqlx.Prepare.Prepare with Mysqlx.Prepare.Execute
  • Mysqlx.Prepare.Prepare with Mysqlx.Cursor.Open

This keeps the new protocol messages clean, by applying the "single responsibility" principle and paying small price of around additional 7 bytes (X header + X Message type + stmt_id) of network transfer.

Additionally user pipeline more X Protocol messages to block execution of Prepare.Execute message when "prepare fails", for example:

client->server: Mysqlx.Expect.Open(no_error=true)
client->server: Mysqlx.Prepare.Prepare(stmt_id=1, ...)
client->server: Mysqlx.Prepare.Execute(stmt_id=1, ...)
client->server: Mysqlx.Expect.Close()

alt successful scenraio
  server->client: Mysqlx.Ok
  server->client: ... ResultSet ...
  server->client: StmtExecuteOk
  server->client: Mysqlx.Ok
  server->client: Mysqlx.Ok
else error scenraio
  server->client: Mysqlx.Ok
  server->client: Mysqlx.Error(*prepare failed*)
  server->client: Mysqlx.Error(no-executed)
  server->client: Mysqlx.Error(no-executed)
end

Reseting

Cursors and prepared-statements are part of the session, in case when client is going to reset the session (by using Mysqlx.Session.Reset) those objects must be released.

Same applies to release of the session in case of connection closes, session is responsible for releasing cursors and prepared-statements.

Instrumentation

Status variables

  • mysqlx_prep_prepare - number of prepared-statement messages received
Property Value
Variable Name mysqlx_prep_prepare
Type LONGLONG
Scope GLOBAL/SESSION
Default 0
  • mysqlx_prep_execute - number of prepared-statement-execute messages received
Property Value
Variable Name mysqlx_prep_execute
Type LONGLONG
Scope GLOBAL/SESSION
Default 0

* mysqlx_prep_deallocate - number of prepared-statement-deallocate messages received

Property Value
Variable Name mysqlx_prep_deallocate
Type LONGLONG
Scope GLOBAL/SESSION
Default 0

* mysqlx_cursor_open - number of cursor-open messages received

Property Value
Variable Name mysqlx_cursor_open
Type LONGLONG
Scope GLOBAL/SESSION
Default 0

* mysqlx_cursor_close - number of cursor-close messages received

Property Value
Variable Name mysqlx_cursor_close
Type LONGLONG
Scope GLOBAL/SESSION
Default 0

* mysqlx_cursor_fetch - number of cursor-fetch messages received

Property Value
Variable Name mysqlx_cursor_fetch
Type LONGLONG
Scope GLOBAL/SESSION
Default 0

Performance schema

Prepared statement handling must be observable through already existing infrastructure: https://dev.mysql.com/doc/refman/8.0/en/prepared-statements-instances-table.html

This basically means that all prepared statements must have an entry in "preapared_statements_instances" table and must be updated on every successful "Prepare", "PrepareExecute", "PrepareDeallocate".

X Plugin session holds an association between prepared statement client side id (stmt_id) and server side id. The problem is that performance schema presents server ids. To be able to localize stmt_id in PS, user can create a query that uses mysql_get_prepared_statment_id UDF for translating the identifiers:

SELECT * FROM performance_schema.prepared_statments_instances
  WHERE stmt_id=mysql_get_prepared_statment_id(1 /* client side id */);

Error and Warnings

Property Value
Name ER_X_BAD_STATEMENT_ID
Error code 5110
Text Statement with ID=%i was not prepared.
Example Statement with ID=12 was not prepared.
Property Value
Name ER_X_BAD_CURSOR_ID
Error code 5111
Text Cursor with ID=%i was not opened.
Example Cursor with ID=22 was not opened.
Property Value
Name ER_X_PREPARED_EXECUTE_ARGUMENT_NOT_SUPPORTED
Error code TODO
Text Argument at index '%i' and of type '%s' is not supported
for binding to prepared statement
Property Value
Name ER_X_PREPARED_EXECUTE_ARGUMENT_CONSISTENCY
Error code TODO
Text There is no argument for statement placeholder at position: %i
Property Value
Name ER_X_CURSOR_REACHED_EOF
Error code TODO
Text No more data in cursor (cursor id:'%i')

User defined function

X Protocol introduces its own IDs for prepared-statements, to make it work with current performance-schema infrastructure, client must be able to map the ID used by X Plugin with ID shown in performance schema, using following UDF:

  • mysqlx_get_prepared_statment_id(client_side_prepared_stmt_id) - returns server side prep-stmt-id, allocated by current session or NULL when conversion is not possible.

To make server side ID generation work correctly with prepared statements the plugin must provide following function to embed it into generated SQL queries:

  • mysqlx_generate_document_id([offset INT, [increment INT, [generate_key BOOLEAN]]]) - generate unique document ID that can be used when user did provide a document without an ID. In case when offset or increment were not provided then function is going to use "1" as default value. Last argument can be used to point if the function should generate the id or not, it is useful in case when on SQL level the decision is going to be made about inserting the ID.

Mysqlx.Crud.Insert

When user put a placeholder in place of the whole document, X Plugin can't determine if document contains ID. This means X Plugin doesn't know if the ID should be inserted or not.

The decision about inserting the ID was moved to SQL level which requires that all those operations must translate the X Protocol message:

client->server: Mysqlx.Prepare.Prepare(stmt_id:1 insert{projection{placeholder:0}})

in following way:

INSERT INTO coll(doc) VALUES((SELECT JSON_INSERT(TMP1.doc, '$._id', CONVERT(
         mysqlx_generate_document_id(@@auto_increment_offset,
           @@auto_increment_increment, JSON_CONTAINS_PATH(TMP1.doc, 'one',
             '$._id')) USING utf8mb4 FROM (SELECT ? as doc) as TMP1)))

optionally it can expand to:

INSERT INTO coll(doc) VALUES(JSON_INSERT(?, '$._id', CONVERT(
         mysqlx_generate_document_id(@@auto_increment_offset, @@auto_increment_increment,
           JSON_CONTAINS_PATH(?, 'one', '$._id')) USING utf8mb4)))

where X Plugin must pass same bound argument multiple times to server.

Examples

Execute prepared statement

Statement ID can be allocated to one stmt, old one is deallocated

client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=%VALID%)
client<-server: Mysqlx.Ok
client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=%VALID%)
client<-server: Mysqlx.Ok

Statement ID can be reused after deleting old prep-stmt

client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=%VALID%)
client<-server: Mysqlx.Ok
client->server: Mysqlx.Prepare.Deallocate(stmt_id=1)
client<-server: Mysqlx.Ok
client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=%VALID%)
client<-server: Mysqlx.Ok

Preparing and executing simple SQL

client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=StmtExecute(stmt="SELECT NOW();"))
client<-server: Mysqlx.Ok
client->server: Mysqlx.Prepare.Execute(stmt_id=1)
client<-server: ...Resultsets...
client<-server: Mysqlx.Sql.StmtExecuteOk
client->server: Mysqlx.Prepare.Deallocate(stmt_id=1)
client<-server: Mysqlx.Ok

Opening same ID twice, implicitly closes old one

client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
client<-server: Mysqlx.Resultset.ColumnMetaData(...)
client<-server: Mysqlx.Resultset.FetchSuspended()
client<-server: Mysqlx.Sql.StmtExecuteOk

client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=2})
client<-server: Mysqlx.Resultset.ColumnMetaData(...)
client<-server: Mysqlx.Resultset.FetchSuspended()
client<-server: Mysqlx.Sql.StmtExecuteOk

Cursor ID can be used after closing old one

client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
client<-server: Mysqlx.Resultset.ColumnMetaData(...)
client<-server: Mysqlx.Resultset.FetchSuspended()
client<-server: Mysqlx.Sql.StmtExecuteOk
client->server: Mysqlx.Cursor.Close(cursor_id=1)
client<-server: Mysqlx.Ok
client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=2})
client<-server: Mysqlx.Resultset.ColumnMetaData(...)
client<-server: Mysqlx.Resultset.FetchSuspended()
client<-server: Mysqlx.Sql.StmtExecuteOk

Fetch '0' rows from cursor

Assuming that resultset has two columns:

client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
client<-server: Mysqlx.Resultset.ColumnMetaData(...)
client<-server: Mysqlx.Resultset.ColumnMetaData(...)
client<-server: Mysqlx.Resultset.FetchSuspended()
client<-server: Mysqlx.Sql.StmtExecuteOk

client->server: Mysqlx.Cursor.Fetch(cursor_id=1, fetch_rows=0)
client<-server: Mysqlx.Resultset.FetchSuspended()
client<-server: Mysqlx.Sql.StmtExecuteOk

Fetch all rows from cursor

Assuming that resultset has two columns:

client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
client<-server: Mysqlx.Resultset.ColumnMetaData(...)
client<-server: Mysqlx.Resultset.ColumnMetaData(...)
client<-server: Mysqlx.Resultset.FetchSuspended()
client<-server: Mysqlx.Sql.StmtExecuteOk

client->server: Mysqlx.Cursor.Fetch(cursor_id=1)
client<-server: ...Resultset rows...
client<-server: Mysqlx.Resultset.FetchDone()
client<-server: Mysqlx.Sql.StmtExecuteOk

Fetch same number of rows as there are in resultset

Assuming that resultset has two columns and two rows:

client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
client<-server: Mysqlx.Resultset.ColumnMetaData(...)
client<-server: Mysqlx.Resultset.ColumnMetaData(...)
client<-server: Mysqlx.Resultset.FetchSuspended()
client<-server: Mysqlx.Sql.StmtExecuteOk

client->server: Mysqlx.Cursor.Fetch(cursor_id=1, fetch_rows=2)
client<-server: Mysqlx.Resultset.Row(...)
client<-server: Mysqlx.Resultset.Row(...)
client<-server: Mysqlx.Resultset.FetchSuspended()
client<-server: Mysqlx.Sql.StmtExecuteOk

client->server: Mysqlx.Cursor.Fetch(cursor_id=1, fetch_rows=1)
client<-server: Mysqlx.Resultset.FetchDone()
client<-server: Mysqlx.Sql.StmtExecuteOk

Srv_service requirements

  • To enable out-params on Srv_service implementation needs to set following flag: CLIENT_PS_MULTI_RESULTS.

  • Opening a cursor for every query is not possible. In case when opening a cursor is not possible then the server streams the whole resultset to the user. X Protocols st_command_service_cbs must fail the streaming in such case.

  • On the server side relation between cursor and prepared statement is one-to-one, still X Protocol allows multiple-to-one. The limitation must be handled on X Plugin side when the client tries to open a second cursor for the prepared statement.

  • st_command_service_cbs gives the hint about position inside the cursor using following flag: SERVER_STATUS_CURSOR_EXISTS, returned through call_handle_ok. It must be used to determine if cursor is at EOR (last record was fetched) or not.

  • Server converts the Mysqlx.Prepare.Execute arguments to types expected by placeholders. No additional work needs to be done.

UDF requirements

  • UDF API doesn't pass to plugin/component current THD, thus X Plugin to identify xpl::Session must fetch THD using "current_thd" macro.


Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.