Can't join node to cluster after restart

sapd · December 6, 2017, 4:29am

Hi everyone!. I have three nodes which installed Percona-Xtra-DB 5.7.19-29.22-1.xenial on Ubuntu 16.04, the cluster.cnf configure file.

[mysqld]
datadir = /var/lib/mysql
user = mysql
default-time-zone = '+7:00'
max_connections = 500
bind-address = 10.5.68.19
slow-query-log = /var/log/mysql/mysql-slow.log
long_query_time = 2

# UTF8
default-storage-engine = innodb
innodb_file_per_table = on
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8

# Path to Galera library
wsrep_provider = /usr/lib/galera3/libgalera_smm.so
# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address = gcomm://10.5.68.31,10.5.68.12,10.5.68.33,

# In order for Galera to work correctly binlog format should be ROW
binlog_format = ROW
# MyISAM storage engine has only experimental support
default_storage_engine = InnoDB
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode = 2
# Node #2 address
wsrep_node_address = 10.5.68.31
# Cluster name
wsrep_cluster_name = MySQL_CLUSTER_DEV_TEAM
# SST method
wsrep_sst_method = xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth = "sstuser:aQq0azaaaaaaa

when I reconfigure with value of max_allowed_packet , I restart node -1 then restart node-2 and node3, But node2 , node-3 can;t join cluster with loop message

``
2017-12-06T09:58:42.560757Z 0 [Warning] WSREP: Member 0.0 (percona-xtradb-3) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:43.063215Z 0 [Warning] WSREP: Member 2.0 (percona-xtradb-2) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:43.562064Z 0 [Warning] WSREP: Member 0.0 (percona-xtradb-3) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:44.064023Z 0 [Warning] WSREP: Member 2.0 (percona-xtradb-2) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:44.562928Z 0 [Warning] WSREP: Member 0.0 (percona-xtradb-3) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:45.064980Z 0 [Warning] WSREP: Member 2.0 (percona-xtradb-2) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:45.564467Z 0 [Warning] WSREP: Member 0.0 (percona-xtradb-3) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:46.066043Z 0 [Warning] WSREP: Member 2.0 (percona-xtradb-2) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:46.565168Z 0 [Warning] WSREP: Member 0.0 (percona-xtradb-3) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:47.067126Z 0 [Warning] WSREP: Member 2.0 (percona-xtradb-2) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:47.566116Z 0 [Warning] WSREP: Member 0.0 (percona-xtradb-3) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:48.068213Z 0 [Warning] WSREP: Member 2.0 (percona-xtradb-2) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:48.567062Z 0 [Warning] WSREP: Member 0.0 (percona-xtradb-3) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:49.069196Z 0 [Warning] WSREP: Member 2.0 (percona-xtradb-2) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:49.568081Z 0 [Warning] WSREP: Member 0.0 (percona-xtradb-3) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:50.070429Z 0 [Warning] WSREP: Member 2.0 (percona-xtradb-2) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:50.570048Z 0 [Warning] WSREP: Member 0.0 (percona-xtradb-3) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:51.071358Z 0 [Warning] WSREP: Member 2.0 (percona-xtradb-2) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:51.570904Z 0 [Warning] WSREP: Member 0.0 (percona-xtradb-3) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable
2017-12-06T09:58:52.072327Z 0 [Warning] WSREP: Member 2.0 (percona-xtradb-2) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable


