Are you planning an upgrade for your Percona XtraDB Cluster (PXC)? Upgrading to PXC 8.0 can be a smooth process, but sometimes challenges arise that require careful troubleshooting. In this blog post, we will talk about 2 specific issues that you might face during an upgrade of Percona XtraDB Cluster or Galera.
It is possible that you might already know of these as it’s been already been blogged about but consider this as a refresher.
Upgrading from PXC 5.7 to PXC 8.0
Upgrading from PXC 5.7 to PXC 8.0 or Galera MySQL 8 typically follows a straightforward plan:
- Backup Cluster Data: Begin by backing up your cluster data from one node to ensure data safety.
- Shutdown Cluster Nodes: Temporarily shut down all cluster nodes to prepare for the upgrade.
- Perform MySQL Upgrade: Upgrade MySQL on each node one at a time to the target version.
- Bootstrap the Cluster: After upgrading, it’s time to bootstrap the cluster.
Note that you need to be prepared well and have performed proper upgrade testing before you migrate to the newer version. The Percona tool, pt-upgrade and MySQL’s upgrade checker utility are two amazing tools to help you with upgrading to MySQL 8. This blog is not exactly about How To upgrade but about the issues faced during an upgrade process.
Issue 1 – Bootstrapping PXC doesn’t bring up the cluster node
One common issue encountered during the upgrade is related to bootstrapping the Percona XtraDB Cluster. After upgrading PXC binaries and executing the bootstrap command the cluster not failed to start:
root@production: mysql # systemctl start mysql@bootstrap Job for firstname.lastname@example.org failed because a timeout was exceeded. See "systemctl status email@example.com" and "journalctl -xe" for details.
MySQL error log
2023-07-13T09:24:04.598299Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/lib/mysql/mysqlx.sock 2023-07-13T09:24:04.598348Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32-24.2' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona XtraDB Cluster (GPL), Release rel24, Revision 2119e75, WSREP version 18.104.22.168. 2023-07-13T09:24:04.598439Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.32-24.2). 2023-07-13T09:24:04.598506Z 0 [Note] [MY-000000] [WSREP] Received shutdown signal. Will sleep for 10 secs before initiating shutdown. pxc_maint_mode switched to SHUTDOWN … 2023-07-13T10:14:43.389622Z 0 [Note] [MY-000000] [Galera] announce period timed out (pc.announce_timeout) … 2023-07-13T10:15:13.402826Z 0 [ERROR] [MY-000000] [Galera] failed to open gcomm backend connection: 110: failed to reach primary view (pc.wait_prim_timeout): 110 (Connection timed out) at gcomm/src/pc.cpp:connect():161 2023-07-13T10:15:13.402836Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs_core.cpp:gcs_core_open():219: Failed to open backend connection: -110 (Connection timed out) 2023-07-13T10:15:14.403073Z 0 [Note] [MY-000000] [Galera] gcomm: terminating thread 2023-07-13T10:15:14.403130Z 0 [Note] [MY-000000] [Galera] gcomm: joining thread 2023-07-13T10:15:14.403354Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs.cpp:gcs_open():1811: Failed to open channel 'prod-db' at 'gcomm://10.10.10.10,10.10.10.11,10.10.10.12': -110 (Con nection timed out) 2023-07-13T10:15:14.403381Z 0 [ERROR] [MY-000000] [Galera] gcs connect failed: Connection timed out 2023-07-13T10:15:14.403401Z 0 [ERROR] [MY-000000] [WSREP] Provider/Node (gcomm://10.10.10.10,10.10.10.11,10.10.10.12) failed to establish connection with cluster (reason: 7) 2023-07-13T10:15:14.403417Z 0 [ERROR] [MY-010119] [Server] Aborting
The kernel log
[prod] percona@production: firstname.lastname@example.org $ sudo grep bootstrap /var/log/messages | grep -v pmm ... Jul 13 10:37:15 production systemd: Starting Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap... Jul 13 10:38:45 production systemd: email@example.com start-pre operation timed out. Terminating. Jul 13 10:38:51 production systemd: Failed to start Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap. Jul 13 10:38:51 production systemd: Unit firstname.lastname@example.org entered failed state.
Observing carefully all of them points to “timeout”. The Percona XtraDB Cluster couldn’t start before TimeoutStartSec (90s) and hence it was terminated.
Fixing Percona XtraDB Cluster Timeout Error
systemctl edit mysql@bootstrap [Service] TimeoutStartSec=6000 $ systemctl daemon-reload $ systemctl show mysql.service -p TimeoutStartUSec TimeoutStartUSec=1h 40min
After the change in service config, MySQL (Percona XtraDB Cluster node) started cleanly with the bootstrap command.
Issue 2 – PXC Cluster node doesn’t join cluster
To get the other nodes join the bootstrapped Percona XtraDB Cluster
[13/07/2023, 4:58:24,911 PM] [prod] root@production: mysql # systemctl start mysql
[13/07/2023, 4:58:24,912 PM] Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
The error output has following hints
[Galera] Handshake failed: tlsv1 alert unknown ca [Galera] handshake with remote endpoint ssl://10.0.0.1:4567 failed: asio.ssl:336031996: 'unknown protocol' ( 336031996: 'error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol')
2023-07-13T13:04:13.369141Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32-24.2) starting as process 8662 2023-07-13T13:04:13.381273Z 0 [ERROR] [MY-000059] [Server] SSL error: Unable to get private key from 'server-key.pem'. 2023-07-13T13:04:13.381316Z 0 [Warning] [MY-013595] [Server] Failed to initialize TLS for channel: mysql_main. See below for the description of exact issue. 2023-07-13T13:04:13.381333Z 0 [Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: Unable to get private key 2023-07-13T13:04:13.381340Z 0 [Note] [MY-000000] [WSREP] New joining cluster node configured to use specified SSL artifacts 2023-07-13T13:04:13.381379Z 0 [Note] [MY-000000] [Galera] Loading provider /usr/lib64/galera4/libgalera_smm.so initial position: f0bc6f23-11bd-11ec-a9f8-8f101bd9a1be:2783648882 2023-07-13T13:04:13.381394Z 0 [Note] [MY-000000] [Galera] wsrep_load(): loading provider library '/usr/lib64/galera4/libgalera_smm.so'
This is a reminder that Percona XtraDB Cluster 8.0 by-default enables the
pxc-encrypt-cluster-traffic and with that encrypting SST, IST, and replication traffic.
pxc-encrypt-cluster-traffic is non-dynamic change and will require MySQL restart. It is important to ensure verifying this change while preparing for upgrades for seamless experience.
Fixing SSL Handshake Failures in PXC 8.0
You may either disable the pxc-encrypt-cluster-traffic mode (joking) OR preferably create / use the existing pem files to ship across the cluster.
- Bootstrap the Percona XtraDB Cluster node with SSL keys.
- Transfer key files from the bootstrapped node to the remaining cluster nodes.
- Start the remaining nodes to have them join the cluster successfully.
There’s an extensive blog I recommend reading by Marco on Percona Blog.
Alright, there ends this reminder blog for the upgrade to Percona XtraDB Cluster for MySQL 8.0. But before you go, there is one other issues at MySQL 8 upgrade that you must not forget and one of such corner case is about default for timestamp columns change in MySQL 8. This is an interesting use-case of handling an issue after the upgrade is done.