Print Page | Close Window

domainid in tblwl_autowhitelistforcedelivery

Printed From: LogSat Software
Category: Spam Filter ISP
Forum Name: Spam Filter ISP Support
Forum Description: General support for Spam Filter ISP
URL: https://www.logsat.com/spamfilter/forums/forum_posts.asp?TID=6317
Printed Date: 12 March 2025 at 8:52pm


Topic: domainid in tblwl_autowhitelistforcedelivery
Posted By: lyndonje
Subject: domainid in tblwl_autowhitelistforcedelivery
Date 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 mailto:%|%@abc.com - '%|%@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?


Replies:
Posted By: LogSat
Date 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 mailto:%%7C%@abc.com - '%|%@abc.com' ORDER BY ID




-------------
Roberto Franceschetti

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP

Posted By: lyndonje
Date 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??

Posted By: lyndonje
Date 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 :)

Posted By: lyndonje
Date Posted: 07 December 2007 at 12:07pm
Hi Roberto, Could you clarify please? Thanks.

Posted By: LogSat
Date 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

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP

Posted By: lyndonje
Date Posted: 11 December 2007 at 5:18am
What is the best way to export these tables in SQL 2005?

Posted By: LogSat
Date 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

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP

Posted By: LogSat
Date 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

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP


Print Page | Close Window