PXC setup fails with SST script aborted with error 2 (No such file or directory)

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hello,

I am trying to configure 3 node pxc 8.0.30 on rhel8.
I am able to bootstrap the 1st node, but the joiner nodes fail with error.

2023-02-16T08:47:53.138095Z 0 [Note] [MY-000000] [WSREP-SST] Preparing the backup at /var/lib/mysql//sst-xb-tmpdir
2023-02-16T08:47:53.138223Z 0 [Note] [MY-000000] [WSREP-SST] (debug) Evaluating (@ Xtrabackup prepare stage) /usr/bin/pxc_extra/pxb-8.0/bin/xtrabackup --no-version-check --use-memory=3G --prepare $rebuildcmd $keyringapplyopt $encrypt_prepare_options --rollback-prepared-trx --xtrabackup-plugin-dir=/usr/bin/pxc_extra/pxb-8.0/lib/plugin --target-dir=${DATA} &>${DATA}/innobackup.prepare.log
2023-02-16T08:47:53.138478Z 0 [Note] [MY-000000] [Galera] Member 0.0 (abrv-0052.xxxxx.com) synced with group.
2023-02-16T08:47:53.160467Z 0 [ERROR] [MY-000000] [WSREP-SST] Cleanup after exit with status:2
2023-02-16T08:47:53.813201Z 0 [Note] [MY-000000] [Galera] Member 2.0 (abrv-0053.xxxxxx.com) requested state transfer from ‘any’. Selected 0.0 (abrv-0052.xxxxxx.com)(SYNCED) as donor.
2023-02-16T08:47:55.626976Z 0 [ERROR] [MY-000000] [WSREP] Process completed with error: wsrep_sst_xtrabackup-v2 --role ‘joiner’ --address ‘abrv-0054.xxxxxxx.com’ --datadir ‘/var/lib/mysql/’ --basedir ‘/usr/’ --defaults-file ‘/etc/my.cnf’ --defaults-group-suffix ‘’ --parent ‘28596’ --mysqld-version ‘8.0.30-22.1’ --binlog ‘mysql-bin’ : 2 (No such file or directory)
2023-02-16T08:47:55.627031Z 0 [ERROR] [MY-000000] [WSREP] Failed to read uuid:seqno from joiner script.
2023-02-16T08:47:55.627041Z 0 [ERROR] [MY-000000] [WSREP] SST script aborted with error 2 (No such file or directory)
2023-02-16T08:47:55.627143Z 3 [Note] [MY-000000] [Galera] Processing SST received
2023-02-16T08:47:55.627164Z 3 [Note] [MY-000000] [Galera] SST received: 00000000-0000-0000-0000-000000000000:-1
2023-02-16T08:47:55.627177Z 3 [System] [MY-000000] [WSREP] SST completed
2023-02-16T08:47:55.627341Z 2 [Note] [MY-000000] [Galera] str_proto_ver_: 3 sst_seqno_: -1 cc_seqno: 2 req->ist_len(): 77
2023-02-16T08:47:55.627379Z 2 [ERROR] [MY-000000] [Galera] Application received wrong state:
Received: 00000000-0000-0000-0000-000000000000
Required: 8b65986d-add6-11ed-a93b-f371aa0e8b7f
2023-02-16T08:47:55.627411Z 2 [ERROR] [MY-000000] [Galera] Application state transfer failed. This is unrecoverable condition, restart required.
2023-02-16T08:47:55.627425Z 2 [Note] [MY-000000] [Galera] ReplicatorSMM::abort()
2023-02-16T08:47:55.627441Z 2 [Note] [MY-000000] [Galera] Closing send monitor…
2023-02-16T08:47:55.627450Z 2 [Note] [MY-000000] [Galera] Closed send monitor.
2023-02-16T08:47:55.627460Z 2 [Note] [MY-000000] [Galera] gcomm: terminating thread
2023-02-16T08:47:55.627478Z 2 [Note] [MY-000000] [Galera] gcomm: joining thread
2023-02-16T08:47:55.627602Z 2 [Note] [MY-000000] [Galera] gcomm: closing backend
2023-02-16T08:47:55.628505Z 2 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node

