Why SST Completed But MySQL Still Failed to Start

Nana Zedginidze

— 3 min read
Share

A few days ago, I came across an interesting recovery scenario while working with a Percona XtraDB Cluster.

At first, it looked like a straightforward SST recovery. One node had fallen out of the cluster, SST started automatically, the data transfer completed successfully... and yet MySQL still refused to start.

What initially looked like an SST failure turned out to be something completely different.

Here's how the investigation unfolded.

The Initial Situation

The affected node was no longer participating in the cluster.

Checking the Galera status showed:

SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status';
SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';
SHOW GLOBAL STATUS LIKE 'wsrep_ready';

wsrep_cluster_status       Disconnected
wsrep_local_state_comment  Inconsistent
wsrep_ready                OFF

At this point there wasn't much to do except bring the node back into the cluster.

SST Starts...

After restarting MySQL, something encouraging appeared in the error log:

Waiting for SST streaming to complete

Great.

Galera had determined that the node required a full State Snapshot Transfer (SST), so it automatically started copying the dataset from the donor node.

Everything seemed to be going exactly as expected.

Then, a few moments later, the recovery stopped.

The First Error... That Wasn't Really the Problem

The first message that caught my attention was:

Failed to start the MySQL server that checks for async replication.

Naturally, I assumed this was the reason the recovery had failed.

It wasn't.

This message was only telling me that the temporary MySQL instance used during SST post-processing couldn't start.

The real reason was buried much further down in the error log.

Reading Further Changed Everything

A little deeper I found:

Can't find key from keyring

Cannot get file password for encrypted replication log file

Can't init tc log

Aborting

That immediately shifted my focus away from SST.

The data transfer itself had already completed successfully.

The server was actually failing while trying to initialize encrypted replication metadata.

Comparing the Keyrings

At that point I decided to compare the keyring contents on both nodes.

Running:

strings /data/mysql-keyring/keyring | grep MySQLReplicationKey

revealed something interesting.

The required replication key existed on the donor node but was missing on the joiner.

Now the startup failure finally made sense.

Without that key, MySQL couldn't decrypt the encrypted replication log during startup.

The Plot Twist

My first instinct was simple.

If the key was missing, why not copy the keyring from the healthy node?

Unfortunately, that immediately introduced another error:

Encryption information in datafile ./ibdata1 can't be decrypted

For a moment, I thought I had made things worse.

Replacing the entire keyring solved the replication key problem, but now the node was missing the InnoDB encryption key required to open its existing tablespaces.

It looked like I had simply exchanged one problem for another.

...Until It Started Working

This turned out to be the most interesting part of the entire investigation.

Instead of stopping immediately, I kept watching the logs.

To my surprise, the recovery process continued.

SST finished its post-processing phase, installed the transferred state, and the node automatically continued with IST to catch up with the remaining transactions.

A few moments later:

SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status';

returned:

Primary

and

SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';

returned:

Synced

The node was fully back in the cluster.

What I Learned

This incident reminded me of a few things that are easy to forget during troubleshooting.

  • The first error you see is rarely the real problem.
  • A successful SST doesn't necessarily mean the recovery is finished.
  • The post-processing stage is just as important as the data transfer itself.
  • Keyring-related problems may only appear after SST has already completed.
  • When troubleshooting Galera, always read the MySQL error log all the way to the bottom before drawing conclusions.

One thing still puzzled me

As mentioned above, during the recovery I saw errors that looked fatal, including startup failures related to encrypted replication metadata. Yet, despite those messages, the node eventually completed SST, received IST, and successfully rejoined the cluster.

Final Thoughts

Looking back, the most valuable lesson wasn't related to SST itself.

It was a reminder that recovery is a sequence of multiple stages:

Node joins
    ↓
SST
    ↓
Post-processing
    ↓
MySQL startup
    ↓
IST
    ↓
Synced

It's easy to stop investigating as soon as SST finishes or when the first error appears.

In this case, neither of those turned out to be the actual root cause.

Sometimes the answer is simply hiding a little further down in the error log.

Read more