WL#9763: Support option to RESET PERSIST <option>

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

We have had good reception to SET PERSIST.  One of the feedback requests has been 
that removing an item from PERSIST configuration is non-intuitive.  i.e.

1. InnoDB is started up with 256M buffer pool as defined in /etc/my.cnf
2. SET PERSIST innodb_buffer_pool_size= 512 * 1024 * 1024; # set to 512M
3. SET PERSIST innodb_buffer_pool_size=DEFAULT; # the value is now 128M
4. SHUTDOWN
5. # The value is now 256M after restart

In step 3. the value was set to the compiled default for a transient duration.  
This is quite non-intuitive.  We should have an option to remove from persistent 
configuration and either use the value from (1) or (2).  (2) is easier from a 
technical standpoint, and so my proposal is to add a RESET PERSIST command that 
simply has the semantic of removing from persist configuration (thus converting 
it to have similar behavior to SET GLOBAL..):

RESET PERSIST innodb_buffer_pool_size;

User Documentation
==================

* https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html
* https://dev.mysql.com/doc/refman/8.0/en/reset-persist.html
* https://dev.mysql.com/doc/refman/8.0/en/persisted-variables-table.html
* https://dev.mysql.com/doc/refman/8.0/en/variables-info-table.html
* https://dev.mysql.com/doc/refman/8.0/en/using-system-variables.html
R1) Ability to RESET a variable from persistent storage. This is done by
    extending RESET syntax: RESET PERSIST [[IF EXISTS] <variable>];
R2) RESET PERSIST <variable>; statement will remove the variable from 
    mysqld-auto.cnf file if present. 
R3) RESET PERSIST <variable>; statement will report an error in case variable is 
    not present in persistent storage (which is mysqld-auto.cnf).
R4) RESET PERSIST IF EXISTS <variable>; statement will report a warning in case 
    variable is not present in persistent storage
R5) For plugin variables RESET PERSIST <variable>; statement will remove the
    variable from persistent config file irrespective of plugin is installed
    or not.
R6) For plugin variables RESET PERSIST <variable>; statement will report error
    if plugin variable is not present in the persistent config file.
R7) For plugin variables RESET PERSIST IF EXISTS <variable>; statement will
    report warning if plugin variable is not present in the persistent config
    file.
R8) RESET PERSIST [[IF EXISTS] <variable>]; statement will not change the value
    of variable being reset, either at global level or at session level.
R9) Since RESET PERSIST [[IF EXISTS] <variable>]; statement does not change the
    value, this statement will not affect performance_schema.variables_info.
    variable_source column. However this column will be affected if server is
    restarted.
R10)RESET PERSIST; statement will provide a way to remove everything in persist
    config file. If config file is empty warning is reported.
R11)RESET PERSIST [[IF EXISTS] <variable>]; statement should not be bin-logged.
R12)RESET PERSIST [[IF EXISTS] <variable>]; statement behavior will not change
    based on the value of persisted-globals-load variable.
R13)SET PERSIST <variable>=DEFAULT; this statement behavior will change.
    This statement will not remove variable entry from mysqld-auto.cnf, instead
    will update the variable in config file with a default value.
R14)performance_schema.persisted_variables table provides all information about
    persisted variables.
Extension to RESET Syntax:
--------------------------
RESET reset_option

reset_option:
  PERSIST [[IF EXISTS] variable];

variable:
      system_var_name

RESET PERSIST [IF EXISTS] variable; statement lets variable to be removed
from persistent config file mysqld-auto.cnf. With the introduction of new
reset option existing behavior of unsetting a persistent variable will change.
Setting a variable to DEFAULT using PERSIST keyword will now not only
change the variable value to default but also persist the default value too(
instead of removing from config file).
After resetting a persisted variable the next server restart will cause
variable coming from any other config file or command line to be effective.


Reset variables in mysqld-auto.cnf:
-----------------------------------

In order to reset contents of already persisted variables all we need to do
is to execute a RESET PERSIST statement.

For example if contents of mysqld-auto.cnf file is
{ "mysql_server" : { "innodb_flush_log_at_timeout" : "14" ,
  "autocommit" : "0" , "max_connections" : "42" } }

If we want to reset autocommit variable then execute statement:
RESET PERSIST autocommit;
Now the contents of mysqld-auto.cnf will be
{ "mysql_server" : { "innodb_flush_log_at_timeout" : "14" ,
  "max_connections" : "42" } }

RESET PERSIST max_connections;
Now the contents of mysqld-auto.cnf will be
{ "mysql_server" : { "innodb_flush_log_at_timeout" : "14" } }

If we want to reset everything in persist config file execute
RESET PERSIST;
Now the contents of mysqld-auto.cnf will be empty
{ "mysql_server" : {  } }

RESET PERSIST x;
ERROR: Variable x is not present in persist config file.

RESET PERSIST IF EXISTS x;
Query OK, 0 rows affected, 1 warning (0.01 sec)

List persisted variables:
-------------------------
Although successful execution of SET PERSIST statement is more than enough
to ensure that the variable is persisted, however to ensure that the variable
is persisted we provide a new performance_schema table named
persisted_variables.

CREATE TABLE `performance_schema`.`persisted_variables` (
  `VARIABLE_NAME` varchar(64) NOT NULL,
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`VARIABLE_NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8;

Column VARIABLE_NAME will represent the variable name.
Column VARIABLE_VALUE will represent the variable value which is persisted.