Schema Migrations, Health Checks, Failover Behavior, and Deployment Coordination

Learning outcomes

ServiceHub needs to rename a column, backfill millions of rows, deploy two application versions, and later move traffic after a primary failure. A schema migration is therefore not “run ALTER TABLE before deploy.” It is a distributed change across code, database metadata, data contents, connection pools, routing and rollback assumptions. The safe pattern is a compatibility window: old and new components overlap while both remain correct.

1. Expand/contract creates a deliberate compatibility window

Suppose ServiceHub wants to replace status_text with a constrained status_code. A one-step rename breaks whichever application version deploys second. Expand/contract instead adds the new representation, makes application code temporarily tolerate/write both, backfills historical rows, verifies convergence, switches reads, and only later removes the legacy representation.

2. Build the migration lab with evidence-first DDL

DROP DATABASE IF EXISTS servicehub19_l5;CREATE DATABASE servicehub19_l5 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub19_l5;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,  status_text VARCHAR(20) NOT NULL,  subject VARCHAR(180) NOT NULL,  updated_at DATETIME(6) NOT NULL,  INDEX ix_updated(updated_at)) ENGINE=InnoDB;INSERT INTO tickets(status_text,subject,updated_at)VALUES ('open','A','2026-08-20 10:00:00'),       ('closed','B','2026-08-20 11:00:00'),       ('waiting','C','2026-08-20 12:00:00');SELECT VERSION();SHOW CREATE TABLE tickets;SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTHFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub19_l5' AND TABLE_NAME='tickets';

ALTER TABLE tickets  ADD COLUMN status_code TINYINT NULL,  ALGORITHM=INSTANT,  LOCK=NONE;SHOW CREATE TABLE tickets;

If the requested ALGORITHM/LOCK combination is unsupported on your exact version/table shape, MariaDB should reject rather than silently violating the requested guarantee. Re-plan using the target version’s ALTER documentation and a disposable copy. “Online” does not mean zero metadata locking, zero temporary space, zero replication/Galera effect, or zero rollback cost.

3. Backfill in deterministic bounded chunks

UPDATE ticketsSET status_code = CASE status_text  WHEN 'open' THEN 1 WHEN 'waiting' THEN 2 WHEN 'closed' THEN 3 ENDWHERE ticket_id > ? AND ticket_id <= ?  AND status_code IS NULL;-- Acceptance evidence after all chunks:SELECT COUNT(*) AS missingFROM tickets WHERE status_code IS NULL;SELECT status_text, status_code, COUNT(*)FROM tickets GROUP BY status_text, status_code;

The application or migration worker persists the last completed key/range outside the transaction so it can pause safely. Monitor transaction duration, lock waits, redo/binlog volume and replica/Galera health while backfilling. A fixed chunk size is only a starting point; adjust from measured impact.

4. Liveness, readiness and database-role checks

SELECT 1 AS sql_round_trip,       @@hostname AS server_host,       @@server_id AS server_id,       @@read_only AS read_only,       DATABASE() AS selected_database,       VERSION() AS server_version;-- On Galera nodes, also inspect the exact wsrep state required by your routing policy:SHOW STATUS LIKE 'wsrep_ready';SHOW STATUS LIKE 'wsrep_connected';SHOW STATUS LIKE 'wsrep_local_state_comment';

Liveness asks whether the process should be restarted. Readiness asks whether this instance should receive traffic. A database dependency can make an endpoint unready without meaning the process itself is dead. For a write service, a reachable read-only replica may be unhealthy for writes even though SELECT 1 succeeds.

5. Wrong approach: assume existing pool sockets follow failover

Changing DNS, a configuration value, or a proxy backend does not necessarily move connections already open in an application pool. Existing TCP sessions can remain attached to the old node until they fail or are recycled. A failover-aware deployment must define how endpoint change is detected, how new connections are validated, how the old pool drains, and what happens to in-flight transactions.

const mariadb = require('mariadb');let activePool;function makePool(cfg) {  return mariadb.createPool({ ...cfg, connectionLimit: 6, connectTimeout: 3000 });}async function verifyWritable(pool) {  const conn = await pool.getConnection();  try {    const [r] = await conn.query(      `SELECT @@hostname AS host, @@server_id AS sid, @@read_only AS ro`    );    if (Number(r.ro) !== 0) throw new Error('target is read-only');    return r;  } finally { conn.release(); }}async function rotatePool(newCfg) {  const candidate = makePool(newCfg);  try {    const evidence = await verifyWritable(candidate);    const old = activePool;    activePool = candidate;    if (old) await old.end();    return evidence;  } catch (e) {    await candidate.end();    throw e;  }}

