MariaDB 10.6+

MariaDB shares MySQL's wire protocol and the @effect/sql-mysql2 driver — but the SQL surface diverges enough that the framework keeps it as a separate dialect tag (DB_DIALECT=mariadb). This page enumerates the differences the framework handles for you, plus the ones that bite when you reach for hand-written SQL.

#Target version

MariaDB 10.6 or later. Two reasons:

1. JSON_OBJECT + JSON_ARRAYAGG were added in 10.5, but JSON_ARRAYAGG(... ORDER BY ...) (a MariaDB-only extension the framework's eager-load compiler uses) is stable from 10.5+. ROW_NUMBER + window functions stabilized earlier (10.2+).
2. Native INSERT … RETURNING is in 10.5+, DELETE … RETURNING is in 10.0+. The framework relies on both.

10.5.x works in principle; 10.6+ gives you the long-term-support windowing semantics the framework tests against.

#Configuration

DB_DIALECT=mariadb
DB_URL=mysql://app:app@db.internal:3306/app
# …or discrete fields — the same DB_* vars every dialect uses
# (there are no MARIADB_* vars):
DB_HOST=…  DB_PORT=…  DB_USER=…  DB_PASSWORD=…  DB_DATABASE=…

# Strict mode is recommended. MariaDB defaults to ON in modern
# versions but is less aggressive about it than mysql. Verify:
# `SELECT @@sql_mode;` should contain STRICT_TRANS_TABLES.

#Driver: @effect/sql-mysql2 (variant='mariadb')

Same driver as MySQL — MariaDB is wire-compatible. The framework keys behaviour on the variant flag passed at store construction:

makeMysqlStore({ sqlLayer, variant: 'mariadb', changeStrategy: 'cdc', cdcConfig })

The variant flows through to per-operation getters (supportsInsertReturning, supportsDeleteReturning, supportsUpdateReturning) and to the JSON-agg compiler's dialect branch.

#UPDATE … RETURNING does NOT exist — anywhere

Despite MariaDB's broad RETURNING support — INSERT … RETURNING * since 10.5, DELETE … RETURNING * since 10.0 — there is no UPDATE … RETURNING in any MariaDB version. The framework's first attempt at supportsReturning treated the whole RETURNING family as one flag and emitted UPDATE … RETURNING * on mariadb, which fails with a parse error.

The split:

Operation	MariaDB native?	Framework path
INSERT	yes (10.5+)	INSERT … RETURNING *
DELETE	yes (10.0+)	DELETE … RETURNING *
UPDATE	no, never	UPDATE then SELECT by PK (same as mysql)

The mysql store's class internally exposes three getters (supportsInsertReturning, supportsDeleteReturning, supportsUpdateReturning). MariaDB gets true on the first two and false on the third. User code that calls ctx.store.update(...) returns the row from the follow-up SELECT — same interface, same return shape, just one extra round-trip.

#Correlated subqueries in non-LATERAL derived tables — rejected

MariaDB refuses correlated outer references inside non-LATERAL derived tables. The classic MySQL eager-load pattern:

-- Works in MySQL 8.0.14+ (auto-promoted to LATERAL).
-- Works in postgres.
-- FAILS in MariaDB: Unknown column 't1.id' in 'WHERE'.
SELECT t1.id,
  (SELECT JSON_ARRAYAGG(JSON_OBJECT('title', x.title))
   FROM (SELECT * FROM posts WHERE author_id = t1.id LIMIT 5) AS x
  ) AS posts
FROM users t1;

And MariaDB does NOT accept the LATERAL keyword as a workaround either.

#The framework's workaround: window function pattern

For many() and manyToMany() branches with limit / offset / orderBy, the framework emits a ROW_NUMBER() OVER (PARTITION BY fk ORDER BY …) pattern. The ranking happens in a derived table with NO outer reference, and the correlation lives in the wrapping subquery's WHERE clause where MariaDB accepts it:

-- What the framework emits on MariaDB for users.with({ posts: { limit: 5 } }):
SELECT JSON_OBJECT(
  'id', t1.id,
  'posts', COALESCE((
    SELECT JSON_ARRAYAGG(JSON_OBJECT(
      'id', ranked.id, 'title', ranked.title
    ) ORDER BY ranked.rn)
    FROM (
      SELECT *, ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY created_at DESC) AS rn
      FROM posts
    ) ranked
    WHERE ranked.author_id = t1.id AND ranked.rn <= 5
  ), JSON_ARRAY())
) AS __row
FROM users t1;

