17. Multi-node replication (Cluster edition)

This chapter describes two independent replications: Miraj's own cluster replication (§17.1 to §17.12, Cluster edition), and binary log replication in the reference format, which connects Miraj to the servers and tools of the reference ecosystem (§17.13, Enterprise and Cluster editions).

17.1 Overview#

License. A cluster has a single license: the same miraj.lic file on every node, which sets the number of nodes allowed. Without a valid Cluster license, the server starts but replication does not. See 31.7.

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, §17.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 (§17.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 (§17.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.

Each node carries a complete copy of every database, except the partitions that a table dedicates to a node (PARTITION … NODE 'n2'): their rows exist only on that node, and the other nodes read and write them through it (§17.10). Automatic election of the primary is described in §17.9.

17.2 Configuration#

17.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
members = ["n1", "n2", "n3"] # identifiers of the admitted nodes (common name of their certificate), this one included

# Optional (default values shown)
tls_crl = "cluster-crl.pem"  # revocation list of the authority (miraj-cluster-pki revoke); absent by default
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 (§17.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, §17.9)
election_timeout_ms = 3000   # raft mode: delay without word from the leader before standing as candidate (≥ 500)
fragment_timeout_ms = 30000  # dedicated partitions (§17.10): wait for a reply from the node that holds them
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 (§17.3).

17.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; a shorter duration, --days 90 or --days 365, limits the use of a lost certificate), ready to be copied into the node's cluster.toml folder. The certificate's common name (CN) is the --id identifier: it is the node's identity (§17.2.3). issue-node records each signed certificate in the certificats.txt register in the authority's folder (serial number, node, end of validity), which is used for revocation. Keep cluster-ca-key.pem off the nodes: it signs the certificates, and only cluster-ca.pem is distributed. --ca <folder> designates the init-ca folder; otherwise, --ca-cert and --ca-key give the authority's certificate and key wherever they are. 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. With your own PKI, the common name (/CN=) of each node certificate must be exactly the node's [node] id identifier.

17.2.3 Node identity and member list#

A node's certificate does not only prove that it belongs to the cluster authority: its common name (CN) is the node's identity. On every connection between nodes, in both directions:

• the identity is read from the peer's certificate; if members is set and does not contain it, the connection is closed immediately, before any frame is read (log: connexion de … refusée : nœud « x » hors de la liste des membres);
• the identifier that the peer then declares (introduction of a secondary, vote request, catch-up request, position probe, epoch announcement, reply from a primary or a voter) must be the one in its certificate; otherwise the peer is refused (log: nœud n1 (…) refusé : se présente comme « n2 » mais son certificat est celui du nœud « n1 »).

A holder of a certificate from the authority can therefore neither impersonate another node (vote in its place, be served a catch-up or the full stream of databases and accounts under its name) nor, if it is not a member, obtain anything. At startup, a node whose own certificate carries a name other than its id is reported in the log (sécurité : le certificat … désigne le nœud « x » et non « n1 »): its peers will refuse it. To add a node, sign its certificate, add its identifier to members on every node and restart them one at a time (the list is read only at startup).

Identities are compared case-insensitively, like node identifiers.

17.2.4 Revoking a certificate#

A lost certificate or a removed node is revoked with the authority, then the revocation list is distributed to the nodes:

# All certificates signed for node n3 (register certificats.txt of the authority)
miraj-cluster-pki revoke n3 --ca pki
# Or one specific certificate (signed by the authority)
miraj-cluster-pki revoke --cert ancien/node.pem --ca pki
# Regenerate the list without a new revocation (before its next-update date)
miraj-cluster-pki crl --ca pki --days 365

revoke records the certificates in the authority's revocations.txt register and regenerates the signed list pki/cluster-crl.pem (--out for another path; --days: next-update date, 365 days by default). The list is replaced as a whole (temporary file renamed). Copy it to each node at the tls_crl location: each node reloads it within seconds of its modification, re-establishes all its links with its peers (the secondaries reconnect immediately) and from then on refuses the revoked certificate, whether it connects (log: connexion de … refusée (TLS) : certificat du pair révoqué (tls_crl)) or is joined. An unreadable list is reported and the old one stays in force. The next-update date is not enforced: a "stale" list remains applied, but regenerate it regularly all the same.

To replace a node's certificate, sign a new one (issue-node … --force), install it, restart that node, then revoke the old one with revoke --cert (revoking by identifier would also revoke the new one).

17.2.5 Safeguards between nodes#

• Epochs: an epoch announcement (promotion, election) more than 1,000 beyond the highest epoch known to the node, or implausible (2⁴⁸ and above, u64::MAX), is ignored and logged (époque … annoncée par le nœud … ignorée (saut anormal …)): it does not fence off the primary and is not adopted. Only a node that follows a primary (proven identity) accepts a larger jump from it (new or long-absent node), never an implausible epoch.
• Frames: at most 16 MiB per frame, except on a secondary's link with its primary and during a catch-up initiated by the node itself (256 MiB: a log record can be large).
• Connections: at most four simultaneous incoming connections per known member (members or seeds), sixteen at least, on the inter-node port; further ones are closed immediately (refusal logged at most every ten seconds). The TLS handshake is bounded to ten seconds in total, in both directions.
• Received files (seeding, LOB stores): a file name sent by a peer is accepted only if it is a plain name (ASCII letters and digits, _ . $ -, spaces), with no path, drive (C:), alternate data stream (a:b), leading or trailing dot or space, nor Windows device name (CON, NUL, COM1…).

17.3 Step-by-step setup#

1. Prepare the certificates for each node (§17.2.2) and one cluster.toml file per node, with the same [cluster] name, the same tls_ca, the same members list (identifiers of all the nodes, §17.2.3), 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 (§17.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.

17.4 Switchover#

17.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 (§17.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.

17.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 §17.4.3):

1. Confirm that the former primary is stopped or unreachable.
2. Promote the chosen secondary (SET GLOBAL cluster_role = 'primary', see §17.4.4 for choosing it).
3. Repoint the client applications to the new primary's address (Miraj does not do it for them, see §17.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.

17.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.

17.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).

17.5 Monitoring and supervision#

17.5.1 System variables#

17.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):

