Spam Filter ISP Support - mySQL Not So Smooth

Print Page | Close Window

mySQL Not So Smooth

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=5726
Printed Date: 27 April 2025 at 2:34am

Topic: mySQL Not So Smooth

Posted By: caratking
Subject: mySQL Not So Smooth
Date Posted: 23 July 2006 at 3:26am

I've been logging the quarantine db to MSSql, and thought I would try mySQL.

The process is not so smooth, because the commands that create the databases are in all lower case. For example:

CREATE TABLE `tblmsgs` (
`MsgID` int(11) NOT NULL auto_increment,
`Msg` longtext,
PRIMARY KEY (`MsgID`)
) TYPE=MyISAM

CREATE TABLE `tblquarantine` (
`QuarID` int(11) NOT NULL auto_increment,
`EmailFrom` varchar(100) default NULL,
`EmailTo` varchar(100) default NULL,
`Subject` varchar(100) default NULL,
`MsgDate` datetime default NULL,
`MsgID` int(11) default NULL,
`RejectID` int(11) default NULL,
`RejectDetails` varchar(200) default '',
`Deliver` tinyint(1) default NULL,
`Expire` tinyint(1) default NULL,
`ServerID` int(11) null,
PRIMARY KEY (`QuarID`),
KEY `Subject` (`Subject`),
KEY `MsgID` (`MsgID`),
KEY `EmailFrom` (`EmailFrom`),
KEY `EmailTo` (`EmailTo`),
KEY `MsgDate` (`MsgDate`),
KEY `ServerID` (`ServerID`)
) TYPE=MyISAM

But then when the system goes to check the databse is setup correctly it is looking for:

tblMsgs - not found

I changed the case of the table names to what it is looking for, but which is right? The system is not successful when trying to log into the mySQL tables, but I don't know if the problem is the table name or field names as they are using mixed case as well.

Anyone know for sure how they should be setup?

Replies:

Posted By: LogSat
Date Posted: 23 July 2006 at 9:45pm

caratking,

Is your MySQL running on a Windows server or on a Linux/Unix server? Can you please also let us know what versino of MySQL you're running?

-------------
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: caratking
Date Posted: 24 July 2006 at 2:36am

The mySQL server is running on Linux. It is version 4.x - not sure the exact version. I am 100% certain at least part of the problem is related to case of the table names.

I can see errors in the activity log stuff like this:

Error occurred during TAddToQuarantineThread when accessing ... spamfilter.tblrejectcodes doesn't exist

I guess the way around it for now would be to create tables with both the lower case and camel case to satisfy the part that does the checking / creating of the tables and the actual running system.

Posted By: caratking
Date Posted: 24 July 2006 at 7:24am

I did some more testing, I created tables with both upper and lower case in the position different parts of the software were looking for them.

The result is, that messages are put in Quarantine (I can see them in the table 'tblQuarantine' but when I go and look via the quarantine via the software it shows nothing. I'm betting it is looking in the 'tblquarantine' table, which is empty.

Posted By: LogSat
Date Posted: 24 July 2006 at 7:35am

caratking,

I believe you did find a bug. We're still looking into this issue, but so far it does seem that, as table names are case-sensitive in the Linux version of MySQL (not the Windows version), SpamFilter is issuing come queries using the wrong case.

I'll keep ou updated on what we find later today.

-------------
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: 24 July 2006 at 10:27pm

caratking,

We did indeed find two problems. The script we provided for MySQL does indeed have the incorrect case for the table names. I'm including an updated, corrected version in this post.

Unfortunately we also found one single query in SpamFilter that uses the wrong case on the tblRejectCodes. Unfortunately because the case is wong in SpamFilter as well, most likely it won't suffice to correct the database creation script, as the code in SpamFilter needs to be patched as well.

We've uploaded the new patched version of SpamFilter, v3.1.3.594 in the registered user area with the updated query.

=======================

CREATE TABLE `tblMsgs` (
`MsgID` int(11) NOT NULL auto_increment,
`Msg` longtext,
PRIMARY KEY (`MsgID`)
) TYPE=MyISAM;

CREATE TABLE `tblQuarantine` (
`QuarID` int(11) NOT NULL auto_increment,
`EmailFrom` varchar(100) default NULL,
`EmailTo` varchar(100) default NULL,
`Subject` varchar(100) default NULL,
`MsgDate` datetime default NULL,
`MsgID` int(11) default NULL,
`RejectID` int(11) default NULL,
`RejectDetails` varchar(200) default '',
`Deliver` tinyint(1) default NULL,
`Expire` tinyint(1) default NULL,
`ServerID` int(11) null,
PRIMARY KEY (`QuarID`),
KEY `Subject` (`Subject`),
KEY `MsgID` (`MsgID`),
KEY `EmailFrom` (`EmailFrom`),
KEY `EmailTo` (`EmailTo`),
KEY `MsgDate` (`MsgDate`),
KEY `ServerID` (`ServerID`)
) TYPE=MyISAM;

CREATE TABLE `tblRejectCodes` (
`RejectID` int(11) NOT NULL default '0',
`RejectDesc` varchar(100) default NULL,
PRIMARY KEY (`RejectID`)
) TYPE=MyISAM;

CREATE TABLE `tblLogins` (
`LoginID` int(11) NOT NULL auto_increment,
`EMail` varchar(100) NOT NULL default '',
`Password` varchar(50) default '',
PRIMARY KEY (`LoginID`),
UNIQUE KEY `EMail` (`EMail`)
) TYPE=MyISAM;

CREATE TABLE `tblServers` (
`ServerID` int(11) NOT NULL auto_increment,
`ServerName` varchar(50),
PRIMARY KEY (`ServerID`),
KEY `ServerName` (`Servername`)
) TYPE=MyISAM;

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: caratking
Date Posted: 25 July 2006 at 6:03pm

Thanks, things seem to be running much more smooth with mySQL on Linux now.

Print Page | Close Window