🐛: MySQL: Driver error: `Transactions couldn't be nested.

[Bixilon]

What happened?

Hi there,
I am sometimes noticing the following lines in the logs (and an error on the client side):

2025-03-01T21:00:38Z ERROR MySQL error (store.mysql-error) listenerId = "imap", localPort = 143, remoteIp = 172.18.0.1, remotePort = 36340, reason = "Driver error: `Transactions couldn't be nested.'", causedBy = crates/email/src/ingest.rs:526, id = "5971"
2025-03-01T21:01:01Z ERROR MySQL error (store.mysql-error) reason = "Driver error: `Transactions couldn't be nested.'", details = Failed to lock event., causedBy = crates/store/src/dispatch/lookup.rs:323, accountId = 43, documentId = 346, details = Failed to lock email task
2025-03-01T20:56:52Z ERROR MySQL error (store.mysql-error) listenerId = "imap", localPort = 143, remoteIp = 172.18.0.1, remotePort = 36340, reason = "Driver error: `Transactions couldn't be nested.'", causedBy = crates/common/src/core.rs:570, causedBy = crates/email/src/ingest.rs:447, id = "512"
2025-03-01T20:53:21Z ERROR MySQL error (store.mysql-error) reason = "Driver error: `Transactions couldn't be nested.'", details = Failed to lock event., causedBy = crates/store/src/dispatch/lookup.rs:323, accountId = 74, documentId = 181, details = Failed to lock email task

This is probably caused, because mysql (I am using it as data, lookup and directory storage) does not support (see https://stackoverflow.com/questions/1306869/are-nested-transactions-allowed-in-mysql).

I suspect you retry some action that failed before, but without rolling back (or commiting) properly. I don't know where exactly.

How can we reproduce the problem?

Hard to say, happens farily rarely.

Version

v0.11.x

What database are you using?

SQLite

What blob storage are you using?

mySQL

Where is your directory located?

SQL

What operating system are you using?

Linux

Relevant log output

MySQL: Trying write
MySQL: Started transaction
MySQL: Operation: assert
MySQL: Operation: value-set
MySQL: Committing transaction
MySQL: Commit server error: retry
MySQL: Trying write
MySQL: error

Code of Conduct

• I agree to follow this project's Code of Conduct

[Bixilon]

So I figured out what is going on:

Due to multiple stalwart servers running on the same database, they do some operation (fts or data storing or anything else) and somehow the commit fails (due to a deadlock or something else) and then the transaction is retried after some time. The problem is, that it was never rolled back (see https://stackoverflow.com/questions/38133260/is-it-necessary-to-rollback-if-commit-fails). This is just a problem because the same connection is reused.

Instead of

        trx.commit().await.map(|_| result).map_err(Into::into)

something like this is better:

        match trx.commit().await {
            Ok(_) => {
                Ok(result)
            },
            Err(e) => {
                trx.rollback().await?;
                Err(e.into())
            }
        }

(this does not work, because commit is by value and rollback afterwards is not allowed).

This actually makes me thing if that is the right approach at all. The database is preventing us, because values might have changed. Wouldn't it make more sense to reapply the changes and try to merge them somehow? Still hard.

[mdecimus]

The mySQL driver should automatically rollback transactions that are dropped, it that is not the case there might be a bug in the mySQL rust crate.

[CertainLach]

The mySQL driver should automatically rollback transactions that are dropped, it that is not the case there might be a bug in the mySQL rust crate.

It is kinda hard to properly rollback on drop, since Rust doesn't have AsyncDrop yet

[mdecimus]

It is kinda hard to properly rollback on drop, since Rust doesn't have AsyncDrop yet

What I meant to say is that they are supposed to rollback on errors, in this case the library is taking ownership of the transaction when commit is called. If it fails Stalwart can't do anything about it because it no longer has access to to the transaction object.

For every other error Stalwart will always roll back the transaction as long as it has access to the trx object.