Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - domainid in tblwl_autowhitelistforcedelivery
  FAQ FAQ  Forum Search   Register Register  Login Login

domainid in tblwl_autowhitelistforcedelivery

 Post Reply Post Reply
Author
lyndonje View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 January 2006
Location: United Kingdom
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote lyndonje Quote  Post ReplyReply Direct Link To This Post Topic: domainid in tblwl_autowhitelistforcedelivery
    Posted: 04 December 2007 at 5:01pm
Just finding my way round SFE and have a query..... Just ran the following SQL statement:
 

select * from tblwl_autowhitelistforcedelivery where email like '%|%@abc.com'

All entries end in abc.com (recipient domain address), yet the domainid changes (in batches, not per line), yet I dont understand why? What am I missing here?

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

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 04 December 2007 at 5:26pm
lyndonje,

Do you have "something" that may be removing/re-adding domains in your tbl_LocalDomains table..? If so, deleting and then re-adding a domain causes the domain ID to change, and this would explain the symptoms.

If not, could you please change the query above to the following (sorting it by ID), and see if the domainID then also increases as you go down the list, or if it has random values?

select * from tblwl_autowhitelistforcedelivery where email like '%|%@abc.com' ORDER BY ID


Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
lyndonje View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 January 2006
Location: United Kingdom
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote lyndonje Quote  Post ReplyReply Direct Link To This Post Posted: 04 December 2007 at 5:41pm

Right... think I've worked it out....

Ordered by ID, the domainid numbers are not incremental but are in batches. If I add an and statement, ie and where domainid = 'one of the domainid numbers', there were x number of entries, and this number remained the same on changing the domainid. I then did a query on a specific emailfrom|emailto pair in the email column and found the same number of duplicates for every pair. What I have done tonight is set a few domains not to use the default config, and on checking, all domainid's mentioned correspond to domains not using default settings. Therefore every entry in the tblwl_autowhitelistforcedelivery is duplicated per domain that does not have default settings.
 
Was that the idea? To my thoughts, isn't that a bit unnessecary? As the domain the whitelist entry appertains to is following the 2nd @ sign anway??
Back to Top
lyndonje View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 January 2006
Location: United Kingdom
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote lyndonje Quote  Post ReplyReply Direct Link To This Post Posted: 04 December 2007 at 5:45pm
Just thinking on a little further here... what happens if a recipient from domainid 6 (that is not using the default config) whitelists an email, does that entry get input into the tblwl_autowhitelistforcedelivery once under domainid 6, or is it duplicated per custom domain id? If the first, what would happen if you later set domain 6 back to default config? I'm gussing the entries in tblwl_autowhitelistforcedelivery with domain 6 wouldnt just be deleted?
 
Assuming you already know of this I'm sure you'll be able to make all this clear :)
Back to Top
lyndonje View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 January 2006
Location: United Kingdom
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote lyndonje Quote  Post ReplyReply Direct Link To This Post Posted: 07 December 2007 at 12:07pm
Hi Roberto, Could you clarify please? Thanks.
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 08 December 2007 at 6:01pm
We were trying to figure out what could cause the duplicates, but couldn't... Could you post of email us an export of the tblwl_autowhitelistforcedelivery and the tbl_localdomains tables so we can take a look?
An entry to the autowhitelist table shold be added only once. If however a domain is set to be using custom filters, and then changed to "default" settings, the "old" entries are lost.
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
lyndonje View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 January 2006
Location: United Kingdom
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote lyndonje Quote  Post ReplyReply Direct Link To This Post Posted: 11 December 2007 at 5:18am
What is the best way to export these tables in SQL 2005?
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 11 December 2007 at 10:12pm
From the MSSQL Server Management Studio, click on "New Query". Select the SpamFilter database from the drop down containing the database names (or use the syntax below specifying full database name and owner name):

SELECT     *
FROM         tblwl_autowhitelistforcedelivery

or...

select * from [spamfilter].dbo.tblwl_autowhitelistforcedelivery

and click on the "Execute" button. If you don't have the "Execute" buton and/or the list of databases, chose "View - Toolbars - SQL Editor"

Once the results are shown, click on "Query - Results To - Results to Text (or o file).

Note that this only works if you use a query window, it doesn't work in the window where yo can view the table data. Go figure...
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: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 12 December 2007 at 11:03pm
Lyndon,

We received your files today, and saw exactly what you mean. As a matter of fact, while loading your data in our of our databases in our lab, we actually saw that our own test data already present on our testing server also had the same symptom you reported. We had duplicate entries ourselves for all domains that had custom settings.

The problem is that we're still not ale to replicate this now. All new emails that we force-deliver are added to the whitelist correctly, once. We tried using the same version we believe you're using (3.5.4.710 - please correct us if wrong), but that seems to work just fine as well.

Is this "duplication" still occurring to you right now, or do you believe the entries you see could have been created with a previous version of SpamFilter?

In the SpamFilter.ini file, there should be a line like the following:

DBPatchesApplied=3.5.3.690,3.5.3.696

can you please tr to stop SpamFilter, and delete everything to the right of the "=" sign above, so the line reads:
DBPatchesApplied=

and then restart SpamFilter? This forces SpamFilter to re-add patches to the database, in case they have not been applied correctly.
Can you check at this point if you still have the duplication?


Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.082 seconds.