WL#7947: Service for server sessions

Affects: Server-5.7   —   Status: Complete

For daemon plugins, but not also limited to them, there is a need for a service
that will make it possible to execute SQL in a OS thread.
Functional requirements
.) open_session() should initialize a new session and return a handler for it.
   New THD should be added to the list of all threads, and visible to a user
   via SHOW PROCESSLIST. The allocation of sessions should stop and NULL value
   should be returned when number of allocated sessions and server connections
   opened by other means reaches number specified by max_connections variable.
   completion_handler should be called to indicate this error.
.) close_session() should de-initialize session. After that thread should
   disappear from SHOW PROCESSLIST output. Calling the function to
   de-initialize a session that wasn't obtained from open_session, or to 
   deinitialize a session second time would cause undefined behavior
   (a crash could be expected).
.) error_handler provided to open_session() must be a pointer to a valid
   function that matches definition of completion_handler. In case when
   invalid pointer is provided or it doesn't match the definition the server
   behavior is undefined (a crash could be expected).
.) plugin context shouldn't influence session service in any way. It should be
   passed back to plugin as is when a callback is called, e.g. completion
   callback or callbacks defined in WL#8177. 
.) a session could be opened/closed independently of other sessions.
.) opening and using a session by non-daemon plugin shouldn't influence caller
.) (requires #8177) running a command in a session shouldn't affect other
  sessions, even if such action requires detaching another session.
.) (requires #8177) running a command should automatically detach current
  session (if it differs from the one provided), attach given session if (and
  only if) it's already detached from current or another thread. If given
  session can't be attached for any reason, error should be returned and
  current session shouldn't be detached.
.) attachment and detachment (implicit or explicit) of a session which is
  already attached to another thread should fail.

Non-functional requirements
Shouldn't introduce bugs and performance regressions.
Core concept
This WL will provide means for a daemon and non-daemon plugins to open an
session allowing them running various server commands, SQL queries among them.
Overall usage schema is following:
1) In case of daemon plugin, physical thread is initialized.
2) A plugin opens a session and gets MYSQL_SESSION handler, security context of
the THD wouldn't be initialized.
3) The plugin authenticates the session, see WL#7254.
4) Run commands (WL#8177), or call any other API that requires MYSQL_SESSION,
  or obtain MYSQL_THD via API implemented in WL#8733.
5) Close session.

Same session can be used for whole lifetime of a plugin, no matter daemon or

There's two differences between daemon and non-daemon plugins in context of
this WL. First is that daemon plugins acquire physical thread outside of
server and such threads should be initialized prior to opening a session. The 
fact that physical thread is initialized can't be automatically checked, thus
plugin have to initialize thread explicitly after acquiring. Failure to do so
would cause undefined behavior.
Second differences is that non-daemon plugins are called from a server thread,
do their task and return. This dictates that when non-daemon plugin wants to
execute something in its own session, caller's context should be saved in order
to avoid damaging it. There is no server context (THD) in daemon plugin's
thread, so nothing to save.

Handling multiple sessions
In many cases one session isn't enough. This WL allows plugin to open as much
sessions as needed (but under limit set by max_connections). Sessions could be
opened and closed in any order, opening and closing sessions could be mixed.


init_thread() // no current session
s1= open_session(..) // no current session, s1 is detached
s2= open_session(..) // no current session, s2 is detached
run_command(s1) // s1 attached to thread and is current session
run_command(s2) // s2 attached to thread and is current session, s1 detached
close_session(s2) // no current session
run_command(s1) // s1 attached to thread and is current session
close_session(s1) // no current session

Session attachment
All sessions are opened in detached state. On the first use (e.g. first call
to 8177's run_command() using given session) it's attached:
1) current physical thread id (result of my_thread_self()) is saved by API
implementation in MYSQL_SESSION.
2) its id and THD is saved to thread_local storage
3) thd->store_globals() is called

Each API method that uses MYSQL_SESSION have to do the attachment procedure.
To simplify development of APIs, this WL will provide non-exported function to
be used by API implementations internally. This function would do attachment
and could look like following(in pseudo code):

  Attach session to current thread.

  @param session  Session to be attached

    true  session can't be attached
    false on success

bool attach_session(MYSQL_SESSION session)
  // Detach current session, if any
  if (current_thd && current_thd != get_thd(session))
    // Don't attach session that are already attached to another physical thread
    my_thread_handle thread= get_thread_self(session);
    if (thread && my_thread_self() != thread)
      return true;
    // Find current session by its thd
    cur_session= find_session(current_thd);
    //Detach current session

    // Mark current session as detached
    cur_session->detached= false;
  // Attach session
  if (get_thd(session)->store_globals())
    return true;
  // Mark session as attached
  set_thread_self(session, my_thread_self());
  // Handle PSI

  return false;

Support for non-daemon plugins
Non-daemon plugins always have caller's session which have to be preserved.
For better usability, this have to be done by API internally. To support that
this WL will provide two non-exported functions to be used by API
implementations internally. Functions are bool push_session(session) and
bool pop_session();
First one saves caller's thd in get_thd(session)->parent_thd and attaches
session as described above. If parent_thd is already set to a value,
error is returned.
Second one detaches current session and restores thd saved by the first
function. Since push/pop expected to be called in pairs, parent_thd asserted to
be not empty.

