MySQL Bugs: #113191: System parameters are different from actual ones

Bug #113191	System parameters are different from actual ones
Submitted:	23 Nov 2023 2:10	Modified:	6 Dec 2023 2:06
Reporter:	mingzhong zhao	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Group Replication	Severity:	S3 (Non-critical)
Version:	mysql8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	group_replication_group_seeds、group_replication_local_address

Description:
Deploy MGR through mysqlshell. After successful deployment, query the important parameters group_replication_group_seeds of the two MGRs on the mysql client.
, group_replication_local_address, it was found that the parameter content did not match the actual value.
For example
mysql [(none)]>select @@group_replication_group_seeds;
+------------------------------------------------- --------------------------------+
| @@group_replication_group_seeds |
+------------------------------------------------- --------------------------------+
| 192.168.14.11:33069,192.168.14.12:33062,192.168.14.13:33063
#This content is the same as the content of the configuration item loose-group_replication_group_seeds in my.cnf;
And mysql [(none)]>SELECT @@group_replication_local_address;
+----------------------------------+
| @@group_replication_local_address |
+----------------------------------+
| 192.168.14.11:33061 |
+----------------------------------+
#This content shows that deploying MGR through mysqlshell will change the MGR communication port to 33061. The actual port detection is also 33061 instead of 33069 configured by my.cnf, but the select @@group_replication_group_seeds; query is still the same as the content in the configuration file. I think this is a bug, group_replication_group_seeds should be updated with group_replication_local_address

How to repeat:
Deploy mgr through mysqlshell and customize the mgr communication port of each node (non-default 33061), such as:
loose-group_replication_local_address= "192.168.14.11:33069"
loose-group_replication_group_seeds= "192.168.14.11:33069,192.168.14.12:33062,192.168.14.13:33063"
After successful deployment, you will find that the mgr port of each node is completely different from the configuration in my.cnf. At the same time, by querying select @@group_replication_group_seeds; and SELECT @@group_replication_local_address;, we will also find that it is inconsistent with the actual situation.

Suggested fix:
It is recommended that when deploying mgr through mysqlshell, give priority to the user-defined my.cnf instead of automatically modifying the user-defined port. At the same time, group_replication_group_seeds should be dynamically updated according to the actual situation, rather than inconsistent with the actual situation, which may cause greater trouble in later maintenance.

Thanks for the report.
Few changes to the report, this is not related to ndb (MySQL Cluster), this is Group Replication / InnoDB Cluster related issue.

Other thing is that this is not in any way S1 issue, not even S2.

Changing metadata to reflect proper situation before checking if I can reproduce this issue.

Hi,
I'm verifying the behavior but will have to let GR team decide if this is actually a bug or not. 

Thank you for the report

This problem is very easy to reproduce.

Deploy MGR through mysqlshell and customize the MGR communication port when configuring my.cnf, such as

Loose group_ Replication_ Local_ Address="192.168.14.11:33067"

Loose group_ Replication_ Group_ Seeds="192.168.14.11: 33067192.168.14.12: 33068192.168.14.13: 33069"

After deploying MGR with one master and two followers through MySQL shell, log in to MySQL on the main node (assuming it is 192.168.14.11), and query these two parameters at this time

Select @ @ group_ Replication_ Group_ Seeds

Select @ @ group_ Replication_ Local_ Address

You will see that the MGR port in this instance has changed to 33061 instead of the configured 33069, but the group_ Replication_ Group_ Seeds also recorded port 33069 in my.cnf.

as follows
mysql [(none)]>select @@group_replication_group_seeds;
+-----------------------------------------------------------------------------------------------------+
| @@group_replication_group_seeds                                                                     |
+-----------------------------------------------------------------------------------------------------+
| 192.168.14.11:33067,192.168.14.12:33068,192.168.14.13:33069,192.168.14.12:33061,192.168.14.13:33061 |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [(none)]>SELECT @@group_replication_local_address;
+-----------------------------------+
| @@group_replication_local_address |
+-----------------------------------+
| 192.168.14.11:33061               |
+-----------------------------------+
1 row in set (0.00 sec)

