Switchover

4.5.3.1 Switchover Configuration

switchover-at-equal-gtid (1.1), gtidcheck (0.7)

switchover-at-sync (1.1)

switchover-wait-kill (1.1), wait-kill (0.7)

switchover-wait-write-query (1.1), wait-write-query (0.7)

What is checked. First thing in a switchover, before anything is frozen or locked, replication-manager counts on the master the queries other than SELECT running for at least this many seconds, and the InnoDB transactions open for at least this long.

What happens. Nothing is locked at that point, so the application does not see the wait: the count is repeated every 2 seconds for up to switchover-wait-trx seconds, and the switchover goes on as soon as it reaches zero. Each pass logs one line per session, with its id, user, host, running time, transaction age, rows modified and rows locked, so you can see what the switchover is waiting for. Still there at the deadline: the switchover is cancelled with Long updates running on master. Cannot switchover.

While it waits, the cluster object of the API (GET /api/clusters/{name}) carries switchoverLongWriteWait with the master, the number of long writes, the start and the deadline of the wait; it is null otherwise. A switchover runs on the monitoring loop itself, so no state opens and no alert fires until it returns: the first tick after it opens the state WARN0217 with the outcome, "completed after N s, switchover proceeding" or "still running after switchover-wait-trx, switchover cancelled".

Why it is never killed. Killing a transaction starts a rollback whose duration nobody knows: proportional to the rows it modified, not interruptible, and resumed by InnoDB recovery if the server is restarted. That rollback would run on the server being demoted, holding its row locks while the new master's writes replicate onto it. Waiting or cancelling loses nothing; killing can cost an hour.

How to get past a cancel.

• Let the transaction finish and run the switchover again. The log lines above and the Top page show the session.
• Raise switchover-wait-trx so the switchover waits longer. Since 3.1.42 both settings are dynamic: Settings → Replication Failover → Switchover Long Write Threshold and Switchover Wait Transactions, or the settings API; before that release they live in the cluster configuration file and take a restart of replication-manager.
• Kill the session yourself only when the rows modified in the log line is small enough that its rollback is immediate.

A failover does not run this check: a failed master has no queries left to protect.

What follows the guard. The switchover then flushes the master's tables without a lock (FLUSH TABLES, so the later read lock is fast), also bounded by switchover-wait-trx, and freezes the master: read only, a grace period of switchover-wait-kill milliseconds for the writes still in flight, then every remaining client session is killed and the global read lock (FLUSH TABLES WITH READ LOCK) is taken before the candidate is promoted.

switchover-wait-trx (2.0), wait-trx (1.0)

switchover-slave-wait-catch (2.0)