I am getting a syntax error log entry when trying to connect to a new email account with thunderbird

[ghevge]

I have configured the mail server to use a postgresql DB, in which I've defined the required schema and a new user account.

[directory."sql"]
type = "sql"
address = "postgresql://user:password@mw-db:5432/mw?currentSchema=emailserver&sslmode=disable"

When trying to connect to this new user account using thunderbird, I see in the logs the errors below:

Any idea what is causing them?

�[2m2023-08-14T16:09:26.196023Z�[0m �[32m INFO�[0m �[1msession�[0m�[1m{�[0m�[3minstance�[0m�[2m=�[0m"imap" �[3mprotocol�[0m�[2m=�[0mImap �[3mremote.ip�[0m�[2m=�[0m"::ffff:138.229.16.231" �[3mremote.port�[0m�[2m=�[0m47036�[1m}�[0m�[2m:�[0m �[2mutils::listener::listen�[0m�[2m:�[0m �[3mcontext�[0m�[2m=�[0m"tls" �[3mevent�[0m�[2m=�[0m"handshake" �[3mversion�[0m�[2m=�[0mTLSv1_3 �[3mcipher�[0m�[2m=�[0mTLS13_AES_256_GCM_SHA384
�[2m2023-08-14T16:09:26.263961Z�[0m �[33m WARN�[0m �[2mdirectory�[0m�[2m:�[0m SQL directory error �[3mcontext�[0m�[2m=�[0m"directory" �[3mevent�[0m�[2m=�[0m"error" �[3mprotocol�[0m�[2m=�[0m"sql" �[3mreason�[0m�[2m=�[0merror returned from database: syntax error at or near "AND"
�[2m2023-08-14T16:09:26.273741Z�[0m �[33m WARN�[0m �[2mdirectory�[0m�[2m:�[0m SQL directory error �[3mcontext�[0m�[2m=�[0m"directory" �[3mevent�[0m�[2m=�[0m"error" �[3mprotocol�[0m�[2m=�[0m"sql" �[3mreason�[0m�[2m=�[0merror returned from database: syntax error at or near "AND"

I'm aslo seeing these errors from time to time in the logs:

�[2m2023-08-14T16:13:25.484382Z�[0m �[32m INFO�[0m �[1msession�[0m�[1m{�[0m�[3minstance�[0m�[2m=�[0m"smtp" �[3mprotocol�[0m�[2m=�[0mSmtp �[3mremote.ip�[0m�[2m=�[0m"::ffff:209.85.221.42" �[3mremote.port�[0m�[2m=�[0m53495�[1m}�[0m�[2m:�[0m �[2mutils::listener::listen�[0m�[2m:�[0m �[3mcontext�[0m�[2m=�[0m"tls" �[3mevent�[0m�[2m=�[0m"handshake" �[3mversion�[0m�[2m=�[0mTLSv1_3 �[3mcipher�[0m�[2m=�[0mTLS13_AES_256_GCM_SHA384
�[2m2023-08-14T16:13:55.744376Z�[0m �[33m WARN�[0m �[2mdirectory�[0m�[2m:�[0m SQL directory error �[3mcontext�[0m�[2m=�[0m"directory" �[3mevent�[0m�[2m=�[0m"error" �[3mprotocol�[0m�[2m=�[0m"sql" �[3mreason�[0m�[2m=�[0merror returned from database: syntax error at or near "LIMIT"
�[2m2023-08-14T16:15:00.001197Z�[0m �[32m INFO�[0m �[2mjmap::services::housekeeper�[0m�[2m:�[0m Purging session cache.

Thanks

[mdecimus]

Have you checked that the queries you are using are valid PostgreSQL queries? That error means that the query you are using is invalid. Perhaps you used the default queries in the configuration file which are for SQLite?

[ghevge]

@mdecimus I use indeed the default queries, but I can't see anything problematic with them. All the queries containing AND run against the postgres DB are executing successfully

[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 '%' || ? || '%' 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 '%@' || ? LIMIT 1"

Is there any way to print in the log the whole query behind the error ? Returning just the: syntax error at or near "AND" is not helpful when trying to debug it

[ghevge]

OK found the problem: the "?" has to be replaced with "$1" in order for the queries to work in postgresql.
I found this behavior a bit weird, copared with how things works in Java for example. I'm not a Rust person, so I can only guess that this behavior is OK in Rust...

[mdecimus]

Is there any way to print in the log the whole query behind the error ?

Yes, you can change the log level to trace.

OK found the problem: the "?" has to be replaced with "$1" in order for the queries to work in postgresql.

Perfect.