[Sqlgrey-users] More on sqlgrey stop/recover issue

[Andrew D. <and...@gm...>]

Hello SQLgrey,

I've run into quite a few instances over the last month or so where
SQLgrey gets a "SQLgrey database error" and then recovers.  I think it
has to do with unicode domain names.  I started running SQLgrey with
more logging, and here's a snippet for when things go badly:

Dec 18 12:32:41 tango postfix/smtpd[10133]: connect from unknown[81.215.189=
.168]
Dec 18 12:32:42 tango sqlgrey: 2006/12/18-12:32:42 CONNECT TCP Peer: "127.0=
.0.1:54494" Local: "127.0.0.1:2501"
Dec 18 12:32:42 tango sqlgrey: optin: greylisting active for
jb...@ex...
Dec 18 12:32:42 tango sqlgrey: grey: unknown RDNS: 81.215.189.168
Dec 18 12:32:42 tango sqlgrey: warning: Use of uninitialized value in conca=
tenation (.) or string at /usr/sbin/sqlgrey line 1062.
Dec 18 12:32:42 tango sqlgrey: dbaccess: error: couldn't access from_awl ta=
ble:=20
Dec 18 12:32:43 tango sqlgrey: grey: from awl match: updating 81.215.189.16=
8(81.215.189.168), man...@ab...(man=C3=A8gednervy@a=
byss.wesn.sun.com)
Dec 18 12:32:43 tango sqlgrey: dbaccess: warning: couldn't do query: UPDATE=
 from_awl SET last_seen =3D NOW(), first_seen =3D first_seen WHERE sender_n=
ame =3D 'man=C3=A8genervy' AND sender_domain =3D 'abyss.wesn.sun.com' AND s=
rc =3D '81.215.189.168': ERROR:  invalid byte sequence for encoding "UNICOD=
E": 0xe86765 , reconnecting to DB
Dec 18 12:32:43 tango postfix/pickup[9793]: 04D7E1A6BD: uid=3D1004 from=3D<=
sqlgrey>

That line in SQLgrey is a log error:
 $self->mylog('dbaccess', 0, "error: couldn't access $from_awl table: $DBI:=
:errstr");

and the whole function is:
## Match connections to AWLs ##

sub is_in_from_awl {
    my ($self, $sender_name, $sender_domain, $host) =3D @_;

    # last_seen less than $self->{sqlgrey}{awl_age} days ago
    my $sth =3D $self->prepare("SELECT 1 FROM $from_awl " .
                 'WHERE sender_name =3D ? ' .
                 'AND sender_domain =3D ? ' .
                 'AND src =3D ? ' .
                 'AND last_seen > ' .
                 $self->past_tstamp($self->{sqlgrey}{awl_age},
                        'DAY')
                );
    if (!defined $sth or !$sth->execute($sender_name, $sender_domain, $host=
)) {
    $self->db_unavailable();
    $self->mylog('dbaccess', 0, "error: couldn't access $from_awl table: $D=
BI::errstr");
        return 1; # in doubt, accept
    } else {
    $self->db_available();
    }
    my $result =3D $sth->fetchall_arrayref();
    if ($#$result !=3D 0) {
    return 0; # not a single entry
    } else {
    return 1; # one single entry (no multiple entries by design)
    }
}


Is there anything I can do in either SQLgrey or postfix to stop this
from happening?  As far as I can tell it always reconnects right away,
but it is a little concerning.

--=20
Best regards,
 Andrew