Skip to main content

MySQL durable store

Relayer.Outbox.MySql implements the durable store contract using MySqlConnector and MySQL 8.4+ InnoDB. It supplies bounded reads, atomic claims, renewal, completion, failure persistence and eligible backlog counts. Scoped ADO.NET transactions commit business writes and buffered sends together.

Deployment and registration​

Apply the schema with your application deployment tooling. The connection must select an existing database; Relayer does not create databases or upgrade existing table definitions. Keep the prefix identical in deployment and registration. Prefixes allow at most 52 BMP characters, reserving space for the table suffix and MySQL's generated CHECK constraint names. NUL and supplementary Unicode characters are rejected; embedded backticks are quoted safely.

using Microsoft.Extensions.DependencyInjection;
using MySqlConnector;
using Relayer;
using Relayer.Outbox.MySql;

await using var dataSource = new MySqlDataSource(connectionString);
var options = new MySqlOutboxOptions { TablePrefix = "orders_" };
await using (var command = dataSource.CreateCommand(MySqlOutboxSchema.CreateScript(options)))
{
    await command.ExecuteNonQueryAsync();
}
services.AddRelayerMySqlOutboxStore("orders", dataSource, options);

Resolve IOutboxStore with GetRequiredKeyedService<IOutboxStore>("orders") inside a DI scope. Each scope owns and disposes its store's cached commands. Your application owns the data source; dispose it after all scopes finish. Registration rejects duplicate names and invalid identifiers. An optional outbox health provider can use this keyed store.

Native connection settings pass through unchanged. UseAffectedRows must be false (the MySqlConnector default): ownership renewal must report a matching live row even when its expiry does not change. Construction and registration reject UseAffectedRows=true with an actionable error. GuidFormat and DateTimeKind do not change Relayer's stored representation.

Reads and fencing​

Reads select unordered rows or one lane, honor the caller's buffer length and exclusive ID cursor, and return ascending UUIDv7 IDs. UUIDs use network-order BINARY(16) bytes, without the time-field swap used by some UUIDv1 integrations. Payload bytes, duplicate headers, header order, ordering keys and tenant IDs survive reads.

Eligibility means pending, due at the supplied UTC instant, and without a live claim. Counts span all lanes. A conditional InnoDB update claims one eligible row atomically. Renewal never shortens expiry; renewal, completion and failure require the matching token and a live claim. Failure increments attempts and releases the claim. Repeated or stale mutations return false.

Dates use UTC DATETIME(6) values with microsecond precision. Await each operation before the next operation on the same store. Finish using borrowed rows before another read. Database errors and cancellation propagate; they never become a zero backlog. A cancelled mutation may already have committed.

Store operations require native autocommit and reject an ambient TransactionScope before opening a connection, including reads and counts. MySqlConnector's default AutoEnlist=true would otherwise enlist the connection and defer durability until the caller's transaction commits. Call the store outside the ambient transaction; native connection settings remain unchanged.

The headers column uses the same versioned binary codec as the PostgreSQL store: little-endian 32-bit version 1 and count, followed by length-prefixed UTF-8 names and byte values. Empty values, duplicate names and arbitrary value bytes are preserved. Invalid versions, lengths or trailing bytes fail explicitly. Empty headers require the encoded version and zero count, not an empty BLOB.

Transactional enqueue with ADO.NET or Dapper​

Register one scoped outbox integration, separately from the named read/claim store:

services.AddRelayerMySqlOutbox(new MySqlOutboxOptions
{
    TablePrefix = "orders_",
    Lanes = 64
});

Resolve MySqlOutbox and ISender from the same scope. Configure the message's route with SendTransactionRequirement.Outbox as described in the send guide. Begin a transaction, use its Connection for business commands, and commit through the returned DbTransaction:

using Dapper;
using System.Data.Common;

await using DbTransaction transaction = await outbox.BeginTransactionAsync(dataSource,
    cancellationToken: ct);
await transaction.Connection!.ExecuteAsync(new CommandDefinition(
    "INSERT INTO orders (id) VALUES (@Id)", new { Id = orderId },
    transaction: transaction, cancellationToken: ct));
await sender.SendAsync(new OrderPlaced(orderId), ct);
await transaction.CommitAsync(ct);

BeginTransactionAsync also accepts an already open MySqlConnection and an isolation level. The application retains ownership of that connection. The data-source overload owns and disposes only the connection it opens. Neither overload disposes the data source. Use one transaction at a time per scope; overlapping operations are unsupported. Configuration can also bind through AddRelayerMySqlOutbox(configurationSection), with immutable Options snapshots per scope, reload for new scopes, and startup validation.

Enqueue takes an owned copy of the original UUIDv7 identity, stable message type, payload, headers, ordering key and tenant metadata. At commit, Relayer uses MySqlConnector's native MySqlBatch inside the same transaction. Native batching controls packet construction and execution; it does not promise a single round trip on MySQL. MySqlBulkCopy is not required, so neither AllowLoadLocalInfile nor server local-infile settings need changing. Cached buffers and parameter views are reused after warm-up. Dates and UUIDs use the representation described above, regardless of native DateTimeKind and GuidFormat settings.

Both synchronous and asynchronous commit, rollback and disposal are supported. An insert failure or cancellation before commit rolls back the entire transaction, including earlier successful inserts in the batch. Disposing the transaction or scope without committing discards buffered sends and rolls back business writes. As with the native client, loss of connection or cancellation during COMMIT can leave the outcome unknown; retry the complete unit with application idempotency rather than assuming it failed.

Use InnoDB for every participating business table. Do not issue transaction-control SQL, DDL, LOCK TABLES, or other statements that implicitly commit through business commands. MySQL's native implicit commits and nontransactional tables cannot be rolled back by Relayer. Complete the transaction only through the returned wrapper. Use its Connection for Dapper; assigning the wrapper to a command created directly on a native connection is unsupported. Savepoints and CommandBehavior.CloseConnection are rejected because their effects would not match the buffered sends. Disposing the borrowed connection view has no effect; dispose the transaction instead. Ambient TransactionScope is rejected before beginning a unit.

Ordering keys use the shared stable lane hash. All writers and future ordered workers must agree on Lanes (default 64); changing it requires draining or migrating existing rows. The enlistment reports no scheduling support until matching due-row workers are implemented. Registering this integration alone does not send persisted rows to a broker.

Remaining integration​

Inbox deduplication, direct and ordered dispatch, recovery, poison-row handling and retention workers remain planned in #291. The package creates the outbox table, registers read/claim primitives and optionally registers scoped IOutboxEnlistment for sends. It does not establish lane ownership or start workers. The schema helper is not exposed through dotnet relayer db yet. Full SQL Server and MySQL parity remains tracked by #41.