MariaDB Operator Failover: Clients Stuck?

Hey guys, have you ever run into a nasty situation where your MariaDB database suddenly goes read-only after a failover, and your client applications just… hang? I've been there, and it's a real headache. Let's dive into this frustrating issue with the MariaDB Operator, especially when you aren't using MaxScale. This guide will help you understand the problem, why it happens, and what you can do about it. So, let's get started!

The Heart of the Problem: Graceful Failover and Stuck Connections

So, you've got your MariaDB replication setup humming along, right? You've got your primary and some replicas, and everything is peachy… until a failover. Now, with the MariaDB Operator, we are expecting a smooth transition. That's the idea behind a 'graceful' failover – the operator is supposed to switch things over cleanly, promoting a replica to the new primary role. But what happens when things get… well, not so graceful? That's where the read-only connection problem comes in.

Basically, the MariaDB Operator does its job: it switches the primary, making the old primary a replica. But here's the kicker: your client applications, especially those using connection pooling, often don't get the memo. They keep chugging along, sending write requests to the old primary, which is now stubbornly read-only. Boom! Error 1290 – "The MariaDB server is running with the --read-only option…" And everything grinds to a halt. This is a common issue and one that has significant impact on availability and data integrity if not addressed. This is not just a nuisance; it's a potential data disaster. Imagine critical transactions failing silently, or worse, data diverging between your servers. That's the risk when clients remain connected to a read-only node.

Now, here's why this happens: Kubernetes conntrack, which manages network connections, keeps existing TCP connections mapped to the original pod, even after the service selector has changed. This is the core issue! Connection pools, which are designed to keep connections open for performance, end up holding onto these outdated connections, and it's a vicious cycle that leads to those write failures and outages. The applications are still attempting to write to the old primary, but the MariaDB server is no longer accepting write commands. The result is failed transactions and a frustrated development team. The good news is, by understanding the problem, we can find some workarounds or mitigation strategies.

The Role of Kubernetes Conntrack

Let’s briefly delve deeper into Kubernetes conntrack. Kubernetes uses conntrack (connection tracking) to manage network traffic. When a client connects to a service, conntrack keeps track of that connection. During a graceful failover, the MariaDB Operator changes the underlying pods that the service points to (e.g., changes the primary pod). However, conntrack doesn't automatically update existing connections. It continues to direct traffic to the old pod, even if that pod is now a replica and in read-only mode. This is the root cause of the problem. This behavior is by design, primarily for performance reasons.

Connection Pools: The Double-Edged Sword

Connection pools are another key player here. They’re great for performance because they avoid the overhead of establishing new database connections for every request. However, in this scenario, they work against us. Because the connections are long-lived, they tend to remain connected to the old primary unless specifically designed to handle failover scenarios. So, it's a double-edged sword – speed and efficiency on one side, and potential downtime on the other. This makes for a real mess.

Reproducing the Bug: Step-by-Step Guide

Alright, let's get hands-on. Here's how you can reproduce this bug in your environment. These steps will help you confirm the issue, so you know exactly what you are dealing with. Remember that the goal here is to experience the bug and understand the specific conditions that trigger it. So, let's go:

1. Set up Your MariaDB Replication Cluster: Start by deploying a MariaDB replication cluster. You'll need at least three nodes: one primary and two replicas. Make sure this cluster is up and running correctly and that replication is working as expected. You will need to install MariaDB operator using the installation method you prefer like Helm.
2. Deploy a Client Application with Connection Pooling: Next, deploy a client application that uses connection pooling. This could be any application that connects to your database and performs write operations. Important: Test with a non-privileged database user. Start the application and begin continuous write operations, such as inserting data into a table or updating existing records.
3. Trigger a Graceful Failover: This is where the magic happens (or, rather, the problem reveals itself). Using kubectl, trigger a graceful failover. You can do this by patching the MariaDB resource. The command below provides the exact patch: You'll be using `kubectl patch mariadb mariadb-cluster -n default --type=merge -p '{