SQL cluster issue, need help please

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Currently we have been running (3) SQL servers bootstrapped with Percona with HAProxy as the handler between out APP and SQL. Our cluster had a failure where SQL2 and SQL3 stopped handling requests and talking to SQL1. We were able to restart and recover SQL2 but SQL 3 is providing us with the below error. We are looking for assistance in restoring our cluster to full functionality. Appreciate any help.

● mysql.service - LSB: Start and stop the mysql (Percona XtraDB Cluster) daemon
Loaded: loaded (/etc/init.d/mysql)
Active: failed (Result: exit-code) since Fri 2016-12-23 10:24:27 UTC; 4 days ago
Process: 7108 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Dec 23 10:24:27 nj-sql3 mysql[7108]: Stale sst_in_progress file in datadir: mysqldStarting MySQL (Percona XtraDB Cluster) database server: mysqldState transfer in progress, setting sleep higher: mysqld . . .The server quit without upd
ating PID file (/var/run/mysqld/mysqld.pid). … failed!
Dec 23 10:24:27 nj-sql3 mysql[7108]: failed!
Dec 23 10:24:27 nj-sql3 systemd[1]: mysql.service: control process exited, code=exited status=1
Dec 23 10:24:27 nj-sql3 systemd[1]: Failed to start LSB: Start and stop the mysql (Percona XtraDB Cluster) daemon.
Dec 23 10:24:27 nj-sql3 systemd[1]: Unit mysql.service entered failed state.

2

Delete the “sst_in_progress” file in your data-dir directory and restart mysql? probably in /var/lib/mysql if you haven’t changed it…

3

We have removed the sst_in_progress prior but we are getting the error when starting mysqld on the third server. Server 1 and 2 get online fine.

4

017-03-07 09:49:41 14029 [Note] WSREP: Service thread queue flushed.
2017-03-07 09:49:41 14029 [Note] WSREP: Assign initial position for certification: 128576583, protocol version: 3
2017-03-07 09:49:41 14029 [Note] WSREP: Service thread queue flushed.
2017-03-07 09:49:41 14029 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (25a7fe74-3121-11e5-bf1c-1aa1e8bb93a8): 1 (Operation not permitted)
at galera/src/replicator_str.cpp:prepare_for_IST():489. IST will be unavailable.
2017-03-07 09:49:41 14029 [Note] WSREP: Member 2.0 (nj-sql3) requested state transfer from ‘any’. Selected 0.0 (nj-sql2)(SYNCED) as donor.
2017-03-07 09:49:41 14029 [Note] WSREP: Shifting PRIMARY → JOINER (TO: 128576587)
2017-03-07 09:49:41 14029 [Note] WSREP: Requesting state transfer: success, donor: 0
WSREP_SST: [INFO] WARNING: Stale temporary SST directory: /var/lib/mysql//.sst from previous state transfer (20170307 09:49:41.948)
WSREP_SST: [INFO] Proceeding with SST (20170307 09:49:41.956)
WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20170307 09:49:41.965)
WSREP_SST: [INFO] Cleaning the existing datadir and innodb-data/log directories (20170307 09:49:41.967)
2017-03-07 09:49:42 14029 [Note] WSREP: (610d24c7, ‘tcp://0.0.0.0:4567’) turning message relay requesting off
removed ‘/var/lib/mysql/ib_logfile0’
removed ‘/var/lib/mysql/ib_logfile1’
removed ‘/var/lib/mysql/auto.cnf’
removed ‘/var/lib/mysql/ibdata1’
WSREP_SST: [INFO] Waiting for SST streaming to complete! (20170307 09:49:42.132)
xbstream: Can’t create/write to file ‘././backup-my.cnf’ (Errcode: 17 - File exists)
xbstream: failed to create file.
WSREP_SST: [ERROR] Error while getting data from donor node: exit codes: 0 1 (20170307 09:49:53.194)
WSREP_SST: [ERROR] Cleanup after exit with status:32 (20170307 09:49:53.197)
2017-03-07 09:49:53 14029 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role ‘joiner’ --address ‘192.168.175.191’ --auth ‘sstuser:sstpass’ --datadir ‘/var/lib/mysql/’ --defaults-file ‘/etc/mysql/my.cnf’ --defaults-group-suffix ‘’ --parent ‘14029’ ‘’ : 32 (Broken pipe)
2017-03-07 09:49:53 14029 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
2017-03-07 09:49:53 14029 [ERROR] WSREP: SST failed: 32 (Broken pipe)
2017-03-07 09:49:53 14029 [ERROR] Aborting

2017-03-07 09:49:53 14029 [Warning] WSREP: 0.0 (nj-sql2): State transfer to 2.0 (nj-sql3) failed: -22 (Invalid argument)
2017-03-07 09:49:53 14029 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():733: Will never receive state. Need to abort.
2017-03-07 09:49:53 14029 [Note] WSREP: gcomm: terminating thread
2017-03-07 09:49:53 14029 [Note] WSREP: gcomm: joining thread
2017-03-07 09:49:53 14029 [Note] WSREP: gcomm: closing backend
2017-03-07 09:49:53 14029 [Note] WSREP: view(view_id(NON_PRIM,204d9823,7) memb {
610d24c7,0
} joined {
} left {
} partitioned {
204d9823,0
4ac3c76f,0
})
2017-03-07 09:49:53 14029 [Note] WSREP: view((empty))
2017-03-07 09:49:53 14029 [Note] WSREP: gcomm: closed
2017-03-07 09:49:53 14029 [Note] WSREP: /usr/sbin/mysqld: Terminated.
Aborted
170307 09:49:53 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended