WL#9809: RESTART command

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

To enable remote management of the MySQL server through a remote SQL connection, 
it must be possible to set certain non-dynamic configuration variables which may 
require a restart of the server. 

Because requiring a manual restart of the server through a shell session in the 
host where MySQL is running would not be completely useful, it must also be 
possible to restart the server itself through a SQL connection.

Accordingly this WL shall implement  SQL command RESTART which allows a server to 
restart itself via a SQL connection.
FR1: Platform support
FR1.1: The RESTART command will be supported for *nix OSes which support systemd
FR1.2: The RESTART command will be supported on Windows when mysqld is started 
either as a windows service or standalone.
FR1.3: For *nux platforms not supporting systemd, the RESTART command will be
supported through mysqld_safe.

FR2: Installation
FR2.1: *nix which support systemd: The installer will configure systemd service
file to enable RESTART.
FR2.2: For *nix platforms not supporting systemd, there are no special
installation requirements.

FR3: The RESTART command should be logged as a force print note to the error log.

FR4: The RESTART command should have the same privilege requirements as SHUTDOWN.
     SHUTDOWN needs to be accorded to user who want to execute RESTART.

FR5: The RESTART command will shut down the server with a special exit-code, to
distinguish the shutdown from a normal shutdown.

If a restart fails after a RESTART command has been issued, the user is left to
sort out the failure.

If possible, the server should try to detect a situation where the server is
restarted in a circular fashion, and then just fail with a message to the error log.
RESTART command


The worklog introduces a RESTART sql command. The semantics of this sql command 
is that it shuts the mysql server and allows it to restart. This command is 
similar to that of the SHUTDOWN sql and is an administrative command. The 
SHUTDOWN privilege needs to be accorded to the user for the 
execution of this command.
RESTART shall be administrative DDL type of command and there would be no 
replication semantics associated with this command.

Parser Component
The parser component consists of required changes in the parser related code to 
implement the SQL interface RESTART. This component provides interaction with 
DBAs and end-users for RESTART implementation. It does syntax validation and 
invokes the core runtime executor to implement the functionality.

Runtime execution component

The runtime execution component implements the RESTART functionality. The 
implementation is different for Unix and Windows.

Unix Implementation

The restart on unix is performed by an external supervisor process. The 
supervisor processessystemd (on linux platforms) and mysqld_safe are supported 
by default for restart. Other supervisor processes can be configured for 
restart. The supervisor needs to set an environment variable MYSQLD_PARENT_PID. 
systemd and mysqld_safe are configured to set this variable accordingly. If 
mysqld is invoked without this environment, it shall fail with ER_RESTART_FAILED 
with appropriate indicating that it is not under the control of a supervisor 
The restart implementation sends a signal SIGUSR2 to the signal handler thread. 
The signal handler thread closes the listener sockets and then closes all client 
connections. Then signal thread then joins the main mysqld thread. Further 
shutdown sequence happens in this thread. For the restart case, once all 
cleanups, we exit mysqld with exit code of 16. Please note an exit code of zero 
signifies successful exit. The exit code of 16 will be used by server only for 
restart and most cases of unsuccessful exit from server via mysqld_exit has an 
exit code of 1. In some case the server uses a direct exit and return exit value 
of 3. We use the exit value 16.An assert has been added in mysqld_exit to 
enforce this.The supervisor process makes use of the special exit code to 
restart mysqld. It is up to the end user to ensure system is in a safe state 
before RESTART is invoked.

Windows Implementation
Windows service only restart on failure codes and an event is logged in event 
log indicating an failure. Hence we can't make use of the windows to configure 
for restart. We have in-built parent monitor process which shall spawn the child 
mysqld to allow for restart under windows.

In windows, when mysqld is invoked, the initial process acts as monitor process 
and it add the environment MYSQLD_PARENT_PID and then spawns the child mysqld  
with this modified environment. The monitor the waits on the process handle of 
spawned mysqld monitoring for restart and shutdown. This works when mysqld is 
started as a standalone.
For integration with windows service, in addition a named pipe shall be created 
between the monitor and mysqld. The mysqld will send service status to the 
monitor which then shall rely to the windows SCM. This is used indicate when the 
servic has started successfully and is running as well as for setting slow start 
timeout of the service in case the server does some recovery. The service 
initialization needs to be done by the initial monitor (parent) process.