Why is postfix not connecting to mysql?

March 9, 2015 4.6k views
Email MySQL Ubuntu

I upgraded some of my software and since then my postfix dovecot mysql setup that was working perfectly (following this tutorial) is broken...

I am currently running the following on my droplet:

  • Ubuntu 14.04.2 LTS Release: 14.04 Codename: trusty
  • Postfix 2.11.0
  • mysql 14.14 Distrib 5.6.23-72.1, for debian-linux-gnu (x86_64) using EditLine wrapper (Percona version)
  • dovecot 2.2.9

I can connect to mysql, and both postfix and dovecot are running. For some reason it appears that postfix cannot connect to the mysql server, even though the user and password work when I access the database directly. The error seems to be reflected by these repeating warnings in the postfix log file: (the hostname and username were changed for privacy)

<lots of stuff deleted>
Mar 8 18:25:58 hostname postfix/cleanup[31858]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
Mar 8 18:25:58 hostname postfix/cleanup[31858]: warning: mysql:/etc/postfix/mysql-virtual-alias-maps.cf lookup error for "username@hostname"
Mar 8 18:25:58 hostname postfix/cleanup[31858]: warning: 149EB1845F3: virtual_alias_maps map lookup problem for username@hostname -- message not accepted, try again later
<lots of stuff deleted>

When I change the .cf files that define the mysql connections and use "localhost" instead of "127.0.0.1" the error message changes to:

<lots of stuff deleted>
Mar 8 18:30:52 hostname postfix/cleanup[32254]: warning: connect to mysql server localhost: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Mar 8 18:30:52 hostname postfix/cleanup[32254]: warning: mysql:/etc/postfix/mysql-virtual-alias-maps.cf lookup error for "username@hostname"
Mar 8 18:30:52 hostname postfix/cleanup[32254]: warning: 0A28C1845F3: virtual_alias_maps map lookup problem for username@hostname -- message not accepted, try again later
<lots of stuff deleted>

Any thoughts how to troubleshoot this further? I have tried pretty much everything for the last six hours but don't seem to have any success. I posted this to the entry that provides the tutorial, but thought this area might have a bit more traffic...

Thanks for your input!

Andreas

3 comments
  • is your mysql user set to use localhost?

    If you have phpmyadmin installed, you can check this by going to users and looking at what host that user is set for.

    I am guessing you already checked that, but just thought I would start with the easy stuff first.

  • Hello,

    I am going through the exact same problem. Did you end up getting it fixed, and if so, what did you do?

    Nothing I've tried is working so far...

    As an interesting side note. When I run the following command:

    postmap -q example.org mysql:/etc/postfix/mysql-mailbox-domains.cf
    

    The request hangs and doesn't return anything - whereas before it would return 1. If I change various settings from 127.0.0.1 to localhost then postmap returns 1, but then my email client wont connect...

    Thank you,

    Brent

  • I had a similar error message, and found I had the wrong database set in the mysql-mailbox-domains.cf file. Make sure you are using the right database name.

    Second, using 127.0.0.1 instead of localhost is the right direction here: MySQL treats the name 'localhost' specially and tries to use a socket to connect. On my server, this caused problems because the postfix daemon is chrooted and couldn't access that socket. 127.0.0.1 makes the mysql client library used by postfix properly use the network stack to get to the MySQL server.

    Also, I found an error that virtual domains shouldn't be listed in the database and mydestination:
    postfix/trivial-rewrite[18739]: warning: do not list domain <domain.tld> in BOTH mydestination and virtualmailboxdomains

    I found that removing the domain from mydestination fixed that.

    Hopefully that helps. I seem to have a working configuration. I built it from scratch a few years ago, but it is very similar in that it is using MySQL for the virtual domains & mailboxes & aliases. Hopefully what I've found helps. If not, maybe I can do more looking through my working config to help you out. (This is running on a Ubuntu 14.04 LTS trusty server the the moment.)

    Brent's command was very helpful in debugging this: it should return the result of trying to lookup the -q value using the mapping specified by the provided file.

Be the first one to answer this question.