Postfixadmin – debian – mysql and errors


A lot of our users use postfixadmin, it means email users are in the database, its easy to use, easy to admin. However the setup can be a little tricky for those not used to it.

If you want to have a go doing it yourself, we have a great tutorial here http://rimuhosting.com/knowledgebase/linux/mail/postfixadmin

Today however we had the most exasperating problem trying to get postfix to connect to the database however on one particular VPS. 2 of us sat for over an hour trying to figure out why all the credentials worked fine, but postfix still couldnt connect.

The logs had the following error messages

Nov  5 01:12:40 hostname postfix/trivial-rewrite[8740]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (40)
Nov  5 01:12:40 hostname postfix/trivial-rewrite[8740]: fatal: mysql:/etc/postfix/mysql_virtual_alias_maps.cf(0,lock|fold_fix): table lookup problem
Nov  5 01:12:40 hostname postfix/trivial-rewrite[8741]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (40)
Nov  5 01:12:40 hostname postfix/trivial-rewrite[8741]: fatal: mysql:/etc/postfix/mysql_virtual_alias_maps.cf(0,lock|fold_fix): table lookup problem
Nov  5 01:12:41 hostname postfix/smtpd[8657]: warning: problem talking to service rewrite: Success
Nov  5 01:12:41 hostname postfix/smtpd[8491]: warning: problem talking to service rewrite: Connection reset by peer
Nov  5 01:12:41 hostname postfix/master[8481]: warning: process /usr/lib/postfix/trivial-rewrite pid 8740 exit status 1
Nov  5 01:12:41 hostname postfix/master[8481]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup — throttling
Nov  5 01:12:41 hostname postfix/smtpd[8685]: warning: problem talking to service rewrite: Success
Nov  5 01:12:41 hostname postfix/master[8481]: warning: process /usr/lib/postfix/trivial-rewrite pid 8741 exit status 1

So clearly it was a case of not being able to connect to the MySQL server, but why? the user was fine, the host was fine, everything was checked, and rechecked. Finally I saw an email which touched on postfix being in a chroot, which meant it was unable to access the mysql.sock file (i had already checked permissions on this multiple times by now).

To make a long story short, this is a Debian based box, and the fix is to change the mysql socket to a place that the mail could access it.

/etc/init.d/mysql stop
cd /etc/mysql
mkdir backup # always
cp *.cnf backup/ # backup everything
sed -i s@/var/run/mysqld/@/var/spool/postfix/var/run/mysqld/@g my.cnf # this edits the file inline
sed -i s@/var/run/mysqld/@/var/spool/postfix/var/run/mysqld/@g debian.cnf #changing the socket location
mkdir /var/spool/postfix/var/run/mysqld/
chown mysql /var/spool/postfix/var/run/mysqld/
/etc/init.d/mysql start

This changed the mysql sock into a directory so that they postfix was able to access it, enabling all mail to run freely.


2 responses to “Postfixadmin – debian – mysql and errors”

  1. The “easier” less intrusive way to fix this is to replace ‘localhost’ with ‘127.0.0.1’ in the postfix configuration files related to the mysql setup (ie. /etc/postfix/mysql_virtual_alias_maps.cf).

    As per the HOWTO in http://rimuhosting.com/knowledgebase/linux/mail/postfixadmin

    Also keep in mind that postfix may complain about not being able to connect to mysql. This is because postfix runs in a chroot by default. If later on when you are testing the setup postfix fails to connect, change all the “hosts = localhost” lines to “hosts = 127.0.0.1” to force postfix to connect via tcp instead of via socket.

    Example:
    user = postfix
    password = SecretPassword!
    hosts = localhost
    dbname = postfix
    query = SELECT goto FROM alias WHERE address = '%s'

    becomes:
    user = postfix
    password = SecretPassword!
    hosts = 127.0.0.1
    dbname = postfix
    query = SELECT goto FROM alias WHERE address = '%s'

  2. Another thing you can do, is change the setting so its not in chroot mode. this is done in /etc/postfix/master.cf

    Look for the lines which have y in the column for chroot. ie

    # ==========================================================================
    # service type  private unpriv  chroot  wakeup  maxproc command + args
    #               (yes)   (yes)   (yes)   (never) (50)
    # ==========================================================================
    tlsmgr    fifo  -       -       y       300     1       tlsmgr