Spam Filter ISP Forums : Database is Busy

Database is Busy : Thanks for the details. I think...

Mon, 06 Aug 2007 18:53:33 +0000

Author: atifghaffar
Subject: 6088
Posted: 06 August 2007 at 6:53pm

Thanks for the details. I think this will be quiet helpful.

Database is Busy : This change only affects users...

Mon, 06 Aug 2007 18:18:25 +0000

Author: LogSat
Subject: 6088
Posted: 06 August 2007 at 6:18pm

This change only affects users who implemented the "AuthorizedTO" whitelist that specifies the list of valid emails accounts to receive email for.

In the past, SpamFilter has always forced a disconnect of the remote server if they specified an incorrect address in the RCPT TO command. This prevented the spammers from harvesting valid email addresses, and also greatly reduced spam as if the spammer would try again to send emails to different invalid addresses, after a few retries they would eventually be blocked by the IP cache filter, which cached the bad IP and blocked further connections for a few minutes.

The downside is that, if there was an email with multiple recipients, and one of them was misspelled or simply did not exist anymore, this would have caused the sender to be disconnected, and cause delivery problems to valid addresses as well.

If the Bayesian filter is enabled, and the "Receive full emails if recipient not in AuthorizedTO whitelist" option was checked, SpamFilter's behavior is different. In this scenario, if a recipient is not in the "AuthorizedTO" whitelist, SpamFilter will reject the invalid recipient, but will continue to accept further RCPT TO commands for more recipients.

With this build, we added another option in the SpamFilter.ini file that will achieve this same functionality, even if the Bayesian filter is disabled.

Database is Busy : Roberto,Thankyou very much. It...

Mon, 06 Aug 2007 03:36:51 +0000

Author: atifghaffar
Subject: 6088
Posted: 06 August 2007 at 3:36am

Roberto,

Thankyou very much. It works great now.

Can you please expand a bit more on this new feature.
ForceDisconnectOnNonAuthorizedTO. How does this differ from before?

Database is Busy : Atif,We're releasing a new...

Sun, 05 Aug 2007 23:49:22 +0000

Author: LogSat
Subject: 6088
Posted: 05 August 2007 at 11:49pm

Atif,

We're releasing a new build right now, please see the release notes below.

// New to VersionNumber = '3.5.4.702';
{TODO -cNew : Due to memory leak issues and Access Violations in the PDF library used, by default SpamFilter will not scan for images within PDF files by setting the value SpamPDFMaxPagesToScan=0 in the SpamFilter.ini file. If this setting was enabled in a previous version however, it will remain enabled after upgrading to this version}
{TODO -cFix : Due to memory leak issues and Access Violations in the PDF library used, we have been forced to remove the ability to scan keywords within PDF files. We may re-add this functionality at a later time when a more stable PDF library is available}
{TODO -cNew : Added new option ForceDisconnectOnNonAuthorizedTO in SpamFilter.ini file to change the default behavior that forces a disconnect when a recipient is specified that is not in the AuthorizedTO list}
{TODO -cNew : Added new option DoNotDeleteExpiredEmailsFromQuarantine in SpamFilter.ini to prevent SpamFilter to perform the routine cleanup of the quarantine database by deleting old archived emails. Useful if admins want to perform their own cleanup}

Database is Busy : PS: The db in question which I...

Thu, 02 Aug 2007 20:12:50 +0000

Author: atifghaffar
Subject: 6088
Posted: 02 August 2007 at 8:12pm

PS: The db in question which I have problems with has 3.5million rows for tblqurantine and thats just 11 days of data. We advertize retention period of 10 days and I add 1 more just for paranoid reasons.

Even providing access to some 50K rows for a single domain via a web interface is becoming difficult, but I am working over the idea of creating a couple of report tables (incoming and outgoing) per domain on first operation which can then be used in the webinterface for browsing/releasing/deleting.

perhaps you see now, why I want to keep the qurantine data separate from SFE tables... Ah perhaps the MysqlProxy might be able to do some tricks there. http://dev.mysql.com/downloads/mysql-proxy/

Database is Busy : Roberto,Thanks. I will wait for...

Thu, 02 Aug 2007 20:07:00 +0000