For the no-pagination case (no limit, no offset), the framework emits a direct correlated subquery with JSON_ARRAYAGG(… ORDER BY col) — the MariaDB-only extension — for cleaner SQL.

Performance cost: ROW_NUMBER materializes ranking across the whole table on every invocation. For tables in the millions this hurts; MariaDB users running into the cliff should add FK indices and consider limit-less queries against the walker path. For typical app-tier sizes (10k–100k rows) the optimizer prunes by partition + the cost stays linear with per-parent row count.

#JSON_ARRAYAGG(… ORDER BY …) — MariaDB-only

This extension lets you order rows inside a JSON aggregate without a derived-table wrap:

SELECT JSON_ARRAYAGG(x.title ORDER BY x.title)  -- MariaDB ✓ / MySQL ✗
FROM posts x WHERE author_id = ?;

MySQL 8 rejects ORDER BY inside JSON_ARRAYAGG with a parse error. MariaDB has supported it since 10.5.

The framework uses it for the no-pagination case of many() / manyToMany() on MariaDB — cleaner SQL than the ROW_NUMBER pattern when you don't need per-parent limits.

#CDC — binlog (ROW format)

MariaDB has no LISTEN/NOTIFY, but it gets a real out-of-band CDC source: the framework tails the primary's ROW-format binary log via the @vlasky/zongji replication client. This is the MariaDB equivalent of postgres's LISTEN/NOTIFY — every replica tails the binlog itself, so a write on any instance surfaces on every instance's onChange (true cross-instance reactivity in a multi-replica deploy).

This is one binlog-CDC path shared with mysql — mysql-8 and mariadb speak the same ROW binlog to the reader, so both get cross-instance CDC. The only engine difference is the binlog-end query (mariadb SHOW MASTER STATUS; mysql 8.4 SHOW BINARY LOG STATUS), which the store picks per variant.

#How it works

1. With CDC=1 (the default for any SQL dialect) on mariadb, the store starts a binlog reader on a separate replication connection — distinct from the SQL pool.
2. The reader subscribes to WriteRows / UpdateRows / DeleteRows events and turns each into a ChangeEvent { table, op, old, new }. UPDATE events carry both the BEFORE and AFTER row images (needs binlog_row_image=FULL), so old is richer than inline mode can produce.
3. In CDC mode the binlog reader is the sole emitter — the write path stays silent, so each write surfaces exactly once per instance, delivered by that instance's own reader.
4. Framework-internal _voltro_-prefixed tables (and any table not in the reactive set) are skipped by the reader.

#Requirements

The reader fails fast at boot if these aren't met:

• binlog_format=ROW — statement/mixed formats don't carry per-row images.
• binlog_row_image=FULL — needed for complete UPDATE/DELETE before-images (a non-FULL image logs a warning; before-images may be partial).
• log_bin=ON.
• A DB user with REPLICATION SLAVE, REPLICATION CLIENT.
• A UNIQUE server_id per reader. Duplicate server_ids silently break binlog streams — two readers with the same id collide. The framework derives one per pod from POD_NAME / HOSTNAME (falling back to the PID in dev).
• The @vlasky/zongji package. It ships as an optionalDependency of @voltro/sql-mysql; if it's absent, a cdc request throws a clear "install @vlasky/zongji" error rather than silently degrading.

