quarantine release process |
Post Reply 
|
| Author | |
Terry 
Senior Member 
Joined: 06 February 2005 Status: Offline Points: 155 |
 Post Options
 Thanks(0)
 Quote Reply
 Topic: quarantine release processPosted: 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/28/05 07:25:01:280 -- (4092) Adding to d:\program files\spamfilter\AutoWhiteListForceDelivery.txt:SKIS-YOUNG@b bl-inc.com|summea@portptld.com |
|
 |
|
|
LogSat
Admin Group 
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
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? |
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
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. |
|
|
|
|
Guests
Guest Group 
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
|
|
|
Terry
Senior Member
Joined: 06 February 2005 Status: Offline Points: 155 |
Post Options
Thanks(0)
Quote Reply
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
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
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. |
|
|
|
|
Terry
Senior Member
Joined: 06 February 2005 Status: Offline Points: 155 |
Post Options
Thanks(0)
Quote Reply
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 |
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
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. |
|
|
|
|
Terry
Senior Member
Joined: 06 February 2005 Status: Offline Points: 155 |
Post Options
Thanks(0)
Quote Reply
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...
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
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. |
|
|
|
|
Terry
Senior Member
Joined: 06 February 2005 Status: Offline Points: 155 |
Post Options
Thanks(0)
Quote Reply
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...
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
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. |
|
|
|
|
Terry
Senior Member
Joined: 06 February 2005 Status: Offline Points: 155 |
Post Options
Thanks(0)
Quote Reply
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. |
|
|
|
|
Desperado
Senior Member
Joined: 27 January 2005 Location: United States Status: Offline Points: 1143 |
Post Options
Thanks(0)
Quote Reply
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 |
|
|
|
|
Terry
Senior Member
Joined: 06 February 2005 Status: Offline Points: 155 |
Post Options
Thanks(0)
Quote Reply
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.
|
|
|
|
|
Desperado
Senior Member
Joined: 27 January 2005 Location: United States Status: Offline Points: 1143 |
Post Options
Thanks(0)
Quote Reply
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 |
|
|
|
|
Terry
Senior Member
Joined: 06 February 2005 Status: Offline Points: 155 |
Post Options
Thanks(0)
Quote Reply
Posted: 16 May 2006 at 11:14am |
|
I hope not...but I don't know how to prove it
|
|
|
|
Topic Options|
Post Reply
|
|
Tweet
|
| Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |
This page was generated in 0.221 seconds.