WL#8688: Support ability to persist SET GLOBAL settings

= High Level Overview =

Currently a number of server variables are both GLOBAL and DYNAMIC, and thus can 
be reconfigured while the server is running.  For example:

 mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

We should support being able to persist these changes through a server restart.  
For example:

 mysql> SET PERSIST sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

R1) Ability to persist any GLOBAL DYNAMIC variable to survive restarts
R2) Ability to UNSET a variable from persistent storage. This is done by
    setting the variable to DEFAULT value.
R3) Ability to LIST all variables (variable name, source, path, min/max values) 
    based on how they are set. New table performance_schema.variables_info will 
    have information about all variables.
    To list all variables set at command line run this query:
    select * from performance_schema.variables_info where variable_source like
    'COMMAND_LINE';
    To list all variables set from persistent storage:
    select * from performance_schema.variables_info where variable_source like
    'PERSISTED';
R4) Variables set in persistent storage must take precedence over 
    configuration files and command line options.
R5) It must be possible to enable/disable loading of persistent config file
    during server startup. persisted-globals-load variables will enable/disable    
    reading from persistent config file.
R6) Variables must be persisted in a MySQL config file $DATADIR/mysqld-auto.cnf
R7) $DATADIR/mysqld-auto.cnf should be the last configuration file read on
    server startup, and follow the existing rules for precedence.
R8) New Syntax:
    SET PERSIST option=value;
    SET @@persist.option=value;
R9) Multiple variables set as part of this new syntax is not allowed if 
    updation to one the variable fails. It means multiple set statement
    must be atomic.
R11)performance_schema.variables_info table provides all information like
    variable name, variable source, path, min/max values for all the
    variables (both session and global)
R12)VARIABLE_SOURCE column value should be as below:
    Variables by default should have value as 'COMPILED'.
    Variables set as part of global option files should have value as 'GLOBAL'.
    Variables set as part of global option file $MYSQL_HOME/my.cnf should have 
    value as 'SERVER'.
    Variables set as part of defaults-file option file should have value as 
    'EXPLICIT'.
    Variables set as part of defaults-extra-file option file should have value 
    as 'EXTRA'.
    Variables set as part of option file ~/.my.cnf should have value as 'USER'.
    Variables set as part of login path option file should have value as 
    'LOGIN'.
    Variables set on command line should have value as 'COMMAND_LINE'.
    Variables set as part of option file mysqld.auto.cnf should have value as 
    'PERSISTED'.
    Variables which are dynamic should have value as 'DYNAMIC'.
R13)persisted-globals-load if turned off then server should not load config file
    mysqld-auto.cnf even if present.
R14)Only successful SET statements with PERSIST option will be recorded. If
    SET statement fails then the variables are not recorded to config file.
R15)plugin variables can also be persisted. Once plugin is loaded all its  
respective persisted variables are applied on server.
R16)--no-defaults when specified, no config file (including mysqld-auto.cnf)
    must be loaded.
R17)upgrade to new version of server should go smoothly even if there exists
    persisted config files.
R18) SET PERSIST statement should not be bin logged.

New SET Syntax:
---------------
SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | [GLOBAL | SESSION | PERSIST] system_var_name = expr
    | [@@global. | @@session. | @@persist. | @@]system_var_name = expr

The new PERSIST option lets global variable name and its corresponding value
to be stored in a new config file. We will not allow a list of global variables
to be changed in one single set statement if PERSIST option is used.
Note: PERSIST will be a reserved word.

New config file:
----------------
A new config file named mysqld-auto.cnf will be created in data directory
and will be applied on server after all the global options files and
command line options. The format for this file will a JSON style format.
This new file will be created when first SET PERSIST statement is executed on
the server. If file already exists data will be overwritten. To ensure atomicity
of this file operation, we ensure that whatever needs to be written to file will
be kept ready in a buffer, then acquire global level lock do file open, write
the buffer, close and release lock. File operation will be kept very quick.
The only section supported in this config file is mysql_server. Any manual
changes to this file outside the server is not advised as the changes may 
result in error during parsing the file at server startup.

Few scenarios:
case1:
When server is started and there is no mysqld-auto.cnf file and following
statements are executed
SET PERSIST innodb_flush_log_at_timeout= 14;
SET @@persist.@@autocommit= OFF;
In this case the mysqld-auto.cnf file is created with first SET statement.
After above 2 SET statements are executed contents of this file will be
{ "mysql_server" : { "innodb_flush_log_at_timeout" : "14" ,
  "autocommit" : "0" } }
case2:
When server is started and there exists mysqld-auto.cnf file with contents as
{ "mysql_server" : { "innodb_flush_log_at_timeout" : "14" ,
  "autocommit" : "0" } }
If SET PERSIST character_set_client= "sjis"; is executed then the
contents of mysqld-auto.cnf file will be
{ "mysql_server" : { "innodb_flush_log_at_timeout" : "14" ,
  "autocommit" : "0" , "character_set_client" : "sjis" } }
case3:
INSTALL PLUGIN audit_log soname 'audit_log.so';
SET PERSIST audit_log_connection_policy=ERRORS;
UNINSTALL PLUGIN audot_log;
restart the server
During restart server will report an error saying unknown variables 
audit_log_connection_policy. This is because audit_log plugin is not loaded.

New System Variable:
--------------------
Name                : persisted-globals-load
Scope               : Global
Type                : Bool
Default             : ON
Dynamic             : No

This variable will enable/disable the option of reading mysqld-auto.cnf file.
If enabled (which is default) and mysqld-auto.cnf file exist then this file
will be read and applied last after all global config files are applied.
If this variable is disabled then mysqld-auto.cnf file will be just ignored
if present. Irrespective of this variable value, behavior of SET PERSIST
statement will remain same.

