I have used Falkos HowTo on the virtual users, domains etc on Postfix and Courier without any problems for many years (since Ubuntu 10.04), but I have lately upgraded my server to ubuntu 16.04 with a fresh install, and with MariaDB in place of MySQL. Just about everything works, except I cant get the pesky thing to authenticate. So I've set Courier-imap logging to maximum and this i what I get: Code: Aug 18 20:38:37 hostname imapd: Connection, ip=[IP.ADDRESS] Aug 18 20:38:37 hostname authdaemond: received auth request, service=imap, authtype=login Aug 18 20:38:37 hostname authdaemond: authmysql: trying this module Aug 18 20:38:37 hostname authdaemond: authmysqllib: connected. Versions: header 50711, client 50713, server 50505 Aug 18 20:38:37 hostname authdaemond: SQL query: SELECT email, password, "", 5000, 5000, "/home/vmail", CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(em$, quota, "", "" FROM users WHERE email = '[email protected]' Aug 18 20:38:37 hostname authdaemond: mysql_query failed, reconnecting: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM users WHERE email = '[email protected]'' at line 1 Aug 18 20:38:37 hostname authdaemond: authmysqllib: connected. Versions: header 50711, client 50713, server 50505 Aug 18 20:38:37 hostname authdaemond: mysql_query failed second time, giving up: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM users WHERE email = '[email protected]'' at line 1 Aug 18 20:38:37 hostname authdaemond: authmysql: REJECT - try next module Aug 18 20:38:37 hostname authdaemond: FAIL, all modules rejected Aug 18 20:38:37 hostname imapd: LOGIN FAILED, [email protected], ip=[IP.ADDRESS] Aug 18 20:38:43 hostname imapd: Disconnected, ip=[IP.ADDRESS], time=6, starttls=1 It's very obvious there is something wrong with the query (no closing parenthesis in second ´SUBSTRING_INDEX'), but I'm at a loss as to how and where to remedy this so the correct query gets submitted to the authentication daemon. With testsaslauthd I get a simple "connect() : Permission denied" Is there anyone here who can point me in the right direction?
As a temporary workaround, you can try to set the sql_mode in mariadb from "NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES" to "NO_ENGINE_SUBSTITUTION" which makes it compatible with older MySQL versions. Then you should compare the config files of the tutorial with the ones of your setup, maybe a config file got overwritten during system upgrade.
Thanks for the quick reply till, I've checked and the current value for sql_mode is "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" I'lll check on the configs, bit Iḿ pretty sure thay must be OK, since I copypasted them from the old server. But double checking never hurt anybody, so..
...and it this case it seems to have been the solution. Somehow, in /etc/courier/authmysqlrc, the line Code: MYSQL_MAILDIR_FIELD CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') got truncated after the "em" in the second "SUBSTRING_INDEX", which messed up the query. The clients are authenticating again. Thanks a bunch for putting me on my tracks! Martin P.S.: Since I'm a newcomer on the forums: how do I close this thread?
You don't have to close it. Just report back how you solved it and that's what you did already so this thread might help others
