Access denied for user 'distributed'@'IP' (using password: YES)

emanuelbesliu · November 9, 2021, 10:21am

I’m trying to configure replication without success on a Kubernetes Environment;

error from replicating database cluster:

11142737585 2021-11-09 10:17:11.564 ERROR: Thread 99979: SetConnFromMetadataNode: Connection failure connecting to node id 1: distributed:x@'node-memsql-cluster-master-0.svc-memsql-cluster':3306. Failed with error: Access denied for user 'distributed'@'10.240.1.191' (using password: YES) 11142747766 2021-11-09 10:17:11.575 WARN: Thread 99979: SyncToMasterLsn: cluster_18384551547992704098: _SYNC failed due to recurrent error (state replicating).

I suppose the connection from main cluster to replicating one is working fine. The problem is the reverse connection from replicating cluster to main one.

adam · November 10, 2021, 3:52pm

Yes, for cluster to cluster replication all memsqld processes in both clusters need to be able to reach each other using the host names they were added to their respective clusters with (i.e., cluster to cluster replication will setup a bunch of replication streams between the leaf nodes in both clusters). This is sometimes a challenge in k8s environments depending on how things are configured.

emanuelbesliu · December 6, 2021, 8:59pm

@adam do you have any idea how should I begin to investigate?

This is the cluster I’m trying to connect.

The cluster configuration is as follows:
Primary:

Secondary:

I am using SingleStore Studio to connect directly to these clusters using their internal IPs. However, I cannot setup the replication.

These are the only logs I could find:

Maybe @robrich has an idea. I know he knows Kubernetes environment.

cynn · December 8, 2021, 12:53pm

Both clusters and associated leaf nodes need to be network reachable as Adam has already stated.

We provide documentation on how to configure the S2 Operator running within Kubernetes so that when it deploys a SingleStore DB cluster the nodes are reachable externally to the Kubernetes cluster environment.

A key part of this setup requires that the Kubernetes cluster itself be configured with subnets / CIDR blocks that are accessible to the replicating clusters network infrastructure.

A way to check this once the configuration is applied (found in documentation link from above), connect to the MA node and run the following query:

SELECT
   TYPE AS type,
   IP_ADDR AS host,
   PORT AS port,
   EXTERNAL_HOST AS externalHost,
   EXTERNAL_PORT AS externalPort
 FROM information_schema.mv_nodes;

From the output, you will see the external host and ports configured.

From the replicating cluster, you will need to be able to reach/ping the listed external IP addresses.

emanuelbesliu · December 8, 2021, 2:50pm

I have set

replicationSpec:
  enable: true
  connectivity: viaHost

for both clusters but cannot reach each other. Could not find any documentation on this

The clusters are deployed inside one K8S Cluster with CIDR

@cynn any advice how to continue on this?

cynn · December 14, 2021, 12:56pm

@emanuelbesliu,

Are both S2DB clusters within the same Kubernetes cluster?

If so, then you should be able to use the below set of steps to test for network connectivity:

If the Kubernetes network is configured correctly you should be able to kubectl exec into the Master Aggregator Pod.
Once inside the MA Pod you should be able to run memsql -uadmin -hnode-memsql-cluster-leaf-ag1-0.svc-memsql-cluster -p<<adming password>>.
Feel free to test from one S2 cluster to another by supplying the appropriate host to help diagnose / test if you have network connectivity.

If the above doesn’t work then you need to look at your network stack configuration.

As stated before, both clusters have to have mappable network routes between each other – if this isn’t the case then your Kubernetes / Infrastructure Engineering team will need to look at troubleshooting / configuring the network path.

For what it’s worth, we recommend Cilium or Calico for production-level CNI’s. Kubenet from our experience has been very limiting and we’ve not used Kubenet for Disaster Recovery (replication) S2 cluster configurations.

The other item of note, I see you stated that you applied the following in the memsql-cluster spec.

replicationSpec:
  enable: true
  connectivity: viaHost

What happens is that the operator submits the request to the Kubernetes Scheduler to configure the Pod for an IP or node port (depending on which connectivity option you supply).

The CNI / Kubernetes network stack must be configured correctly to enable this to work.

I bring this up because if you did supply the above replicationSpec and you get the following output then something isn’t configured correctly because the Operator did not receive the externalHost and externalPort parameters from the Kubernetes cluster.

Can you try:

First deploy each of your S2 cluster’s without the replicationSpec
After the clusters are fully online update their memsql-cluster configs with the replicationSpec

