Postfix can't connect to mysql problem

[Jes Andersen-2]

I have small problem with postfix when trying to switch to mysql for postfix.

I used:
$sudo postmap -q [hidden email] hash:/etc/postfix/virtual
[hidden email]
$sudo postmap -q [hidden email]
mysql:/etc/postfix/mysql-list.cf mysql:/etc/postfix/mysql.cf
[hidden email]

to test the configuration before changing main.cf
from
virtual_alias_maps = hash:/etc/postfix/virtual
to
virtual_alias_maps = mysql:/etc/postfix/mysql.cf

But it didn't work after a restart of postfix

From /var/log/mail.log:
[...]
postfix/smtpd[18039]: warning: connect to mysql server localhost:
Can't connect to local MySQL server through socket
'/var/run/mysqld/mysqld.sock' (2)
[...]

I can't understand why postmap works when smptd doesn't

It is possible for the postfix user to connect to the local server
through the socket as this command proves
$sudo -u postfix mysqladmin --protocol=SOCKET
--socket=/var/run/mysqld/mysqld.sock version
mysqladmin  Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486
[..]

I do not run the vanilla postfix but the debian modified one but I
don't think that should be the problem.
$sudo postconf -d | grep "mail_version ="
mail_version = 2.5.2

Thanks in advance
 Jes

[Jes Andersen-2]

found out it was a chroot problem.

My fix if any comes by the same problem was to add this to the startup script:

if [ -e /var/run/mysqld/mysqld.sock ]; then
  if [ -e /var/spool/postfix/var/run/mysqld/mysqld.sock ]; th$
    rm /var/spool/postfix/var/run/mysqld/mysqld.sock
  fi
  mkdir -p /var/spool/postfix/var/run/mysqld
  chown mysql /var/spool/postfix/var/run/mysqld
  ln /var/run/mysqld/mysqld.sock /var/spool/postfix/var/run/m$
fi

On Thu, Aug 21, 2008 at 4:23 PM, Jes Andersen <[hidden email]> wrote:

> I have small problem with postfix when trying to switch to mysql for postfix.
>
> I used:
> $sudo postmap -q [hidden email] hash:/etc/postfix/virtual
> [hidden email]
> $sudo postmap -q [hidden email]
> mysql:/etc/postfix/mysql-list.cf mysql:/etc/postfix/mysql.cf
> [hidden email]
>
> to test the configuration before changing main.cf
> from
> virtual_alias_maps = hash:/etc/postfix/virtual
> to
> virtual_alias_maps = mysql:/etc/postfix/mysql.cf
>
> But it didn't work after a restart of postfix
>
> From /var/log/mail.log:
> [...]
> postfix/smtpd[18039]: warning: connect to mysql server localhost:
> Can't connect to local MySQL server through socket
> '/var/run/mysqld/mysqld.sock' (2)
> [...]
>
> I can't understand why postmap works when smptd doesn't
>
> It is possible for the postfix user to connect to the local server
> through the socket as this command proves
> $sudo -u postfix mysqladmin --protocol=SOCKET
> --socket=/var/run/mysqld/mysqld.sock version
> mysqladmin  Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486
> [..]
>
> I do not run the vanilla postfix but the debian modified one but I
> don't think that should be the problem.
> $sudo postconf -d | grep "mail_version ="
> mail_version = 2.5.2
>
> Thanks in advance
>  Jes
>

[mouss-2]

Jes Andersen wrote:

> found out it was a chroot problem.
>
> My fix if any comes by the same problem was to add this to the startup script:
>
> if [ -e /var/run/mysqld/mysqld.sock ]; then
>   if [ -e /var/spool/postfix/var/run/mysqld/mysqld.sock ]; th$
>     rm /var/spool/postfix/var/run/mysqld/mysqld.sock
>   fi
>   mkdir -p /var/spool/postfix/var/run/mysqld
>   chown mysql /var/spool/postfix/var/run/mysqld
>   ln /var/run/mysqld/mysqld.sock /var/spool/postfix/var/run/m$
> fi

use TCP instead of creating mysql sockets all around your fs.

[Jes Andersen-2]

> use TCP instead of creating mysql sockets all around your fs.

I'll rather have the higher efficiency from a unix socket and besides
my mysql is network disabled ;)

Even looking for a way to handle some caching of the response. Also
working on a more push based system, but I don't like the need for
reloading postfix configuration either, but I'm sure I'll figure
something out.

[mouss-2]

Jes Andersen wrote:
>> use TCP instead of creating mysql sockets all around your fs.
>
> I'll rather have the higher efficiency from a unix socket and besides
> my mysql is network disabled ;)

Then use DOS. your unix is spending too much time switching contexts and
checking permissions.

Do you have actual measurements or are you just speculating? the
benefits of a tcp connection generally outweight the handshake costs.
and with connection "caching", the handshake costs are irrelevant.

Moreover, in some implementations, localhost TCP is as fast as unix
streams.


>
> Even looking for a way to handle some caching of the response. Also
> working on a more push based system, but I don't like the need for
> reloading postfix configuration either, but I'm sure I'll figure
> something out.

there is no need to reload. postfix processes will pickup the new values
sooner or later. do you really change your config every second?

[Jes Andersen-2]

> Then use DOS. your unix is spending too much time switching contexts and checking permissions.
>
> Do you have actual measurements or are you just speculating? the benefits of
> a tcp connection generally outweight the handshake costs. and with
> connection "caching", the handshake costs are irrelevant.
>
> Moreover, in some implementations, localhost TCP is as fast as unix streams.

it's roughly 25% faster even with a with a persistent connection.

albatros@XXXXXX:~$ time sudo mysql --protocol TCP XXXXXXXX <
test10000.sql > /dev/null

real 0m6.397s
user 0m0.112s
sys 0m0.163s
albatros@XXXXXX:~$ time sudo mysql --protocol SOCKET XXXXXXX <
test10000.sql > /dev/null

real 0m4.816s
user 0m0.070s
sys 0m0.058s

1 - (4.81600 / 6.39700) = 0.2471471

from a quick benchmark using the 10000 request. (and the sql request
is the same as the one postfix uses)

But the 158.1 microseconds per call it's faster is probably not a good
enough reason ;)

but i'm just work-damaged I think. I build large-scale real-time
systems with hard realtime demands (and yes we often skip unix, but
not for DOS but our own homemade handoptimized assembly code, even
harddisks, TCP/IP are too slow and unpredictable in such cases too)

For example capturing electric burts in the 20-400eV range lasting
roughly 2-10ns and with a frequency up to 400MHz range and no
possibility to fix any problems after deployment.

> there is no need to reload. postfix processes will pickup the new values
> sooner or later. do you really change your config every second?
nice, i'm new to postfix so I didn't knew that.

anyways I found an even better solution the proxy_map, since it
actually runs outside the chroot and provides throttling too ;)
best of both worlds :D

Disclaimer: I'm still configuring proxy_map so I haven't tested if it will work