Author: atifghaffar
Subject: 6088
Posted: 02 August 2007 at 8:07pm

Roberto,

Thanks. I will wait for the next build rather than hacking in something.

Another possibility I was looking at was to implement the recently released Alpha MysqlProxy. This will allow me to intercept and rewrite/drop/give_fake_result for the delete from tblqurantine left join.... command.

Database is Busy : Atif,Revoking the delete rights...

Thu, 02 Aug 2007 19:59:43 +0000

Author: LogSat
Subject: 6088
Posted: 02 August 2007 at 7:59pm

Atif,

Revoking the delete rights on that table "should" work, but has not been tested before. You do have a point in that with a large interval SpamFilter will still perform the delete routines on startup. Your request is very legitimate, and as is also very simple to implement, we'll try to include it in the next build.

Database is Busy : Would it help if I revoke delete...

Thu, 02 Aug 2007 12:30:49 +0000

Author: atifghaffar
Subject: 6088
Posted: 02 August 2007 at 12:30pm

Would it help if I revoke delete right of the user that is doing the quarantine?

Database is Busy : Roberto,Is is possible to disable...

Thu, 02 Aug 2007 04:09:17 +0000

Author: atifghaffar
Subject: 6088
Posted: 02 August 2007 at 4:09am

Roberto,

Is is possible to disable the deleting of quarantined emails. (Delete expired emails).

Even if I set the value very high (9999 minutes), SF tries to delete when it restarts.

Would it help if I set this value to zero and a large number in number of days to store rejected mails.

Database is Busy : Hello Simone,We run a couple of...

Wed, 25 Jul 2007 18:23:20 +0000

Author: atifghaffar
Subject: 6088
Posted: 25 July 2007 at 6:23pm

Hello Simone,

We run a couple of scripts for doing this.
We run these scripts on linux under supervision of daemontools.

Script 1. sf.expire.old.records.pl
#!/usr/bin/perl
# Find all records older than 11 days and set their exipre to 1
BEGIN{
   use FindBin; unshift @INC, ($FindBin::Bin , "$FindBin::Bin/../lib");
}
$|++;

use DBI;

$dbserver="servername";
$dbname="dbname";
$dbuser="username";
$dbpass="password";

my $dsn = "DBI:mysql:database=$dbname;host=$dbserver";
$dbh=DBI->connect_cached($dsn, $dbuser, $dbpass);

use vars qw($dbh);

$query="update tblQuarantine set Expire=1 where Expire=0 and date_add(MsgDate, interval 11 day)$sth=$dbh->prepare($query);

while(true){
        $sth->execute();
        if ($sth->rows < 10) {
                 print "Less than 10 rows affected. Disconnecting from the database, sleeping for one hour and then exiting\n";
                 $dbh->disconnect or warn $dbh->errstr;
                 sleep 3600;
                 exit;
        }
        print "Rows affected: ", $sth->rows, "\n";
        sleep(1);
}
__END__

Script 2. sf.purge.expired.records.pl

#!/usr/bin/perl
# Find all expired rows and delete from both tblquarantine and tblmsgs
BEGIN{
   use FindBin; unshift @INC, ($FindBin::Bin , "$FindBin::Bin/../lib");
}
$|++;

use DBI;

$dbserver="servername";
$dbname="dbname";
$dbuser="username";
$dbpass="password";

my $dsn = "DBI:mysql:database=$dbname;host=$dbserver";
$dbh=DBI->connect_cached($dsn, $dbuser, $dbpass);

use vars qw($dbh);

# be nice to the db. dont select too much at a time
$query="select QuarID, MsgID from tblQuarantine where Expire=1 limit 10";
$sth=$dbh->prepare($query);

$quar_del_sth=$dbh->prepare("delete from tblQuarantine where QuarID=? limit 1");
$msg_del_sth=$dbh->prepare("delete from tblMsgs where MsgID=? limit 1");

while(true){
   $sth->execute();

   while (($QuarID, $MsgID)=$sth->fetchrow_array()){
       print "Purging QuarID $QuarID, MSGID $MsgID\n";
       $quar_del_sth->execute($QuarID);
       $msg_del_sth->execute($MsgID);
   }
}
__END__