Unset variables in mysqld-auto.cnf:
-----------------------------------
In order to unset contents of already persisted variables all we need to do
is to set the variable with default values.

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 unset autocommit variable then execute this SET statement.
SET PERSIST autocommit= DEFAULT;
Now the contents of mysqld-auto.cnf will be
{ "mysql_server" : { "innodb_flush_log_at_timeout" : "14" ,
  "max_connections" : "42" } }

SET PERSIST max_connections= 151; (assuming 151 is default)
Now the contents of mysqld-auto.cnf
will be
{ "mysql_server" : { "innodb_flush_log_at_timeout" : "14" } }
This way there is no need to introduce new syntax like RESET/UNSET GLOBALS...

New table with below definition will be created

CREATE TABLE `performance_schema`.`variables_info` (
  `VARIABLE_NAME` varchar(64) NOT NULL,
  `VARIABLE_SOURCE` enum('COMPILED','GLOBAL','SERVER',
                         'EXPLICIT','EXTRA','USER','LOGIN','COMMAND_LINE',
                         'PERSISTED','DYNAMIC') DEFAULT NULL,
  `VARIABLE_PATH` varchar(1024) DEFAULT NULL,
  `MIN_VALUE` varchar(64),
  `MAX_VALUE` varchar(64)
)ENGINE=PERFORMANCE_SCHEMA;

Column VARIABLE_NAME will represent the variable name.

Column VARIABLE_SOURCE will be an enum type with permitted values
'compiled', 'global', 'server', 'explicit', 'extra', 'user', 'login',
'command line', 'persisted' and 'dynamic'. This column will be set
to any of the above enum values based on how system variables are set.
Enum values are described as follows:

'compiled' - Variables with (compiled) default value is set to COMPILED.
'global' - Variables set as part of global option files.
'server' - Variables set as part of global option file $MYSQL_HOME/my.cnf
'explicit' - Variables set as part of defaults-file option file
'extra' - Variables set as part of defaults-extra-file option file
'user' - Variables set as part of option file ~/.my.cnf
'login' - Variables set as part of login path option file.
'command line' - Variables set on command line.
'persisted' - Variables set as part of option file mysqld-auto.cnf
'dynamic' - Variables set as part of any session are set to be dynamic.

For persisted variables performance_schema.variables_info table will be 
populated based on how persisted-globals-load is set. If persisted-globals-load
is ON and if there are variables persisted then variable_source will have value
as 'PERSISTED' for persisted variables. If persisted-globals-load is OFF then 
the same persisted variables are not loaded from persistent config file, thus 
variables_info.variable_source will have value based on from where value is
loaded. If variable is read from ~/.my.cnf then variable_source is 'USER', if
set in command line then variable_source will have value as COMMAND_LINE.

ex:

SET PERSIST max_connections=47;
restart server;
mysql> select variable_source from performance_schema.variables_info where 
variable_name='max_connections';
+-----------------+
| variable_source |
+-----------------+
| PERSISTED       |
+-----------------+
1 row in set (0.00 sec)
restart server with --persisted-globals-load=OFF --max-connections=58
mysql> select variable_source from performance_schema.variables_info where 
variable_name='max_connections';
+-----------------+
| variable_source |
+-----------------+
| COMMAND_LINE    |
+-----------------+
1 row in set (0.01 sec)


Column named VARIABLE_PATH will be varchar type to store the directory path.
This column value represents location from where the variables are set.
Any global variable which is set with PERSIST option will have VARIABLE_SOURCE
value set to 'PERSISTED' and VARIABLE_PATH will have the data directory path.
To list all variables which are persistent can be done using
SHOW GLOBAL VARIABLES where VARIABLE_SOURCE='PERSISTED';
When a persistent variable is unset to a default value then VARIABLE_SOURCE
will be changed to 'DYNAMIC' and VARIABLE_PATH will be set to NULL.
Based on persisted-globals-load system variable value these column values
will be set accordingly for persisted variables. If persisted-globals-load
system variable is disabled and if mysqld-auto.cnf file exists then this file
will not be read and these columns will have values as ','dynamic',NULL,
,  for persisted variables.
Since variable paths are more of privileged information it would thus
need SELECT privilege on performance_schema.variables_info for any user who
needs access to VARIABLE_PATH.

Columns MIN_VALUE and MAX_VALUE will hold the min and max values for the
corresponding variables. Value 0 will be set in these columns for those
variables which don't have any min or max values. Min/max values does not
print any decimal values if present in any of the variables.

User should have SELECT privilege on `performance_schema`.`variables_info`
to access this table.


Few ex:

To list all global variables:
SELECT VI.VARIABLE_NAME, GV.VARIABLE_VALUE, VI.VARIABLE_SOURCE,
  VI.VARIABLE_PATH, VI.MIN_VALUE,VI.MAX_VALUE FROM variables_info AS VI,
  global_variables AS GV WHERE VI.VARIABLE_NAME = GV.VARIABLE_NAME;

To list all persisted global variables:
SELECT VI.VARIABLE_NAME, GV.VARIABLE_VALUE, VI.VARIABLE_PATH,
  VI.MIN_VALUE,VI.MAX_VALUE FROM variables_info AS VI,
  global_variables AS GV WHERE VI.VARIABLE_NAME = GV.VARIABLE_NAME and
  VI.VARIABLE_SOURCE = 'PERSISTED';

Note: If server is started with --no-defaults option then none of the defaults
file will be read including mysqld-auto.cnf.

During upgrade if there exists few persisted variables which are removed in
higher version, then before upgrade we need to unset these variables.