This verifies only a routing/application contract. It does not promote a replica, fence an old primary, prove GTID convergence, establish Galera quorum, or prevent split brain. Those are database/HA control-plane responsibilities from Chapters 14 and 15.

6. Free local routing-change lab

# Docker or Podman syntax may differ slightly by platform.docker run -d --name m19a -p 33191:3306 -e MARIADB_ROOT_PASSWORD=labroot mariadb:12.3docker run -d --name m19b -p 33192:3306 -e MARIADB_ROOT_PASSWORD=labroot mariadb:12.3# Wait for both servers, then seed the same minimal schema/account on each.# Point the application pool first at 127.0.0.1:33191 and capture @@server_id/@@hostname.# Stop m19a, rotate the application config to 127.0.0.1:33192, recreate/verify the pool,# and record the new server identity plus request failures during the transition.docker stop m19a# ...call rotatePool({host:'127.0.0.1', port:33192, ...})...docker rm -f m19a m19b

CREATE DATABASE IF NOT EXISTS servicehub19_route CHARACTER SET utf8mb4;DROP USER IF EXISTS 'svc19_route'@'%';CREATE USER 'svc19_route'@'%' IDENTIFIED BY 'route-lab-only';GRANT SELECT ON servicehub19_route.* TO 'svc19_route'@'%';SELECT @@hostname AS host, @@server_id AS sid, @@read_only AS ro;

Use svc19_route only in this disposable topology and pass its password through local environment/configuration, not source control. For the routing-mechanics probe, set database: 'servicehub19_route' in the pool configuration. The @'%' host is used only to keep this disposable two-container routing lab portable; do not copy wildcard-host accounts into production. Narrow account hosts/network paths there.

7. Deployment ordering and acceptance gates

A safe release pipeline encodes dependencies instead of relying on human memory. A typical order is: preflight/version/backup → expand DDL → deploy compatibility-capable app → backfill in bounded chunks → verify data parity → switch reads/feature flag → observe → deploy versions that no longer use legacy schema → contract later. If any step makes rollback impossible, make that a named gate with recovery evidence.

8. Reproducible cleanup and checks

SELECT COUNT(*) AS unmappedFROM servicehub19_l5.ticketsWHERE status_code IS NULL;-- Only after the compatibility window is complete in the lab:ALTER TABLE servicehub19_l5.tickets DROP COLUMN status_text;SHOW CREATE TABLE servicehub19_l5.tickets;DROP DATABASE IF EXISTS servicehub19_l5;

Production judgment and bridge to Chapter 20

Treat schema change and failover as rehearsed protocols with explicit compatibility windows, gates and evidence. Migration tooling may automate commands, but it cannot decide whether a table rewrite fits the maintenance window or whether an old primary is safely fenced. Application health checks must reflect role semantics, and pool refresh must be part of routing change. Chapter 20 builds on this deployment discipline for maintenance updates, LTS/current release upgrades, mariadb-upgrade, compatibility testing, rolling replication/Galera upgrades and rollback limits.

Deployment choreography: schema, application, and routing must overlap safely

A low-risk deployment is a compatibility window rather than a single instant. During an expand/contract change, old and new application versions may run at the same time because of rolling deployment, retries, background workers, or delayed rollback. The schema must therefore support both versions until traffic and asynchronous work have drained from the old contract. Backfills should be resumable and observable, and the final constraint or column removal belongs in a later deployment after evidence shows the legacy path is unused.

Health checks should test the contract they claim to represent. Liveness answers whether the process should be restarted; readiness answers whether it can safely receive traffic. A readiness check may validate pool acquisition and a cheap database operation, but it should not perform expensive business queries or mutate data. After failover, readiness also needs to reflect whether the application has refreshed stale pooled connections and whether the destination node is in the intended write/read role.

Coordinate migration locks, application rollout, pool refresh, and rollback in one runbook. A schema change that is technically online can still wait on metadata locks or generate I/O/replication pressure, while an application rollback may become impossible after a destructive contract step. Explicit gates turn those hidden dependencies into observable deployment decisions.

Authoritative references

Primary references are current MariaDB documentation or official connector source; verify the target server/connector version before relying on defaults or option behavior.

• MariaDB ALTER TABLE
• MariaDB metadata locking
• MariaDB standard replication
• MariaDB Galera Cluster documentation
• MariaDB Connector/Node.js Promise API