Status UNREACHABLE

[Gabriel Marais]

Good day,

I am having issues with one of my four data nodes.

cluster.status() shows db-80-cst1-dn4-pr-aws-eu-i-west-1b as being unreachable:-

{
"clusterName": "db_80_cst1_pr",
"defaultReplicaSet": {
"name": "default",
"primary": "db-80-cst1-dn1-h1-pr-ter-isa-za:3306",
"ssl": "REQUIRED",
"status": "OK_PARTIAL",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure. 1 member is not active",
"topology": {
"db-80-cst1-dn1-h1-pr-ter-isa-za:3306": {
"address": "db-80-cst1-dn1-h1-pr-ter-isa-za:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"db-80-cst1-dn2-h2-pr-ter-isa-za:3306": {
"address": "db-80-cst1-dn2-h2-pr-ter-isa-za:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"db-80-cst1-dn3-h3-pr-ter-isa-za:3306": {
"address": "db-80-cst1-dn3-h3-pr-ter-isa-za:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306": {
"address": "db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "UNREACHABLE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db-80-cst1-dn1-h1-pr-ter-isa-za:3306"
}

I am able to ping the node
# ping -c 2 db-80-cst1-dn1-h1-pr-ter-isa-za
PING db-80-cst1-dn1-h1-pr-ter-isa-za (172.16.24.58) 56(84) bytes of data.
64 bytes from db-80-cst1-dn1-h1-pr-ter-isa-za (172.16.24.58): icmp_seq=1 ttl=64 time=0.060 ms
64 bytes from db-80-cst1-dn1-h1-pr-ter-isa-za (172.16.24.58): icmp_seq=2 ttl=64 time=0.038 ms

Checking the node state:-
Cluster.checkInstanceState: The instance 'db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306' already belongs to the cluster: 'default'. (RuntimeError)

I have tried removing the node form the cluster:-
ERROR: db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306 is reachable but has state OFFLINE

Do you want to continue anyway (only the instance metadata will be removed)? [y/N]: y

The instance will be removed from the InnoDB cluster. Depending on the instance
being the Seed or not, the Metadata session might become invalid. If so, please
start a new session to the Metadata Storage R/W instance.

NOTE: The recovery user name for instance 'db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306' does not match the expected format for users created automatically by InnoDB Cluster. Skipping its removal.
NOTE: Transaction sync was skipped
NOTE: The instance 'db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306' is OFFLINE, Group Replication stop skipped.
Cluster.removeInstance: db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306: Slave channel 'group_replication_applier' does not exist. (RuntimeError)

Force Removing the instance:-

{
"clusterName": "db_80_cst1_pr",
"defaultReplicaSet": {
"name": "default",
"primary": "db-80-cst1-dn1-h1-pr-ter-isa-za:3306",
"ssl": "REQUIRED",
"status": "OK_PARTIAL",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"db-80-cst1-dn1-h1-pr-ter-isa-za:3306": {
"address": "db-80-cst1-dn1-h1-pr-ter-isa-za:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"db-80-cst1-dn2-h2-pr-ter-isa-za:3306": {
"address": "db-80-cst1-dn2-h2-pr-ter-isa-za:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"db-80-cst1-dn3-h3-pr-ter-isa-za:3306": {
"address": "db-80-cst1-dn3-h3-pr-ter-isa-za:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db-80-cst1-dn1-h1-pr-ter-isa-za:3306"
}

I can now see that the node has been removed.

Checking the instance state :-
MySQL localhost:3306 ssl JS > cluster.checkInstanceState('clusteradmin@db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306')
Cluster.checkInstanceState: The instance 'db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306' is a standalone instance but is part of a different InnoDB Cluster (metadata exists, instance belongs to that metadata, but Group Replication is not active). (RuntimeError)

Re-Scan the cluster :-
MySQL localhost:3306 ssl JS > cluster.rescan();
Rescanning the cluster...

Result of the rescanning operation for the 'db_80_cst1_pr' cluster:
{
"name": "db_80_cst1_pr",
"newTopologyMode": null,
"newlyDiscoveredInstances": [
{
"host": "db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306",
"member_id": "d4000165-bacc-11ea-ae49-06cb086e3282",
"name": null,
"version": "8.0.20"
}
],
"unavailableInstances": []
}

A new instance 'db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306' was discovered in the cluster.
Would you like to add it to the cluster metadata? [Y/n]: Y
Adding instance to the cluster metadata...
The instance 'db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306' was successfully added to the cluster metadata.


Cluster status again shows node 4 as being unreachable.

MySQL localhost:3306 ssl JS > cluster.status();
{
"clusterName": "db_80_cst1_pr",
"defaultReplicaSet": {
"name": "default",
"primary": "db-80-cst1-dn1-h1-pr-ter-isa-za:3306",
"ssl": "REQUIRED",
"status": "OK_PARTIAL",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure. 1 member is not active",
"topology": {
"db-80-cst1-dn1-h1-pr-ter-isa-za:3306": {
"address": "db-80-cst1-dn1-h1-pr-ter-isa-za:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"db-80-cst1-dn2-h2-pr-ter-isa-za:3306": {
"address": "db-80-cst1-dn2-h2-pr-ter-isa-za:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"db-80-cst1-dn3-h3-pr-ter-isa-za:3306": {
"address": "db-80-cst1-dn3-h3-pr-ter-isa-za:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306": {
"address": "db-80-cst1-dn4-pr-aws-eu-i-west-1b:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "UNREACHABLE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db-80-cst1-dn1-h1-pr-ter-isa-za:3306"
}


What am I doing wrong? How do I get the node back into the cluster as ONLINE?



Thanks in advance, Gabriel