Thread pool
In some cases plugin might want to implement thread pool. In this case many
open sessions will be run on smaller number of physical threads. To support
that, this WL would provide a function which allows to detach a session
from a physical thread. After that, the session would be automatically attached
on the first use in any other thread. The detachment function is
pretty easy and could look like following (in pseudo code):

  Detach a session from current thread

  @param session Session to be detached

    false on success
    true  session can't be detached form this thread
bool detach_session(MYSQL_SESSION session)
  // Don't do anything to detached session
  if (session->detached)
    return false;

  // Check whether session can be detached from this thread
  if (my_thread_self() != get_thread_self(session))
    return true;

  // Detach session
  if (current_thd == get_thd(session)

  // Mark session as detached
  session->detached= true;
  return false;

This function is exported as a part of this WL's API. 

In thread pool a session could be in 3 states:
1) inactive - not occupied by a connection
2) idle - occupied by a connection, but not running a command
3) active - occupied by a connection and running a command

Initially session is created in state 1. The protocol is set to
Protocol_error. When plugin runs a command, current protocol is set to the one
provided by the plugin, and session is moved to the state 3. After finishing
execution, session is moved to the state 2 (indicated by the current command
set to COM_SLEEP). Session detach doesn't affect current protocol, it remains
set to the protocol given for the last command run. When connection is closed,
plugin should call reset_connection() method of this API to detach session,
set current protocol to Protocol_error, and discard security context (to avoid
misuse of previously authenticated session).
This func could look like following:

  Reset session

  @param session  Session to reset

    0  on success
    1  invalid session or detach failed

int reset_session(MYSQL_SESSION session)
  if (!session || detach_session(session))
    return 1;
  return 0;

Note that since this WL doesn't create the physical thread, number of
initialized thread isn't controlled in any way.


This command should show all sessions created by plugins, mo matter what type
they are (system or non-system), and in what state they are (see above).
This differs from system server threads which aren't shown. Reason is to not
let 3rd party plugins to secretly create background threads. The info field
should always indicate the it's a thread created by a plugin, e.g by prepending
"Plugin:" to the 'Info' field, similar to 'Extra' field in EXPLAIN output.
Sessions created by plugins could be distinguished from server's thread by not
using text and binary protocols.  If session isn't authenticated yet and
security context isn't available, this command should show all fields empty,
but 'State: opened' and 'Info: Plugin'. For inactive sessions, the 'Info'
field should indicate that the session is inactive, e.g 'Plugin, inactive'.
For inactive session to be shown, protocol_error should indicate that
connection is alive.

The service itself

1) Default error callback

    Callback function called by server to indicate an error

    @param ctx       Plugin's context
    @param sql_errno O - Command succeeded
                     1 - Session is being shutdown by server
                     >=1000 SQL error code (see mysqld_error.h)
    @param err_msg   Text error message if sql_errno >= 1000. NULL otherwise
  void (*error_handler)(void *ctx, uint sql_errno, const char *err_msg)

This is the callback, server would call on in case no other means of
communication with plugin is defined (e.g no protocol handler is obtained
through SQL service API).
If the plugin isn't interested in handling errors/oks, dummy function
could be provided.

struct my_session_service
     This function initializes the physical (think of pthread) thread in which
     the session will be opened. Needed only by daemon plugins.
     Thread should be initialized once at the beginning of main thread function
     and de-initialized once afterwards. Any deviation from this schema would
     cause undefined behavior.
     Call to this function in a non-daemon plugin also causes undefined

     @returns 0 on success and non-zero on failure.

    bool (*init_thread)()
      De-initializes the physical thread. If called on a non-initialized thread
      the behavior is undefined. Current session (if any) is detached prior to

      @returns 0 on success and non-zero on failure.
    void (*deinit_thread)()

      Open server session

      @param errok_cb   Default completion callback
      @param plugin_ctx Plugin's context, opaque pointer that would be
                        provided to callbacks. Might be NULL.

      @details Allocates and returns session handler - MYSQL_SESSION. The
      handler could be used for other services that require it, e.g SQL service.
        handler of session on success
        NULL on failure or max_connections limit is reached
    MYSQL_SESSION (*open_session)(error_handler *errok_cb,
                                  void * plugin_ctx);

      Close session: detach session and deallocate MYSQL_THD.
      @param thd  Session handler to close

        true  Session wasn't found
        false Session successfully closed
    bool (*close_session)(MYSQL_SESSION session);

      Detaches given session from current physical thread. 

      .) If session doesn't belong to current thread, nothing is done and error
         is returned. 
      .) If thread isn't attached, nothing is done and no error is returned.
      .) If thread is attached, current_thd and id is set to 0, performance
         schema is detached from thread. Thread marked as detached.

        true  on error
        false on success
    bool (*detach_session)(MYSQL_SESSION session);

      Reset session when client connection is closed

      @param session  Session to reset

      @notes This function returns session into initial state:
        session is detached
        session's protocol is set to protocol_error
        auth context is reset

        0  on success
        1  invalid session or detach failed

    int (*reset_session)(MYSQL_SESSION session);

In order to have a backup means of interaction with the caller plugin, this
service introduces the  Protocol_errok class, derived from Protocol.
It's a minimalistic protocol that calls the default completion handler provided
by the plugin in case of send_ok/send_error/send_eof are called. Other
Protocol's methods will generate an error (e.g "Incomplete protocol") and
return true to indicate error.
This way service using MYSQL_THD would be able to send error/ok as the result
of its work.