Print Page | Close Window

issue with quarrantine database...

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=1600
Printed Date: 12 March 2025 at 8:03pm


Topic: issue with quarrantine database...
Posted By: Guests
Subject: issue with quarrantine database...
Date Posted: 04 August 2003 at 1:12pm

Hello -

I have found a small issue with the quarrantine database, and I don't know the resolution.  If a message that was blocked is supposed to be delivered to more than one recipient, SpamFilter ISP creates a quarrantine record for each, and one message record.  If any of the recipents flag their message for deletion (which flags the quar. record) and then the system processes the flagged records, the message gets deleted, possibly before the other recipients even get a chance to see the message, and they get a 'No message found for MsgID=xxxx' warning when they try to view the message.

Any suggestions?

-Ric

 


Replies:
Posted By: Desperado
Date Posted: 05 August 2003 at 12:53am

Ric,

What version of SpamFilter and type DB are you using?  I am not able to duplicate your error.  I dant a message to 2 of my accounts by putting both addresses in the "To" field.  I made sure it had content that would get blocked.  I the went to the quarantine for one of the addresses and deleted the message.  I the waited until the message actually deleted and went to the other account.  Not only was it there, but I was able to view it and the send it and it showed up in my other account.

Dan S.

Posted By: Guests
Date Posted: 05 August 2003 at 12:40pm

Dan -

Currently running v1.2.0.190 on MySQL 4.0.14...

-Ric

Posted By: LogSat
Date Posted: 05 August 2003 at 10:56pm

Ric,

You are correct, we were able to duplicate the issue with MySQL. It looks like the problem is caused by a missing foreign key in the MySQL database between the MsgID field in the tblMsgs and the tblQuarantine tables. The MS SQL server database has it, MySQL does not. We'll try to have an updated MySQL script in the next build.

Roberto F.
LogSat Software

Posted By: Guests
Date Posted: 17 September 2003 at 12:14pm
I haven't followed up on this is awhile - but I'm still experiencing this problem.  Is this an issue I can correct by adding a key in the MySQL tables, or is it a problem within the program code itself?

Posted By: deano92964
Date Posted: 17 July 2005 at 3:05am

This is strange. I know this topic is allmost 2 years old, but i found it when i was searching the forum for the problem i am having.

The strange thing is i am running 2.5.1.441 using MySQL version 4.1 and having the exact same problem. I would have thought seems how this problem has been reported before, that it would have been fixed by now.

When a person marks a message to be deleted that was sent to more than one recipient, when spamfilter expires the messages, it removes the msgid from tblmsgs so the other recipients will get a No message found error for that ID when they try to view it. Same thing occures when Spamfilter purges messages beyond the number of days set to leave messages in the quarantine.

It doesn't appear that this problem has been fixed. Money is very tight for me right now. I am having enough problems trying to come up with the cash to pay for this software. I certainaly don't want to have to switch to MS SQL.

Dean

 

 

Posted By: LogSat
Date Posted: 17 July 2005 at 6:34pm
Dean,

The update in the MySQL script was added a long time ago.

From the solution in the previous postings:
========================================
It looks like the problem is caused by a missing foreign key in the MySQL database between the MsgID field in the tblMsgs and the tblQuarantine tables.
========================================

Can you please verify that in your database the foreign key is actually missing? If it is indeed missing, can you try to manually add it by running the following SQL query:

ALTER TABLE `tblQuarantine` ADD
    CONSTRAINT `FK_tblQuarantine_tblMsgs` FOREIGN KEY
    (
        `MsgID`
    ) REFERENCES `tblMsgs` (
        `MsgID`
    ) ON DELETE CASCADE;



-------------
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: deano92964
Date Posted: 18 July 2005 at 2:24am

The foreign key was indeed missing. Manual addition of it was sucessful. I have not yet tested it. I will through out the day today to see if this has taken care of the problem.

 

Thanks.

Posted By: deano92964
Date Posted: 18 July 2005 at 2:46am

I just ran a quick test to see if the install of my copy just goofed and that key didn't get added. I installed it on another machine and had it create the tables in another database server i am running, and again, it did not add that foreign key that is missing.

When i get a chance i am going to see if the default database engine used for the tables makes any difference in how the keys are created by spamfilter. MySQL 4.1 defaults to MyISAM databases. The think it used to be just ISAM which is no longer used.

Perhaps it makes a difference what database engine is used.

Anyhow. I let you know if i find anything out.

Dean

Posted By: deano92964
Date Posted: 18 July 2005 at 1:54pm

This is what I found out.

 

SpamFilter does appear to create the tables forcing the use of the MyISAM engine.

 

However, in the Windows version of MySQL version 4.1 the MyISAM engine will not allow that foreign key to be created. It does not return an error, it simply ignores the query to create it. It cannot be added manually either. Again, no error, the query just seems to be ignored.

 

However, if you change the tables to use the InnoDB engine the foreign key can be created by manually executing the query.

 

Is there a specific reason for using the MyISAM engine in SpamFilter?

 

So far I have not had any problem using the InnoDB engine, and the addition of the foreign key seems to have fixed the missing MsgID problem. So far things look good.

 

You may have to change the scripts to create the tables using InnoDB instead.

 

This may be something recent with the 4.1 version, or perhaps it only affects the Windows version. I do not know this. I do not have this new of a version on any of my Linux boxes. But I do know that this foreign key will not insert with this version of MySQL while using the MyISAM engine.

 

Dean

 

 


Print Page | Close Window