12. Multi-node replication (Cluster edition)

12.1 Overview#

The Cluster edition of MIRAJ adds multi-node replication to the engine: a primary server receives writes, and one or more secondary servers continuously receive the primary's log, replay it, and remain available for reads by client applications.

Principles:

• A single primary at a time. It executes all statements that write (DML, DDL, accounts) and logs each of them.
• Read-only secondaries, which replay the primary's log in the same order and with the same sequence numbers (LSN). A client can connect to them as to any MIRAJ server, on the usual client port (7007 by default), and run reads there.
• Asynchronous replication by default, semi-synchronous by choice: by default the primary does not wait for the secondaries before returning control to the writing client (see the limitations, §12.7); a session, or the whole server, can request that a write be confirmed only once it has been received (or applied) by one or more secondaries (§12.8).
• Manual, controlled promotion, with no automatic election or consensus: the administrator designates the primary, when the cluster starts and during a switchover; the promoted node first checks with its peers that the switchover is safe and retrieves whatever the most advanced secondary has received beyond what it has itself (§12.4.1).

What this provides:

• High availability for reads: if the primary becomes unavailable, the secondaries keep answering reads, and one of them can be promoted to primary to resume writes.
• Read load balancing: reports, exports, or dashboards can query one or more secondaries without weighing on the server that handles writes.

What the Cluster edition does not provide as it stands: there is no distribution of data across several nodes (each node carries a complete copy of every database), no automatic primary failover, and no consensus between nodes. These topics belong to a later product roadmap and are not covered by this chapter (see the chapter on known limitations for what is currently out of scope).

12.2 Configuration#

12.2.1 The cluster.toml file#

