Bug 5464 - SQLite user prefs: filehandle to database is not closed ('closing dbh with active statement handles')
Summary: SQLite user prefs: filehandle to database is not closed ('closing dbh with ac...
Status: RESOLVED FIXED
Alias: None
Product: Spamassassin
Classification: Unclassified
Component: spamc/spamd (show other bugs)
Version: 3.2.0
Hardware: PC FreeBSD
: P5 normal
Target Milestone: 3.2.4
Assignee: SpamAssassin Developer Mailing List
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2007-05-16 07:44 UTC by Mike
Modified: 2007-11-25 18:05 UTC (History)
1 user (show)


Attachment Type Modified Status Actions Submitter/CLA Status
possible fix patch None Daryl C. W. O'Shea [HasCLA]
spamd_sql_prefs t test text/plain None Michael Parker [HasCLA]
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Mike 2007-05-16 07:44:18 UTC 
Spamassassin 3.2.0
Freebsd 6.2
p5-DBD-SQLite-1.13
sqlite3-3.3.16

We are using sqlite for user preferences only. The following error scrolls in
the error log and if you do lsof |grep dbname the filehandles to the database
never close. At somepoint the system will run out of filehandles (and probaly
crash). Not sure if this has anything to do with Spamassassin/SQL.pm or if its a
bug with sqlite or DBD-SqLite.

Thanks.

spamd[9824]: closing dbh with active statement handles at /usr/local/lib/perl5/site
_perl/5.8.8/Mail/SpamAssassin/Conf/SQL.pm line 172, <GEN51> line 2
Comment 1 Daryl C. W. O'Shea 2007-05-16 16:06:25 UTC 
You'll likely never run out of FHs due to each child's life being limited to 200
messages.  Have you actually seen that "At somepoint the system will run out of
filehandles"?  Nonetheless it's something to look into.
Comment 2 Michael Parker 2007-05-16 16:08:02 UTC 
Try turning on autocommit in your connect string.
Comment 3 Mike 2007-05-17 11:39:27 UTC 
I added AutoCommit => 1 in
/usr/local/lib/perl5/site_perl/5.8.8/Mail/SpamAssassin/Conf/SQL.PM on line 125.

Average memory usage is about 400mb. I switched it to use sqlite and memory
continues to go up (I stopped it at 2gig). Switched back to postgres and it
works fine. There's some sort of memory leak somewhere. Any thoughts?

Thanks guys.
Comment 4 Matt Sergeant 2007-05-18 07:56:50 UTC 
You need to explicitly $sth->finish with SQLite. It's a bit of a bug in sqlite
which I hope to fix in the future, but for now that should fix it.
Comment 5 Mike 2007-05-18 08:14:19 UTC 
/usr/local/lib/perl5/site_perl/5.8.8/Mail/SpamAssassin/Conf/SQL.pm 
does call $sth->finish line 163. Is it missing this somewhere else?

Is it even a problem with 
/usr/local/lib/perl5/site_perl/5.8.8/Mail/SpamAssassin/Conf/SQL.pm

thanks.
Comment 6 Matt Sergeant 2007-05-18 08:23:12 UTC 
OK, so this might fix it:

Index: lib/Mail/SpamAssassin/Conf/SQL.pm
===================================================================
--- lib/Mail/SpamAssassin/Conf/SQL.pm   (revision 539498)
+++ lib/Mail/SpamAssassin/Conf/SQL.pm   (working copy)
@@ -165,6 +165,7 @@
        else {
         die "config: SQL error: $sql\n".$sth->errstr."\n";
        }
+       undef $sth;
      }
      else {
        die "config: SQL error: " . $dbh->errstr . "\n";
Comment 7 Mike 2007-05-18 09:23:10 UTC 
Hi, thanks. Tried that and it didnt work. Memory was up to 1.2Gig used in about
30 seconds. Switched the database back to postgres and average used memory is 400mb.
Comment 8 Sidney Markowitz 2007-06-04 21:58:22 UTC 
Moving items off the 3.2.1 queue to 3.2.2 to facilitate a quick release.
If you can get this in Review status right away feel free to move it back
Comment 9 Justin Mason 2007-08-12 06:38:59 UTC 
3.2.3 was released without these fixed, moving to 3.2.3
Comment 10 Daryl C. W. O'Shea 2007-11-06 14:06:03 UTC 
Created attachment 4181 [details]
possible fix

try this Mike
Comment 11 Michael Parker 2007-11-24 22:35:31 UTC 
FYI, I wrote a t test that shows this and I'm look into why its happening now. 
Tried Daryl's fix but it is still showing up.
Comment 12 Michael Parker 2007-11-24 22:47:51 UTC 
Created attachment 4194 [details]
spamd_sql_prefs t test

Here is the t test I'm working on, it doesn't currently pass due to this bug. 
If you want to try it out you'll need to add:

run_sql_pref_tests=y

to your t/config file.
Comment 13 Michael Parker 2007-11-25 18:05:02 UTC 
Ok, I stand corrected, adding undef $sth does in fact fix it, I must have
forgotten to run make or something before.

Sending        lib/Mail/SpamAssassin/Conf/SQL.pm
Sending        t/config.dist
Adding         t/spamd_sql_prefs.t
Transmitting file data ...
Committed revision 598107.