#Resume offsets

Each replica persists its progress in the _voltro_cdc_offsets table — one row per replica (PK = replicaId), holding the last binlog (file, position) it fully processed. On boot the reader resumes from that point; events between crash and resume replay and self-heal via the dispatcher's per-subscribe re-query. (Resume is by binlog file + position, not by GTID — failover relies on the re-query self-heal, not GTID portability.)

If the persisted offset has been purged (err 1236) or rejected after a failover, the reader jumps to the current binlog end and signals a resync so dependent subscriptions re-query rather than missing the gap.

#Boot summary

[voltro:dev] sql dialect resolved: mariadb — CDC: binlog CDC (ROW), RETURNING: native (INSERT/DELETE); UPDATE then SELECT
[voltro:dev] mariadb binlog CDC enabled { replicaId: 'pod-0', serverId: 1234567, reactiveTables: 42 }
[voltro:dev] cdc: binlog reader attached { serverId: 1234567, from: 'current-end' }

Set CDC=0 to fall back to inline-emit (single-process only, no binlog dependency) — useful for tests and single-binary deploys.

#Workflow cluster

@effect/cluster's mysql branch (GET_LOCK + ON DUPLICATE KEY UPDATE) works on MariaDB. The framework dispatches it via the same variant: 'mariadb' flag the store carries.

#Read replicas

MariaDB's GTID format differs from MySQL's: 0-1-100 (domain-server-sequence) vs aaaaaaaa-...:1-100 (UUID-based). The framework's replication adapter probes the right variable per variant — @@global.gtid_current_pos on mariadb, @@global.gtid_executed on mysql. As on mysql, the catch-up comparison is a stub today (compare() always answers 'behind'): the default RYW_POLICY=fallback never calls it, but RYW_POLICY=wait always routes RYW reads to the primary. A real GTID-subset round-trip is a tracked follow-up.

#Identifier quoting

`name` — backticks. Same as MySQL.

#Known caveats

• mariadb schema package is wire-compatible with mysql. If you migrate from MySQL → MariaDB, the framework re-emits DDL cleanly via applySchema(..., 'mariadb'). Production data round-trips through mysqldump without translation.
• sql_mode=NO_BACKSLASH_ESCAPES is sometimes set on MariaDB deploys. The framework's identifier escaping handles it, but user-written unsafe() strings that hand-escape backslashes may produce wrong output. Leave that mode off if you can.
• Sequence-based ID columns. MariaDB has true CREATE SEQUENCE; the framework doesn't use it (TypeID / ULID / Snowflake are client-side). If you reach for sequences for legacy reasons, they're outside the framework's auto-injection path.
• Hand-rolled AUTO_INCREMENT primary keys work the same as on mysql: an insert / insertMany with no client-side id recovers the DB-generated id via LAST_INSERT_ID() (connection-pinned; insertMany recovers the whole consecutive range). See the mysql page for the worked example — the recovery path is identical on both engines.

#Where it lives

• voltro/packages/sql-mysql/src/index.ts — exports mariadbDialect
• voltro/packages/sql-mysql/src/store.ts — supportsInsertReturning / supportsDeleteReturning / supportsUpdateReturning getters branch on variant
• voltro/packages/database/src/jsonEagerCompiler.ts — mariadbManySubquery / mariadbManyToManySubquery ROW_NUMBER window-function pattern
• voltro/packages/database/src/migrate.ts — mariadb shares the mysql DDL branch (text-DEFAULT stays as TEXT — mariadb allows it)
• voltro/packages/sql-mysql/src/binlogCdc.ts — ROW-format binlog reader (@vlasky/zongji), per-pod server_id, file/position resume + self-heal on purge/failover
• voltro/packages/sql-mysql/src/cdcOffsetsTable.ts — _voltro_cdc_offsets per-replica binlog (file, position) checkpoint