Motivation

SQL that use MDL (metadata locking) and "sleep" function, periodically test a connection to see if it is alive. This functionality helps in case of long lasting queries, where its essential to stop them because they take resources like:

• Srv_session,
• memory,
• file descriptors etc.

Not releasing them might lead to denial of service.

This is already implemented for the classic-protocol.

For protocols that use the Command service (the xprotocol), the "connection_alive" check isn't performed and assumed to be true.

Example of functions which are affected by this issue:

• SELECT SLEEP(1000);
• LOCK TABLES some_table WRITE; // In case when its blocking call
• SESSION1: START TRANSACTION; SESSION1: SELECT * FROM t; ... SESSION2: INSERT INTO TABLE .... // is going to wait for end of transaction on session1

Goal

Allow SLEEP() or other waiting functions to abort quickly if the client connection is closed for users of the Command Service.

Functional requirements

F1. X Protocol user must be able to disconnect in middle of a long-lasting-query

a. server endpoint, must be able to detect disconnection and cleanup resources

b. user must be able to reconnect after such disconnection (no DoS)

General

Metadata lock framework and "sleep" function already implement periodic check that is done on MySQL Servers Protocol class. The check is implemented as connection_alive virtual method.

In case of Protocol_classic derived class, it checks the state of the connection. The problem is in implementation of Protocol_callback, which assumes that there is always an connection under it.

Implementation

The Protocol_callback must forward calls from connection_alive method to callback supplied by user (in this case X Plugin):

include/mysql/service_command.h

Extend st_command_service_cbs by:

connection_alive_t connection_alive;

With:

typedef bool (*connection_alive_t)(void *ctx);

sql/protocol_callback.cc

Change:

bool Protocol_callback::connection_alive() { return true; }

to:

bool Protocol_callback::connection_alive() {
  if (callbacks.connection_alive)
    return callbacks.connection_alive(callbacks_ctx);

  return true;
}