MySQL Bugs: #83246: Improve dba.validateInstance() error output to include my.cnf format

Bug #83246	Improve dba.validateInstance() error output to include my.cnf format
Submitted:	3 Oct 2016 13:43	Modified:	4 Apr 2018 17:47
Reporter:	Ronald Bradford	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Document Store: MySQL Shell	Severity:	S4 (Feature request)
Version:	5.7.15-labs-gr090	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When following the tutorial to setup Group Replication and perform a validation of the cluster you get an error message providing a text table of the difference in current configuration  and required configuration.

For Example:

$ mysqlsh
> dba.validateInstance('root@mysql3:3306')

...
ERROR: Error executing the 'check' command: The operation could not continue due to the following requirements not being met:
Some active options on server 'mysql3@3306' are incompatible with Group Replication.
Please restart the server 'mysql3@3306' with the updated options file and try again.
Option name                      Required Value   Current Value    Result
-------------------------------  ---------------  ---------------  -----
binlog_checksum                  NONE             CRC32            FAIL
master_info_repository           TABLE            FILE             FAIL
relay_log_info_repository        TABLE            FILE             FAIL
transaction_write_set_extraction  XXHASH64         OFF              FAIL
 at (shell):1:4
in 

How to repeat:
Follow the MySQL Group Replication Tutorial steps by @lefred at Percona Live Amsterdam.  

See slide 125 / 191

Suggested fix:
Add applicable my.cnf syntax to error output simplify the correction needed to being a cut/paste of valid my.cnf syntax, for example.

binlog_checksum = none
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction = XXHASH64

For MySQL 8.0, it should of course offer to set these settings for you :)

All are GLOBAL/DYNAMIC, so they meet the requirements for SET PERSIST:

mysql-sql> set persist binlog_checksum='none';
Query OK, 0 rows affected (0.00 sec)

mysql-sql> set persist master_info_repository='table';
Query OK, 0 rows affected (0.00 sec)

mysql-sql> set persist transaction_write_set_extraction='xxhash64';
Query OK, 0 rows affected (0.00 sec)

mysql-sql> set persist relay_log_info_repository='table';
Query OK, 0 rows affected (0.00 sec)

Hello Ronald,

Thank you for the feature request!

Thanks,
Umesh

Posted by developer:
 
The output for the checkInstanceConfiguration() function is now in JSON format and the reported issue is no longer applicable nor reproducible.

Example of the current output:

{
    "config_errors": [
        {
            "action": "restart",
            "current": "OFF",
            "option": "enforce_gtid_consistency",
            "required": "ON"
        },
        {
            "action": "restart",
            "current": "OFF",
            "option": "gtid_mode",
            "required": "ON"
        }
    ],
    "errors": [],
    "restart_required": true,
    "status": "error"
}

Posted by developer:
 
This is a feature request, the JSON output value can be used to help the user set the configurations properly.

Posted by developer:
 
dba.validateInstance() has been replaced by dba.checkInstanceConfiguration(), which presents to the user the information about which settings are invalid for InnoDB Cluster usage and the required values. Example:

Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 'tecra'

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update",
            "current": "CRC32",
            "option": "binlog_checksum",
            "required": "NONE"
        },
        {
            "action": "restart",
            "current": "OFF",
            "option": "enforce_gtid_consistency",
            "required": "ON"
        },
        {
            "action": "restart",
            "current": "OFF",
            "option": "gtid_mode",
            "required": "ON"
        },
        {
            "action": "restart",
            "current": "1",
            "option": "server_id",
            "required": "<unique ID>"
        }
    ],
    "errors": [],
    "status": "error"
}

Apart from presenting the information about the invalid settings, it is mentioned that to configure the instance for InnoDB Cluster usage, one must use dba.configureInstance():

"Please use the dba.configureInstance() command to repair these issues."

The AdminAPI provides means to configure instances for InnoDB Cluster usage without the manual intervention of the user. As so, there's absolutely no need to print the required settings in my.cnf format.