MySQL/MariaDB Schema and Troubleshooting

Questions
1

Your question

Using MySQL/MariaDB for authentication - Is there a preferred schema for Stalwart - ie: what information does Stalwart require for operation and configuration?

I am unable to synchronize a directory, authenticate or browse for users using a MySQL/MariaDB backend. I saw another port whereby positional parameters are required for query, so that is not an issue. However, I see nothing in the documentation regarding the values that should be returned (ie: mailtype).

I am using the system journal for logging with debug mode, however I am unable to troubleshoot as nothing regarding posted queries appears. I cannot see the errors to be able to troubleshoot.

I understand that topics in this category are triaged by a bot first but a human reply will follow up. If I’d prefer a human-only reply, I’ll add the no-ai tag to my topic.

on

2

Here is a complete answer to your questions.

Required MySQL/MariaDB Schema

The docs provide an official reference schema for MySQL/MariaDB. You need three tables:

CREATE TABLE accounts (
  name        VARCHAR(32)  PRIMARY KEY,
  secret      VARCHAR(1024),
  description VARCHAR(1024),
  type        VARCHAR(32)  NOT NULL,
  active      BOOLEAN      DEFAULT 1
);

CREATE TABLE group_members (
  name      VARCHAR(32) NOT NULL,
  member_of VARCHAR(32) NOT NULL,
  PRIMARY KEY (name, member_of)
);

CREATE TABLE emails (
  name    VARCHAR(32)  NOT NULL,
  address VARCHAR(128) NOT NULL,
  type    VARCHAR(32),
  PRIMARY KEY (name, address)
);

What Values Are Expected in Each Column

accounts.type (the “mailtype” equivalent) must be one of:

  • individual (or person) – a regular mailbox user
  • group – a group/distribution account

accounts.secret must be a hashed password in a supported format. You can generate a SHA-512 crypt hash with:

openssl passwd -6

emails.type should be primary for the main address, or alias for aliases.

Column Mappings (for custom schemas)

The default queries expect these column names: name, secret, description, type. If your table uses different column names, you can override the mappings on the Directory object:

  • columnEmail (default: "name")
  • columnSecret (default: "secret")
  • columnClass (default: "type") – this is the individual/group field
  • columnDescription (default: "description")

Inserting a Test User

INSERT INTO accounts (name, secret, description, type)
  VALUES ('john', '<HASHED_SECRET>', 'John Doe', 'individual');

INSERT INTO emails (name, address, type)
  VALUES ('john', '[email protected]', 'primary');

Troubleshooting: Enabling Trace-Level Logging

debug level may not show SQL query details. For authentication troubleshooting, the docs specifically recommend setting the log level to trace, which provides a granular view of authentication operations. You can adjust the tracer’s level field to trace in the WebUI under Settings > Telemetry > Tracers.

The full SQL backend documentation is at: SQL Database | Stalwart

This is an automated reply from the Stalwart Help Bot. Other community members may follow up if this answer is incomplete or wrong.

3

Which tracers should be implemented to view all SQL queries?

4

The reference schema and the default queries are documented at SQL Database | Stalwart : it lays out the accounts, group_members and emails tables, the four queries (queryLogin, queryRecipient, queryMemberOf, queryEmailAliases), and the column mappings. The account “type”/class column values you’re after are individual (or person) and group.

The reason you see no queries in the logs is that store.sql-query is a TRACE-level event, so a debug level won’t show it; enable TRACE for that event and the executed SQL appears. For failures specifically, watch the ERROR-level store.mysql-error event, which is where connection and query errors surface. If sync/auth still fails once your schema matches the reference, paste the store.mysql-error output and your configured queries and I’ll take a look.