Posted by developer:
 
Not a bug.

MySQL Shell's AdminAPI provides parameters to customize the internal Group Replication configurations. For this particular scenario, the parameter `localAddress` can be used to customize the value for `group_replication_local_address`: 

  - https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.0/classmysqlsh_1_1dba_1_1_dba.html#...
  - https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.0/classmysqlsh_1_1dba_1_1_cluster.h...

When using MySQL Shell to deploy and manage MySQL architectures, no manual intervention in the target instances should be done. All must be done using the AdminAPI. Shell will ensure the settings are changed and persisted accordingly.

For those reasons, this is not considered a bug but rather a misusage of the product.

I have confirmed that deploying MGR through mysqlshell will indeed allow mysqlshell to customize communication ports. However, looking at the MGR communication port on the startup node is still ambiguous

mysql [(none)]>select @@group_replication_group_seeds;
+-----------------------------------------------------------------------------------------------------+
| @@group_replication_group_seeds                                                                     |
+-----------------------------------------------------------------------------------------------------+
| 192.168.14.13:33061,192.168.14.13:33062,192.168.14.13:33063,192.168.14.13:33261,192.168.14.13:33361 |
+-----------------------------------------------------------------------------------------------------+

But it looks normal on other nodes
mysql [(none)]>select @@group_replication_group_seeds;
+-----------------------------------------+
| @@group_replication_group_seeds         |
+-----------------------------------------+
| 192.168.14.13:33161,192.168.14.13:33361 |
+-----------------------------------------+

----------------------------------------------------
mysql [(none)]>select @@group_replication_group_seeds;
+-----------------------------------------+
| @@group_replication_group_seeds         |
+-----------------------------------------+
| 192.168.14.13:33161,192.168.14.13:33261 |
+-----------------------------------------+
Suggestion for optimization: make the boot nodes appear uniform as well

Posted by developer:
 
group_replication_group_seeds are not the communication ports: https://dev.mysql.com/doc/refman/8.0/en/group-replication-system-variables.html#sysvar_gro...

Those can be configured using MySQL Shell's AdminAPI too: https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.0/classmysqlsh_1_1dba_1_1_cluster.h...

This creates ambiguity

The document states:“group_replication_group_seeds is a list of group members to which a joining member can connect to obtain details of all the current group members.”

But when viewing the value of this parameter in the boot node of mgr:
mysql [(none)]>select @@group_replication_group_seeds;
+-----------------------------------------------------------------------------------------------------+
| @@group_replication_group_seeds                                                                     |
+-----------------------------------------------------------------------------------------------------+
| 192.168.14.13:33061,192.168.14.13:33062,192.168.14.13:33063,192.168.14.13:33261,192.168.14.13:33361 |
+-----------------------------------------------------------------------------------------------------+
You can see that the parameter value is "192.168.14.13:33061192.168.14.13:33062192.168.14.13:33063," which I set in the configuration file. The following 192.168.14.13:33261192.168.14.13:33361 is the actual value generated during MySQL shell deployment.

By listening to the server port, it can also be determined that the actual node discovery ports have been changed to 33161, 33261, and 33361 after deploying mgr through MySQL shell. Instead of 33061, 33062, and 33063 in the configuration file.

[ root@mgr3 Soft] # netstat - lnp | grep 33

Tcp6 0 0::: 3336:: * LISTEN 25437/mysqld

Tcp6 0 0::: 33261:: * LISTEN 25330/mysqld

Tcp6 0 0::: 33361:: * LISTEN 25437/mysqld

Tcp6 0 0::: 3316:: * LISTEN 26244/mysqld

Tcp6 0 0::: 3326:: * LISTEN 25330/mysqld

Tcp6 0 0::: 33060:: * LISTEN 26244/mysqld

--------------------------------------------------
So I think querying select @ @ group in the boot node_ Replication_ Group_ Seeds; It is ambiguous and should be corrected to the address+port generated by MySQL shell, rather than the address+port in the original configuration file