Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - mySQL Not So Smooth
  FAQ FAQ  Forum Search   Register Register  Login Login

mySQL Not So Smooth

 Post Reply Post Reply
Author
caratking View Drop Down
Groupie
Groupie


Joined: 13 March 2006
Location: United States
Status: Offline
Points: 79
Post Options Post Options   Thanks (0) Thanks(0)   Quote caratking Quote  Post ReplyReply Direct Link To This Post Topic: mySQL Not So Smooth
    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?

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

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4104
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post 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

LogSat Software

Spam Filter ISP
Back to Top
caratking View Drop Down
Groupie
Groupie


Joined: 13 March 2006
Location: United States
Status: Offline
Points: 79
Post Options Post Options   Thanks (0) Thanks(0)   Quote caratking Quote  Post ReplyReply Direct Link To This Post 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.


Back to Top
caratking View Drop Down
Groupie
Groupie


Joined: 13 March 2006
Location: United States
Status: Offline
Points: 79
Post Options Post Options   Thanks (0) Thanks(0)   Quote caratking Quote  Post ReplyReply Direct Link To This Post 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.



Edited by caratking
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4104
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post 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

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: 4104
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post 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

LogSat Software

Spam Filter ISP
Back to Top
caratking View Drop Down
Groupie
Groupie


Joined: 13 March 2006
Location: United States
Status: Offline
Points: 79
Post Options Post Options   Thanks (0) Thanks(0)   Quote caratking Quote  Post ReplyReply Direct Link To This Post Posted: 25 July 2006 at 6:03pm
Thanks, things seem to be running much more smooth with mySQL on Linux now.
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.139 seconds.