Bug #85567 cluster.rejoinInstance() doesn't use the user in the connection string parameter
Submitted: 21 Mar 2017 13:25 Modified: 12 Jul 2017 12:29
Reporter: Frederic Descamps Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Document Store: MySQL Shell Severity:S3 (Non-critical)
Version:1.0.8-rc OS:Linux
Assigned to: CPU Architecture:Any
Tags: cluster, mysqlsh

[21 Mar 2017 13:25] Frederic Descamps
Description:
When rejoinInstance() function is used on a cluster type variable, the user in the connection string is ignored and 'root' is used.

How to repeat:
Create a MySQL InnoDB Cluster of 3 nodes but use a dedicate user (in my case fred@% with password fred).
Then just stop group_replication on one node (I used the primary master):

mysql-js> cluster.status()
{
    "clusterName": "MyCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.90.4:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "192.168.90.2:3306": {
                "address": "192.168.90.2:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "192.168.90.3:3306": {
                "address": "192.168.90.3:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.90.4:3306": {
                "address": "192.168.90.4:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

now try to add the node again:

mysql-js> cluster.checkInstanceState(i1)
Please provide the password for 'fred@192.168.90.2:3306': 
Analyzing the instance replication state...

The instance '192.168.90.2:3306' is valid for the cluster.
The instance is fully recoverable.

{
    "reason": "recoverable", 
    "state": "ok"
}
mysql-js> cluster.rejoinInstance(i1)
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.

Please provide the password for 'fred@192.168.90.2:3306': 
Rejoining instance to the cluster ...

Cluster.rejoinInstance: Access denied for user 'root'@'mysql1' (using password: YES) (MySQL Error 1045)

Suggested fix:
The node should be able to rejoin.

it might be related to https://bugs.mysql.com/bug.php?id=83344
[22 Mar 2017 12:48] Umesh Shastry
Hello Frederic,

Thank you for the report and feedback!
I spent quite a sometime trying to reproduce reported issue but not seeing the issue which you have reported. Could you please confirm my steps(shortly joining to activity log to the bug report), and may be share the exact steps you have tried?  Thanks..

Thanks,
Umesh
[22 Mar 2017 12:49] Umesh Shastry
test results

Attachment: 85567.results (application/octet-stream, text), 7.38 KiB.

[22 Mar 2017 13:38] Frederic Descamps
Hi Umesh, this is the full procedure:

3 different machines:

mysql1: 192.168.90.2
mysql2: 192.168.90.3
mysql3: 192.168.90.4

on all of them:
===============

# systemctl stop mysqld
# rm -rf /var/lib/mysql/*
# mysqld --initialize-insecure -u mysql --datadir /var/lib/mysql/
# systemctl start mysqld

# mysqlsh
mysql-js> dba.configureLocalInstance()

output example:
----------------

[root@mysql1 ~]# mysqlsh
Welcome to MySQL Shell 1.0.8-rc

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> dba.configureLocalInstance()
Please provide the password for 'root@localhost:3306': 

Detecting the configuration file...
Found configuration file at standard location: /etc/my.cnf
Do you want to modify this file? [Y|n]: y
MySQL user 'root' cannot be verified to have access to other hosts in the network.

1) Create root@% with necessary grants
2) Create account with different name
3) Continue without creating account
4) Cancel
Please select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: fred@%
Password for new account: 
Confirm password: 
Validating instance...

The instance 'localhost:3306' is valid for Cluster usage
You can now use it in an InnoDB Cluster.

{
    "status": "ok"
}

Now on mysql1:
==============

mysql-js> var i1 = 'fred@192.168.90.2:3306'
mysql-js> var i2 = 'fred@192.168.90.3:3306'
mysql-js> var i3 = 'fred@192.168.90.4:3306'
mysql-js> shell.connect(i1)
Please provide the password for 'fred@192.168.90.2:3306': 
Creating a Session to 'fred@192.168.90.2:3306'
Classic Session successfully established. No default schema selected.
mysql-js> var cluster = dba.createCluster('MyCluster');
A new InnoDB cluster will be created on instance 'fred@192.168.90.2:3306'.

Creating InnoDB cluster 'MyCluster' on 'fred@192.168.90.2:3306'...
Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

mysql-js> cluster.checkInstanceState(i2); 
Please provide the password for 'fred@192.168.90.3:3306': 
Analyzing the instance replication state...

The instance '192.168.90.3:3306' is valid for the cluster.
The instance is new to Group Replication.

{
    "reason": "new", 
    "state": "ok"
}
mysql-js> cluster.checkInstanceState(i3);
Please provide the password for 'fred@192.168.90.4:3306': 
Analyzing the instance replication state...

The instance '192.168.90.4:3306' is valid for the cluster.
The instance is new to Group Replication.

{
    "reason": "new", 
    "state": "ok"
}
mysql-js> cluster.addInstance(i2);
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'fred@192.168.90.3:3306': 
Adding instance to the cluster ...

The instance 'fred@192.168.90.3:3306' was successfully added to the cluster.

mysql-js> cluster.addInstance(i3);
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'fred@192.168.90.4:3306': 
Adding instance to the cluster ...

The instance 'fred@192.168.90.4:3306' was successfully added to the cluster.

mysql-js> cluster.status()
{
    "clusterName": "MyCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.90.2:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.90.2:3306": {
                "address": "192.168.90.2:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.90.3:3306": {
                "address": "192.168.90.3:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.90.4:3306": {
                "address": "192.168.90.4:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

Then on mysql2:
---------------

mysql2 mysql> stop group_replication;
Query OK, 0 rows affected (8.60 sec)

mysql1:
--------

mysql-js> cluster.status()
{
    "clusterName": "MyCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.90.2:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "192.168.90.2:3306": {
                "address": "192.168.90.2:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.90.3:3306": {
                "address": "192.168.90.3:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "192.168.90.4:3306": {
                "address": "192.168.90.4:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

NOW LET's TRY TO ADD MYSQL2 AGAIN:

(on mysql1):

mysql-js> cluster.rejoinInstance(i2)
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.

Please provide the password for 'fred@192.168.90.3:3306': 
Rejoining instance to the cluster ...

Cluster.rejoinInstance: Access denied for user 'root'@'mysql1' (using password: YES) (MySQL Error 1045)

**password of fred@192.168.90.3:3306 is requested but root@mysql1 is used.**
[23 Mar 2017 11:39] Umesh Shastry
Thank you Frederic.
I'll try to follow your steps, and will get back to you if needed any further info for this.
[27 Mar 2017 9:09] Umesh Shastry
Thank you Frederic for the inputs.
Verified as described.

Thanks,
Umesh
[9 Jun 2017 21:34] Miguel Araujo
Posted by developer:
 
Cluster.rejoinInstance() needs a connection to the seed instance for validations and uses it as peer instance for the rejoining instance.
The implementation of rejoinInstance() was assuming that a Cluster was created using the 'root' account, so the connection to the seed instance was assuming that the account was 'root'. If the user used a different account than 'root', the command would fail.

Apart from that, if the cluster was created with an account and the rejoinInstance() command executed with a different one, the command would fail.

The patch fixes both issues, and:

- Allows the use of different accounts for the seed instance and the rejoining instance
- It does not assume that the Cluster was created using the 'root' account, so it allows using different accounts for the .rejoinInstance() command.
[12 Jul 2017 12:29] David Moss
Posted by developer:
 
Thank you for your feedback, this has been fixed in upcoming versions and the following was added to the 1.0.10 changelog:
When using cluster.rejoinInstance() any user specified in the connection string passed in was being ignored and the connection was using the root user.