I removed /var/lib/mysql/* on node-3, But It can't solve this problem.

Please help me. Thanks

krunalbauskar · December 8, 2017, 12:20am

what are states of other nodes of the cluster. From the message, it sounds like it is unable to find a valid DONOR. This could be because existing nodes are already in DONOR/DESYCNED state or some other state that prevent it from donating data.

Qanah · September 20, 2020, 1:41am

i have same issue and this is my status DONOR/DESYCNED

Qanah · September 22, 2020, 7:44am

version: '3.7'

services:
  pxc-node1:
    image: percona/percona-xtradb-cluster:8.0
    volumes:
      - pxc-node1-data:/var/lib/mysql
      - ~/deploy/mysql/pxc-docker/cert:/cert
      - ~/deploy/mysql/pxc-docker/config:/etc/percona-xtradb-cluster.conf.d
    networks:
      - pxc_distributed
    deploy:
      resources:
        limits:
          memory: 1GB
      restart_policy:
        delay: 10s
        max_attempts: 10
        window: 60s
      placement:
        constraints:
          - node.labels.pxc-node1==true
    environment:
      MYSQL_ROOT_PASSWORD: "root"
      XTRABACKUP_PASSWORD: "backup"
      CLUSTER_NAME: "pxc-cluster"

  pxc-node2:
    image: percona/percona-xtradb-cluster:8.0
    depends_on:
      - pxc-node1
    volumes:
      - pxc-node2-data:/var/lib/mysql
      - ~/deploy/mysql/pxc-docker/cert:/cert
      - ~/deploy/mysql/pxc-docker/config:/etc/percona-xtradb-cluster.conf.d
    networks:
      - pxc_distributed
    deploy:
      resources:
        limits:
          memory: 1GB
      restart_policy:
        delay: 10s
        max_attempts: 10
        window: 60s
      placement:
        constraints:
          - node.labels.pxc-node2==true
    environment:
      MYSQL_ROOT_PASSWORD: "root"
      XTRABACKUP_PASSWORD: "backup"
      CLUSTER_NAME: "pxc-cluster"
      CLUSTER_JOIN: "pxc-node1"

  pxc-node3:
    image: percona/percona-xtradb-cluster:8.0
    depends_on:
      - pxc-node1
    volumes:
      - pxc-node3-data:/var/lib/mysql
      - ~/deploy/mysql/pxc-docker/cert:/cert
      - ~/deploy/mysql/pxc-docker/config:/etc/percona-xtradb-cluster.conf.d
    networks:
      - pxc_distributed
    deploy:
      resources:
        limits:
          memory: 1GB
      restart_policy:
        delay: 10s
        max_attempts: 10
        window: 60s
      placement:
        constraints:
          - node.labels.pxc-node3==true
    environment:
      MYSQL_ROOT_PASSWORD: "root"
      XTRABACKUP_PASSWORD: "backup"
      CLUSTER_NAME: "pxc-cluster"
      CLUSTER_JOIN: "pxc-node1"

  pxc-node4:
    image: percona/percona-xtradb-cluster:8.0
    depends_on:
      - pxc-node1
    volumes:
      - pxc-node4-data:/var/lib/mysql
      - ~/deploy/mysql/pxc-docker/cert:/cert
      - ~/deploy/mysql/pxc-docker/config:/etc/percona-xtradb-cluster.conf.d
    networks:
      - pxc_distributed
    deploy:
      resources:
        limits:
          memory: 1GB
      restart_policy:
        delay: 10s
        max_attempts: 10
        window: 60s
      placement:
        constraints:
          - node.labels.pxc-node4==true
    environment:
      MYSQL_ROOT_PASSWORD: "root"
      XTRABACKUP_PASSWORD: "backup"
      CLUSTER_NAME: "pxc-cluster"
      CLUSTER_JOIN: "pxc-node1"

volumes:
  pxc-node1-data:
  pxc-node2-data:
  pxc-node3-data:
  pxc-node4-data:

networks:
  pxc_distributed:
    driver: overlay

this is my docker swarm stack depend on this https://www.percona.com/doc/percona-xtradb-cluster/LATEST/install/docker.html

matthewb · September 22, 2020, 2:32pm

Which node did you bootstrap? You must always bootstrap the first node. You cannot bring up all 4 nodes at the same time. You must bootstrap the first node, wait for it to finish, then you can start the next node, then the next node.

Qanah · September 24, 2020, 7:26am

thank you @matthewb i am using docker swarm stack , for answer your question as i understand from docker swarm documentation if i use depends_on this mean the pxc-node1 will be my bootstrap node and the rest will start after pxc-node1 complete

Qanah · September 24, 2020, 7:28am

i will try to do it manually without using the stack but i thank you again i will check also if depends_on is booting my first node before load the rest

matthewb · September 24, 2020, 7:47am

@Qanah https://docs.docker.com/compose/compose-file/

depends_on does not wait for db and redis to be “ready” before starting web

depends_on option is ignored when deploying a stack in swarm mode

You might want to verify this behavior. Additionally, the first node to start does not automatically bootstrap. You must tell the mysqld service this particular parameter. When starting PXC in a non-docker-swarm env, you would typically say “systemctl start mysql@bootstrap” to bootstrap the first node, then start the other remaining nodes. You might need to figure out a way to pass this parameter to the container.

Qanah · September 24, 2020, 1:36pm

i addthis to node2, node3, node4

command: [“./home/wait-for-it.sh”, “pxc-node1”, “root”, “mysqld”]

#!/bin/sh

set -e

host="$1"
pass="$2"

shift
cmd="$@"

until /usr/bin/mysqladmin ping -h "$host" -P 3306 -p$pass | grep "mysqld is alive" ; do
  >&2 echo "MySQL is unavailable - waiting for it... ?"
  sleep 1
done

>&2 echo "$host is up - executing command"
exec $cmd

but the issue now with node1 Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’

matthewb · September 24, 2020, 2:08pm

How are you bootstrapping node1?

Qanah · September 25, 2020, 2:34pm

first thank you for your responding.

i have 4 servers, one as docker swarm manager and 3 as workers all joined to the swarm manager node

i copy this files to all this servers

by this command

docker-machine scp -r pxc-docker/ chat-manager:/root
docker-machine scp -r pxc-docker/ chat-worker1:/root
docker-machine scp -r pxc-docker/ chat-worker2:/root
docker-machine scp -r pxc-docker/ chat-worker3:/root
docker-machine scp -r docker-compose-stack.yml chat-manager:/root
also i added labels to my nodes as this
docker node update --label-add pxc-node1=true chat-manager
docker node update --label-add pxc-node2=true chat-worker1
docker node update --label-add pxc-node3=true chat-worker2
docker node update --label-add pxc-node4=true chat-worker3
and this is my last version of docker-compose-stack.yml
version: '3.7'

services:

  pxc-node1:
    image: percona/percona-xtradb-cluster:8.0
    volumes:
      - pxc-node1-data:/var/lib/mysql
      - ~/pxc-docker/config:/etc/percona-xtradb-cluster.conf.d
    networks:
      - pxc-network
    deploy:
      placement:
        constraints:
          - node.labels.pxc-node1==true
    environment:
      MYSQL_ROOT_PASSWORD: "root"
      CLUSTER_NAME: "pxc-cluster"

  pxc-node2:
    depends_on:
      - pxc-node1
    image: percona/percona-xtradb-cluster:8.0
    volumes:
      - pxc-node2-data:/var/lib/mysql
      - ~/pxc-docker/cert:/cert
      - ~/pxc-docker/config:/etc/percona-xtradb-cluster.conf.d
      - ~/pxc-docker/bin/wait-for-it.sh:/home/wait-for-it.sh
    networks:
      - pxc-network
    deploy:
      placement:
        constraints:
          - node.labels.pxc-node2==true
    environment:
      MYSQL_ROOT_PASSWORD: "root"
      CLUSTER_NAME: "pxc-cluster"
      CLUSTER_JOIN: "pxc-node1"
    command: ["./home/wait-for-it.sh", "pxc-node1", "root", "mysqld"]

  pxc-node3:
    depends_on:
      - pxc-node1
    image: percona/percona-xtradb-cluster:8.0
    volumes:
      - pxc-node3-data:/var/lib/mysql
      - ~/pxc-docker/cert:/cert
      - ~/pxc-docker/config:/etc/percona-xtradb-cluster.conf.d
      - ~/pxc-docker/bin/wait-for-it.sh:/home/wait-for-it.sh
    networks:
      - pxc-network
    deploy:
      placement:
        constraints:
          - node.labels.pxc-node3==true
    environment:
      MYSQL_ROOT_PASSWORD: "root"
      CLUSTER_NAME: "pxc-cluster"
      CLUSTER_JOIN: "pxc-node1"
    command: ["./home/wait-for-it.sh", "pxc-node1", "root", "mysqld"]

  pxc-node4:
    depends_on:
      - pxc-node1
    image: percona/percona-xtradb-cluster:8.0
    volumes:
      - pxc-node4-data:/var/lib/mysql
      - ~/pxc-docker/cert:/cert
      - ~/pxc-docker/config:/etc/percona-xtradb-cluster.conf.d
      - ~/pxc-docker/bin/wait-for-it.sh:/home/wait-for-it.sh
    networks:
      - pxc-network
    deploy:
      placement:
        constraints:
          - node.labels.pxc-node4==true
    environment:
      MYSQL_ROOT_PASSWORD: "root"
      CLUSTER_NAME: "pxc-cluster"
      CLUSTER_JOIN: "pxc-node1"
    command: ["./home/wait-for-it.sh", "pxc-node1", "root", "mysqld"]

volumes:
  pxc-node1-data:
  pxc-node2-data:
  pxc-node3-data:
  pxc-node4-data:

networks:
  pxc-network:
    driver: overlay
after this i run this command to bootstrap all nodes 
 docker stack deploy -c docker-compose-stack.yml mysql

matthewb · January 4, 2021, 9:18pm

@Qanah Were you able to get this working?

tmahaffey · December 7, 2021, 11:21pm

I am seeing this same fault when I try to connect another node to the first node after the first time I bootstrapped the first node.

2021-12-07T23:18:15.324293Z 0 [Warning] [MY-000000] [Galera] Member 0.0 (pxc-2) requested state transfer from ‘any’, but it is impossible to select State Transfer donor: Resource temporarily unavailable.

Topic		Replies	Views
Help! Node won't join Cluster after Restart. Percona XtraDB Cluster 5.x	1	898	November 22, 2017
Error when joining a new node on cluster Percona XtraDB Cluster 5.x	1	2068	July 29, 2013
The master node starts normally, and other nodes cannot join Percona XtraDB Cluster 8.x	10	765	October 13, 2023
Clustering 3 node .Why this error 'Percona XtraDB Cluster Node is not synced or non-PRIM' .help me Percona XtraDB Cluster 5.x	5	4145	November 12, 2015
Nodes don't join after upgrade to XtraDB-Cluster-5.6.22-72.0 and xtrabackup-2.2.10 Percona XtraDB Cluster 5.x	2	2410	June 16, 2015

Can't join node to cluster after restart

Related topics