Virtual users etc. no longer authenticates after switch to MariaDB

Discussion in 'HOWTO-Related Questions' started by mlentink, Aug 18, 2016.

  1. mlentink

    mlentink New Member HowtoForge Supporter

    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?
     
    Last edited: Aug 19, 2016
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    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.
     
  3. mlentink

    mlentink New Member HowtoForge Supporter

    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..
     
  4. mlentink

    mlentink New Member HowtoForge Supporter

    ...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?
     
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    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 :)
     

Share This Page