Re: [Sqlgrey-users] sqlgrey fails when configured with multiple machines

[Alex <mys...@gm...>]

Hi,

> > By hanging, i mean "any network connection or connection attempt, that
> > stalls for more than a few seconds".
>
> Okay, that's how I understand it, but that's not what's happening here.
>
> All evidence so far, points to this explanation. Including the fact that
my timeout-fix worked.
> Im unsure what you base your assumption on, that this is not whats
happening, as the logs wont show you
> this and you'd need to do somthing like modifying the sqlgrey code to
provide you with debugging information or
> telnet/netcat to talk to sqlgrey & postfix.

It's only based on the fact that there is no stalling or any delays here -
it happens immediately when sqlgrey isn't running at all. Hopefully I'm
being pedantic here. I just mean that the connection attempt is never
successful if sqlgrey isn't running. It should realize this immediately.

> That's assuming sqlgrey is still around to respond.  I need to also
consider the possibility where sqlgrey dies.
>
> I've never experienced sqlgrey just dying on me, but if it happens, it is
Postfix that decides what to respond.
> It cannot it be influenced by sqlgrey.
> And the error, 451, is a temporary error, so mails will be delivered once
sqlgrey is running again.

Okay, right, I should have known that's a temporary error. I do know
sqlgrey can't control postfix if it's not running, of course.

> > >> db_host = mail02.example.com;mysql_connect_timeout=1
>
> I don't see that option in the default documentation. Where is this
documented?
>
> Its not an option. Its a hack i made up for this occasion.
>
> Sqlgrey uses a "DSN" internally for connecting to mysql. They look
somthing like this:
>
> DBI:mysql:sqlgrey;host=db.example.com;port=3306
>
> And in sqlgrey $host is just inserted into this DSN somthing like this.
> DBI:mysql:sqlgrey;host=$host;port=3306
>
> Which is why, if $host = "127.0.0.2;whatever=3", the DSN will contain
> DBI:mysql:sqlgrey;host=127.0.0.2;whatever=3;port=3306
>
> and mysql_connect_timeout happens to be an option you can add to the DSN.
> So its just a hack. Its definitely something we should add as an option
in a later version.

Okay, great, got it. It's also nice to hear another version is intended at
some point.

> > In my tests, this solves the issue, because postfix doesn't have to
> > timeout the connection to sqlgrey and everything remains shiny.
> >
> > (shiny = "mails will pass through unhindered, while the sql-server is
down")
>
> So postfix was always waiting patiently enough; it was sqlgrey that was
responding with failure too quickly?
>
> No. The other way around. sqlgrey may be 3 minutes in getting a timeout
from its mysql-connect(). But
> postfix "aint got time for that" and is disconnecting already after, eg.
, 100 seconds. So sqlgrey is too slow to
> respond to postfix and postfix just disconnects.  And THATS why you get
"Server configuration problem".

Right, okay. So is the "mysql_connect_timeout=1" instructing sqlgrey to
wait for 1s? Or is that just an on/off thing? I'm trying to understand the
postfix interaction part. In other words, postfix must have a fixed-length
amount of time it waits, since you mentioned it wasn't adjustable.
Hardcoded in sqlgrey is something that makes sure it waits less units of
time than this postfix timeout default, correct?

> Each client has a local copy of the database, no? And by setting
read_hosts to contain at least localhost, it should
> then be able to query the local database, no?
>
> In theory, we could query the localhost. But since sqlgrey will fall-back
to to allowing all mails through, it doesnt
> matter what is in the database. Since the mail will go through anyway.
>
> And sqlgrey doesnt really work without being able to write, so it smarter
just to accept all mail.

Okay, that's a big help. So although mysql itself replicates the data
between each host, sqlgrey isn't designed to read the data from that local
host, and it doesn't make sense to do that.

> > >> read_hosts=localhost prepend = 0 optmethod = optout discrimination =
on
>
> I don't see where these options are defined either.
>
> I see all of them, with comments, in the sample config that comes with
sqlgrey-1.8.0. Have a look there and
> see if not everything is explained.

I'll have to look again.

> Hope that answers everything :)

Really appreciate all your hard work, both here and in the code. I've
learned so much.

Thanks,
Alex