#16 spurious db reconnections in shared db environment

open
database (9)
5
2008-05-16
2008-05-05
No

We have 3 front end postfix servers fed SMTP by a load balancer, each running
sqlgrey-1.6.7 with a single mysql backend shared by all 3.

We see several spurious reconnections of the db each day; however
the logs didn't show clearly what was going on until I fixed some code
that was erroneously trying to display $DBI::errstr after the database
had been closed; see patch below.

$DBI::errstr is undefined when the database is closed.

May 2 15:17:01 MYHOST sqlgrey: warning: Use of uninitialized value in concatenation (.) or string at /opt/local/sbin/sqlgrey line 227.
May 2 15:17:01 MYHOST sqlgrey: dbaccess: warning: couldn't do query:\nINSERT INTO from_awl (sender_name, sender_domain, src, first_seen, last_seen) VALUES('acf','blah.com','1.2.3','2008-05-02 15:06:25',NOW()):\n, reconnecting to DB

*** sqlgrey.orig Sat May 20 03:22:07 2006
--- sqlgrey Sat May 3 12:51:17 2008
***************
*** 223,232 ****
return $result;
} else {
# failure
$self->db_unavailable();
$self->mylog('dbaccess', 0, "warning: couldn't do query:\n" .
"$query:\n" .
! "$DBI::errstr, reconnecting to DB");
return undef;
}
}
--- 223,233 ----
return $result;
} else {
# failure
+ my($e) = "$DBI::errstr";
$self->db_unavailable();
$self->mylog('dbaccess', 0, "warning: couldn't do query:\n" .
"$query:\n" .
! "$e, reconnecting to DB");
return undef;
}
}

From code inspection the latest version 1.7.6 also has
this problem, and in a number of places.

Once the above patch was applied, it became obvious what was
actually causing the spruious reconnections... duplicate key's
when inserting from_awl entries.

May 4 11:59:39 MYHOST sqlgrey: dbaccess: warning: couldn't do query:\nINSERT INTO from_awl (sender_name, sender_domain, src, first_seen, last_seen) VALUES('blah','blah.com','1.2.3','2008-05-04 11:32:08',NOW()):\nDuplicate entry '1.2.3-blah.com-blah' for key 1, reconnecting to DB

The code has a race condition between checking if a given from_awl
entry has been inserted and inserting one because it thought it wasn't
there... that shows up when multiple sqlgrey's are sharing the same
database.

A simple fix would be to check for the duplicate-key error on all
inserts of this nature, and silently forge on if found, rather than
considering this a database error and noisily doing a reconnecticon.

Otherwise I've been using this version for a couple of years now
and its worked flawlessly.

Discussion

  • Lionel Bouton

    Lionel Bouton - 2008-05-16

    Logged In: YES
    user_id=89899
    Originator: NO

    Thanks for the heads-up, it's not a huge problem (the greylisting process remains unaffected) so don't expect a quick fix, but it's on my TODO list.

     
  • Lionel Bouton

    Lionel Bouton - 2008-05-16

    Logged In: YES
    user_id=89899
    Originator: NO

    Thanks for the heads-up, it's not a huge problem (the greylisting process remains unaffected) so don't expect a quick fix, but it's on my TODO list.

     
  • Lionel Bouton

    Lionel Bouton - 2008-05-16

    Logged In: YES
    user_id=89899
    Originator: NO

    Thanks for the heads-up, it's not a huge problem (the greylisting process remains unaffected) so don't expect a quick fix, but it's on my TODO list.

     
  • Lionel Bouton

    Lionel Bouton - 2008-05-16
    • assigned_to: nobody --> gyver
     
  • Stefano Mason

    Stefano Mason - 2009-04-08

    is there any patch/fix?

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks