MariaDB and MySQL

@ocoda/event-sourcing-mariadb provides the MariaDBEventStore and the MariaDBSnapshotStore, on InnoDB, for MariaDB and, from 4.1, for MySQL. It needs the mariadb driver (^3.5.3), MariaDB’s connector, which connects to MySQL too. The driver is a peer dependency: see Installation and Database drivers are peer dependencies. The same classes and options serve both servers: connect() asks the server what it is (@@version, @@version_comment), and treats every server that doesn’t name itself MariaDB as MySQL. The tested versions are in Supported versions.

Coming from 3.x?

4.0 stores events and snapshots in a new schema (schema v2). Tables that 3.x created must be migrated once with migrate(), which runs on MariaDB only: see Migrating 3.x tables. The design is in ADR 0002.

Supported versions

Server	Tested on
MariaDB	10.11, 11.4, 11.8 and 12.3 (long-term releases), 13.0 (rolling release)
MySQL	8.4 and 9.7 (long-term releases), 26.7 (Innovation release)

• Every version runs the stores’ tests and the shared conformance suite, read-all-gap-safe included. The MySQL servers run with the binary log on, as MySQL does by default. The tests of the 3.x migration run on MariaDB only, since migrate() does.
• MariaDB supports a rolling release, and MySQL an Innovation release, only until the next one, so the tests move on to each new rolling and Innovation release. MySQL 8.0 reached its end of life in April 2026 and isn’t tested.
• Other servers that speak MySQL’s protocol (Percona Server, Amazon Aurora MySQL, TiDB, Vitess) and clusters (Galera, Group Replication) aren’t tested. See Topologies and capabilities for what the event store claims on them, and Other databases for an overview.

Configuration

The store options are the options of a mariadb pool (PoolConfig), plus ddl:

import { EventSourcingModule } from '@ocoda/event-sourcing';
import {
  MariaDBEventStore,
  type MariaDBEventStoreConfig,
  MariaDBSnapshotStore,
  type MariaDBSnapshotStoreConfig,
} from '@ocoda/event-sourcing-mariadb';

const connection = { host: 'localhost', port: 3306, user: 'app', password: process.env.DB_PASSWORD, database: 'app' };

EventSourcingModule.forRoot<MariaDBEventStoreConfig, MariaDBSnapshotStoreConfig>({
  eventStore: { driver: MariaDBEventStore, ...connection, connectionLimit: 20 },
  snapshotStore: { driver: MariaDBSnapshotStore, ...connection },
});

• ddl: 'auto' (the default) lets ensureCollection() create the tables of a pool. ddl: 'none' only checks them and registers them in the catalog: see Creating the tables yourself.
• connect() checks the connection, so a wrong host or password fails the application’s bootstrap. disconnect() can be called more than once, and before connect().
• The store reads and writes times as UTC text, so the connector’s timezone and the session’s time_zone don’t change them. Positions are read as text too, so bigIntAsNumber and insertIdAsNumber don’t change them either.
• The stores run their sessions in READ COMMITTED: they add SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED after your initSql, so a read never returns an append that could still roll back, whatever the server’s default. When the binary log is on (MySQL turns it on by default), its format must be MIXED (MariaDB’s default) or ROW (MySQL’s default): InnoDB refuses READ COMMITTED writes with binlog_format=STATEMENT.

Connecting to MySQL

MySQL users log in with caching_sha2_password: MySQL 8.4 turns mysql_native_password off by default, and 9.0 removed it. When the server hasn’t cached a user’s password yet, which is the case for the first login after a restart, a FLUSH PRIVILEGES or a password change, the connector must send the password over TLS, or encrypted with the server’s RSA public key. Over plain TCP, without one of the options below, the connection fails with ER_CANNOT_RETRIEVE_RSA_KEY (“RSA public key is not available client side”). A setup without them can seem to work while another client’s login keeps the password cached, and then fail after the server restarts. Pick one:

• TLS (ssl): the password goes over the encrypted connection, and no key is needed. The connector verifies the server’s certificate, so pass the CA that signed it (ssl: { ca }); MySQL’s auto-generated certificates are self-signed. A Unix socket (socketPath, not on Windows) needs no key either.
• cachingRsaPublicKey: the server’s RSA public key, as a file path or as the PEM text. SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key' prints it.
• allowPublicKeyRetrieval: true: the connector asks the server for its key, over the unencrypted connection. Use it on a network you trust only: whoever can intercept the connection can hand the connector a key of their own and read the password.

import { readFileSync } from 'node:fs';

const connection = {
  host: 'mysql.internal',
  port: 3306,
  user: 'app',
  password: process.env.DB_PASSWORD,
  database: 'app',
  // One of:
  ssl: { ca: readFileSync('/etc/mysql/ca.pem', 'utf8') },
  // cachingRsaPublicKey: '/etc/mysql/public_key.pem',
  // allowPublicKeyRetrieval: true,
};

Schema

Every pool has an event table, events for the default pool and <pool>-events for the others, and a snapshot table, snapshots or <pool>-snapshots. ensureCollection() registers every table in the catalog, event_sourcing_collections, one table per database:

CREATE TABLE IF NOT EXISTS `event_sourcing_collections` (
  name VARCHAR(64) NOT NULL PRIMARY KEY,
  kind ENUM('events', 'snapshots') NOT NULL,
  schema_version SMALLINT NOT NULL,
  last_position BIGINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `events` (
  stream_id VARCHAR(255) NOT NULL,
  version INT NOT NULL,
  event VARCHAR(255) NOT NULL,
  payload JSON NOT NULL,
  event_id VARCHAR(40) NOT NULL,
  aggregate_id VARCHAR(255) NOT NULL,
  occurred_on DATETIME(3) NOT NULL,
  correlation_id VARCHAR(255) NULL,
  causation_id VARCHAR(255) NULL,
  global_position BIGINT NOT NULL,
  headers JSON NULL,
  event_version INT NULL,
  PRIMARY KEY (stream_id, version),
  UNIQUE KEY ux_global_position (global_position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `snapshots` (
  stream_id VARCHAR(255) NOT NULL,
  version INT NOT NULL,
  payload JSON NOT NULL,
  snapshot_id VARCHAR(40) NOT NULL,
  aggregate_id VARCHAR(255) NOT NULL,
  registered_on DATETIME(3) NOT NULL,
  aggregate_name VARCHAR(255) NOT NULL,
  latest VARCHAR(270) NULL,
  PRIMARY KEY (stream_id, version),
  UNIQUE KEY ux_latest (aggregate_name, latest)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

• The catalog lists the collections (listCollections() reads it, so 3.x tables aren’t listed until they are migrated), and its last_position is the counter of each event pool’s global positions.
• Times are DATETIME(3): the UTC wall time, to the millisecond. 3.x stored a TIMESTAMP to the second.
• Stream ids are case-sensitive: the tables compare text in binary (utf8mb4_bin), like the PostgreSQL, MongoDB and in-memory stores. Acc-1 and acc-1 are two streams. The 3.x tables used the database’s default collation, which usually ignores case; migrate() gives a 3.x stream whose ids differ in case only one id (see Stream ids that differ in case only).
• Limits. Stream ids, aggregate ids, event names, correlation ids and causation ids are at most 255 characters (the core checks them before any I/O), and the headers of an event at most 8 KiB of JSON. A table name is at most 64 characters, so a pool name is at most 57 characters (54 for a pool that also has snapshots); ensureCollection() rejects a longer one with an EventStoreCollectionCreationException or a SnapshotStoreCollectionCreationException.
• Pool names are table names, compared with their case, as the server does with lower_case_table_names = 0, the default on Linux. Where it isn’t 0 (the default on Windows and macOS), the server doesn’t tell table names apart by case: use lower-case pool names there, and never two pool names that differ only in case (Orders and orders). MySQL accepts the setting only when its data directory is initialized, so it can’t be changed later.
• Payloads are JSON. A bigint in a payload can’t be stored: the append fails with outcome: 'not-persisted' before any I/O. Store big integers as strings.
• On MySQL, payload and headers are LONGTEXT, in the tables the stores create, and hold the text that JSON.stringify wrote. MySQL’s JSON type is a binary format that sorts the keys of objects and prints doubles with 16 significant digits, so 0.9999999999999999 would come back as 1. MariaDB’s JSON, shown above, is an alias of LONGTEXT with a JSON_VALID check, which keeps the text as written. The stores read both column types, but a table you create by hand with MySQL’s JSON type is accepted as it is, and rounds doubles and sorts keys as described: use LONGTEXT.

Topologies and capabilities

connect() asks the server what it is and sets the event store’s capabilities to match. headers is true everywhere: headers are stored in the headers column.

Deployment	atomicAppend	globalOrder	Tested
One MariaDB or MySQL server on InnoDB	true	'gap-safe'	Yes, every supported version
The primary of a replica set: asynchronous or semi-synchronous replication, MySQL Group Replication in single-primary mode	true	'gap-safe'	No; see Failover and restores
Amazon Aurora MySQL, the writer	true	'gap-safe'	No: expected, not proven
Galera: MariaDB Galera Cluster, Percona XtraDB Cluster	true	'best-effort'	No
MySQL Group Replication in multi-primary mode	true	'best-effort'	No
TiDB	true	'best-effort'	No
Vitess or PlanetScale, transaction_mode SINGLE or TWOPC	true	'best-effort'	No
Vitess or PlanetScale, any other transaction_mode (MULTI is the default)	false	'best-effort'	No
Behind a router, proxy or endpoint that sends reads to replicas	true	Not supported: readAll can skip events, and the store still reports 'gap-safe'	–

'gap-safe' rests on one InnoDB server ordering the commits of a pool by the lock on its counter row (below). connect() tells the other deployments apart by system variables that only they have, and logs one warning per process and kind of deployment:

• Galera: wsrep_on is on and a provider is loaded (wsrep_provider isn’t none). Row locks are not cluster-wide.
• Group Replication in multi-primary mode: group_replication_single_primary_mode is off. Row locks are per member, and a conflict with another member surfaces as error 3101 when the append commits, which the store reports as outcome: 'not-persisted': nothing was stored.
• TiDB: tidb_txn_mode exists. TiDB isn’t InnoDB: its locking reads don’t wait for an append in flight, so there readAll reads the pool’s counter at a gap without LOCK IN SHARE MODE, which TiDB refuses.
• Vitess (vtgate, PlanetScale): transaction_mode exists, or @@version or @@version_comment is Vitess’s. Vitess commits a transaction that spans shards shard by shard, and reads every shard at a snapshot of its own. With MULTI, the pool’s counter and its events can sit on different shards, so an append isn’t atomic.
• Amazon Aurora MySQL reports itself as MySQL and keeps 'gap-safe': a cluster has one writer, on which the isolation levels work as in MySQL. That is expected, not tested: Aurora is closed source, and Aurora MySQL 3 is based on MySQL 8.0, which isn’t tested. Connect the store to the cluster’s writer endpoint; see the caution below about reader endpoints. An unplanned failover of a Global Database can lose recent commits: see Failover and restores.

Percona Server for MySQL, and managed services that run MariaDB or MySQL themselves (Amazon RDS, Azure Database for MySQL, Google Cloud SQL), count as one server or the primary of a replica set, and aren’t tested.

Global positions. An append takes its positions from its pool’s row in the catalog, UPDATE … SET last_position = LAST_INSERT_ID(last_position + n), as the first write of its transaction. The row lock is held until the append commits or rolls back, so the appends to a pool commit in the order of their positions, and a rolled-back append gives its positions back: a pool’s positions have no holes. The counter never goes down: a pool whose table is dropped and created again continues after its last position, and ensureCollection() raises a counter that is behind the stored positions.

Why readAll is gap-safe. On MariaDB, InnoDB builds the read view of a statement from a walk over the active transactions that is not atomic against commits, so, rarely, a statement sees an append without the one that committed just before it. readAll therefore hands out a batch only as far as its positions follow on from where it is. At a gap, it reads the pool’s counter with a shared lock, which waits for the append that holds it; every position up to that value has committed and is visible then, and a gap below it is permanent (a deleted event, or a pool that was dropped and created again). A reader that tails the store and resumes at lastPosition + 1n therefore never skips an event. Each batch is one statement; no transaction, cursor or connection is held between batches.

On MySQL, a read view is atomic against commits: a committing transaction leaves the list of active transactions before it releases its locks, under the same mutex under which a new read view copies that list. So every statement sees the positions up to some point, without holes, and the counter read above only runs at a permanent gap. The store uses the same reader on both servers. The evidence is in ADR 0002, for MariaDB and for MySQL, the latter with the InnoDB source of 8.4 and 9.7 and a side-by-side run in which MariaDB’s read views tore and MySQL’s didn’t.

Every read must reach the primary

'gap-safe' holds when every statement of readAll, the batch reads and the counter read at a gap, reaches the server the appends run on. Connect the event store to the primary, directly or through a route that sends every statement there. Don’t route its pool through a router or proxy that splits reads from writes (MySQL Router’s read/write splitting, ProxySQL query rules that send reads to replicas, MaxScale readwritesplit), or through a reader endpoint (Aurora’s reader endpoint, a custom endpoint that includes readers): a batch read on a replica that lags would pass the lag’s gap off as a permanent one, and readAll would skip those events.

Failover and restores

MariaDB and MySQL replication is asynchronous unless you configure it otherwise, so a promoted replica can lack the newest commits; so can a restored backup, or an Aurora Global Database after an unplanned failover. A pool’s counter (last_position in event_sourcing_collections) then lags behind positions that readers already read, and new appends take those positions again. Failover and replicas explains what that does to readers, the replication settings that guard against it, and how to check the readers after a failover or a restore.

Appends

An append runs on a connection of its own, in a READ COMMITTED transaction (set explicitly, since MariaDB and MySQL both default to REPEATABLE READ: with innodb_snapshot_isolation, the default from MariaDB 11.6.2, a REPEATABLE READ transaction could fail with error 1020):

1. the version check, outside the transaction (SELECT MAX(version));
2. the counter update, which takes the pool’s row lock and the positions;
3. one multi-row INSERT with explicit columns;
4. COMMIT.

• A duplicate on the primary key (stream_id, version) is a version conflict: another append took the version. A duplicate on ux_global_position means the counter is behind the stored positions; the append fails with outcome: 'not-persisted', and ensureCollection() heals the counter.
• ensureCollection() registers a table without locking its rows, so an instance can start while others append to the pool.
• Every failure before the COMMIT rolls back (explicitly, also after a lock wait timeout, which InnoDB doesn’t roll back) and throws an EventStorePersistenceException with outcome: 'not-persisted'. So does a COMMIT that the server refuses and rolls back: a deadlock (how Galera reports a failed certification), or error 3101 (a conflict with another member of a multi-primary Group Replication group). Any other failure while committing, such as a lost connection, is 'unknown'.
• An append to a pool without a catalog row (never ensured, or not migrated) fails with outcome: 'not-persisted' and an EventCollectionNotFoundException as its cause, and creates nothing.
• ExpectedVersion.Any makes up to 16 attempts while concurrent appends take the versions, and can still conflict under sustained contention on one stream: retry such a conflict in your application.

Throughput. The counter’s row lock is held through the commit, so the appends to one pool are serialized, and each one costs a commit flush (innodb_flush_log_at_trx_commit=1), plus the binary log’s when it is on. Pools are the unit of scale: appends to different pools don’t wait for each other. Measured with 8 writers, 1 to 3 events per append:

Server	One pool	8 pools, one writer each
MariaDB 10.11 and 11.8 in Docker, data in memory (tmpfs)	555 to 685 appends/s	1,180 to 1,560 appends/s in total
MariaDB 10.11 in Docker, data on disk, binary log on	101 appends/s (212 without the binary log)	438 appends/s in total

These are small machines under other load; measure your own. A single writer that waits for each append does 75 (on disk) to 345 (in memory) appends per second.

The binary log on MySQL. MySQL turns the binary log on by default, with sync_binlog = 1, so every append also waits for a flush of the binary log while it holds its pool’s counter (MariaDB leaves the binary log off by default). That wasn’t measured on MySQL; on MariaDB, on disk, the binary log halved what one pool takes, from 212 to 101 appends/s (above). Spread write-heavy work over more pools, and turn the binary log off only on a server that needs it for nothing (replication, point-in-time recovery).

Locks and timeouts. An append holds its pool’s row in event_sourcing_collections from the counter update to its COMMIT, a few milliseconds. The next append to the pool waits for it, and so does a readAll that meets a gap; each permanent gap (a deleted event) costs a readAll that crosses it one locked read of the counter. A waiting append gives up after innodb_lock_wait_timeout (50 s by default) with outcome: 'not-persisted'. If a session stalls between the counter update and its commit (a frozen process, a network partition), the server keeps its transaction, and every append to the pool waits, until it ends the session, by default only after wait_timeout (8 hours). On MariaDB, bound that with idle_write_transaction_timeout, for the application’s user or in the store options:

eventStore: { driver: MariaDBEventStore, ...connection, initSql: 'SET SESSION idle_write_transaction_timeout = 30' },

MySQL has no idle_write_transaction_timeout: there, this initSql fails every connection (error 1193, unknown system variable), and the bootstrap with it. On MySQL, lower wait_timeout, after which the server ends a session that sent nothing, whether or not it is in a transaction, and keep the pool’s idleTimeout (1,800 s by default) below it with minimumIdle: 0, so that the pool closes idle connections before the server does (by default minimumIdle is the connectionLimit, and the pool closes none). The pool checks a connection before handing it out and replaces one that the server closed:

eventStore: { driver: MariaDBEventStore, ...connection, initSql: 'SET SESSION wait_timeout = 60', idleTimeout: 30, minimumIdle: 0 },

Snapshots

• The last snapshot of a stream is the one with the highest version. The store flags it as the latest (latest = 'latest#<streamId>'), and the unique key ux_latest allows one flagged snapshot per stream (unflagged snapshots have a NULL flag, and NULLs are distinct).
• appendSnapshot locks the stream’s flagged snapshot, checks that the new version is higher, unflags it and inserts the new snapshot, in one READ COMMITTED transaction. Concurrent appends to a stream wait for each other there; appends that race on a new stream end on the unique keys, with a SnapshotStoreVersionConflictException for all but one. Under load, InnoDB can pick an append as the victim of a deadlock on the unique key (also between streams of one aggregate), and a multi-primary Group Replication group can refuse its COMMIT (3101); the store runs it again, up to 10 times.
• getLastEnvelopesForAggregate (SnapshotRepository.loadAll()) reads the flagged snapshots in descending binary order of the aggregate ids, with aggregateId as an exclusive cursor.
• A 3.x snapshot table keeps working until it is migrated: ensureCollection() logs a warning and registers it as schema version 1, and the store reads and writes registered_on the 3.x way, to the second, without the unique flag. The store decides this when it first uses the table, so restart the instances that use a 3.x snapshot table after you migrate it.

Creating the tables yourself

With ddl: 'none', the stores never run CREATE, ALTER or DROP. ensureCollection() checks the tables and registers them in the catalog, and throws when something is missing, with the statements to run in the error (EventStoreSchemaException with found: 'missing' and a remedy for events, SnapshotStoreCollectionCreationException whose cause has the statements for snapshots; the snapshot store logs them too). The statements are the ones in Schema and the registration:

INSERT INTO `event_sourcing_collections` (name, kind, schema_version, last_position)
  SELECT 'events', 'events', 2, COALESCE(MAX(global_position), 0) FROM `events`
  ON DUPLICATE KEY UPDATE schema_version = 2, last_position = GREATEST(last_position, VALUES(last_position));
INSERT INTO `event_sourcing_collections` (name, kind, schema_version, last_position)
  VALUES ('snapshots', 'snapshots', 2, 0)
  ON DUPLICATE KEY UPDATE schema_version = 2;

On MySQL, the statements in the error create payload and headers as LONGTEXT (see Schema), and MySQL runs the event table’s registration statement with a deprecation warning (1287) for VALUES(), which it still accepts. The stores themselves don’t use VALUES().

Privileges. With ddl: 'none', the application’s user needs SELECT and INSERT on the event tables, SELECT, INSERT and UPDATE on the snapshot tables, and SELECT, INSERT and UPDATE on event_sourcing_collections. With ddl: 'auto' it also needs CREATE on the database. migrate(), on MariaDB, needs SELECT, INSERT, UPDATE, CREATE, ALTER and DROP on the database (the event migration creates, renames and drops tables); a user with ALL PRIVILEGES on the database has them. The dry run writes nothing, so it can’t check them. The event migration checks the privileges of the copy and the swap before it copies anything: its first steps drop and create the copy, then rename the empty copy and back (probe-swap), which needs what the swap needs. A step that lacks a privilege fails with the privileges in its error, and the migration continues from there once they are granted. With the PROCESS privilege, a lock wait timeout also names the sessions that hold a transaction open.

Migrating 3.x tables

MariaDB only

migrate() and migrations/4.0.sql run on MariaDB only: their session settings and checks are MariaDB’s (tx_isolation, max_statement_time, wsrep_*). On MySQL, MariaDBEventStore.migrate() and MariaDBSnapshotStore.migrate(), static or on a store, throw an UnsupportedOperationException before they change anything. To bring 3.x data on MySQL to 4.x, move the 3.x tables aside (RENAME TABLE), let 4.x create its own, and import the events as pre-built envelopes, the way DynamoDB users move their data. This route isn’t tested.

3.x tables can’t be used as they are: they have no global positions, their inserts are positional, and they store times to the second. ensureCollection() refuses a 3.x event table with an EventStoreSchemaException (found: 'v1'), so an application whose default pool has a 3.x table fails its bootstrap, with a message that points here. The migration is offline: no 3.x instance may write while it runs. Afterwards every 3.x event append fails (error 1136), but 3.x snapshot writes still succeed, with registered_on off by the time zone of the 3.x process: stop every 3.x instance before you migrate the snapshots too.

Runbook

1. Take a backup.

2. Deploy nothing yet. Run the dry runs with the 4.0 package, from a script or a REPL (no NestJS application is needed):

   import { MariaDBEventStore, MariaDBSnapshotStore } from '@ocoda/event-sourcing-mariadb';
   
   const config = { host: 'db', user: 'admin', password: '…', database: 'app' };
   console.dir(await MariaDBEventStore.migrate(config, { dryRun: true }), { depth: null });
   console.dir(await MariaDBSnapshotStore.migrate(config, { dryRun: true }), { depth: null });

   A dry run writes nothing. For every table it reports the rows, the exact statements of each step and the locks they take, what blocks the migration (blocking: a trigger or foreign key on the table, a leftover __es_v1 backup), the streams with gaps in their versions (gappedStreams), the 3.x streams whose ids differ in case only (caseVariantStreams) and the stream ids the migration replaces (canonicalizedStreams, with the rows and a sample of up to 1,000 streams; see Stream ids that differ in case only), and how occurred_on will be repaired (occurredOnRepair). The report’s environment shows the server’s version and time zones.

3. Stop every 3.x instance.

4. Migrate the events, then the snapshots: MariaDBEventStore.migrate(config), then MariaDBSnapshotStore.migrate(config). options.pools limits a run to some pools (undefined is the default pool); by default every table with the name and columns of a store table is migrated, one at a time. onProgress reports each step. A second run reports skip for the tables that are done. Keep the events’ __es_v1 backups (the default) until the snapshots are migrated: the snapshot migration takes the stream ids of the snapshots from them.

5. Deploy 4.0. When the report lists canonicalizedStreams, the application must use the stream ids it lists from now on.

6. When you are satisfied, drop the backups that the event migration kept: run MariaDBEventStore.migrate(config, { keepBackup: false }) once more, which only drops the backups of migrated tables, or DROP TABLE \events__es_v1`, one per event table (the report lists the statement). keepBackup: false` on the first run drops them right away, before the snapshots can use them; its dry run and report warn when a pool’s snapshot table isn’t migrated yet.

A migration that fails (a crash, a lost connection, a lock timeout) leaves each table either untouched or in a state the next run continues from: run it again. A lock wait timeout means that a session still uses the table; with the PROCESS privilege, the error names the sessions that hold a transaction open (KILL <id> ends one). After a lost connection the server may still be running the step: the error names the IS_USED_LOCK() query to wait on. migrate() ignores the connector’s socketTimeout, since a copy runs for minutes without a byte on the socket. Two migrations of the same table don’t run at once: each table is migrated under a named lock (GET_LOCK), and a table whose lock is taken is reported as blocked.

For DBAs who run the migration by hand, migrations/4.0.sql (also in the npm package) holds the statements for the default pool’s tables as 3.x creates them. Its lock statements fail with error 1242 when another migration holds the table’s lock, which stops the mariadb client there. For other pools, or tables that were changed, use the statements of a dry run.

Galera. Run the migration against one node: the named lock is per node, not cluster-wide. The migration replicates the copy in fragments (streaming replication) of 64 MiB, or of half the node’s wsrep_max_ws_size when that is smaller, so that no write set exceeds it and neither needs changing; every node needs the copy’s space. The snapshot conversion (ALTER TABLE) runs on every node at once (total order isolation) and holds the writes to the table on the whole cluster while it runs. The dry run’s environment.topology is 'galera' on such a node, and its warnings repeat this.

What the event migration does

The event migration copies each table into a new schema v2 table and swaps the two, so it never updates a 3.x row:

1. It creates <table>__es_v2, renames it to <table>__es_vp and back in one statement, which checks the privileges of the swap before anything is copied, and copies every row into it, in one INSERT … SELECT, with unique and foreign key checks off for the bulk load into the empty table. The copy numbers the events in 3.x’s order, (event_date, event_id, stream_id, version), except that the events of a stream keep their version order: readAll returns the events the way 3.x’s getAllEnvelopes did. The stream is the 3.x stream, and every row of it gets the stream id of its lowest version (see Stream ids that differ in case only).
2. It repairs occurred_on: 3.x stored a TIMESTAMP to the second, as the wall time of the Node.js process, so an application in another time zone than the database server stored times that are hours off. When the first 10 characters of the event id are a ULID time and occurred_on differs from it by a whole number of quarter hours, at most 14 hours, the migration takes the event id’s time, to the millisecond. Otherwise it keeps the stored value. repairOccurredOn: false keeps every stored value.
3. RENAME TABLE swaps the copy in and keeps the 3.x table as <table>__es_v1 (the name is shortened with a hash when it exceeds 64 characters). From here on, a 3.x insert fails with error 1136 (the column count doesn’t match).
4. It copies the rows that 3.x wrote to the old table during the copy, if any, after the others, with the stream ids the copy gave their streams, and warns about them.
5. It registers the table in the catalog, with the counter at the last position. The first 4.0 append continues at the next position.

Locks. The migration sets its session to REPEATABLE READ, whatever the server’s default, so the copy takes a shared lock on every row of the 3.x table until it ends: reads continue, and a 3.x write waits and then fails with a lock wait timeout (1205). That’s a fence, not a way to run 3.x during the migration. The locks take about 3.5 to 5 MB of the buffer pool per million rows. If a copy fails with ER_LOCK_TABLE_FULL (1206), increase innodb_buffer_pool_size, or run the dry run’s statements by hand with SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED before the copy (no shared locks; the catch-up step copies what was written meanwhile; a binary log in MIXED format logs the copy by row, and one in STATEMENT format refuses it). The swap takes exclusive metadata locks for an instant. lockTimeoutMs (default 10 s) bounds every lock wait: a table that a session still uses fails the step instead of hanging.

Time and space. Measured on MariaDB 10.11 and 11.8 in Docker, data in memory, with the default 128 MB buffer pool:

	100,000 events (93 MB)	1,000,000 events (615 MB)
Dry run	2 to 3.5 s	about 45 s, most of it for the occurred_on counts
Migration	2.4 to 2.7 s	not measured; 75 s for the design’s copy, which numbered the events in one pass

The copy the migration runs also keeps every stream in version order, which takes two more passes: at 50,000 and 100,000 events it took about twice as long as the one-pass copy. Plan for about 3 minutes per million events for the migration and 0.75 minutes per million for the dry run, more when the table is much bigger than the buffer pool. Plan for free space of 1.5 times the event table in the data directory (the copy; the 3.x table stays as the backup until you drop it) and 1.5 times the event table in tmpdir, where the numbering sorts (measured: about 1.35 times the table’s data and indexes). A copy that runs out of tmpdir fails with Temp file write failure or The table … is full, and fails again until tmpdir has room: point it at a larger volume, then run the migration again.

The migration writes the copy to the redo log and, when the binary log is on, to it too. In ROW format that is about the size of the table. In MIXED format the copy is logged as one statement, which every replica runs again: replicas need the same tmpdir space, and fall behind by the copy’s duration.

Gapped streams. 3.x accepted appends that left a gap in a stream’s versions. In 4.0, the next append to such a stream conflicts: its expected version is the number of events the application read, the stream’s version is higher. The conflict’s actualVersion is the version to append after.

What the snapshot migration does

The snapshot migration first gives every snapshot stream the stream id of its events (see Stream ids that differ in case only), in one UPDATE. Then it converts each table in place (ALTER TABLE … ALGORITHM=COPY, LOCK=SHARED: reads continue, writes wait): binary collation, the 4.0 column sizes, registered_on as DATETIME(3) (the conversion also drops the ON UPDATE attribute of tables created by servers before MariaDB 10.10), then flags exactly the highest version of every stream as the latest, adds the unique key and registers the table.

• 3.x stored registered_on as the wall time of its process, and snapshot ids carry no time to repair it from: when 3.x ran in another time zone than the server, the migrated registered_on is off by that offset. The dry run warns about it.
• On servers created before MariaDB 10.10, 3.x’s own updates may already have overwritten the registered_on of superseded snapshots (the ON UPDATE attribute). The migration can’t repair that; it never lets the attribute fire itself. The dry run warns when a table has it.

Stream ids that differ in case only

The 3.x tables compared stream ids in the database’s default collation, which usually ignores case (and, depending on the collation, accents and trailing spaces). So an application that appended account-Acc-1 version 1 and then account-acc-1 version 2 wrote one 3.x stream. Schema v2 compares stream ids in binary, where those would be two streams, the second starting at version 2. The migration gives such a stream one stream id, so that nothing needs fixing by hand:

• Events. Every row of a 3.x stream takes the stream id of the stream’s lowest version, the id it was created with: account-Acc-1 for both rows above. A row whose stream id changes also takes that version’s aggregate id when the two differ in case only (acc-1 becomes Acc-1). The copy does this, so it is deterministic, a rerun gives the same ids, and the catch-up gives the rows 3.x wrote during the copy the same ids.
• Snapshots. Every snapshot of a 3.x stream takes the stream id of the stream’s events, so that the snapshots and the events of an aggregate keep one id: the id of the lowest event version in the pool’s 3.x event table, or in its __es_v1 backup once the events are migrated. A stream without events there takes the id of its lowest snapshot. A snapshot whose stream id changes also takes the aggregate id and the aggregate name that go with the new id, each when the two differ in case only. This runs before the conversion, as one UPDATE that a rerun repeats without changing anything; the flags are repaired afterwards, so the stream ends up with one latest snapshot, its highest version.
• The report lists them: caseVariantStreams counts the 3.x streams whose ids differ in case only, and canonicalizedStreams lists the ids the migration gives (streamId), the ids it replaces (variants) and the rows that change, for up to 1,000 streams, with a warning that shows a few. The gapped streams are counted per 3.x stream, so such a stream isn’t reported as gapped.
• After the migration, the application must use those stream ids: 4.0 reads account-acc-1 as another, empty stream, like the other stores do. Review the dry run’s canonicalizedStreams: where the application takes aggregate ids from input whose casing varies, it must now map them to the stored ids.
• The snapshots can’t take the ids of the events when the events’ backup was dropped before the snapshots were migrated (keepBackup: false), or when the event table’s stream ids compare in another collation than the snapshots’. The dry run and the report warn about it; such a snapshot stream takes the id of its lowest snapshot, and when that differs from its events’ id, 4.0 doesn’t find it and rebuilds the aggregate from its events.