A cluster node is configured by a cluster.toml file, read by an in-house parser for a subset of TOML ([node] and [cluster] tables, bare keys, quoted strings, integers, booleans, single-line arrays of strings, # comments). By default, MIRAJ looks for cluster.toml next to the miraj-server executable; the --cluster-config <file> option lets you specify another one.

Without a cluster.toml file, a MIRAJ server behaves exactly as it does outside a cluster: no inter-node port is opened, nothing is logged for replication, and the behavior is strictly that of the edition without replication.

Commented example:

[node]
id = "n1"                    # stable node identifier (letters, digits, _ and $, at most 64 characters)
listen = "0.0.0.0:7107"      # inter-node listening port: distinct from the client port (7007 by default)
advertise = "10.0.0.1:7107"  # address advertised to the other nodes (default: listen)

[cluster]
name = "gestium-prod"                            # cluster name: a node from another cluster is refused at connection
seeds = ["10.0.0.1:7107", "10.0.0.2:7107"]       # peer addresses (the node's own advertised address is ignored in this list)
tls_cert = "node.pem"        # this node's certificate, paths relative to cluster.toml
tls_key = "node-key.pem"     # this node's private key
tls_ca = "cluster-ca.pem"    # authority that signed all the certificates of the cluster's nodes

# Optional (default values shown)
ack = "written"              # "written" (the secondary has written the batch to its log) or "durable"
                              # (the secondary has also flushed it to disk) before acknowledging receipt
journal_retention_mb = 1024  # beyond this, a secondary that has been unreachable for too long is re-seeded
                              # by a full copy of the databases rather than caught up from the log
max_promotion_lag_mb = 0     # 0: unlimited; otherwise, promotion is refused (except 'force_primary') if the
                              # candidate, after catching up, remains more than N MiB behind the last
                              # known position of the primary
promotion_catchup_mb = 64    # log retained by each secondary to serve the catch-up of a
                              # promoted peer (0: no retention)
sync_commit = "off"          # semi-synchronous replication (§12.8): "off", "received", "applied" or
                              # "majority", initial value of @@GLOBAL.cluster_sync_commit
sync_replicas = 1            # secondary acknowledgements required per write
sync_timeout_ms = 10000      # maximum wait for a write, in milliseconds
sync_timeout_action = "fallback"  # on timeout: "fallback", "error" or "wait"
mode = "manual"              # "manual" (primary designated by the administrator) or "raft" (elected, §12.9)
election_timeout_ms = 3000   # raft mode: delay without word from the leader before standing as candidate (≥ 500)
test_hooks = false           # test hooks (node isolation): never in production

[node] table:

[cluster] table:

At a node's first startup (no cluster state recorded), all nodes start as secondaries: a cluster never starts with two primaries by default. It is up to the administrator to designate the primary once (§12.3).

12.2.2 Mutual TLS certificates#

Nodes talk to each other only over mutual TLS: each node presents to the others a certificate signed by the cluster authority (tls_ca), and likewise verifies the certificate of each peer it connects to or that connects to it. Each node's certificate must carry its advertise address (DNS name or IP address) in its SAN (subjectAltName), and an extended key usage covering both serverAuth and clientAuth (a node is both a TLS server and a TLS client with respect to its peers).

The miraj-cluster-pki tool. MIRAJ provides a small command-line tool, independent of the server, that creates the cluster authority and signs the nodes' certificates (ECDSA P-256 keys, without openssl):

# Once only, on the administration workstation
miraj-cluster-pki init-ca --name gestium-prod --out pki

# For each node, with its advertised address (`advertise`) as --san (repeatable: IP or DNS name)
miraj-cluster-pki issue-node --ca pki --id n1 --san 10.0.0.1 --san n1.exemple.local --out pki/n1
miraj-cluster-pki issue-node --ca pki --id n2 --san 10.0.0.2 --out pki/n2
miraj-cluster-pki issue-node --ca pki --id n3 --san 10.0.0.3 --out pki/n3

init-ca produces cluster-ca.pem and cluster-ca-key.pem (10-year validity by default, --days to change it); issue-node produces node.pem, node-key.pem and a copy of cluster-ca.pem (825-day validity by default), ready to be copied into the node's cluster.toml folder. Keep cluster-ca-key.pem off the nodes: it signs the certificates, and only cluster-ca.pem is distributed. An existing file is never overwritten without --force.

You can also use your own PKI (an existing enterprise authority). For reference, here is the equivalent with openssl (EC P-256 keys) — to be adapted with your own keys and a reasonable validity period, these example values not being intended for real use:

# Cluster authority
openssl genpkey -algorithm EC -pkeyopt ec_paramgen_curve:P-256 -out cluster-ca-key.pem
openssl req -new -x509 -key cluster-ca-key.pem -out cluster-ca.pem -days 3650 -sha256 \
  -subj "/CN=Autorité du cluster gestium-prod" \
  -addext "basicConstraints=critical,CA:TRUE" \
  -addext "keyUsage=critical,keyCertSign,cRLSign"

# Certificate for one node (repeat for each node, with its advertised address in the SAN)
openssl genpkey -algorithm EC -pkeyopt ec_paramgen_curve:P-256 -out node-key.pem
openssl req -new -key node-key.pem -subj "/CN=n1" -out node.csr
openssl x509 -req -in node.csr -CA cluster-ca.pem -CAkey cluster-ca-key.pem -CAcreateserial \
  -days 825 -sha256 -out node.pem \
  -extfile <(printf "basicConstraints=critical,CA:FALSE\nkeyUsage=critical,digitalSignature,keyEncipherment\nextendedKeyUsage=serverAuth,clientAuth\nsubjectAltName=IP:10.0.0.1,DNS:n1.exemple.local\n")
rm node.csr

Then distribute cluster-ca.pem to all nodes, and to each node its own node.pem/node-key.pem. A certificate signed by an authority other than the one given in tls_ca is refused at the inter-node handshake.

12.3 Step-by-step setup#

1. Prepare the certificates for each node (§12.2.2) and one cluster.toml file per node, with the same [cluster] name, the same tls_ca, and a seeds list covering the other nodes.

1. Start each server with --cluster-config (or by placing cluster.toml next to the executable), and always with --log:

    miraj-server --root data-n1 --port 7007 --cluster-config n1/cluster.toml --log
    miraj-server --root data-n2 --port 7007 --cluster-config n2/cluster.toml --log
    miraj-server --root data-n3 --port 7007 --cluster-config n3/cluster.toml --log

   At startup, each node with no prior state presents itself as a secondary and tries to reach its seeds over mutual TLS.

1. Designate the primary, once only, by connecting to the node chosen as primary (any SQL client):

    SET GLOBAL cluster_role = 'primary';

   This node lifts its read-only mode, now logs its DDL, and presents itself to the others as primary of a new epoch (@@cluster_epoch).

   The command returns before promotion is complete (§12.4.1): a write sent immediately afterward receives error 1290. Wait until SELECT @@cluster_role returns PRIMARY before creating databases or writing.

1. Have the secondaries join: the other nodes, already secondaries by default, connect to the primary as soon as they find it among their seeds. If they have no database in common with it (new node) or a log that is too far behind, they are seeded automatically by a full copy of the primary's databases, then catch up with the log stream.

1. Check the cluster state:

    SHOW CLUSTER STATUS;

    +---------+----------------+-----------+-----------+-------+-----------+-------------+---------------------+------------+------+
    | NODE_ID | ADDRESS        | ROLE      | STATE     | EPOCH | LAG_BYTES | LAG_SECONDS | CONNECTED_SINCE     | LAST_ERROR | SYNC |
    +---------+----------------+-----------+-----------+-------+-----------+-------------+---------------------+------------+------+
    | n1      | 10.0.0.1:7107  | PRIMARY   | SELF      |     1 |         0 | NULL        | NULL                |            | OFF  |
    | n2      | 10.0.0.2:7107  | SECONDARY | CONNECTED |     1 |         0 | 0.021       | 2026-09-23 10:04:12 |            | YES  |
    | n3      | 10.0.0.3:7107  | SECONDARY | CONNECTED |     1 |       512 | 0.048       | 2026-09-23 10:04:15 |            | YES  |
    +---------+----------------+-----------+-----------+-------+-----------+-------------+---------------------+------------+------+

   STATE is SELF for the queried node itself (PROMOTING during its promotion), then CONNECTING, BOOTSTRAPPING, CONNECTED, DISCONNECTED, REBOOTSTRAP_NEEDED, FENCED, REACHABLE or UNREACHABLE for the other nodes as seen by it. LAG_BYTES drops to 0 once the secondary is up to date; this value differs from one node to another since each knows only its own direct peers.

12.4 Switchover#

12.4.1 Promoting a secondary#

On the chosen secondary:

SET GLOBAL cluster_role = 'primary';

Promotion is controlled: before returning, the statement queries all the peers in seeds (a few seconds at most, in parallel) and refuses promotion, with error 9003 (Promotion refused: …, reason in plain text), in the following cases:

An unreachable peer does not prevent promotion: this is the normal case after the loss of the primary. It is merely logged.

If promotion is accepted, the client immediately receives OK, and the rest proceeds in the background (@@cluster_role changes to PRIMARY at the end; the node's row in SHOW CLUSTER STATUS is in the PROMOTING state in the meantime):

1. The link with the former primary is cut.
2. Catch-up: if another secondary has received more of the former primary's log, the node asks it for what it is missing, database by database (log, long values, DDL included). Secondaries of the same primary have identical logs up to the position: the catch-up is exact. It concerns the most advanced secondary (just one); each secondary keeps for this purpose the last promotion_catchup_mb MiB of its log.
3. The applier finishes applying everything received.
4. A new epoch (@@cluster_epoch + 1) is written into the node's state.
5. The node lifts its read-only mode, restores the event scheduler (stopped while it was a secondary) and now presents itself as primary of this new epoch; it keeps its log from the position of the other probed secondaries, so that they join it through the stream rather than through seeding.
6. The other secondaries, as soon as their link with the former primary is broken (or their PINGs fail), join among their seeds the primary of the highest epoch.

If the catch-up fails (peer became unreachable, damaged local log…), promotion is abandoned: the node remains a secondary and starts following a primary again, the reason is logged in the server log and shown in the LAST_ERROR column of its own row in SHOW CLUSTER STATUS. You must therefore check @@cluster_role after the command.

Forced promotion. When the loss is accepted knowingly (most advanced peer permanently lost, lag above the threshold, primary unreachable for this node but not for the others…):

SET GLOBAL cluster_role = 'force_primary';

Each refusal becomes a logged warning (lines Cluster : promotion de … (forcée) : …), the catch-up is attempted when possible and its failure does not stop promotion. If the former primary is still active, it is fenced off as soon as it sees the higher epoch (§12.4.2): its writes from the last moments are not silently lost, they are quarantined when it is demoted.

There is no automatic election: it is the administrator who chooses the node to promote and runs the command.

12.4.2 Return of a former primary#

A node that believed itself primary and that receives from a peer proof of a more recent epoch than its own moves to the FENCED state ("fenced off"):

• it goes back to read-only mode (any client write receives error 1290);
• @@cluster_role is FENCED on this node;
• the incident is logged in the server log (--log).

A FENCED node does not join the new primary automatically. To put it back in service as a secondary:

SET GLOBAL cluster_role = 'secondary';

From there:

• if it has databases whose log does not go beyond the point where the switchover took place, it simply catches up with the new primary's stream;
• if it has local writes that nobody else received (because it kept accepting writes after the cut, before restarting and discovering it was fenced off), those databases are re-seeded: their local log is moved to miraj/quarantaine/<timestamp>/<database>/, with a report, then the database is copied again from the new primary. No unreplicated write is therefore silently lost — it remains viewable in its quarantine folder, but it is no longer in the active database.

Recommended full procedure after the loss of the primary (for a planned switchover, see §12.4.3):

1. Confirm that the former primary is stopped or unreachable.
2. Promote the chosen secondary (SET GLOBAL cluster_role = 'primary', see §12.4.4 for choosing it).
3. Repoint the client applications to the new primary's address (MIRAJ does not do it for them, see §12.6).
4. Restart the former primary: it starts with the state it had, connects to its seeds, and discovers it is FENCED as soon as it sees the more recent epoch.
5. Check SHOW CLUSTER STATUS: the node appears as FENCED.
6. SET GLOBAL cluster_role = 'secondary' on this node to have it join: it catches up or re-seeds as appropriate, and its quarantine folder, if any, can be examined then archived or deleted by the administrator.

12.4.3 Planned switchover without loss#

To change primary without losing anything (server maintenance, migration):

1. Stop the applications' writes, or accept that they receive error 1290 during the switchover.
2. On the current primary: SET GLOBAL cluster_role = 'secondary'. It goes back to read-only mode, cuts its secondaries and keeps its log.
3. Check SELECT @@cluster_role on this node (SECONDARY).
4. On the chosen secondary: SET GLOBAL cluster_role = 'primary'. The probe finds the former primary as a secondary; if it wrote transactions that the candidate had not yet received, the candidate catches them up from it before being promoted.
5. Repoint the applications to the new primary.

The former primary and the other secondaries then join the new primary through the log stream, with no re-seeding or quarantine.

12.4.4 Choosing the secondary to promote#

As long as a secondary has no primary, it polls its peers every 10 seconds: on each one, SHOW CLUSTER STATUS shows the other nodes in the REACHABLE state (with their role, their epoch and, in LAG_BYTES, their lag behind the last position announced by the primary) or UNREACHABLE. Its own row (SELF) gives its own lag; information_schema.MIRAJ_REPLICATION details it per database (WRITTEN_LSN and PRIMARY_LSN).

It is not necessary to choose the most advanced secondary: the promoted node itself catches up with the most advanced of its reachable peers. It is better to choose the node best placed to receive writes (network, capacity), provided the other secondaries are reachable at the time of promotion. With promotion_catchup_mb = 0, no secondary keeps enough to serve a catch-up: promoting a secondary that is behind a peer is then refused, and you must promote the most advanced one (or force, accepting the loss).

12.5 Monitoring and supervision#

12.5.1 System variables#

12.5.2 SHOW CLUSTER STATUS and information_schema.MIRAJ_NODES#

SHOW CLUSTER STATUS returns exactly the columns of information_schema.MIRAJ_NODES, one row per node known to the queried server (REPLICATION CLIENT or PROCESS privilege):

12.5.3 information_schema.MIRAJ_REPLICATION#

Detail per node and per database:

Example:

SELECT NODE_ID, `DATABASE`, WRITTEN_LSN, APPLIED_LSN
FROM information_schema.MIRAJ_REPLICATION
WHERE `DATABASE` = 'gestium_prod'
ORDER BY NODE_ID;

12.5.4 Logging#

With --log (always recommended, see the chapter on server startup), the info level receives the connection and disconnection of each peer, the start and end of a seeding, and each promotion; the server log (file) receives every replication error: handshake refusal (different cluster name, foreign TLS authority, incompatible edition), log sequence break, failure to replay a DDL, a secondary moving to the "to be re-seeded" state, or a table rewritten outside the log by the primary (re-seeding requested by the secondary, §12.7).

Lines specific to promotion:

Lines specific to semi-synchronous replication (§12.8), on the primary, at state changes only (never one line per write):

12.6 Behavior for application clients#

• Reading on a secondary: a client connects to a secondary exactly as to any MIRAJ server, on its usual client port (7007 by default) — no protocol or tool change on the client side.
• Writing on a secondary: any statement that writes (DML, DDL, account management) receives error 1290 (ER_OPTION_PREVENTS_STATEMENT, message "the server is running with the read_only option so it cannot execute this statement"), the code that drivers and load balancers usually recognize in order to redirect a write to the primary. The following remain allowed on a secondary: all reads, the session's temporary tables, SET, START TRANSACTION/COMMIT/ROLLBACK (without writes), KILL, FLUSH, LOCK TABLES … READ, CHECK TABLE and REPAIR TABLE (local to the secondary).
• Recommended application strategy: write only to the primary, known through configuration on the application side (MIRAJ provides no mechanism for automatic primary discovery or for repointing clients — this is outside the engine's scope, see §12.7); spread the reads that tolerate a slight lag (LAG_SECONDS) across one or more secondaries; on receiving error 1290, consider that the contacted node is no longer (or not) the primary and reconnect to the expected primary, querying if needed SHOW CLUSTER STATUS or @@cluster_primary on a node known to the cluster to find it again.

12.7 Known current limitations#

These points are established in the engine's code and internal design documentation, not future intentions:

• Asynchronous replication by default. With @@cluster_sync_commit = OFF (the default value), the primary does not consult the secondaries before returning control to the writing client. A write confirmed to the client may therefore not yet have reached any secondary; if the primary is lost at that moment, this write remains on the former primary, quarantined when it returns, and is never automatically replayed on the new primary. The catch-up at promotion reduces this loss window to writes received by no reachable secondary (rather than those that only the promoted node had not received). Semi-synchronous replication (§12.8) closes it for writes confirmed without a warning.
• Semi-synchronous, visibility before acknowledgement. In semi-synchronous replication, a write is committed and visible to the primary's other sessions before the acknowledgements arrive; an exceeded timeout, KILL QUERY or a demotion during the wait leaves the write committed locally (§12.8.4). The SET GLOBAL cluster_sync_* settings are not persisted (those from cluster.toml apply on restart).
• Manual mode: a single primary, manual promotion, no consensus. There is no automatic election of the new primary. Controlled promotion refuses to promote a secondary as long as the primary is reachable by it or by a peer, but a primary that is unreachable from all probed nodes and yet active (complete network partition) can still accept writes until it sees the new epoch; 'force_primary' lifts these checks.
• Raft mode (increment 1): quorum election, with its limitations (§12.9.8): an isolated leader still accepts OFF writes during its lease (they end up in quarantine); reads are not linearizable; an election can remain blocked (database absent from the survivors, databases with incomparable epoch marks) until a node returns or 'force_primary' is used; a database dropped while a node was absent may reappear if that node is elected; the members are fixed (seeds, restart to change them).
• Bounded catch-up. The catch-up at promotion concerns a single peer, the most advanced, and what that peer still keeps of its log (promotion_catchup_mb, 64 MiB per database by default); there is no seeding between secondaries. Two promotions launched at the same time on two nodes refuse each other, with no automatic tie-break.
• Each node carries a complete copy of every database. There is no distribution of data across nodes at this stage (no distributed shards).
• What is not replicated: the system accounts database is relayed by a separate channel (outside the log), not by the log replication mechanism itself; temporary tables (session and global); REPAIR TABLE (local to each node; on the primary, a repair that replaces values causes the database to be re-seeded on the secondaries, see "Table rewritten outside the log"); the last execution date of a scheduled event (scheduled events run only on the primary, a secondary never runs them); settings set by SET GLOBAL; the contents of cached views; the slow query log.
• Non-deterministic DDL. A DDL replayed via SQL on a secondary that evaluates an expression on existing rows at the time of its execution (for example a computed default value) may produce a result different from the one obtained on the primary, since it is re-executed rather than replayed row by row.
• Table rewritten outside the log: automatic re-seeding. When the primary rewrites a table's file in full, carrying modifications that no log record describes (file without a log position, row numbers beyond 2^32, REPAIR TABLE that replaces values, conversion of a column to a BLOB type that externalizes its values), it first writes a "table rewritten outside the log" marker into the log. Each secondary that reaches it without already having this file stops just before it, applying nothing of what follows, and is re-seeded: the database is copied again from the primary, then the stream resumes. The secondary is therefore behind for the duration of the copy, never wrong; the reason is in the LAST_ERROR column of its own row until the copy completes, the line Cluster : base … : table … réécrite hors journal par le primaire (position …), réamorçage nécessaire : la base sera recopiée depuis le primaire. is logged in its server log, and Miraj_cluster_unlogged_rebootstraps (§12.8.5) counts these re-seedings. As long as the copy has not been made, 'primary' refuses to promote this secondary (error 9003, database(s) … waiting to be copied again from the primary). If the primary is lost during this wait, 'force_primary' overrides it: each waiting database is served as it is, that is, in its consistent state from before the rewrite; the log received beyond that point (never applied) is copied to quarantine and then removed, and the former primary's modifications from the marker onward are lost (line Cluster : promotion forcée de … : base … servie telle qu'avant la réécriture hors journal …). As after any forced promotion, the former primary that returns is fenced off (FENCED) then re-seeded once demoted, and the secondaries that had received the continuation diverge and are re-seeded. In raft mode, a node that is thus waiting for its re-seeding abandons the election it would win (reason in LAST_ERROR): another election follows; only 'force_primary' promotes it. A table of more than 2^32 rows rewrites its file at every write: each one carries a marker, and its secondaries re-seed as long as these writes continue. A standalone node, outside an active cluster, writes no marker.
• Lowered LOB threshold. All nodes must have the same --lob-threshold (handshake). When a primary in manual mode restarts with a lower threshold (or reads the file of an earlier engine), the BLOB values remaining in its tables that reach the threshold are moved to the store when opened by ordinary UPDATEs recorded in the log, in slices of 10,000 rows or 4 MiB: the secondaries apply them and receive the values through the stream, without re-seeding, provided they reconnect before the restarted primary's first checkpoint (checkpoint_interval, 60 s by default): a restarted primary knows a secondary's position only when it reconnects, and a secondary whose position has left its log is re-seeded. A stop in the middle is resumed at the next restart. Beyond a per-table ceiling (256 MiB, or journal_retention_mb if smaller), nothing is moved: the values stay in the table, readable and modifiable, only the values written afterward follow the new threshold, and the server log receives AVERTISSEMENT : base.table : … valeur(s) BLOB … restent dans la table, au-delà du plafond de migration journalisée …. A secondary never moves anything; a node in raft mode always restarts as a follower and therefore does not migrate its values.
• DDL loss window on abrupt stop. A DDL is logged after the durable success of its effects on disk; an abrupt stop of the primary occurring exactly between these two steps leaves the DDL applied locally without having been sent to the secondaries (same class of risk as a relaxed save policy in a power outage).
• Unbounded lag in case of a durably absent secondary. A secondary unreachable beyond journal_retention_mb of accumulated log can no longer be caught up by the normal stream: it is re-seeded by a full copy of the databases when it reconnects.
• No built-in client-side load balancing. MIRAJ provides no proxy, nor any mechanism for automatic discovery or redirection of the primary for applications: the connection strategy (which node to contact to write, how to react to a failover) remains the responsibility of the application or its infrastructure.
• Log version 3, specific to a cluster node. A database on a Cluster edition node uses a version 3 log (which can carry DDL records and long-value compaction records); an edition predating cluster support cleanly refuses this log rather than misreading it, whereas a Enterprise or Express edition reads it and ignores it without replication.

12.8 Semi-synchronous replication#

By default, a write is confirmed to the client as soon as it is committed on the primary (asynchronous replication). Semi-synchronous replication makes the primary wait, before confirmation, until one or more secondaries have received the write (or applied it). It is configured per session: an application can request it only for its critical writes (an invoice, a payment) and leave the others asynchronous.

12.8.1 Levels#

The secondaries that count are those connected to the primary and streaming (not being seeded): the SYNC column of SHOW CLUSTER STATUS shows them as YES on the primary.

12.8.2 Scopes#

• SET [SESSION] cluster_sync_commit = 'received': for the session's subsequent writes; SET cluster_sync_commit = DEFAULT restores the server's value.
• SET STATEMENT cluster_sync_commit = 'applied' FOR INSERT …: for a single statement.
• SET GLOBAL cluster_sync_commit = 'received': the server's value, followed by sessions that have not set their own.
• cluster_sync_replicas, cluster_sync_timeout (milliseconds) and cluster_sync_timeout_action exist only at the global level (SET GLOBAL, error 1229 otherwise): these are operational settings.

Initial values come from cluster.toml (sync_commit, sync_replicas, sync_timeout_ms, sync_timeout_action, §12.2.1); a SET GLOBAL takes effect immediately but is not persisted. These variables can be read in all editions (OFF, 1, 10000, FALLBACK); modifying them outside the Cluster edition returns error 9002. Without cluster.toml, they are accepted with no effect. When set on a secondary, they apply at its promotion.

The wait applies to the whole statement: a statement committed on its own (autocommit), a COMMIT, an implicit commit (DDL, SET autocommit = 1); a CALL waits only once, at its end, for all the procedure's writes (likewise for writes made by triggers or stored functions). A ROLLBACK and a read never wait. Scheduled events never wait.

12.8.3 Timeout, behaviors and degraded state#

When acknowledgements do not arrive within @@cluster_sync_timeout milliseconds (10,000 by default), or when fewer than @@cluster_sync_replicas secondaries are streaming (immediate decision, without waiting for the timeout), @@cluster_sync_timeout_action decides:

An exceeded timeout puts the database in a degraded state (server log: réplication synchrone dégradée sur la base …, SYNC column at DEGRADED on the primary's row): subsequent writes to this database no longer wait and directly receive the warning (or the error), instead of each paying the timeout. As soon as @@cluster_sync_replicas secondaries have caught up with the end of the database's log (written, or applied if the expired wait was in APPLIED), the degraded state is lifted (réplication synchrone rétablie sur la base …). The WAIT behavior ignores the degraded state.

12.8.4 Precise semantics#

• Local commit first. The write is committed on the primary (log written, or flushed according to save_policy), its locks are released, then the session waits for the acknowledgements before replying. During this wait, the write is already visible to the primary's other sessions.
• KILL QUERY during the wait: the statement succeeds with warning 9004 wait cancelled, transaction committed locally. KILL CONNECTION: the connection is closed (1927), the write remains committed.
• Demotion or fencing of the node during the wait: error 9004 node is no longer primary (demoted); transaction committed locally, acknowledgement unknown. If no secondary had received it, this write will end up in quarantine when the node returns (§12.4.2).
• save_policy: in periodic, a write in semi-synchronous mode waits at least until the log is written to its file (only the written bytes are sent to the secondaries). In manual, nothing is logged or replicated: the setting has no effect.
• APPLIED and DDL: a DDL applied by a secondary waits until no client transaction holds the table on that secondary (lock_wait_timeout); a write in APPLIED that follows it may therefore time out. Likewise if application fails on a secondary (see LAST_ERROR), or if a session on the secondary holds the table through LOCK TABLES … READ.
• Cost: each write waits for one network round trip and a few milliseconds (the primary requests an immediate acknowledgement from the secondary as soon as a session is waiting). With OFF, nothing changes compared to asynchronous replication.
• A secondary that is stopped abruptly is seen as disconnected immediately in most cases (link closed), but a silent network outage is detected only after 15 seconds without a frame: until then, writes wait for the timeout.
• @@cluster_sync_replicas greater than the number of secondaries: each write receives the warning (or the error) immediately.

12.8.5 Counters#

SHOW STATUS LIKE 'Miraj_cluster_sync%' (active replication only):

Outside semi-synchronous replication, SHOW STATUS LIKE 'Miraj_cluster_unlogged_rebootstraps' counts, on a secondary, the database re-seedings it has requested since its startup after a table rewritten outside the log by the primary (§12.7).

12.8.6 Guarantee at promotion#

With RECEIVED (or APPLIED) and @@cluster_sync_replicas = N, every write confirmed without a warning is in the log of N secondaries. When the primary is lost, controlled promotion (§12.4.1) catches up the most advanced secondary: the write is lost only if all N of those secondaries are lost or unreachable. The primary also announces to its secondaries the highest confirmed position of each database (SYNCED_LSN, §12.5.3); a promotion that could not reach it (no reachable node holds it) is refused (9003, writes confirmed to clients), except with 'force_primary'. This position is kept only in memory: a restarted node no longer knows it.

Example, read-after-write between nodes:

-- Application session, on the primary
SET SESSION cluster_sync_commit = 'applied';
INSERT INTO facture (id, montant) VALUES (1042, 250.00);
-- OK: the invoice is applied on the secondary; a report that reads it there now sees it

12.9 Automatic election (raft mode)#

With mode = "raft" in cluster.toml (all nodes), the primary is no longer designated by the administrator: it is elected by a majority of the members, and another one is elected automatically when it disappears. The mechanism follows the Raft consensus protocol (terms, one vote per term, pre-vote, leader lease), adapted to MIRAJ's per-database log. This is a first increment: it guarantees the safety of confirmed writes; it changes neither the members on the fly nor the distribution of data.

12.9.1 Members, quorum, term#

• The voting members are the addresses in seeds (deduplicated), which must contain the advertised address of each node. With N members, a majority is N / 2 + 1 members (2 out of 3, 3 out of 5). Three members tolerate the loss of one node, five that of two; two members tolerate none.
• The term is the node's epoch (@@cluster_epoch): a candidate increments it, a node that learns of a higher one adopts it. Each node grants at most one vote per term, written to miraj/cluster.mrs before replying.
• A node always restarts as a follower (SECONDARY); its term and its vote are kept.

12.9.2 Course of an election#

1. A follower with no word from the leader for a delay drawn at random between election_timeout_ms and twice that value first performs a pre-vote: it asks the members whether they would vote for it, without changing anything on their side. A node that still hears its leader answers no (stickiness): an isolated node that comes back does not depose the sitting leader.
2. With a majority of "yes", it increments its term, votes for itself and requests votes. A member grants its vote if its own log is not more up to date than the candidate's, database by database: each database carries the epoch mark of the last leader that wrote to it; a candidate with a lower mark is refused, with an equal mark its position does not matter (it will catch up), and a database that the candidate lacks results in a refusal.
3. Once elected, the node first catches up, database by database, from the most advanced voter with the same mark, applies everything, then writes its own epoch mark into each database before accepting any write: @@cluster_role then changes to PRIMARY. A catch-up that is impossible abandons the election (reason in LAST_ERROR).
4. The other nodes follow the new leader; those whose log is not a prefix of its own (former leader that had unconfirmed writes) are re-seeded, their old copy being quarantined.

The server logs trace the election: Cluster : candidat au terme 4., Cluster : vote accordé à n1 (terme 4)., Cluster : nœud n1 élu leader (terme 4, 2 vote(s) sur 3)., Cluster : aucune majorité : 1 vote(s) sur les 2 requis au terme 4.

12.9.3 Leader lease#

A leader that has received no frame from a majority of the members (itself included) for election_timeout_ms (after a grace period of the same length at its election) gives up its role: it goes back to read-only mode (1290), sessions that were waiting for an acknowledgement receive error 9004 (quorum lost), and it logs it (nœud n1 rétrogradé automatiquement (mode raft) : majorité perdue…). A leader that learns of a higher term is demoted in the same way, without intervention (instead of being fenced off, FENCED, as in manual mode).

12.9.4 The MAJORITY level#

In raft mode, sync_commit is majority and sync_timeout_action is error unless explicitly set: a write is confirmed to the client only once written to the log of a majority of the members; otherwise the client receives error 9004 (the write remains committed locally, its fate depends on the next election). MAJORITY can also be used in manual mode (majority computed over the seeds).

12.9.5 Guarantees#

• A single leader per term. Two leaders can briefly coexist at different terms (an isolated former leader, before the end of its lease), never at the same term; only the most recent one can confirm a write in MAJORITY.
• No write confirmed in MAJORITY is lost by an election: it is in the log of a majority; the newly elected node is elected by a majority, which intersects it; the common voter refused a less up-to-date candidate, or the candidate catches it up before serving.
• What can be lost: OFF writes (or MAJORITY writes that received error 9004) made on a leader that loses the majority; they remain in quarantine on the former leader.
• Reads are not linearizable: a former leader still reads its data until the end of its lease, and a follower is behind.

12.9.6 Manual commands in raft mode#

12.9.7 Moving from manual mode to raft mode#

Let the cluster converge (no lag), stop the nodes, add mode = "raft" (and if needed election_timeout_ms) to each cluster.toml, checking that seeds contains all the nodes, then restart them: they restart as followers and elect a leader. Returning to manual mode follows the same path (the node to be designated primary then becomes so through 'primary').

12.9.8 Limitations of the current increment#

• An election can remain blocked without being dangerous: database absent from all possible candidates (created while a node was absent), databases whose epoch marks contradict each other among the survivors. It unblocks when the missing node returns, or through 'force_primary'; the reason is in LAST_ERROR.
• A database dropped while a node was absent reappears if that node is elected afterward.
• A follower whose log precedes the leader's election with an older mark is re-seeded (full copy) rather than caught up.
• The members are fixed: changing them requires a restart of all nodes.
• The timeouts assume fast disk writes: a very slow disk (flushes of several hundred milliseconds) requires a larger election_timeout_ms.