SQL pool timeout

[KoenTech]

I set up a MySQL server as a directory and when starting the mail server everything works for about 5 minutes, after that I get the following error message:

pool timed out while waiting for an open connection

Here is my pool config:

[directory."sql".pool]
max-connections = 10
min-connections = 0

When I set max-connections to something like 100 it takes about 30 minutes for the error to show up so I'm thinking it might have something to do with connections silently failing until all connections in the pool have failed.

[mdecimus]

A few questions:

• Is the SQL connection done over TLS?
• Are you running Stalwart in a Docker container?
• Can you share all the connection settings you are using including the connection string? (of course removing sensitive information)

[KoenTech]

Both the mail server and the MySQL server are running in a docker-compose project.

Here is a snippet of the config:

[directory."sql"]
type = "sql"
address = "mysql://user:pass@mysql:3306/stalwart?ssl_mode=disabled"

[directory."sql".pool]
max-connections = 10
min-connections = 0
#idle-timeout = "10m"

[directory."sql".cache]
entries = 500
ttl = {positive = '1h', negative = '10m'}

[directory."sql".query]
name = "SELECT name, type, secret, description, quota FROM accounts WHERE name = ? AND active = true"
members = "SELECT member_of FROM group_members WHERE name = ?"
recipients = "SELECT name FROM emails WHERE address = ?"
emails = "SELECT address FROM emails WHERE name = ? AND type != 'list' ORDER BY type DESC, address ASC"
verify = "SELECT address FROM emails WHERE address LIKE CONCAT('%', ?, '%') AND type = 'primary' ORDER BY address LIMIT 5"
expand = "SELECT p.address FROM emails AS p JOIN emails AS l ON p.name = l.name WHERE p.type = 'primary' AND l.address = ? AND l.type = 'list' ORDER BY p.address LIMIT 50"
domains = "SELECT 1 FROM emails WHERE address LIKE CONCAT('%@', ?) LIMIT 1"

When I start the server everything seems to work for a couple of minutes, I can login and receive emails so it is able to connect to the MySQL server.

[mdecimus]

The Stalwart Docker image has the CA certificates missing (this will be fixed on the next release scheduled for next week), so just in case this is causing the problem please install the CA certs in the Stalwart container as follows:

$ apt-get update
$ apt-get install ca-certificates

If this keeps happening please switch the logging level to debug and post here any relevant errors you may find. It might be related to a bug in the connection pool library.

[KoenTech]

I installed the CA certificates and set the log level to debug and the issue is still there. There aren't any SQL/directory related log entries (except the pool timed out error).

I tried testing it with Postgres which does seem to work. So the issue might be with the implementation of the connection pool for MySQL.

[mdecimus]

I'll have a look at this. Both PostreSQL and mySQL use the same pool and library so this might be a problem on the mySQL implementation. Can you share the directory schema you are using? Or if it also fails with the sample directory schema included in the documentation then it is not necessary.

[KoenTech]

I made some small changes to the schema/queries to make them compatible with MySQL/Postgres

MySQL:

CREATE TABLE accounts (name VARCHAR(32) PRIMARY KEY, secret VARCHAR(1024), description VARCHAR(1024), type VARCHAR(32) NOT NULL, quota INTEGER DEFAULT 0, 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));

name = "SELECT name, type, secret, description, quota FROM accounts WHERE name = ? AND active = true"
members = "SELECT member_of FROM group_members WHERE name = ?"
recipients = "SELECT name FROM emails WHERE address = ?"
emails = "SELECT address FROM emails WHERE name = ? AND type != 'list' ORDER BY type DESC, address ASC"
verify = "SELECT address FROM emails WHERE address LIKE CONCAT('%', ?, '%') AND type = 'primary' ORDER BY address LIMIT 5"
expand = "SELECT p.address FROM emails AS p JOIN emails AS l ON p.name = l.name WHERE p.type = 'primary' AND l.address = ? AND l.type = 'list' ORDER BY p.address LIMIT 50"
domains = "SELECT 1 FROM emails WHERE address LIKE CONCAT('%@', ?) LIMIT 1"

Postgres:

CREATE TABLE accounts (name TEXT PRIMARY KEY, secret TEXT, description TEXT, type TEXT NOT NULL, quota INTEGER DEFAULT 0, active BOOLEAN DEFAULT true);
CREATE TABLE group_members (name TEXT NOT NULL, member_of TEXT NOT NULL, PRIMARY KEY (name, member_of));
CREATE TABLE emails (name TEXT NOT NULL, address TEXT NOT NULL, type TEXT, PRIMARY KEY (name, address));

name = "SELECT name, type, secret, description, quota FROM accounts WHERE name = $1 AND active = true"
members = "SELECT member_of FROM group_members WHERE name = $1"
recipients = "SELECT name FROM emails WHERE address = $1"
emails = "SELECT address FROM emails WHERE name = $1 AND type != 'list' ORDER BY type DESC, address ASC"
verify = "SELECT address FROM emails WHERE address LIKE '%' || $1 || '%' AND type = 'primary' ORDER BY address LIMIT 5"
expand = "SELECT p.address FROM emails AS p JOIN emails AS l ON p.name = l.name WHERE p.type = 'primary' AND l.address = $1 AND l.type = 'list' ORDER BY p.address LIMIT 50"
domains = "SELECT 1 FROM emails WHERE address LIKE '%@' || $1 LIMIT 1"

[mdecimus]

Thanks, I'll run some tests this week and let you know.

[mdecimus]

Update: This is indeed a bug in the sqlx library, an issue has been opened with the details. I'll post another update here as soon as this is solved.

[mdecimus]

There is indeed a bug in sqlx but I was able to find a workaround. All tests pass on mySQL now. The fixes will be released at some point this week but if you want to test it out now you can compile the latest version available on the repo.

[mdecimus]

This issue has now been fixed in version 0.3.2.