CSharpEssentials.LoggerHelper.Sink.MySql 5.2.4.1

CSharpEssentials.LoggerHelper.Sink.MySql

MySQL and MariaDB structured log storage with JSON support and custom columns for CSharpEssentials.LoggerHelper.

Targets: net8.0 · net9.0 · net10.0 — Part of the CSharpEssentials.LoggerHelper ecosystem. Install only the sinks you need.

Your structured fields land in real, queryable columns — not buried inside a JSON blob — so this sink stays at feature parity with the PostgreSQL sink.

Install

dotnet add package CSharpEssentials.LoggerHelper
dotnet add package CSharpEssentials.LoggerHelper.Sink.MySql

Built directly on MySqlConnector — fully async, no MySql.Data dependency.

Compatibility: MySQL 5.7.8+, MySQL 8.x, MariaDB 10.2+

Quick Setup — JSON

{
  "LoggerHelper": {
    "ApplicationName": "MyApp",
    "Routes": [
      { "Sink": "MySql", "Levels": ["Warning", "Error", "Fatal"] }
    ],
    "Sinks": {
      "MySql": {
        "ConnectionString": "Server=localhost;Database=logs;Uid=app;Pwd=secret;",
        "TableName": "app_logs",
        "AutoCreateTable": true
      }
    }
  }
}

builder.Services.AddLoggerHelper(builder.Configuration);

var app = builder.Build();
app.UseLoggerHelper();   // ← required: activates sinks and registers middleware

Quick Setup — Fluent API

builder.Services.AddLoggerHelper(b => b
    .WithApplicationName("MyApp")
    .AddRoute("MySql", LogEventLevel.Warning, LogEventLevel.Error, LogEventLevel.Fatal)
    .ConfigureMySql(m => {
        m.ConnectionString = "Server=localhost;Database=logs;Uid=app;Pwd=secret;";
        m.TableName = "app_logs";
        m.AutoCreateTable = true;
    })
);

The sink name is matched case-insensitively and also accepts MySQL and MariaDB, so { "Sink": "MariaDB" } routes here too.

What You'll See

Events are batched and inserted as rows into the configured table. With AutoCreateTable: true the sink issues this DDL on the first write:

CREATE TABLE IF NOT EXISTS `app_logs` (
  `Id`               BIGINT AUTO_INCREMENT PRIMARY KEY,
  `ApplicationName`  VARCHAR(255),
  `message`          TEXT,
  `message_template` TEXT,
  `level`            VARCHAR(32),
  `raise_date`       DATETIME(6),
  `exception`        TEXT,
  `properties`       JSON,
  `MachineName`      VARCHAR(255),
  `Action`           VARCHAR(255),
  `IdTransaction`    VARCHAR(255)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

A single logger.LogError("Payment failed for order {OrderId}", 4711) becomes:

Because properties is a native JSON column, structured fields stay queryable even when they have no dedicated column:

-- Errors of the last hour, grouped by template
SELECT message_template, COUNT(*) AS hits
FROM app_logs
WHERE level IN ('Error','Fatal')
  AND raise_date >= NOW() - INTERVAL 1 HOUR
GROUP BY message_template
ORDER BY hits DESC;

-- Drill into a single structured property inside the JSON column
SELECT raise_date, message
FROM app_logs
WHERE properties->>'$.OrderId' = '4711';

Configuration Options

Legacy lowercase JSON keys (connectionstring, tableName, autoCreateTable, needAutoCreateTable, addAutoIncrementColumn, ColumnsMySql) are accepted for compatibility with older configuration files.

Default Columns

When Columns is omitted the sink creates this table automatically:

The generated table uses CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, so log messages containing emoji or non-BMP characters are stored correctly.

Custom Columns — Replicate or Extend the Default Schema

Use Columns to define exactly which columns the sink writes. When Columns is present it replaces the default set entirely.

Example A — Replicating the default schema via JSON

"Sinks": {
  "MySql": {
    "ConnectionString": "Server=localhost;Database=logs;Uid=app;Pwd=secret;",
    "TableName": "app_logs",
    "AutoCreateTable": true,
    "StoreTimestampInUtc": true,
    "Columns": [
      { "Name": "ApplicationName",  "Writer": "Single",     "Type": "VarChar",  "Length": 255, "Property": "ApplicationName" },
      { "Name": "message",          "Writer": "Rendered",   "Type": "Text" },
      { "Name": "message_template", "Writer": "Template",   "Type": "Text" },
      { "Name": "level",            "Writer": "Level",      "Type": "VarChar",  "Length": 32 },
      { "Name": "raise_date",       "Writer": "Timestamp",  "Type": "DateTime" },
      { "Name": "exception",        "Writer": "Exception",  "Type": "Text" },
      { "Name": "properties",       "Writer": "Serialized", "Type": "Json" },
      { "Name": "MachineName",      "Writer": "Single",     "Type": "VarChar",  "Length": 255, "Property": "MachineName" },
      { "Name": "Action",           "Writer": "Single",     "Type": "VarChar",  "Length": 255, "Property": "Action" },
      { "Name": "IdTransaction",    "Writer": "Single",     "Type": "VarChar",  "Length": 255, "Property": "IdTransaction" }
    ]
  }
}

Example B — Custom schema with application-specific properties

Add only the columns you need, including custom properties pushed via BeginScope:

"Columns": [
  { "Name": "message",    "Writer": "Rendered",   "Type": "Text" },
  { "Name": "level",      "Writer": "Level",      "Type": "VarChar", "Length": 32 },
  { "Name": "raise_date", "Writer": "Timestamp",  "Type": "DateTime" },
  { "Name": "exception",  "Writer": "Exception",  "Type": "Text" },
  { "Name": "properties", "Writer": "Properties", "Type": "Json" },
  { "Name": "TenantId",   "Writer": "Single",     "Type": "VarChar", "Length": 64, "Property": "TenantId" },
  { "Name": "UserId",     "Writer": "Single",     "Type": "VarChar", "Length": 64, "Property": "UserId" },
  { "Name": "RequestId",  "Writer": "Single",     "Type": "VarChar", "Length": 64, "Property": "RequestId" }
]

Populate the custom properties at runtime with BeginScope:

using (_logger.BeginScope(new Dictionary<string, object?> {
    ["TenantId"]  = "acme",
    ["UserId"]    = "usr_99",
    ["RequestId"] = HttpContext.TraceIdentifier
}))
{
    _logger.LogWarning("Payment failed for order {OrderId}", orderId);
}

Property is required only for Writer: "Single" when the column name differs from the Serilog property name. If Name and the property name match, you can omit Property.

MySqlColumnConfig reference

Writer values:

Type values:

Any unrecognized value falls back to TEXT.

Using an Existing Table

Set AutoCreateTable: false and declare Columns matching your schema. The sink never alters an existing table.

Table and column names are validated against ^[A-Za-z0-9_]{1,64}$ and quoted with backticks; values are always passed as command parameters, never concatenated into SQL.

Timestamps and Time Zones

MySQL has no time-zone-aware type equivalent to the PostgreSQL timestamptz. The sink emits DATETIME(6) and leaves the choice of clock to you:

"StoreTimestampInUtc": true

If you define the column yourself, prefer DATETIME(6) over TIMESTAMP: the MySQL TIMESTAMP type is limited to the range 1970-01-01 → 2038-01-19.

Migrating from the PostgreSQL Sink

Type mapping for an existing PostgreSQL log table:

The Writer values are identical across both sinks, so an existing Columns block can be reused by adjusting only the Type values.

Behaviour and Reliability

• Batched and async. Events are queued and flushed every Period or once BatchPostingLimit is reached, whichever comes first. The first event is emitted eagerly so you see output immediately at startup.

• Never throws into your app. Write failures are reported through the Serilog SelfLog and swallowed — a database outage will not take the host process down. Enable diagnostics to see them:

  Serilog.Debugging.SelfLog.Enable(Console.Error);
  

• Bounded queue. The internal queue holds 10,000 events. Once it is full, further events are dropped until it drains — memory stays bounded even if the database is unreachable.

• Each batch runs in a transaction, so a batch is written in full or not at all.

Strict mode and VARCHAR lengths. MySQL runs with STRICT_TRANS_TABLES by default since 5.7 and rejects over-long values with ERROR 1406: Data too long for column instead of truncating. The sink does not truncate either, so the whole batch is lost and reported to SelfLog. Size VARCHAR columns generously — fully qualified host names and container IDs routinely exceed 50 characters.

Quick Local Setup with Docker

The fastest way to get a MySQL instance to log into:

docker run -d --name mysql-logs   -e MYSQL_ROOT_PASSWORD=secret   -e MYSQL_DATABASE=logs   -p 3306:3306   mysql:8

"MySql": {
  "ConnectionString": "Server=localhost;Port=3306;Database=logs;Uid=root;Pwd=secret;",
  "TableName": "app_logs",
  "AutoCreateTable": true,
  "StoreTimestampInUtc": true
}

MariaDB works the same way — swap the image for mariadb:11 and keep the connection string as is.

Inspect what arrived:

docker exec -it mysql-logs mysql -uroot -psecret logs -e "SELECT level, message, raise_date FROM app_logs ORDER BY raise_date DESC LIMIT 10;"

Troubleshooting

Links

• Documentation
• CSharpEssentials.LoggerHelper (core)
• GitHub Repository
• MIT License