I have spent almost 4 days now, trying to find the root cause.
Any help would be really appreciated.

2

Hi @ulka01

Could you post an anonymized version of your MySQL configuration file?

Thanks

Pep

3

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
default_authentication_plugin = mysql_native_password
early-plugin-load=keyring_file.so
port = 3306
bind-address = ‘0.0.0.0’
datadir = /var/lib/mysql
tmpdir = ‘/tmp’
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
sql_mode = NO_ENGINE_SUBSTITUTION

thread_stack = 524288
key_buffer_size= 52428800
tmp_table_size = 104857600
max_heap_table_size = 104857600
cte_max_recursion_depth = 1000
max_connections = 150
max_allowed_packet = 268435456
connect_timeout = 360
wait_timeout = 300

#validate_password.policy = MEDIUM
#validate_password.length = 8

wsrep_cluster_name = MYSQL-CLUSTER
wsrep_cluster_address= gcomm://abrv-0052.xxxx.com,abrv-0053.xxxx.com,abrv-0054.xxxxxx.com
wsrep_provider = /usr/lib64/galera4/libgalera_smm.so
pxc_encrypt_cluster_traffic = OFF
pxc_strict_mode= PERMISSIVE
wsrep_node_name = abrv-0053.xxxxx.com
wsrep_node_address = abrv-0053.xxxxx.com
wsrep_sst_method = xtrabackup-v2

sync_binlog = 1
binlog_format = ROW
log-bin = mysql-bin
binlog_expire_logs_seconds = 259200
max_binlog_size = 100M
server-id = 102

audit_log_policy = LOGINS
audit_log_format = JSON
audit_log_file = /var/log/mysql_audit/audit.log
audit_log_rotate_on_size = 100M
audit_log_rotations = 5
audit_log_exclude_accounts = ‘pmm_user@localhost’

slow_query_log = ON
long_query_time = 5

wsrep_slave_threads = 4

gtid_mode = ON
enforce_gtid_consistency

log_error = /var/log/mysqld.log
symbolic-links = 0

innodb_file_per_table = ON
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 3G
innodb_io_capacity = 2000
innodb_io_capacity_max = 2000
innodb_log_file_size = 100M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 100M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 100
innodb_autoinc_lock_mode = 2
innodb_thread_concurrency = 0

performance-schema-instrument=‘statement/%=ON’
performance-schema-consumer-statements-digest=ON

[mysqldump]
quick
max_allowed_packet = 268435456

[sst]
wsrep-debug=1

[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
log_error = /var/log/mysqld.log

!include /etc/my.cnf.d

4

Hello @ulka01,
Please add the parameter keyring_file_data to my.cnf on both servers with a proper path, even if it is the default path of /var/lib/mysql-keyring There is a known issue with this.

5

Hi @ulka01

As @matthewb said, you can fix the problem by providing a proper keyring configuration on each cluster server.

But you need to do this only if you want to configure any of the features that use the keyring, mainly encryption. You don’t need the keyring if you are not encrypting any of the database files. In that case, just remove the early-plugin-load=keyring_file.so line. And follow the instructions to uninstall a plugin that you can find here:
https://dev.mysql.com/doc/refman/8.0/en/plugin-loading.html

If you plan to encrypt the database files, I recommend you test the setup thoroughly before going into production. Losing the keyring can make your database unrecoverable.

You also need to take into consideration that the keyring_file plugin does not make your database compliant with security regulations. Quoting MySQL Documentation: “Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs)”

You can find all the information about keyring plugins and components here:
https://dev.mysql.com/doc/refman/8.0/en/keyring.html

I hope this will help you.

Pep

6

Yes and many thanks, removing the line early-plugin-load=keyring_file.so, got the cluster up and running.
But strange it works on other cluster with same version and OS.