Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - quarantine release process
  FAQ FAQ  Forum Search   Register Register  Login Login

quarantine release process

 Post Reply Post Reply
Author
Terry View Drop Down
Senior Member
Senior Member


Joined: 06 February 2005
Status: Offline
Points: 155
Post Options Post Options   Thanks (0) Thanks(0)   Quote Terry Quote  Post ReplyReply Direct Link To This Post Topic: quarantine release process
    Posted: 12 May 2006 at 11:15am

I am being told that some people have lost emails from quarantine, I can't prove it but here is the scenario they describe...

a) email comes in addressed to 3 recipients

b) email is quarantined because of keywords in the content

c) recipient 1 releases the email from quarantine and receives it in their inbox

d) recipient 2 and 3 no longer have it in their quarantine list...it is gone

here are some log extracts:

03/25/05 15:17:38:199 -- (1852) Connection from: 12.152.130.18  -  Originating country : United States
03/25/05 15:17:38:965 -- (1852) Resolving 12.152.130.18 - Error resolving IP address (DNS Server Reports Query Server Error)
03/25/05 15:17:39:011 -- (1852) - SPF analysis for bbl-inc.com done: - none
03/25/05 15:17:39:011 -- (1852) Mail from: SKIS-YOUNG@bbl-inc.com
03/25/05 15:17:39:277 -- (1852) - MAPS search done...
03/25/05 15:17:39:277 -- (1852) RCPT TO: davids@portptld.com accepted
03/25/05 15:17:39:402 -- (1852) Mail from: SKIS-YOUNG@bbl-inc.com
03/25/05 15:17:39:402 -- (1852) RCPT TO: koehlk@portptld.com accepted
03/25/05 15:17:39:511 -- (1852) Mail from: SKIS-YOUNG@bbl-inc.com
03/25/05 15:17:39:511 -- (1852) RCPT TO: summea@portptld.com accepted
03/25/05 15:17:42:558 -- (1852) Found Keywords: [((?i)(font\-size\:[\x20]{0,1}(\d){1,}\.[1-9]{1,}[\d]{0,1}((\")|(\;)|(p(t|x)))))]
03/25/05 15:17:42:558 -- (1852) EMail from SKIS-YOUNG@bbl-inc.com to davids@portptld.com, koehlk@portptld.com, summea@portptld.com matches content filter rules - rejected.
03/25/05 15:17:42:824 -- (1852) EMail from SKIS-YOUNG@bbl-inc.com to davids@portptld.com, koehlk@portptld.com, summea@portptld.com was received and quarantined. Size: 300 KB, 307200 bytes
03/25/05 15:17:42:933 -- (1852) Disconnect

 

03/28/05 07:25:01:280 -- (4092) Adding to d:\program files\spamfilter\AutoWhiteListForceDelivery.txt:SKIS-YOUNG@b bl-inc.com|summea@portptld.com
03/28/05 07:25:01:295 -- (4092) Delivering quarantined email from <SKIS-YOUNG@bbl-inc.com> to summea@portptld.com
03/28/05 07:25:01:405 -- (3364) Sending email from SKIS-YOUNG@bbl-inc.com to summea@portptld.com
03/28/05 07:25:01:420 -- (2884) Time to add Msg to Bayes corpus:0
03/28/05 07:25:02:030 -- (3364) EMail from SKIS-YOUNG@bbl-inc.com to summea@portptld.com  was forwarded to 10.192.34.83:25

Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 12 May 2006 at 6:52pm
Terry,

If an email arrives as you described, there will 3 records added to the tblQuarantine table, one per recipient, with the email's header information.
The email itself will only be stored once in the tblMsgs table, and the 3 records in the tblQuarantine will point to it.
If one of the recipients chooses to force-deliver the email, only their own record in the tblQuarantine table should be deleted. The records for the other recipients are not altered, and their emails will still be in the database.
We have verified this with the latest build of SpamFilter 3.0.1.560, and are unable to replicate your symptoms.
we do however see from the logs that the email was blocked on 3/25/05, and a copy was force-delivered on 3/28, almost 3 days later. For how long do you store your quarantined items? Is it possible that the other emails were aged out of the quarantine as their retension period expired?
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 12 May 2006 at 6:57pm
----- UPDATE -----

I'm sorry Terry, you are indeed correct. With MySQL we just noticed that while the above statements are correct, the record for the email content itself is being deleted , even though 2 other  records are pointing to it. This is definetly a bug. We will be checking the other database platforms to see if they have the same problems, and will issue a fix within the next few hours.

Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
Guests View Drop Down
Guest Group
Guest Group
Post Options Post Options   Thanks (0) Thanks(0)   Quote Guests Quote  Post ReplyReply Direct Link To This Post Posted: 13 May 2006 at 9:11am
If it helps Roberto we are using SQL server for the quarantine database.  We keep items in quarantine for 14 days.  Also so you know we are currently on release 3.0.1.558.  I realize that we weren't when these logs were produced but I am still hearing that the problem occurs today but have not been given any specifics.
Back to Top
Terry View Drop Down
Senior Member
Senior Member


Joined: 06 February 2005
Status: Offline
Points: 155
Post Options Post Options   Thanks (0) Thanks(0)   Quote Terry Quote  Post ReplyReply Direct Link To This Post Posted: 13 May 2006 at 9:13am
Sorry...that last post came from me...not sure why it said guest...thought I logged in first...Terry
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 14 May 2006 at 11:44pm
Terry,

The fix was rather complicated as we had to completely revise the quarantine deleteion queries. We did have a nice bug in there that caused the behavior you saw. All database platforms where affected.

Build 561 is now avail in the registered user area with the fix. We're still testing it as, again, the revised SQL queries are dramatically different now, but so far all looks good.
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
Terry View Drop Down
Senior Member
Senior Member


Joined: 06 February 2005
Status: Offline
Points: 155
Post Options Post Options   Thanks (0) Thanks(0)   Quote Terry Quote  Post ReplyReply Direct Link To This Post Posted: 15 May 2006 at 9:37am

I moved the new files into our spamfilter directory and restarted the service....I now get a timeout error on the quarantine database as shown in the logs....when I go back to the 558 release the errors go away.

5/15/06 06:24:00:991 -- (90004) 71.9.76.248 - Mail from: airfoil@ireland.com To: ab@portptld.com will be rejected
05/15/06 06:24:03:022 -- (88900) Error occurred during RECEIVEMESSAGE when accessing the quarantine database: Timeout expired ( 1 2 2b 3 3a 4 5 6 7 8 9 11 12 13 15 16 17 18 18b 19 29 30 31 32 33 54 55 56 77 78 79 80 81 82 83 84 85 86 87 88)
05/15/06 06:24:03:022 -- (88900) EMail from calaelayfield@terrusa.net to vernchinstocky@portptld.com was received and quarantined. Size: 3 KB, 3072 bytes
05/15/06 06:24:03:053 -- (88744) Time to add Msg to Bayes corpus:0
05/15/06 06:24:03:428 -- (88900) Blacklist cache - Added 82.53.104.163 to limbo
05/15/06 06:24:03:444 -- (88900) Disconnect
05/15/06 06:24:04:475 -- (89068) Connection from: 210.76.59.189  -  Originating country : China
05/15/06 06:24:05:413 -- (89068) Resolving 210.76.59.189 - Not found
05/15/06 06:24:05:413 -- (89068) - Reverse DNS not found -
05/15/06 06:24:05:413 -- (89068) 210.76.59.189 - Mail from: mccambridcleon@lvg.bwl.de To: benamh@portptld.com will be rejected
05/15/06 06:24:05:616 -- (88764) Connection from: 211.178.123.28  -  Originating country : Korea, Republic of
05/15/06 06:24:06:444 -- (88492) Error occurred during RECEIVEMESSAGE when accessing the quarantine database: Timeout expired ( 1 2 2b 3 3a 4 5 6 7 8 9 11 12 13 15 16 17 18 18b 19 29 30 31 32 33 54 55 56 77 78 79 80 81 82 83 84 85 86 87 88)
05/15/06 06:24:06:444 -- (88492) EMail from misaki_m_m_misaki@yahoo.co.jp to ter@portptld.com was received and quarantined. Size: 1 KB, 1024 bytes
05/15/06 06:24:06:475 -- (88744) Time to add Msg to Bayes corpus:0

Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 15 May 2006 at 4:17pm
Terry,

Are you still having the problem? From the logs, even though it may appear a strange coincidence, the problem is completely separate from the one that was patched with the last build.

The error is very specific (a timeout on a query), and all those weird numbers pinpoint the query that caused the error. It's:

SELECT * FROM tblQuarantine WHERE 0=1;

can you please try executing that query on the SpamFilter database from either SQL's Enterprise Manager or the Query Analyzer tool? It should retuen an empty result set with no records displayed. If it retuens an error, that would indicate issues with either the database/database server.
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
Terry View Drop Down
Senior Member
Senior Member


Joined: 06 February 2005
Status: Offline
Points: 155
Post Options Post Options   Thanks (0) Thanks(0)   Quote Terry Quote  Post ReplyReply Direct Link To This Post Posted: 15 May 2006 at 4:24pm
the select statement worked just fine and returned 0 rows.  I had to uninstall the fix because it appeared to be failing in access to the quarantine and I wasn't sure any new stuff was getting there...
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 15 May 2006 at 6:23pm
You're correct on the "wasn't sure any new stuff was getting there". With that error, emails where not being quarantined.

I'd like to ask you to try again if you can, as there is nothing in this build that would explain this behavior, other than a temporary issue with the SQL server.

If you wish, you could rename the SpamFilter.exe file in the zip update to something else, like SF.exe, and copy it in the SpamFilter directory.
You can then stop SpamFilter,  and then run the new SF.exe for a few seconds to see if the error occurs. SF.exe will run in standalone, non-service mode, and you will see from the GUI what is happening.

If the problem still persists, please zip and email us SpamFilter's activity log for the day so we can try to see what is going on.
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
Terry View Drop Down
Senior Member
Senior Member


Joined: 06 February 2005
Status: Offline
Points: 155
Post Options Post Options   Thanks (0) Thanks(0)   Quote Terry Quote  Post ReplyReply Direct Link To This Post Posted: 15 May 2006 at 8:03pm

Same problem Roberto....moved the file into the directory after stopping the service...ran it for a couple of minutes...here is another extract...if you want the whole day...it would be better for me to do it tomorrow as that would be a fresh log

5/15/06 16:55:37:481 -- (5776) - MAPS search done...
05/15/06 16:55:37:481 -- (5776) RCPT TO: Stan.Jones@portofportland.com accepted
05/15/06 16:55:37:528 -- (5100) Connection from: 220.150.169.59  -  Originating country : Japan
05/15/06 16:55:37:653 -- (5776) EMail from Stan@terrahydrinc.com to Stan.Jones@portofportland.com passes Bayesian filter - 0% spam  (0ms)
05/15/06 16:55:37:715 -- (5160) Error occurred during RECEIVEMESSAGE when accessing the quarantine database: Timeout expired ( 1 2 2b 3 3a 4 5 6 7 8 9 11 12 13 15 16 17 18 18b 19 29 30 31 32 33 54 55 56 77 78 79 80 81 82 83 84 85 86 87 88)
05/15/06 16:55:37:715 -- (5160) EMail from cpuhoqaqe@dow.jones.com to brantm@portptld.com, bailej@portptld.com, biebeg@portptld.com was received and quarantined. Size: 11 KB, 11264 bytes
05/15/06 16:55:37:746 -- (5424) Time to add Msg to Bayes corpus:0
05/15/06 16:55:37:825 -- (5776) EMail from Stan@terrahydrinc.com to Stan.Jones@portofportland.com was queued. Size: 1 KB, 1024 bytes
05/15/06 16:55:37:840 -- (5484) Sending email from Stan@terrahydrinc.com to Stan.Jones@portofportland.com
05/15/06 16:55:37:856 -- (5424) Time to add Msg to Bayes corpus:15
05/15/06 16:55:37:965 -- (5776) Disconnect
05/15/06 16:55:37:981 -- (5484) EMail from Stan@terrahydrinc.com to Stan.Jones@portofportland.com  was forwarded to portexfe.pop.portptld.com:25
05/15/06 16:55:38:262 -- (5160) Blacklist cache - Added 218.155.140.30 to limbo
05/15/06 16:55:38:387 -- (5100) Resolving 220.150.169.59 - 59.169.150.220.ap.yournet.ne.jp
05/15/06 16:55:38:715 -- (5924) Connection from: 208.187.190.34  -  Originating country : United States
05/15/06 16:55:39:559 -- (5924) Resolving 208.187.190.34 - cp.ipnshosting.com
05/15/06 16:55:39:559 -- (5924) - Empty Mail From -
05/15/06 16:55:39:559 -- (5924) 208.187.190.34 - Mail from:  To: Eric.Hedaa@portofportland.com will be rejected
05/15/06 16:55:39:934 -- (5504) Error occurred during RECEIVEMESSAGE when accessing the quarantine database: Timeout expired ( 1 2 2b 3 3a 4 5 6 7 8 9 11 12 13 15 16 17 18 18b 19 29 30 31 32 33 54 55 56 77 78 79 80 81 82 83 84 85 86 87 88)
05/15/06 16:55:39:934 -- (5504) EMail from valerie89k@earthlink.net to oestem@portptld.com was received and quarantined. Size: 1 KB, 1024 bytes
05/15/06 16:55:39:965 -- (5424) Time to add Msg to Bayes corpus:15
05/15/06 16:55:40:403 -- (5504) Blacklist cache - Added 217.165.190.89 to limbo
05/15/06 16:55:40:403 -- (5504) Disconnect

 

Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 15 May 2006 at 11:08pm
The more I look into the possible cause in this, the more everything points to a timeout issue with the SQL server.
This new buld of SpamFilter has new SQL queries to clear out the quarantine database. One of the queries is also designed to perform better housekeeping, by deleting any orphan records that may be present in the DB. If your database is large, the initial "cleanup" query may require a lot of resources from the SQL server. Depending on the SQL server's hardware (CPU and RAM) this may cause other queries to timeout while the cleanup queries are executing.

Could you try to manually execute the cleanup procedures? Please try executing the following in SQL Analyzer:

UPDATE    tblQuarantine
SET         &nb sp;    Expire = 1
WHERE     (MsgDate < DATEADD(day, - 14, GETDATE()))
GO

DELETE FROM tblQuarantine WHERE tblQuarantine.Expire <> 0
GO

DELETE tblMsgs FROM tblQuarantine RIGHT OUTER JOIN tblMsgs
    ON tblQuarantine.MsgID = tblMsgs.MsgID WHERE (tblQuarantine.MsgID IS NULL)
GO


You can also try to execute the 3 queries, in the order given, one at a time in the SQL Enterprise Manager. Please monitor the CPU and RAM on the SQL server while they execute to see if they spike (and remain spiked) for a while (minutes).

This cleanup should take the most resources only the 1st time ever it's run, and that is only if there were orphan records to begin with in the database.

After this is done, could you please, once more, try running the latest SpamFilter?

Thanks.


Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
Terry View Drop Down
Senior Member
Senior Member


Joined: 06 February 2005
Status: Offline
Points: 155
Post Options Post Options   Thanks (0) Thanks(0)   Quote Terry Quote  Post ReplyReply Direct Link To This Post Posted: 16 May 2006 at 9:31am

Well that did seem to help...statement #1 took about 2 seconds and affected 6 rows, statement #2 took about 1 second and affected 38 rows, statement #3 took 12 minutes and 36 seconds and affected 59,399 rows (lots of orphaned messages).

After running the statements from sql analyzer I am able to run the new version spamfilter with no  timeouts so far.  It is running at this time and I will monitor it during the day today. 

Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

Joined: 27 January 2005
Location: United States
Status: Offline
Points: 1143
Post Options Post Options   Thanks (0) Thanks(0)   Quote Desperado Quote  Post ReplyReply Direct Link To This Post Posted: 16 May 2006 at 10:49am

Terry,

I too am working on this with LogSat.  How many records did you have before and after the query?

The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com

Back to Top
Terry View Drop Down
Senior Member
Senior Member


Joined: 06 February 2005
Status: Offline
Points: 155
Post Options Post Options   Thanks (0) Thanks(0)   Quote Terry Quote  Post ReplyReply Direct Link To This Post Posted: 16 May 2006 at 10:54am
In the tblmsgs table we had approximately 84,027 messages before the statement ran...and purged 59,399 of them....in the tblQuarantine table we had closer to 25,000 records.
Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

Joined: 27 January 2005
Location: United States
Status: Offline
Points: 1143
Post Options Post Options   Thanks (0) Thanks(0)   Quote Desperado Quote  Post ReplyReply Direct Link To This Post Posted: 16 May 2006 at 10:57am

Terry,

So ... you think that you did not actually loose anything EXCEPT orphaned messages?

The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com

Back to Top
Terry View Drop Down
Senior Member
Senior Member


Joined: 06 February 2005
Status: Offline
Points: 155
Post Options Post Options   Thanks (0) Thanks(0)   Quote Terry Quote  Post ReplyReply Direct Link To This Post Posted: 16 May 2006 at 11:14am
I hope not...but I don't know how to prove it
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.094 seconds.