node crashed and i am unable to recover

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

i have a 3 node PXC setup we i have added some performance variable to my config file in node1 and restarted i was unable to add the node1 to existing cluster . i have comments all the changes on tried to restart the node but this didn’t work out . please find below for error messages that were logged. Need help as it is production PXC setup .

160519 23:24:12 mysqld_safe Skipping wsrep-recover for b4821329-1e39-11e6-8378-baa69bfe7dc9:4 pair
160519 23:24:12 mysqld_safe Assigning b4821329-1e39-11e6-8378-baa69bfe7dc9:4 to wsrep_start_position
2016-05-19 23:24:12 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-19 23:24:12 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-72.2-56) starting as process 2547 …
2016-05-19 23:24:12 2547 [Note] WSREP: Read nil XID from storage engines, skipping position init
2016-05-19 23:24:12 2547 [Note] WSREP: wsrep_load(): loading provider library ‘/usr/lib64/libgalera_smm.so’
2016-05-19 23:24:12 2547 [Note] WSREP: wsrep_load(): Galera 3.11(ra0189ab) by Codership Oy <info@codership.com> loaded successfully.
2016-05-19 23:24:12 2547 [Note] WSREP: CRC-32C: using hardware acceleration.
2016-05-19 23:24:12 2547 [Note] WSREP: Found saved state: b4821329-1e39-11e6-8378-baa69bfe7dc9:4
2016-05-19 23:24:12 2547 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 10.0.1.225; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; p
2016-05-19 23:24:12 2547 [Note] WSREP: Service thread queue flushed.
2016-05-19 23:24:12 2547 [Note] WSREP: Assign initial position for certification: 4, protocol version: -1
2016-05-19 23:24:12 2547 [Note] WSREP: wsrep_sst_grab()
2016-05-19 23:24:12 2547 [Note] WSREP: Start replication
2016-05-19 23:24:12 2547 [Note] WSREP: Setting initial position to b4821329-1e39-11e6-8378-baa69bfe7dc9:4
2016-05-19 23:24:12 2547 [Note] WSREP: protonet asio version 0
2016-05-19 23:24:12 2547 [Note] WSREP: Using CRC-32C for message checksums.
2016-05-19 23:24:12 2547 [Note] WSREP: backend: asio
2016-05-19 23:24:12 2547 [Warning] WSREP: access file(/var/lib/mysql//gvwstate.dat) failed(No such file or directory)
2016-05-19 23:24:12 2547 [Note] WSREP: restore pc from disk failed
2016-05-19 23:24:12 2547 [Note] WSREP: GMCast version 0
2016-05-19 23:24:12 2547 [Note] WSREP: (529594ab, ‘tcp://0.0.0.0:4567’) listening at tcp://0.0.0.0:4567
2016-05-19 23:24:12 2547 [Note] WSREP: (529594ab, ‘tcp://0.0.0.0:4567’) multicast: , ttl: 1
2016-05-19 23:24:12 2547 [Note] WSREP: EVS version 0
2016-05-19 23:24:12 2547 [Note] WSREP: gcomm: connecting to group ‘my_ubuntu_cluster’, peer ‘10.0.1.225:,10.0.1.224:’
2016-05-19 23:24:12 2547 [Warning] WSREP: (529594ab, ‘tcp://0.0.0.0:4567’) address ‘tcp://10.0.1.225:4567’ points to own listening address, blacklisting
2016-05-19 23:24:12 2547 [Note] WSREP: (529594ab, ‘tcp://0.0.0.0:4567’) address ‘tcp://10.0.1.225:4567’ pointing to uuid 529594ab is blacklisted, skipping
2016-05-19 23:24:12 2547 [Note] WSREP: (529594ab, ‘tcp://0.0.0.0:4567’) turning message relay requesting on, nonlive peers: tcp://10.0.1.226:4567
2016-05-19 23:24:12 2547 [Note] WSREP: (529594ab, ‘tcp://0.0.0.0:4567’) address ‘tcp://10.0.1.225:4567’ pointing to uuid 529594ab is blacklisted, skipping
2016-05-19 23:24:12 2547 [Note] WSREP: (529594ab, ‘tcp://0.0.0.0:4567’) address ‘tcp://10.0.1.225:4567’ pointing to uuid 529594ab is blacklisted, skipping
2016-05-19 23:24:12 2547 [Note] WSREP: (529594ab, ‘tcp://0.0.0.0:4567’) address ‘tcp://10.0.1.225:4567’ pointing to uuid 529594ab is blacklisted, skipping
2016-05-19 23:24:12 2547 [Note] WSREP: declaring 56560110 at tcp://10.0.1.224:4567 stable
2016-05-19 23:24:12 2547 [Note] WSREP: declaring 6e832bd2 at tcp://10.0.1.226:4567 stable
2016-05-19 23:24:12 2547 [Note] WSREP: Node 56560110 state prim
2016-05-19 23:24:12 2547 [Note] WSREP: view(view_id(PRIM,529594ab,51) memb {
529594ab,0
56560110,0
6e832bd2,0
} joined {
} left {
} partitioned {
})

2

2016-05-19 23:24:12 2547 [Note] WSREP: save pc into disk
2016-05-19 23:24:13 2547 [Note] WSREP: gcomm: connected
2016-05-19 23:24:13 2547 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
2016-05-19 23:24:13 2547 [Note] WSREP: Shifting CLOSED → OPEN (TO: 0)
2016-05-19 23:24:13 2547 [Note] WSREP: Opened channel ‘my_ubuntu_cluster’
2016-05-19 23:24:13 2547 [Note] WSREP: Waiting for SST to complete.
2016-05-19 23:24:13 2547 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 3
2016-05-19 23:24:13 2547 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 532ea367-1e3a-11e6-9dda-2a89c5480b29
2016-05-19 23:24:13 2547 [Note] WSREP: STATE EXCHANGE: sent state msg: 532ea367-1e3a-11e6-9dda-2a89c5480b29
2016-05-19 23:24:13 2547 [Note] WSREP: STATE EXCHANGE: got state msg: 532ea367-1e3a-11e6-9dda-2a89c5480b29 from 0 (ip-10-0-1-225.ap-southeast-1.compute.internal)
2016-05-19 23:24:13 2547 [Note] WSREP: STATE EXCHANGE: got state msg: 532ea367-1e3a-11e6-9dda-2a89c5480b29 from 1 (ip-10-0-1-224.ap-southeast-1.compute.internal)
2016-05-19 23:24:13 2547 [Note] WSREP: STATE EXCHANGE: got state msg: 532ea367-1e3a-11e6-9dda-2a89c5480b29 from 2 (ip-10-0-1-226.ap-southeast-1.compute.internal)
2016-05-19 23:24:13 2547 [Note] WSREP: Quorum results:
version = 3,
component = PRIMARY,
conf_id = 50,
members = 2/3 (joined/total),
act_id = 2183133,

protocols = 0/7/3 (gcs/repl/appl),
group UUID = e8e2feed-1753-11e6-bfe3-972d646081e6
2016-05-19 23:24:13 2547 [Note] WSREP: Flow-control interval: [28, 28]
2016-05-19 23:24:13 2547 [Note] WSREP: Shifting OPEN → PRIMARY (TO: 2183133)
2016-05-19 23:24:13 2547 [Note] WSREP: State transfer required:
Group state: e8e2feed-1753-11e6-bfe3-972d646081e6:2183133
Local state: b4821329-1e39-11e6-8378-baa69bfe7dc9:4
2016-05-19 23:24:13 2547 [Note] WSREP: New cluster view: global state: e8e2feed-1753-11e6-bfe3-972d646081e6:2183133, view# 51: Primary, number of nodes: 3, my index: 0, protocol version 3
2016-05-19 23:24:13 2547 [Warning] WSREP: Gap in state sequence. Need state transfer.
2016-05-19 23:24:13 2547 [Note] WSREP: Running: ‘wsrep_sst_xtrabackup-v2 --role ‘joiner’ --address ‘10.0.1.225’ --auth ‘sstuser:s3cret’ --datadir ‘/var/lib/mysql/’ --defaults-file ‘/etc/my.cnf’ --defaults-group-suffix ‘’ --parent ‘2547’ ‘’ ’
WSREP_SST: [INFO] Streaming with xbstream (20160519 23:24:13.511)
WSREP_SST: [INFO] Using socat as streamer (20160519 23:24:13.512)
WSREP_SST: [INFO] Stale sst_in_progress file: /var/lib/mysql//sst_in_progress (20160519 23:24:13.515)
WSREP_SST: [INFO] Evaluating timeout -s9 100 socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20160519 23:24:13.537)
2016-05-19 23:24:13 2547 [Note] WSREP: Prepared SST request: xtrabackup-v2|10.0.1.225:4444/xtrabackup_sst//1
2016-05-19 23:24:13 2547 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2016-05-19 23:24:13 2547 [Note] WSREP: REPL Protocols: 7 (3, 2)
2016-05-19 23:24:13 2547 [Note] WSREP: Service thread queue flushed.
2016-05-19 23:24:13 2547 [Note] WSREP: Assign initial position for certification: 2183133, protocol version: 3
2016-05-19 23:24:13 2547 [Note] WSREP: Service thread queue flushed.
2016-05-19 23:24:13 2547 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (b4821329-1e39-11e6-8378-baa69bfe7dc9) does not match group state UUID (e8e2feed-1753-11e6-bfe3-972d646081e6): 1 (Operation not permitted)
at galera/src/replicator_str.cpp:prepare_for_IST():463. IST will be unavailable.
2016-05-19 23:24:13 2547 [Note] WSREP: Member 0.0 (ip-10-0-1-225.ap-southeast-1.compute.internal) requested state transfer from ‘any’. Selected 1.0 (ip-10-0-1-224.ap-southeast-1.compute.internal)(SYNCED) as donor.
2016-05-19 23:24:13 2547 [Note] WSREP: Shifting PRIMARY → JOINER (TO: 2183133)
2016-05-19 23:24:13 2547 [Note] WSREP: Requesting state transfer: success, donor: 1
WSREP_SST: [INFO] WARNING: Stale temporary SST directory: /var/lib/mysql//.sst from previous state transfer (20160519 23:24:13.932)
WSREP_SST: [INFO] Proceeding with SST (20160519 23:24:13.934)
WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20160519 23:24:13.935)
WSREP_SST: [INFO] Cleaning the existing datadir and innodb-data/log directories (20160519 23:24:13.937)
removed /var/lib/mysql/auto.cnf' removed /var/lib/mysql/ib_logfile0’
removed directory: /var/lib/mysql/test' removed /var/lib/mysql/performance_schema/host_cache.frm’

removed /var/lib/mysql/performance_schema/setup_actors.frm' removed /var/lib/mysql/performance_schema/setup_instruments.frm’
removed directory: /var/lib/mysql/performance_schema' removed /var/lib/mysql/ib_logfile1’

removed /var/lib/mysql/mysql/proc.MYI' removed /var/lib/mysql/mysql/slave_worker_info.frm’
removed /var/lib/mysql/mysql/time_zone.MYI' removed directory: /var/lib/mysql/mysql’
removed `/var/lib/mysql/ibdata1’
WSREP_SST: [INFO] Waiting for SST streaming to complete! (20160519 23:24:13.964)
2016-05-19 23:24:15 2547 [Note] WSREP: (529594ab, ‘tcp://0.0.0.0:4567’) turning message relay requesting off
WSREP_SST: [ERROR] xtrabackup_checkpoints missing, failed innobackupex/SST on donor (20160519 23:24:23.935)
WSREP_SST: [ERROR] Cleanup after exit with status:2 (20160519 23:24:23.936)
2016-05-19 23:24:23 2547 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role ‘joiner’ --address ‘10.0.1.225’ --auth ‘sstuser:s3cret’ --datadir ‘/var/lib/mysql/’ --defaults-file ‘/etc/my.cnf’ --defaults-group-suffix ‘’ --parent ‘2547’ ‘’ : 2 (No such file or directory)
2016-05-19 23:24:23 2547 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
2016-05-19 23:24:23 2547 [ERROR] WSREP: SST failed: 2 (No such file or directory)
2016-05-19 23:24:23 2547 [ERROR] Aborting

2016-05-19 23:24:23 2547 [Warning] WSREP: 1.0 (ip-10-0-1-224.ap-southeast-1.compute.internal): State transfer to 0.0 (ip-10-0-1-225.ap-southeast-1.compute.internal) failed: -22 (Invalid argument)
2016-05-19 23:24:23 2547 [ERROR] WSREP: gcs/src/gcs_group.cpp:int gcs_group_handle_join_msg(gcs_group_t*, const gcs_recv_msg_t*)():731: Will never receive state. Need to abort.
2016-05-19 23:24:23 2547 [Note] WSREP: gcomm: terminating thread
2016-05-19 23:24:23 2547 [Note] WSREP: gcomm: joining thread
2016-05-19 23:24:23 2547 [Note] WSREP: gcomm: closing backend
2016-05-19 23:24:23 2547 [Note] WSREP: view(view_id(NON_PRIM,529594ab,51) memb {
529594ab,0
} joined {
} left {
} partitioned {
56560110,0
6e832bd2,0
})
2016-05-19 23:24:23 2547 [Note] WSREP: view((empty))
2016-05-19 23:24:23 2547 [Note] WSREP: gcomm: closed
2016-05-19 23:24:23 2547 [Note] WSREP: /usr/sbin/mysqld: Terminated.
160519 23:24:23 mysqld_safe mysqld from pid file /var/lib/mysql/ip-10-0-1-225.ap-southeast-1.compute.internal.pid ended

3

galera state on node2 and node3 are as below :

GALERA saved state

version: 2.1
uuid: e8e2feed-1753-11e6-bfe3-972d646081e6
seqno: -1
cert_index:

GALERA saved state

version: 2.1
uuid: e8e2feed-1753-11e6-bfe3-972d646081e6
seqno: -1
cert_index:
~
~

show global status like ‘wsrep_local_cached_downto’;
±--------------------------±--------+
| Variable_name | Value |
±--------------------------±--------+
| wsrep_local_cached_downto | 2523303 |
±--------------------------±--------+

show global status like ‘wsrep_local_cached_downto’;
±--------------------------±--------+
| Variable_name | Value |
±--------------------------±--------+
| wsrep_local_cached_downto | 2523303 |
±--------------------------±--------+