mySQL Not So Smooth |
Post Reply ![]() |
Author | |
caratking ![]() Groupie ![]() Joined: 13 March 2006 Location: United States Status: Offline Points: 79 |
![]() ![]() ![]() ![]() ![]() 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` ( CREATE TABLE `tblquarantine` ( 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? |
|
![]() |
|
LogSat ![]() Admin Group ![]() ![]() Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
![]() ![]() ![]() ![]() ![]() |
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? |
|
![]() |
|
caratking ![]() Groupie ![]() Joined: 13 March 2006 Location: United States Status: Offline Points: 79 |
![]() ![]() ![]() ![]() ![]() |
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. |
|
![]() |
|
caratking ![]() Groupie ![]() Joined: 13 March 2006 Location: United States Status: Offline Points: 79 |
![]() ![]() ![]() ![]() ![]() |
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 |
|
![]() |
|
LogSat ![]() Admin Group ![]() ![]() Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
![]() ![]() ![]() ![]() ![]() |
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. |
|
![]() |
|
LogSat ![]() Admin Group ![]() ![]() Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
![]() ![]() ![]() ![]() ![]() |
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; ======================= |
|
![]() |
|
caratking ![]() Groupie ![]() Joined: 13 March 2006 Location: United States Status: Offline Points: 79 |
![]() ![]() ![]() ![]() ![]() |
Thanks, things seem to be running much more smooth with mySQL on Linux now.
|
|
![]() |
Post Reply ![]() |
|
Tweet
|
Forum Jump | Forum Permissions ![]() You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |
This page was generated in 0.139 seconds.