emanuelbesliu · December 14, 2021, 1:36pm

Thank you for assisting, @cynn !

Yes, the dbs are within the same K8S Cluster.

I have done as follows:

I have executed kubectl exec -it <MA Pod> bash
Then I tried to connect to a leaf from the same S2DB Cluster using
memsql -u admin -h node-memsql-cluster-leaf-ag1-0.svc-memsql-cluster -p
and it says that user admin does not have access:

ERROR 1045 (28000): Access denied for user 'admin'@'10.244.2.166' (using password: YES)

Then tried with root and worked

Also tried to connect from this MA Pod to the second S2DB Leaf with root user and worked.

Can admin user not having access break the chain?

I’ve tried deploying S2 clusters and then after they were fully operating I changed the configuration by redeploying using kubectl apply -f <file>. Is that how you update memsql-cluster configs? I could not find any relevant configs files after deployment.

cynn · December 14, 2021, 5:02pm

My mistake btw, you should use root and not admin to connect to the leaves.

I’ve tried deploying S2 clusters and then after they were fully operating I changed the configuration by redeploying using kubectl apply -f <file> . Is that how you update memsql-cluster configs? I could not find any relevant configs files after deployment.

As per SOP with Kubernetes, using kubectl apply is the correct method.

Are you running two Operator deployments?

If you can share, can you grab the output of kubectl get pods -A?

emanuelbesliu · December 15, 2021, 10:23am

I’ve solved the problem. There were no ddl and dml services up.

As following the discussion, I deployed MemSQL Studio using a CentOS 7 image and run it with a LoadBalancer. When I try to access the clusters through LoadBalancer’s IP it works fine. I use NGINX to redirect all the traffic from Public IP with port 80 to memsql-studio:8080 service, from this point I’m not longer able to access the clusters or add new ones. Do you have any idea why? I’m kind of stuck on this as well

emanuelbesliu · January 17, 2022, 5:30pm

@cynn

Is there a way to manually set fields from SELECT
TYPE AS type,
IP_ADDR AS host,
PORT AS port,
EXTERNAL_HOST AS externalHost,
EXTERNAL_PORT AS externalPort
FROM information_schema.mv_nodes?

I cannot make databases communicate between k8s clusters. Whether I use viaPort or viaHost it assigns a Cluster IP to the externalHost which I can no longer match from an outside database.

The clusters are connected using a VNet at pod addresses level, the cluster IP Range is 10.0.0.0/16 on both clusters therefore It’s quite tricky to route to these addresses, even if I use nginx it does not work. It however works with LoadBalancer, which is quite sad because I have to expose it to the internet.

cynn · January 18, 2022, 10:31am

@emanuelbesliu,

Is there a way to manually set fields from SELECT

The Operator sets the EXTERNAL_HOST and EXTERNAL_PORT automatically, and this information is provided as part of the Pod deployment from the K8’s cluster.

The Pod needs to be configured with an IP and Port as part of the K8’s scheduling when it’s spun up.

Once the Pod is deployed and running the Operator checks for the IP and Port configuration and uses that to populate the EXTERNAL_HOST and EXTERNAL_PORT fields.

If the IP and Port aren’t first configured on the Pod then there will not be a connection path with respect to the Kubernetes cluster.

So manually setting the EXTERNAL_HOST and EXTERNAL_PORT fields from the Database level isn’t going to make things work. Keep in mind that it can be common for Pods to reschedule which can cause IP and Port information to change – in a properly configured K8’s environment, the Operator will update the EXTERNAL_HOST and EXTERNAL_PORT information automatically which assures that the replication connection remains stable.

You will need to supply a CIDR block that is accessible on your network between the two SingleStore databases, it appears that the K8’s cluster CIDR block is configured to use the same private 10.0.0.0/16 subnet which will not work. This is part of the K8’s network / CNI configuration and deployment.

In order for cluster replication to work the leaves on the Primary S2 Cluster and the Secondary S2 Cluster need to be able to connect and communicate directly to each other.

It however works with LoadBalancer, which is quite sad because I have to expose it to the internet.

I’m not sure if you are using a cloud service provider, regardless, it’s possible to configure the Load Balancer used on the K8’s cluster to deploy a Load Balancer service and not expose the IP that is used to the Internet.

For example, with GKE you can annotate the service configuration with networking.gke.io/load-balancer-type: "Internal" .