Re: issue connecting to mysql after upgrade

[Jeff Weinberger]

On Jan 1, 2009, at 1:55 PM, mouss wrote:

Jeff Weinberger a écrit :
Hello again:
One more issue on which I would appreciate any help anyone can offer:

Yesterday I upgraded my postfix installation from 2.5.2 to 2.5.5  
and my
MySQL installation from 5.0.51b to 5.0.75. Immediately after  
restarting
everything (the whole computer), I started seeing these messages in  
my
mail log:

    Jan  1 09:48:35 s postfix/smtpd[13199]: warning: connect to mysql
server localhost: Can't connect to local MySQL server through socket
'/tmp' (38)
    Jan  1 09:48:35 s postfix/cleanup[13201]: warning: connect to  
mysql
server localhost: Can't connect to local MySQL server through socket
'/tmp' (38)
    Jan  1 09:48:42 s postfix/trivial-rewrite[13205]: warning:  
connect
to mysql server localhost: Can't connect to local MySQL server  
through
socket '/tmp' (38)

As far as I can tell this has been consistent with smtpd, cleanup and
trivial-rewrite, but nothing else. (there have been pre-existing  
issues,
per my prior conversation with mouss, with dspam, but these have not
changed at all - they existed before the upgrade and still exist)

I use mysql for nearly all my tables in postfix. All of my aliases  
are
working and forwarding fine, which means that postfix can access my
mysql database (and yes, postconf -m shows mysql). virtual is finding
the maildirs correctly for my virtual mailboxes.

However, my sender_canonical_maps are no longer working after the
upgrade (outbound addresses are not being rewritten at all as they  
were
before the upgrade)

As an example, my main.cf contains:
  sender_canonical_maps=mysql:/etc/postfix/mysql_canonical_maps.cf

and mysql_canonical_maps.cf contains:

   user = postfixuser
   password = postfixpassword
   hosts = localhost, 127.0.0.1
   dbname = postfixdatabase
   query = SELECT result FROM canonical WHERE address='%s' AND  
active='Y'

(obvious information changed). This is unchanged before and after the
upgrade.

The query works on all the addresses (I tested it at the mysql  
command
prompt).

I also tested a local telnet to 127.0.0.1 on port 3306 which worked  
as
expected and mysql responds perfectly. I also tested connection  
through
the localhost socket with works as expected. Also several other  
parts of
my mail system (e.g. courier_auth) connect through the socket and  
report
success. And as noted, postfix is able to connect most of the time
(aliases, virtual maildirs, etc.). Other software on my system, e.g.
PHP, is also able to connect to mysql successfully.

I tried all the steps in the MySQL documentation on dev.mysql.com
suggested to diagnose this error, and all confirmed that the MySQL
server is working correctly.

So I find myself not knowing where to look next, and I hope someone  
here
knows what might be happening or at least be able to point me in the
right direction.

One additional piece of information: I am running on Mac OS/X and as
part of this upgrade, I installed the 10.5.5 to 10.5.6 upgrade (thus
needing to restart my computer). This upgrade is known to break  
postfix.
So I reinstalled postfix and my main.cf and master.cf files after the
upgrade and stopped and restarted postfix to be sure. (and mysql)

What other information might be helpful in diagnosing this?



- check whether any services are chrooted in master.cf (the 5th field
must be set to 'n' and not to 'y' or '-')?

All chroot flags are set to "n"


- try using proxymap (foo_maps=proxy:mysql:/etc/....).

Tried this...no change :(



- make sure the mysql socket is accessible by postfix (and not just by
root).

I checked looking at permissions and trying it as the postfix user and  
all works (I suspected it would, as many accesses work fine (aliases,  
etc. as I noted earlier)



- try with "hosts = 127.0.0.1" (without "localhost")

Tried this - no change. :(

I tried each of the suggestions you made with alias, virtual and local  
addresses

I noticed that when local complained about mysql access, it still did  
fine looking up the local alias and forwarding it correctly.

I'm starting to wonder whether this is a "can't connect" or a delay in  
connecting - is it possible that it's taking too long to connect so a  
warning is reported, but the connection happens eventually? or that it  
works on a retry?

The only thing I've found that doesn't work as expected is  
sender_canonical_maps which would invalidate this theory, but the  
local behavior makes me think it's a possibility?

Alternatively, is it possible that something in the mysql capabilities  
of postfix compiled wrong? I am not that familiar with gcc and the  
build process (I use what I know works) - is there something I can  
look at in the ./configure or make output that would tell me?

I did try rebuilding (./configure,. make, make install) three times...



why do use sender_canonical instead of canonical? rewrite should be
consistent, and "your sender is the recipient's recipient"...




This is mostly because I use maildrop as the virtual delivery agent  
for many of the virtual mailboxes. I'm really just testing this, and  
may end up using canonical instead. But here's my thinking:

I have one user who wants a minor change - sounds silly, but gives me  
a good chance to experiment/learn. I'm rewriting the one address to a  
specific capitalization. I know I'll be doing more with more users soon.

I want to rewrite when mail goes to someone outside my postfix  
install. canonical_maps would also rewrite inbound mail to that  
address, which is not bad, but not the desired behavior.

So I am trying sender_canonical_maps to get the behavior I want.

I'm open to suggestions and learning better ways to do this, so any  
thoughts you have are welcome.



Thank you!