17.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;

17.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, revoked certificate, node outside the member list or declaring an identifier other than the one in its certificate, incompatible edition), announced epoch ignored (abnormal jump), excess connections between nodes, 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, §17.7).

Lines specific to promotion:

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

17.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), OPTIMIZE TABLE (no effect: the secondary replays the compaction done on the primary).
• With miraj-proxy (chapter 23): applications connect to the proxy, which routes the write port to the current primary (including after a switchover) and the read port to the least loaded secondary; nothing else to plan for besides reconnecting after a lost connection.
• Application strategy without a proxy: write only to the primary, known through configuration on the application side; 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.
• Following changes from a secondary: LISTEN TABLE and WAIT FOR CHANGES work on all nodes, which return the same events as the primary; NOTIFY stays local to the node that executes it (§17.11).

17.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 (§17.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 (§17.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 (§17.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.
• Data distribution limited to dedicated partitions. Each node carries a complete copy of every database, except the partitions dedicated to a node (§17.10), which have no copy: losing their node makes them unavailable.
• No table renaming. In a multi-node cluster, RENAME TABLE and ALTER TABLE … RENAME are refused on all nodes (error 1290, cluster option), whether or not the table has dedicated partitions; only a temporary table can still be renamed. To change a table's name, create it under the new name, copy the rows (INSERT … SELECT), then drop the old one.
• Disk tables. A disk table (ENGINE = Aria, MyISAM or DISK, see Storage engines) is replicated like an in-memory table: same records on the link, same row numbers on all nodes. Each node stores the rows in its own .dmrj file and makes its own checkpoints: the physical layout of the files may differ from one node to another, only the rows matter (compare rows, never the bytes of the files). When a secondary is seeded, the .dmrj and .dbmrj files are copied hot: the checkpoint of the table being copied is suspended for the duration of the copy (its writes continue, its modified pages stay in memory), and a copy that takes more than 30 seconds is reported in the server log.
• Sequences. CREATE SEQUENCE and DROP SEQUENCE run on the primary and the sequences.mrq file of each database is copied when a secondary is seeded, but the values served by NEXTVAL do not go through the row log (a sequence's state is written in its own file, chapter 7, "Sequences"). After a switchover, check the position of the sequences on the new primary (SELECT * FROM s) and advance it if needed with SETVAL.
• 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 (§17.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: the restarted primary retains its log for them until they return (within the limit of journal_retention_mb, §17.2.1). 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.
• Load balancing through a separate proxy. The server itself does not redirect a write received by a secondary (error 1290). Automatic redirection to the primary and read balancing go through miraj-proxy (chapter 23), which chooses the node when each connection is opened.
• 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.

17.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.

17.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.

17.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, §17.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.

17.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.

17.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 (§17.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.

17.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 (§17.7).

17.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 (§17.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, §17.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

17.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.

17.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.

17.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.

17.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).

17.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).

17.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.

17.9.6 Manual commands in raft mode#

17.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').

17.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.

17.10 Distributing a table across several nodes (dedicated partitions)#

17.10.1 What it is for#

By default, each node of the cluster keeps a complete copy of every database. A partitioned table (chapter 7, "Partitioning") can also dedicate one or more of its partitions to a specific node: the rows of these partitions are stored only on that node. The other nodes know the table and know where its rows are, but do not store them.

For the application, nothing changes: it queries the table as usual, on any node. When a query needs a partition stored elsewhere, the node that receives the query reads it from the node that holds it and assembles the result.

Typical use cases:

• A table too large for a single machine: the accounting entries of each company, or of each fiscal year, on different servers.
• Spreading the load: queries for one company pruned to its partition are served by the node that holds it, without loading the others; a query over all companies makes all the nodes work at the same time.
• Keeping data close to its users: a site's partition on that site's server.

Important: a dedicated partition has no copy. Its rows are not replicated to the secondaries. If the node that holds it is lost (destroyed disk, lost machine), those rows are lost. Reserve dedicated partitions for data that you can rebuild or that you back up elsewhere, and keep critical data in ordinary partitions (replicated on all nodes). See §17.10.10.

17.10.2 Setup#

Prerequisites: a cluster in service (§17.3), Cluster edition, and preferably the members list set in cluster.toml (§17.2.3). The node identifiers used below (n1, n2, n3) are those of the [node] id key of each node.

1. Create the table on the primary, adding NODE 'identifier' to the partitions to dedicate:

CREATE TABLE compta.ecritures (
    societe INT NOT NULL,
    id      INT NOT NULL,
    montant DECIMAL(12,2),
    PRIMARY KEY (societe, id)
) PARTITION BY LIST (societe) (
    PARTITION p1 VALUES IN (1),               -- ordinary partition: copied on all nodes
    PARTITION p2 VALUES IN (2) NODE 'n2',     -- rows of company 2: on n2 only
    PARTITION p3 VALUES IN (3) NODE 'n3'      -- rows of company 3: on n3 only
);

Accepted spellings: NODE 'n2', NODE = 'n2' or NODE n2. The clause is placed on a partition (it then applies to all its subpartitions) or on a subpartition. All partitioning methods are possible (RANGE, LIST, HASH, KEY).

The table definition is replicated like any DDL: a few moments later, each node knows the table, and n2 and n3 have created their partition.

2. Add a dedicated partition to an existing table:

ALTER TABLE compta.ecritures ADD PARTITION (PARTITION p4 VALUES IN (4) NODE 'n2');

DROP PARTITION and TRUNCATE PARTITION also work on a dedicated partition: its rows are deleted on its node.

A CREATE TABLE or an ALTER TABLE of a table with dedicated partitions returns only once it has been applied by the nodes that hold them (at most fragment_timeout_ms): the table can be written and read right afterward. A stopped node (or one cut off for more than a few seconds) is not waited for, nor is a node that lags beyond this delay: the statement succeeds with one 9038 warning per node, and its partitions respond as soon as it has caught up with the primary. A read or a write that targets its partitions in the meantime waits for it: the node that holds them replies only once all the DDL of the database known to the requesting node have been applied (at most half of fragment_timeout_ms, then 9038 "has not applied the table definition yet"); it never replies that the database or the table is unknown because the DDL has not reached it yet.

3. Check the placement, from any node:

SELECT PARTITION_NAME, NODE, STATE, TABLE_ROWS
FROM information_schema.MIRAJ_FRAGMENTS
WHERE TABLE_SCHEMA = 'compta' AND TABLE_NAME = 'ecritures';

+----------------+-------+--------+------------+
| PARTITION_NAME | NODE  | STATE  | TABLE_ROWS |
+----------------+-------+--------+------------+
| p1             | local | LOCAL  |      12408 |
| p2             | n2    | REMOTE |      98311 |
| p3             | n3    | REMOTE |      45020 |
+----------------+-------+--------+------------+

SHOW CREATE TABLE returns the clause (NODE = 'n2').

Placement rules:

• The node named must be part of the cluster in service and, if members is set, be listed in it; otherwise the statement is refused (9037). local is not an admitted node identifier. Outside the Cluster edition, the clause is refused (9002).
• The node named can be the primary itself: the partition's rows then stay on the primary, with no copy on the secondaries.
• A partition does not change node once created (see §17.10.10).

17.10.3 Reading#

Reads are done on any node, primary or secondary, exactly as on an ordinary table: SELECT, joins, subqueries, views, stored procedures.

-- On any node
SELECT societe, SUM(montant) FROM compta.ecritures GROUP BY societe;
SELECT * FROM compta.ecritures WHERE societe = 2 AND id = 101;

• A query whose WHERE designates specific partitions (societe = 2) reads only those: if they are all on the queried node, no other node is contacted. For good performance, filter on the partitioning column as much as possible.
• When several partitions are held by other nodes, they are read at the same time: the response time is that of the slowest node, not the sum of the nodes.
• If a node that holds a required partition does not reply, the query fails (9038): Miraj never returns an incomplete result without saying so. A query that does not need that node (filtered on other partitions) works normally.
• Each partition held elsewhere is read in its last committed state, at the moment it is read. Two partitions held by two different nodes are not read "at the same instant": a query that reads several nodes while writes are going on there may see a write on one node and not yet another one, made just afterward on another node.

17.10.4 Writing#

As for any table of the cluster, writes are done on the primary (on a secondary: error 1290, §17.6), even when the row is stored on another node. The primary forwards the write to the node that holds the partition, which executes it and returns the result (row count, LAST_INSERT_ID(), warnings, errors):

-- On the primary
INSERT INTO compta.ecritures VALUES (2, 101, 50.00);                 -- stored on n2
UPDATE compta.ecritures SET montant = 60 WHERE societe = 2 AND id = 101;
DELETE FROM compta.ecritures WHERE societe = 3 AND id < 100;         -- executed on n3

INSERT, INSERT … SELECT (the source can be the partitioned table itself, including a partition held elsewhere), REPLACE, UPDATE, DELETE, TRUNCATE TABLE are supported, with the usual default values, AUTO_INCREMENT and generated columns.

A statement writes to a single node only. A statement whose rows would go into partitions of several nodes at once (for example an INSERT of several companies, or an UPDATE without a filter on the partitioning column) is refused as a whole, writing nothing (9039). Split it by node:

-- Refused (9039): the rows would go to n2 and to n3
INSERT INTO compta.ecritures VALUES (2, 102, 10.00), (3, 7, 20.00);

-- Accepted: one statement per node
INSERT INTO compta.ecritures VALUES (2, 102, 10.00);
INSERT INTO compta.ecritures VALUES (3, 7, 20.00);

-- Refused (9039): without a filter, the UPDATE would touch all partitions
UPDATE compta.ecritures SET montant = montant * 1.1;
-- Accepted
UPDATE compta.ecritures SET montant = montant * 1.1 WHERE societe = 2;

Ordinary partitions and those held by the primary count together as "the primary": a statement can write to both p1 (ordinary) and a partition dedicated to the primary, but not to p1 and p2 (on n2).

A few operations are not available on a table that has a partition dedicated to another node (9041): INSERT … ON DUPLICATE KEY UPDATE and triggers that would apply to a row stored elsewhere, UPDATE / DELETE on several tables at once, SELECT … FOR UPDATE and vector search on a partition held elsewhere. The full list is in §17.10.9.

17.10.5 Transactions#

A transaction (START TRANSACTION … COMMIT, or autocommit = 0) can write to the partitions of a single other node:

-- On the primary
START TRANSACTION;
INSERT INTO compta.ecritures VALUES (2, 201, 50.00), (2, 202, 12.00);  -- on n2
UPDATE compta.ecritures SET montant = montant * 2 WHERE societe = 2 AND id = 201;
SELECT SUM(montant) FROM compta.ecritures WHERE societe = 2;           -- sees its own writes
COMMIT;                                                                  -- committed on n2

What you can expect:

• All or nothing: COMMIT commits all the transaction's writes on the node; ROLLBACK, closing the connection or KILL CONNECTION cancels them all.
• Reading your own writes: within the transaction, reads of that node's partitions see the rows already written and not yet committed. The other nodes see them only after the COMMIT.
• Conflicts: if another session has modified the same rows, the COMMIT (or the statement) returns error 1213 and the transaction is rolled back; replay it, as for any transaction (chapter 14).
• An ordinary error (duplicate 1062…) cancels only the faulty statement; the transaction continues.

What a transaction cannot do:

• Write to two nodes, or write both on the primary (ordinary tables, ordinary partitions) and on another node: the statement that would attempt it is refused (9039, the message says what the transaction has already written); only that statement fails, the transaction stays open and can be committed or rolled back. Use two transactions. Reads of all tables remain allowed, as do the session's temporary tables.
• Set a SAVEPOINT after writing to another node (9041).
• Stay idle too long: a transaction that has written to another node and does nothing more there for more than fragment_timeout_ms (30 seconds by default) is rolled back by that node, which thus releases its locks. The next statement receives 9040 and the transaction is rolled back. For long interactive transactions, increase fragment_timeout_ms in cluster.toml (§17.2.1).

Failure during the transaction: if the node becomes unreachable before the COMMIT, the statement receives 9038 and the whole transaction is rolled back (the node also cancels its part). If the link is cut during the COMMIT, the 9038 error message states that the outcome is unknown: the node committed if it received the request in full, otherwise it cancelled everything. Re-read the rows concerned before replaying the transaction, so as not to write them twice.

17.10.6 Performance#

Miraj has the node that holds a partition compute whatever reduces the volume exchanged over the network:

• Aggregates: COUNT, SUM, AVG, MIN, MAX (without DISTINCT), with or without GROUP BY on columns. Each node sends only one row per group instead of all its rows.
• Limited sort: ORDER BY on columns with LIMIT n (and OFFSET). Each node sends only its best rows.

These two computations are done remotely when the query reads only the partitioned table (no join or subquery) and its WHERE consists of simple comparisons of columns with constants (=, <, >=, BETWEEN…, linked by AND). In other cases, the result is the same, but the partition's rows are first brought back: on large partitions, prefer simple per-table queries.

EXPLAIN shows what happens:

EXPLAIN PARTITIONS SELECT societe, SUM(montant) FROM compta.ecritures GROUP BY societe;

• partitions column: p1,p2@n2,p3@n3 — @n2 indicates a partition read on n2;
• Extra column: Using remote aggregation (aggregates computed on the nodes) or Using remote top-n (limited sort computed on the nodes).

EXPLAIN ANALYZE (chapter 9, 9.21) executes the query and also measures the remote step: the Remote aggregation (partial results merged) operator appears in the tree with the number of rows it returns (the merged groups) and its time, without per-node detail.

17.10.7 Settings#

A single setting, in cluster.toml (§17.2.1), preferably identical on all nodes:

Increase it for very long queries on remote partitions, clients that read a large result very slowly, or interactive transactions; reduce it to detect a stopped node faster.

17.10.8 Monitoring#

• Partition state: information_schema.MIRAJ_FRAGMENTS (§17.10.2), STATE column.
• Counters, since the startup of the node that issues the requests: SHOW STATUS LIKE 'Miraj_cluster_fragment%'.

• Server log (--log): each failure involving another node is logged there with the table, the partition and the node, for example:

Cluster : lecture de la partition p2 de compta.ecritures chez le nœud n2 en échec (9038) : …
Cluster : écriture de la partition p3 de ecritures chez le nœud n3 en échec (9038) : …

The node that rolls back an idle transaction also logs it: Cluster : transaction de n1 sur … annulée après … ms d'inactivité.

17.10.9 Errors and what to do#

Message details: chapter 28.

17.10.10 Limits and recommendations#

• No copy of a dedicated partition. The permanent loss of its node causes the loss of its rows, and while it is stopped, they are unreadable (9038). Keep the data that must not be lost in ordinary partitions, or back it up elsewhere.
• No BACKUP of a database that contains a dedicated partition (9041).
• No moving of a partition to another node, and no change to the table's structure (columns, indexes) as long as a partition is dedicated to another node (9041). To achieve this, create a new table with the desired structure or placement and copy the rows into it with INSERT … SELECT, node by node (a statement writes to only one node, for example INSERT INTO ecritures_v2 SELECT * FROM ecritures WHERE societe = 2). The new table keeps its name: a multi-node cluster does not accept RENAME TABLE (§17.7).
• A statement or a transaction writes to a single node only (§17.10.4, §17.10.5). A network cut during a COMMIT on another node leaves its outcome unknown.
• No common instant between nodes for a read that spans several of them (§17.10.3).
• KILL QUERY does not interrupt the wait for a reply from another node; this wait remains bounded by fragment_timeout_ms.
• Large results read slowly: while it sends a large partition (more than about 4 MB) to a client that reads it slowly, the node that holds it makes writes wait there; if the client reads nothing for fragment_timeout_ms, the read fails (9038).
• Connections between nodes: each node accepts a limited number of connections from the same peer (four per member, sixteen at least, §17.2.5). Many sessions reading partitions held elsewhere at the same time, or many transactions open on the same node, can reach this ceiling (9038).

17.11 Change events on a cluster#

Change events (LISTEN, NOTIFY, WAIT FOR CHANGES, chapter 21) work on all the nodes of a cluster. Each node publishes the changes of its own tables: a subscriber can therefore connect to the primary or to any secondary.

Points to know:

• Partitioned tables: they are listened to under their name, on all nodes, whatever partition is written. A row that changes partition gives DELETE then INSERT. The change_events_bulk_rows threshold applies per partition, and emptying one or all partitions gives one BULK per emptied partition (reason TRUNCATE PARTITION).
• NOTIFY is local to the node that executes it. Since writes, and therefore the NOTIFYs issued by triggers or procedures, run on the primary, a subscriber to a channel must connect to the primary.
• Switchover: a node's numbers have no meaning on another node. After a promotion, a client that reconnects to another node with LISTEN … FROM n receives a RESYNC row: it re-reads its data, then subscribes again (§21.3). A client already subscribed to the promoted secondary is not affected: the node keeps its numbering and keeps publishing, now at commit.
• Node restart: events are kept in memory only; after a restart, a client that resumes with an old number receives RESYNC.
• Per-node settings: the change_events_… variables and the --change-events-… options (chapter 16) are specific to each node and are not replicated. Two nodes set with different change_events_bulk_rows do not summarize the same transactions as BULK: keep the same setting on all nodes.
• Pessimistic model (--concurrency pessimistic): for a write outside a transaction, a secondary can return more precise events than the primary (DELETE + INSERT for a modified key, only the columns actually changed, deletions detailed row by row rather than a BULK). The multiversion model, the default, gives the same events on all nodes.
• CREATE TABLE … SELECT: if a subscription already targets this table name (for example left over after a DROP TABLE), the copied rows are returned by the secondaries, not by the primary.

Recommended distribution: subscribe application screens and caches to a secondary to offload the primary, and NOTIFY channels to the primary; treat RESYNC (full re-read then new subscription) as the normal response to a switchover.

17.12 Cluster in PostgreSQL language#

All the nodes of a cluster have the same SQL language (the POSTGRESQL element of sql_mode): the text of DDL and writes sent between nodes is the client's, re-read in the language of each node. A joining node announces its language (inter-node protocol version 6); a node of a different language is refused with error 9054, recorded in the primary's server.log ("node n2 refused: error 9054: …") and in that of the refused node ("membership refused by …"). To change a cluster's language: stop all the nodes, change sql_mode everywhere, restart (the text of a DDL still to be applied would be re-read in the other language: re-seed a lagging secondary).

In a cluster in PostgreSQL language, everything replicates as in Miraj language: tables, rows, views, DDL (re-read in PostgreSQL on the secondaries), accounts with their SCRAM-SHA-256 verifier (an account created on the primary connects with SCRAM on the secondaries). The miraj-proxy probe and PostgreSQL clients go through the proxy (chapter 23).

17.13 Binary log replication (reference format)#

Miraj offers two distinct replications, which share neither format nor protocol:

The two can be combined: a cluster primary can also keep a binary log to feed a reference server, a warehouse or a change data capture tool. Cluster replication never goes through the binary log (a cluster secondary does not write to it the rows it receives from its primary).

17.13.1 Enabling the binary log#

The binary log is disabled by default (@@log_bin = 0). It is enabled at startup:

miraj-server --log-bin --server-id 1 [--gtid-domain-id 0] [--max-binlog-size 1073741824] \
             [--binlog-expire-logs-seconds 604800 | --expire-logs-days 7] [--sync-binlog 1] \
             [--binlog-checksum CRC32|NONE] [--gtid-strict-mode] [--log-slave-updates]

Each option can also be written --name=value (--log-bin=D:\binlog\miraj-bin, --server-id=2); --log-bin, --gtid-strict-mode and --log-slave-updates alone mean ON. The command line takes precedence over the configuration file.

or in miraj_config.xml (same names, with underscores):

<log_bin>ON</log_bin>            <!-- or a base path: D:\binlog\miraj-bin -->
<server_id>1</server_id>
<binlog_expire_logs_seconds>604800</binlog_expire_logs_seconds>
<sync_binlog>1</sync_binlog>

Each committed transaction becomes a group: GTID, then for each table a TABLE_MAP and row events (Write_rows_v1, Update_rows_v1 with before and after images, Delete_rows_v1), then XID. A successful DDL becomes a standalone GTID followed by a QUERY carrying the statement text, the current database and the sql_mode. CREATE TABLE … SELECT is logged as the canonical creation of the table, followed by its rows. TRUNCATE is a DDL. Temporary tables, the system database and accounts (CREATE USER, GRANT) are not logged. Versioned table (WITH SYSTEM VERSIONING, chapter 12): only its current rows are logged; the history is not transmitted, a Miraj replica keeps it itself by applying the rows (versions dated by the application) and ignores the ROW START / ROW END columns it receives. UPDATE / DELETE … FOR PORTION OF become the modified, deleted and inserted rows (pieces outside the portion); LOAD XML and statements with RETURNING are logged like ordinary writes.

The group is written at commit, under the locks of the written tables: the log order follows the commit order of a given table. Each startup opens a new file; after an abrupt shutdown, the incomplete end of the last file is removed and the GTID state rebuilt.

Cost. Log disabled: one atomic read per commit. Log kept (measured on 09/10/2026, embedded engine, optimized build, median of alternating runs): about 15 to 25 µs more per single-row transaction (+20 to +45%), and about 1 µs per row of a bulk write.

17.13.2 Inspecting and maintaining the log#

SHOW MASTER STATUS;                 -- or SHOW BINARY LOG STATUS: current file and position
SHOW BINARY LOGS;                   -- or SHOW MASTER LOGS: files and sizes
SHOW BINLOG EVENTS IN 'miraj-bin.000002' FROM 342 LIMIT 10;
SELECT BINLOG_GTID_POS('miraj-bin.000002', 342);   -- GTID state at this position
FLUSH BINARY LOGS;                  -- new file (FLUSH LOGS too)
PURGE BINARY LOGS TO 'miraj-bin.000003';
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;
RESET MASTER;                       -- erase everything, numbering restarts at 1 (RESET MASTER TO n)
SHOW SLAVE HOSTS;                   -- registered replicas (SHOW REPLICA HOSTS)

After a single insert in a new log:

SHOW MASTER STATUS;
SHOW BINLOG EVENTS IN 'miraj-bin.000001' LIMIT 6;
SELECT @@gtid_binlog_pos;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| miraj-bin.000001 |      501 |              |                  |
+------------------+----------+--------------+------------------+
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                         |
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------+
| miraj-bin.000001 |   4 | Format_desc       |         1 |         256 | Server ver: 10.4.32-MIRAJ-log, Binlog ver: 4 |
| miraj-bin.000001 | 256 | Gtid_list         |         1 |         283 | []                                           |
| miraj-bin.000001 | 283 | Binlog_checkpoint |         1 |         326 | miraj-bin.000001                             |
| miraj-bin.000001 | 326 | Gtid              |         1 |         368 | BEGIN GTID 0-1-1                             |
| miraj-bin.000001 | 368 | Table_map         |         1 |         426 | table_id: 1 (gestion.clients)                |
| miraj-bin.000001 | 426 | Write_rows_v1     |         1 |         470 | table_id: 1 flags: STMT_END_F                |
+------------------+-----+-------------------+-----------+-------------+----------------------------------------------+
+-------------------+
| @@gtid_binlog_pos |
+-------------------+
| 0-1-1             |
+-------------------+

The .mrb files are ordinary binary log files: the reference's binary log reading tool decodes them directly (options -vv --base64-output=decode-rows, file miraj-bin.000001.mrb). Reading remotely (--read-from-remote-server) depends on the tool's version: the 10.4 one refuses a server whose VERSION() does not start with 5 or 10 (Miraj announces 8.0.40-miraj-1.0.0).

SHOW BINLOG EVENTS FROM must designate the start of an event (1220 otherwise); PURGE … TO a file of the index (1373). Log disabled: SHOW BINARY LOGS and PURGE return 1381, RESET MASTER 1186, SHOW MASTER STATUS and SHOW BINLOG EVENTS no rows.

Privileges: REPLICATION CLIENT (or SUPER) for the SHOW statements, SUPER for PURGE and RESET MASTER.

17.13.3 Miraj as the source of a reference replica#

1. Start Miraj with --log-bin --server-id 1 (identifier different from the replica's).
2. Create a replication account: CREATE USER repl@'%' IDENTIFIED BY '…'; GRANT REPLICATION SLAVE ON *.* TO repl@'%'; (REPLICATION REPLICA is accepted).
3. On the replica (reference server):

CHANGE MASTER TO MASTER_HOST = 'miraj.example', MASTER_PORT = 7007,
       MASTER_USER = 'repl', MASTER_PASSWORD = '…', MASTER_USE_GTID = slave_pos;
START SLAVE;
SHOW SLAVE STATUS\G

The replica sets its connection variables (@master_binlog_checksum, @slave_connect_state, @master_heartbeat_period…), registers (COM_REGISTER_SLAVE), then requests the stream (COM_BINLOG_DUMP). Miraj starts from the file whose head state precedes the replica's GTID position and skips the groups already applied (start by position: MASTER_LOG_FILE / MASTER_LOG_POS), follows rotations, waits for new writes, sends a heartbeat after the requested period, and adds or removes CRC32 sums as requested. A GTID position absent from the log (purged files) returns 1236 to the replica. SHOW PROCESSLIST shows the connection as Binlog Dump.

Verified with a reference 10.4 server: CREATE DATABASE, CREATE TABLE, ALTER TABLE, INSERT / UPDATE / DELETE, multi-table transactions, 25 column types identical on both sides (signed or unsigned integers, DECIMAL up to 30 digits, DATETIME(n), TIMESTAMP(n), negative TIME(n), DATE, YEAR, ENUM, SET, BIT, BLOB, TEXT, JSON, FLOAT, DOUBLE, CHAR), resumption after STOP SLAVE / START SLAVE, rotation, abrupt shutdown of Miraj.

Type mapping: JSON is sent as LONGTEXT, UUID as CHAR(36), VECTOR(n) as VARBINARY(4n), a geometry as GEOMETRY (SRID and WKB). The DDL text is the client's: a reference replica only applies what it can parse (Miraj-specific types and options excluded).

17.13.4 Miraj as a replica of a source#

A Miraj server replicates a source that keeps a binary log (reference server, or another Miraj started with --log-bin):

CHANGE MASTER TO MASTER_HOST = '10.0.0.5', MASTER_PORT = 3306,
       MASTER_USER = 'repl', MASTER_PASSWORD = '…',
       MASTER_USE_GTID = slave_pos,          -- current_pos, or no with MASTER_LOG_FILE / MASTER_LOG_POS
       MASTER_CONNECT_RETRY = 10, MASTER_HEARTBEAT_PERIOD = 30,
       MASTER_SSL = 1, MASTER_SSL_CA = 'C:/certs/ca.pem', MASTER_SSL_VERIFY_SERVER_CERT = 1;
START SLAVE;                         -- START REPLICA, [IO_THREAD | SQL_THREAD]
SHOW SLAVE STATUS;                   -- SHOW REPLICA STATUS, SHOW ALL SLAVES STATUS
STOP SLAVE;
RESET SLAVE;                         -- positions forgotten; RESET SLAVE ALL: configuration erased

CHANGE REPLICATION SOURCE TO SOURCE_HOST = … is accepted (SOURCE_* names). The reference's other options (MASTER_DELAY, IGNORE_SERVER_IDS, DO_DOMAIN_IDS…) are kept with no effect. CHANGE MASTER while the threads are running: 1198; START SLAVE without CHANGE MASTER: 1200.

• IO thread: connection (native password authentication of the legacy protocol, TLS if MASTER_SSL = 1, certificate verified with MASTER_SSL_VERIFY_SERVER_CERT = 1 and MASTER_SSL_CA, client certificate via MASTER_SSL_CERT / MASTER_SSL_KEY), server_id check (identical: 1593, the thread stops), stream request, whole groups received and placed in an in-memory queue. Connection lost: new attempt every MASTER_CONNECT_RETRY seconds. 1236 from the source: the thread stops.
• SQL thread: each group is applied in a transaction by an internal session (privileges and read_only ignored): rows by column position (INSERT, UPDATE and DELETE by primary key, otherwise by all columns), QUERY in its database and its sql_mode. The domain's position is stored in the system table miraj.gtid_slave_pos in the same transaction: after a shutdown, even an abrupt one, resumption happens exactly after the last committed group. Error: the thread stops, Last_SQL_Errno / Last_SQL_Error in the reference format (Could not execute Write_rows_v1 event on table r.t; Duplicate entry …, Error_code: 1062; …); SET GLOBAL sql_slave_skip_counter = 1; START SLAVE skips the faulty group, slave_skip_errors (list of codes or ALL) ignores them.
• Position: @@gtid_slave_pos (persistent), SET GLOBAL gtid_slave_pos = '0-1-42' (threads stopped); in position mode, the applied file and position are kept in replica-source.json.
• Waiting: SELECT MASTER_GTID_WAIT('0-1-42', 10) (0 reached, -1 timeout), SELECT MASTER_POS_WAIT('miraj-bin.000002', 1234, 10).
• Global read lock: FLUSH TABLES WITH READ LOCK on the replica holds the SQL thread group by group (the current group finishes, the next waits with no timeout or error, Slave_SQL_Running_State: Waiting for global read lock); STOP SLAVE interrupts the wait (see chapter 16, §16.6.4).
• Read-only: SET GLOBAL read_only = 1 (or super_read_only) refuses client writes (1290); the SQL thread continues. Not available on a cluster node.
• Restart: the configuration (<folder>/replica-source.json, password included: private file) is re-read and the threads resume if they were running at shutdown.
• Chain: with --log-bin --log-slave-updates, the replica writes what it applies to its own log, under the original GTID.

Privileges: SUPER for CHANGE MASTER, START / STOP / RESET SLAVE; REPLICATION CLIENT for SHOW SLAVE STATUS.

17.13.5 Editions#

The binary log and the replica belong to the Enterprise and Cluster editions (and Developer). The Express edition reads the variables (@@log_bin is 0) but refuses the source and replica statements (9001); --log-bin is reported and ignored at startup there.

17.13.6 Limits#

• No two-phase commit between Miraj's log and the binary log: an abrupt shutdown between the two writes can leave a committed transaction absent from the binary log.
• ROW format only; no STATEMENT or MIXED, no log compression or encryption, no minimal images.
• Accounts (CREATE USER, GRANT), temporary tables and the system database are not replicated.
• Pessimistic concurrency model (--concurrency pessimistic): an update outside a transaction is logged without a before image (after image repeated).
• Replica: a single channel (no named multi-source), in-memory queue (no relay log on disk), serial application, native password authentication of the legacy protocol only (neither ed25519 nor caching_sha2_password); a STATEMENT-format source is not supported (INTVAR, RAND, USER_VAR events ignored); MASTER_DELAY, replication filters and IGNORE_SERVER_IDS have no effect.
• MASTER_GTID_WAIT / MASTER_POS_WAIT only wait outside the locks when alone in a SELECT (SELECT MASTER_GTID_WAIT(…)); used in a larger expression, they hold the catalog and prevent the SQL thread from applying a DDL during the wait.