MariaDB 10.6+

Wire-compatible with MySQL but diverges on UPDATE-RETURNING (doesn't exist), correlated derived tables (rejected), and JSON_ARRAYAGG ORDER BY (MariaDB-only extension). Framework dispatches all three at the dialect-tag level.

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.

Character set — declared per store

The framework's CREATE TABLE used to name no character set, so a text() column inherited whatever the database was created with, while a json() column did not: MariaDB's JSON alias expands to LONGTEXT CHARACTER SET utf8mb4. On a database created utf8mb3 — the server default of some managed clusters — one table({...}) therefore held a text column that refused an emoji with errno 1366 next to a json column that took it, and nothing in the declaration said so.

The character set is now a property of the store, declared once in app.config.ts:

// app.config.ts
export default {
  type: 'api' as const,
  name: 'myApi',
  store: { dialect: 'mariadb' as const, charset: 'utf8mb4' as const },
}

Declared, it applies to every table the app declares — the framework's own declared _voltro_* tables included:

  • Created with it. Every CREATE TABLE carries DEFAULT CHARACTER SET utf8mb4, so every string column (text, varchar, enum, and the ids and references stored as VARCHAR(64)) is in it, and so is every column a later migration adds.
  • Converted to it. A live table whose string columns are stored otherwise is planned as convert-charset — a table rewrite, one ALTER TABLE … MODIFY COLUMN … CHARACTER SET per table, and the plan says so. A hand-converted table (ALTER TABLE … CONVERT TO CHARACTER SET) is read back from information_schema like any other, so the plan sees it. A column's CHECK survives the rewrite: MariaDB keeps a CHECK written on the column itself — how a text().oneOf([...]) table is created — as part of the column definition, where a bare MODIFY COLUMN would drop it, so the rewrite restates it. The same holds for a nullability, type or default change on such a column.
  • Foreign keys travel with the rewrite. Neither engine changes the character set of a column a foreign key holds (MariaDB 1832/1833, MySQL 3780), so every constraint that touches a converting table is dropped before it and re-created after — and a constraint between two converting tables comes back once both are at the target. That is what lets a pair convert together instead of one side failing with InnoDB errno 150 against the other. A constraint to a table the plan does not convert (one the app does not declare, or one on the ignore list) that is stored otherwise is refused at plan time, with both tables named, rather than found by the ALTER halfway through. When that table is a soft-drop snapshot (<name>__dropped_<stamp>), the refusal names the reclaim instead — voltro db gc-snapshots --before <date>, with the date that covers the newest one — and voltro db restore-snapshot for data you still want.
  • json columns keep the engine's charset. JSON CHARACTER SET x is a syntax error on both engines, and a MariaDB json column is utf8mb4 underneath by construction. So the declaration governs the string columns; under utf8mb4 the two agree, under utf8mb3 they do not — and that is a decision you wrote down, not a finding.
schema diff: 2 operations, 0 blocked

✓ ALTER TABLE comments CONVERT 3 string columns TO CHARACTER SET utf8mb4 (re-creating 1 foreign key)  # table rewrite: 3 string columns utf8mb3 → utf8mb4; 1 foreign key dropped and re-created around it
✓ ALTER TABLE issues CONVERT 2 string columns TO CHARACTER SET utf8mb4 (re-creating 1 foreign key)  # table rewrite: 2 string columns utf8mb3 → utf8mb4; 1 foreign key dropped and re-created around it

utf8mb3 → utf8mb4 is safe: it holds every value the columns contain today. The other direction is lossy and blocked — a 4-byte character already stored is refused (strict mode) or truncated (non-strict) — and waits for the destructive opt-in like a drop does.

Unset, nothing changes: tables inherit the database default, byte for byte as before. utf8mb4 is deliberately not the default — a fresh utf8mb4 column cannot hold a foreign key to an imported utf8mb3 one, so a default that decides what new tables get would hand every existing database that failure on the next table a migration adds. Declare utf8mb3 to pin a database that has to stay there: new tables then match the imported ones whatever the server default says.

The setting has no environment override on purpose. The pre-deploy migrate job and every serving process read the same app.config.ts; DB_DIALECT still overrides the dialect, and when that moves a process off the mysql family the charset does not apply and the boot log says so.

New string columns carry the declared CHARACTER SET in their own ADD COLUMN, including defaulted columns and both phases of a backfill. They therefore use the declaration even when the same plan adds them before converting the table's old default. A new table referencing a converting parent waits for that parent's column changes and conversion; its descendants wait too. New foreign keys wait for both endpoints to exist in the target charset, including constraints deferred from cyclic table creation. One apply converges without needing a second run.

Tables the app does not declare follow it too

The workflow engine creates its storage itself on the first process start — cluster_messages, cluster_replies, cluster_runners, cluster_locks, cluster_migrations — naming no character set, and the runner keeps bookkeeping tables of its own — _voltro_deploys, _voltro_migration_ops, _voltro_migration_plans, _voltro_schema_owner and _voltro_plugin_migrations. None of them is declared, so no plan converts them, and cluster_messages.payload holds every workflow call's input as JSON text: created in a utf8mb3 default, it refuses a workflow whose input carries an emoji. With utf8mb4 declared, every table the framework creates is created in it:

  • The bookkeeping tables are created with the declared charset — whichever step creates them first: a deploy, a file migration's commit probe, the ownership check of a first boot, a plugin's migration step or voltro migrate, which creates the declared tables with it as well.
  • The workflow engine's tables are converted by the process that creates them, on its first boot, under the same lock and before any runner uses them — they are empty, so this costs nothing. A workflow table the process finds is never rewritten at boot: it may hold a backlog and be in use by running processes.
  • Timeline preparation also uses the declared charset when it creates cluster storage after voltro db apply, during voltro migrate --create-only, or during development auto-migration. Those new tables follow the declaration even when the database default remains utf8mb3.
  • Tables that already exist from before the declaration are converted by voltro db apply (with or without --plan, and also when the plan has nothing to do) once the deploy has finished, and by the voltro dev auto-migrate. Each string column is rewritten (MODIFY COLUMN … CHARACTER SET utf8mb4), keeping its type, its nullability and a column CHECK. voltro db plan lists this under the plan:
  store charset: `db apply` also converts, after the plan (tables the framework created that the app does not declare):
   - cluster_messages: 9 string columns utf8mb3 → utf8mb4

The database's own default is left alone. It belongs to whoever runs the database, not to the app's schema; changing it would need a privilege a managed cluster often does not grant and would change what every later table gets. The framework sets a database default only on a database it creates itself — a tenant namespace, below.

The step only ever widens: under utf8mb3 it does nothing. It runs after the deploy has closed, so a deploy that fails and is undone never ran it, and "the database is as it was" stays true; if the step itself fails, the schema stays applied and a re-run of voltro db apply finishes it. A column with a default or a generation expression is named and left alone.

On MySQL, a connection that prepared an INSERT into one of those tables before db apply rewrote it keeps refusing 4-byte values with that statement (Conversion from collation utf8mb4_unicode_ci into utf8mb3_general_ci impossible for parameter) until the connection is replaced — the driver caches the prepared statement. A process started after the migrate job is not affected; MariaDB is not affected at all.

Tenant namespaces follow the declaration too

Under tenancy.isolation: 'namespace' a tenant is a database beside the primary on this family. The provisioner creates it with the declared charset as its default (CREATE DATABASE IF NOT EXISTS … CHARACTER SET utf8mb4) and its tables with it, so a tenant provisioned on a utf8mb3 server holds what the primary holds. The tenant DDL is database-qualified (`tenant_x`.`todos`) and never switches the session — MySQL refuses a prepared USE, and on MariaDB a USE outlived its transaction on the pooled connection.

A tenant provisioned before the declaration is not converted: re-provisioning only creates missing tables, and the plan is scoped to the primary. It is named instead — voltro db plan and voltro doctor read every tenant_* database and list, per namespace, the tables stored otherwise — so it is a line, never a silence. Convert its tables by hand or re-create the namespace.

When nothing is declared, the framework says what disagrees

voltro db plan and voltro doctor read information_schema and name every table whose text columns cannot hold what its json columns can — the reported situation, as a line rather than an afternoon:

schema diff: 0 operations, 0 blocked

  (schema is up to date)
  store charset: 1 table whose text columns cannot hold what a json column can
   - ticketCache: its json column holds every character (utf8mb4); these text columns do not — id (utf8mb3), description (utf8mb3)
   declare the store's character set in app.config.ts — store: { dialect: 'mariadb' | 'mysql', charset: 'utf8mb4' } — and run `voltro db plan`: …

A table with no json column whose string columns are in two character sets (one converted by hand) is named too. A healthy utf8mb4 database reports nothing.

And the write that fails now says why. The store's detail: line carries the driver's sentence, its codes, and the remedy — only for a 1366 over a 4-byte lead byte, never for another 1366:

store operation failed  operation=upsert  table=ticketCache
detail: Incorrect string value: '\xF0\x9F\xA7\xA9 p...' for column `app`.`ticketCache`.`description` at row 1 [code=ER_TRUNCATED_WRONG_VALUE_FOR_FIELD errno=1366 sqlState=22007]
  hint: column 'description' is stored in a character set that cannot hold a 4-byte character (utf8mb3). Declare the store's character set in app.config.ts — store: { dialect: 'mariadb' | 'mysql', charset: 'utf8mb4' } — and run `voltro db plan`: every declared table is created with it and converted to it.

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.

TLS and the pool bounds are shared with MySQL, driver-for-driver: ?sslmode=require / ?ssl=true encrypts (certificate not verified), an unsupported mode fails at boot rather than falling back to plaintext, and the acquire wait is bounded by queue length because mysql2 has no time-based bound for it. See MySQL → TLS and the acquire-timeout table.

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

Every table is reactive by default — nothing to opt into. .nonReactive() turns it off for one table on every dialect: no subscriber fires, locally or across instances. The write still happens; only the notification is suppressed. See the schema overview.

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. Committed local writes emit inline even in CDC mode. The reader adds cross-instance delivery; the delivery gate collapses the matching binlog echo so each local change surfaces once.
  4. Framework-internal _voltro_-prefixed tables (and any table not in the reactive set) are skipped by the reader.

The reader owns two connections outside DB_MAX_CONNECTIONS: one control connection for metadata, persisted-file validation and watchdog probes, plus one replication stream. Those operations share the replication credentials; no extra CDC SQL pool is retained. For a primary pool of 3, active binlog CDC therefore needs up to 5 connections per process, before separately configured read-replica pools. The boot pool line counts both CDC connections. Before a replacement starts, the reader terminates its old binlog thread on the server and confirms that it has disappeared, including when the old control connection was lost. Cleanup and reconnect are serialized; close() waits for owned connections to finish.

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.

A table the reader cannot decode

@vlasky/zongji reads each event's column layout from the binlog's Table_map (fixed on disk) and compares it to a fresh information_schema fetch. A mismatch throws:

Table app.sessions schema changed between binlog event and metadata fetch:
  the event has 9 columns, fetched metadata has 8

The usual cause on MariaDB is a UNIQUE constraint on an unbounded text column. MariaDB can only back that with a HASH long-unique index, and that index adds a hidden DB_ROW_HASH_n column to the InnoDB row — present in the binlog row image, absent from information_schema.COLUMNS. So the counts can never agree, and every write to that table trips it. Nothing is broken; the table is shaped that way.

The reader finds such tables when CDC starts, reports each once, and excludes it — so there is no reconnect loop. Cross-instance change events for that table are lost; own-node reactivity is unaffected, because writes still emit inline.

How long that verdict lasts. It is a reading of the schema at the moment the reader attached, and applying the remedy does not lift it by itself. voltro dev builds its store before it migrates — kv, cross-replica broadcast and the analytics mirror all need one — so on a boot whose own migration bounds the column, the finding is a second older than the fix. That boot therefore re-checks the exclusion set once all its schema work is done and re-admits the table in the same run:

cdc: table 'sessions' looks undecodable … Not a verdict yet; the definitive line follows.
auto-migrate: applied 74 op(s) in 1157ms
cdc: table 'sessions' re-admitted to binlog capture — the hidden-column condition is gone.

The re-check runs in both directions: schema work that creates an unbounded unique on a captured table excludes it there and then, rather than after the reader has failed on three writes.

voltro serve has no equivalent step and needs none — it builds its store after every schema step and never applies DDL itself, so its reading at attach is already the final one. Everywhere else the exclusion holds until the process restarts.

The remedy is to bound the column:

tokenHash: text().maxLength(64).unique()   // VARCHAR(64) → ordinary B-tree index

ALTER TABLE … FORCE does not help. The rebuild recreates the index and therefore recreates the hidden column — measured before and after: same column count both times. If you already tried it, that was not your mistake.

A bounded unique is worth having anyway: it is also what keeps the key inside the index-size limits on every dialect.

The other cause of the same message is a backlog event that predates a migration — the reader was down while a table changed. That one is unreplayable but transient: the reader skips to the current binlog end, signals a resync, and recovers. One warning, then it is over.

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