Store and lookup SpamAssassin bayes in mySQL

This section contains user-submitted tutorials.
Post Reply
User avatar
katip
Senior user
Senior user
Posts: 671
Joined: 2006-12-22 07:58
Location: Istanbul

Store and lookup SpamAssassin bayes in mySQL

Post by katip » 2017-05-01 19:09

i hope and suppose, using a database will speed up bayes checks and trainings in SA.

First of all we create a DB for SA operations if we don't have any yet (otherwise we can use the existing one) and following tables in it (and don't overlook that INSERT after creating bayes_global_vars table):

Code: Select all

CREATE TABLE bayes_expire (
  id int(11) NOT NULL default '0',
  runtime int(11) NOT NULL default '0',
  KEY bayes_expire_idx1 (id)
) ENGINE=InnoDB;

CREATE TABLE bayes_global_vars (
  variable varchar(30) NOT NULL default '',
  value varchar(200) NOT NULL default '',
  PRIMARY KEY  (variable)
) ENGINE=InnoDB;

INSERT INTO bayes_global_vars VALUES ('VERSION','3');

CREATE TABLE bayes_seen (
  id int(11) NOT NULL default '0',
  msgid varchar(200) binary NOT NULL default '',
  flag char(1) NOT NULL default '',
  PRIMARY KEY  (id,msgid)
) ENGINE=InnoDB;

CREATE TABLE bayes_token (
  id int(11) NOT NULL default '0',
  token binary(5) NOT NULL default '',
  spam_count int(11) NOT NULL default '0',
  ham_count int(11) NOT NULL default '0',
  atime int(11) NOT NULL default '0',
  PRIMARY KEY  (id, token),
  INDEX bayes_token_idx1 (id, atime)
) ENGINE=InnoDB;

CREATE TABLE bayes_vars (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(200) NOT NULL default '',
  spam_count int(11) NOT NULL default '0',
  ham_count int(11) NOT NULL default '0',
  token_count int(11) NOT NULL default '0',
  last_expire int(11) NOT NULL default '0',
  last_atime_delta int(11) NOT NULL default '0',
  last_expire_reduce int(11) NOT NULL default '0',
  oldest_token_age int(11) NOT NULL default '2147483647',
  newest_token_age int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE bayes_vars_idx1 (username)
) ENGINE=InnoDB;
bayes_vars table needs 1 (and only 1) user. just insert it with id "1" and username "unknown" (without quotes).

that's all with mySQL.

---------------------------------------------------------------

in etc/spamassassin we write a new .cf (mySQL_bayes.cf for instance) with following content:

Code: Select all

# mySQL bayes

ifplugin Mail::SpamAssassin::Plugin::Bayes

bayes_store_module Mail::SpamAssassin::BayesStore::MySQL

bayes_sql_dsn DBI:mysql:<db_name>:127.0.0.1:3306
bayes_sql_username <mySQL_user_name>
bayes_sql_password <mySQL_password>
bayes_sql_override_username unknown

endif # Mail::SpamAssassin::Plugin::Bayes
that's all with cf.

---------------------------------------------------------------

before proceeding, we make a backup of old flat file database:

Code: Select all

sa-learn.exe --siteconfigpath="<_path_to_your_etc\spamassassin>" --dbpath "<_path_in_your_OS>\.spamassassin>" --backup > "<_path_in_your_OS>\.spamassassin>\backup\bayes_backup.bak"
now it's time to restart SA service to see the spamd.log for any errors before continuing.
(it's a good idea to pause HMS at that stage if it's a spam-busy server. bayes won't work reliable or at all till next step is finished)
if all looks ok, we're almost done.

as last step, we import old flat file database to mySQL by --restore:

Code: Select all

sa-learn.exe --restore="<_path_in_your_OS>\.spamassassin\backup\bayes_backup.bak" -D
(-D is useful to see if there is any error. otherwise not needed)
note that this may take some time (in my box 2-3 minutes for ~ 180.000 tokens from ~ 5MB file) and command line seems hanging. no worry.

that's all. resume HMS if paused, see if all is running as expected.

training is as usual. you don't need to put any --dbpath or --backup or --sync in your sa-learn batchfile commands anymore. just --spam and --ham are fine.

you may want to backup and delete below files in <_path_in_your_OS>\.spamassassin directory. they're retired ;) :
bayes_journal
bayes_toks
bayes_seen

if you fail to make it work or something goes wrong or you don't see any benefit in using a DB for bayes, reverting to old config is very easy.
just delete mySQL_bayes.cf, restore above flat files (if deleted), restart spamd.
Katip
--
HMS 5.7.0-B2428-LTS-64-bit, MySQL 5.7.24, SA 3.4.2, ClamAV 0.101.2 + SaneS

keysteal
Normal user
Normal user
Posts: 91
Joined: 2011-03-25 00:18

Re: Store and lookup SpamAssassin bayes in mySQL

Post by keysteal » 2018-02-18 17:38

Hi,
I tried your procedure. But if I use sa-learn.exe, it appears an error message like this:
"The program can't start because libmysql__.dll is missing from your computer. Try reinstalling
the program to fix this problem"
I find the .dll inside perl folder, and I tried to copy everywhere, but nothing has changed.
Anyone can help me?

User avatar
mattg
Moderator
Moderator
Posts: 19999
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Store and lookup SpamAssassin bayes in mySQL

Post by mattg » 2018-02-19 00:01

Put it in the hmailserver/bin folder
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
jimimaseye
Moderator
Moderator
Posts: 8073
Joined: 2011-09-08 17:48

Re: Store and lookup SpamAssassin bayes in mySQL

Post by jimimaseye » 2018-02-19 00:36

mattg wrote:Put it in the hmailserver/bin folder
I thought about this and that was my first instinct. But then i thought that this was an issue of spamassassin and mysql - went would the dll in an unrelated softwares program directory help?
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
mattg
Moderator
Moderator
Posts: 19999
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Store and lookup SpamAssassin bayes in mySQL

Post by mattg » 2018-02-19 00:55

My 'guess' was that most people who have followed this tutorial, would have had MySQL as the hMailserver database.

Actually reading the original post though, I agree with you. That won't help.

It needs to be in the same directory as the sa-learn.exe
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
jimimaseye
Moderator
Moderator
Posts: 8073
Joined: 2011-09-08 17:48

Re: Store and lookup SpamAssassin bayes in mySQL

Post by jimimaseye » 2018-02-19 01:18

I hope katip can explain to clear it up.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
katip
Senior user
Senior user
Posts: 671
Joined: 2006-12-22 07:58
Location: Istanbul

Re: Store and lookup SpamAssassin bayes in mySQL

Post by katip » 2018-02-19 05:24

indeed, it's "libmysql__.dll" (with double underscore), not "libmysql.dll"
i don't remember what i did then. i was using Strawberry Perl and it actually has a "libmysql__.dll". i see now that i copied it to SA folder on 27.04.2017, my post was on 01.05.2017. so, very likely i have had the same problem but forgot to mention about it & Strawberry.

my guess:
download Strawberry Perl from http://strawberryperl.com/releases.html (zip or portable one)
find libmysql__.dll file from \c\bin folder and extract it to SA folder where sa-learn.exe resides and see if it helps.

pls post back the outcome so i can edit my post. thanks.
Katip
--
HMS 5.7.0-B2428-LTS-64-bit, MySQL 5.7.24, SA 3.4.2, ClamAV 0.101.2 + SaneS

User avatar
jimimaseye
Moderator
Moderator
Posts: 8073
Joined: 2011-09-08 17:48

Re: Store and lookup SpamAssassin bayes in mySQL

Post by jimimaseye » 2018-02-19 09:51

katip wrote:my guess:
download Strawberry Perl from http://strawberryperl.com/releases.html (zip or portable one)
find libmysql__.dll file from \c\bin folder and extract it to SA folder where sa-learn.exe resides and see if it helps.
Yep, that seems to match the consensus of opinion: https://www.google.co.uk/search?q=perl+libmysql__.dll and something Tunis has done in the past viewtopic.php?p=195484#p195484
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

keysteal
Normal user
Normal user
Posts: 91
Joined: 2011-03-25 00:18

Re: Store and lookup SpamAssassin bayes in mySQL

Post by keysteal » 2018-02-19 13:33

Hi guys,

i've already done a copy of dll into the SA folder, and nothing has changed. My dll is
2,359,296 bytes

User avatar
jimimaseye
Moderator
Moderator
Posts: 8073
Joined: 2011-09-08 17:48

Re: Store and lookup SpamAssassin bayes in mySQL

Post by jimimaseye » 2018-02-19 13:53

keysteal wrote:Hi guys,

i've already done a copy of dll into the SA folder, and nothing has changed. My dll is
2,359,296 bytes
Which DLL? Where did you obtain it from?
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

keysteal
Normal user
Normal user
Posts: 91
Joined: 2011-03-25 00:18

Re: Store and lookup SpamAssassin bayes in mySQL

Post by keysteal » 2018-02-19 14:01

I copied libmysql__.dll from a perl folder (Strawberry).

User avatar
jimimaseye
Moderator
Moderator
Posts: 8073
Joined: 2011-09-08 17:48

Re: Store and lookup SpamAssassin bayes in mySQL

Post by jimimaseye » 2018-02-19 14:04

did you restart spamassassin service?
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

keysteal
Normal user
Normal user
Posts: 91
Joined: 2011-03-25 00:18

Re: Store and lookup SpamAssassin bayes in mySQL

Post by keysteal » 2018-02-19 14:07

yesss...

User avatar
katip
Senior user
Senior user
Posts: 671
Joined: 2006-12-22 07:58
Location: Istanbul

Re: Store and lookup SpamAssassin bayes in mySQL

Post by katip » 2018-02-19 15:10

keysteal wrote:Hi guys,

i've already done a copy of dll into the SA folder, and nothing has changed. My dll is
2,359,296 bytes
nope, it should be something about 5 MB!! are you sure we're talking about that one with double underscore??
Katip
--
HMS 5.7.0-B2428-LTS-64-bit, MySQL 5.7.24, SA 3.4.2, ClamAV 0.101.2 + SaneS

keysteal
Normal user
Normal user
Posts: 91
Joined: 2011-03-25 00:18

Re: Store and lookup SpamAssassin bayes in mySQL

Post by keysteal » 2018-02-19 15:13

yes ... with double underscore.

User avatar
katip
Senior user
Senior user
Posts: 671
Joined: 2006-12-22 07:58
Location: Istanbul

Re: Store and lookup SpamAssassin bayes in mySQL

Post by katip » 2018-02-19 15:17

mine is 5.297.152 bytes dated 28.09.2016 v. 5.7.16.0
check original one in Strawberry once again. maybe it got corrupted when extracting/copying.
Katip
--
HMS 5.7.0-B2428-LTS-64-bit, MySQL 5.7.24, SA 3.4.2, ClamAV 0.101.2 + SaneS

keysteal
Normal user
Normal user
Posts: 91
Joined: 2011-03-25 00:18

Re: Store and lookup SpamAssassin bayes in mySQL

Post by keysteal » 2018-02-19 15:26

I 've seen that hasn't double score, only one...but anyway, i 've tried to rename it with double score, and didn't solve it.
I 've tried to extract the libmysql__.dll from a new strawberry perl installation and use it under SA, and nothing changes.

User avatar
katip
Senior user
Senior user
Posts: 671
Joined: 2006-12-22 07:58
Location: Istanbul

Re: Store and lookup SpamAssassin bayes in mySQL

Post by katip » 2018-02-19 15:43

what is it's size now?
i've attached mine which was working fine then. use at your own risk.
Attachments
libmysql__.zip
(1.35 MiB) Downloaded 60 times
Katip
--
HMS 5.7.0-B2428-LTS-64-bit, MySQL 5.7.24, SA 3.4.2, ClamAV 0.101.2 + SaneS

keysteal
Normal user
Normal user
Posts: 91
Joined: 2011-03-25 00:18

Re: Store and lookup SpamAssassin bayes in mySQL

Post by keysteal » 2018-02-19 16:12

This is the output of sa-learn with -D option, if it helps:

feb 19 15:09:56.812 [20864] dbg: logger: adding facilities: all
feb 19 15:09:56.813 [20864] dbg: logger: logging level is DBG
feb 19 15:09:56.813 [20864] dbg: generic: SpamAssassin version 3.4.1
feb 19 15:09:56.813 [20864] dbg: generic: Perl 5.022001, PREFIX=E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\runtime, DEF_RULES_DIR=E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\share\spamassassin, LOCAL_RULES_DIR=E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin, LOCAL_STATE_DIR=..\share
feb 19 15:09:56.813 [20864] dbg: config: timing enabled
feb 19 15:09:56.815 [20864] dbg: config: score set 0 chosen.
feb 19 15:09:56.822 [20864] dbg: util: running in taint mode? no
feb 19 15:09:56.822 [20864] dbg: util: defining getpwuid() wrapper using 'unknown' as username
feb 19 15:09:56.824 [20864] dbg: config: using "E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin" for site rules pre files
feb 19 15:09:56.825 [20864] dbg: config: read file E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin/init.pre
feb 19 15:09:56.825 [20864] dbg: config: read file E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin/v310.pre
feb 19 15:09:56.826 [20864] dbg: config: read file E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin/v312.pre
feb 19 15:09:56.826 [20864] dbg: config: read file E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin/v320.pre
feb 19 15:09:56.826 [20864] dbg: config: read file E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin/v330.pre
feb 19 15:09:56.826 [20864] dbg: config: read file E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin/v340.pre
feb 19 15:09:56.826 [20864] dbg: config: read file E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin/v341.pre
feb 19 15:09:56.827 [20864] dbg: config: using "E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\share\spamassassin" for sys rules pre files
feb 19 15:09:56.827 [20864] dbg: config: using "E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\share\spamassassin" for default rules dir
feb 19 15:09:56.827 [20864] dbg: config: using "E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin" for site rules dir
feb 19 15:09:56.828 [20864] dbg: config: read file E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin/contact.cf
feb 19 15:09:56.828 [20864] dbg: config: read file E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin/local.cf
feb 19 15:09:56.828 [20864] dbg: config: read file E:\Program Files (x86)\JAM Software\SpamAssassin for Windows\etc\spamassassin/mysql_bayes.cf
feb 19 15:09:56.829 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::URIDNSBL from @INC
feb 19 15:09:56.834 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::Hashcash from @INC
feb 19 15:09:56.841 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::SPF from @INC
feb 19 15:09:56.845 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::Pyzor from @INC
feb 19 15:09:56.848 [20864] dbg: pyzor: network tests on, attempting Pyzor
feb 19 15:09:56.848 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::Razor2 from @INC
feb 19 15:09:56.919 [20864] dbg: razor2: razor2 is available, version 2.84
feb 19 15:09:56.920 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::SpamCop from @INC
feb 19 15:09:59.823 [20864] dbg: reporter: network tests on, attempting SpamCop
feb 19 15:09:59.823 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::AutoLearnThreshold from @INC
feb 19 15:09:59.825 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::WhiteListSubject from @INC
feb 19 15:09:59.826 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::MIMEHeader from @INC
feb 19 15:09:59.828 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::ReplaceTags from @INC
feb 19 15:09:59.830 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::DKIM from @INC
feb 19 15:09:59.835 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::Check from @INC
feb 19 15:09:59.843 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::HTTPSMismatch from @INC
feb 19 15:09:59.844 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::URIDetail from @INC
feb 19 15:09:59.846 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::Shortcircuit from @INC
feb 19 15:09:59.847 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::Bayes from @INC
feb 19 15:09:59.859 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::BodyEval from @INC
feb 19 15:09:59.861 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::DNSEval from @INC
feb 19 15:09:59.864 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::HTMLEval from @INC
feb 19 15:09:59.867 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::HeaderEval from @INC
feb 19 15:09:59.874 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::MIMEEval from @INC
feb 19 15:09:59.878 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::RelayEval from @INC
feb 19 15:09:59.881 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::URIEval from @INC
feb 19 15:09:59.882 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::WLBLEval from @INC
feb 19 15:09:59.886 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::VBounce from @INC
feb 19 15:09:59.887 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::ImageInfo from @INC
feb 19 15:09:59.890 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::FreeMail from @INC
feb 19 15:09:59.895 [20864] dbg: plugin: loading Mail::SpamAssassin::Plugin::AskDNS from @INC
feb 19 15:09:59.900 [20864] dbg: config: finish parsing
feb 19 15:09:59.900 [20864] dbg: plugin: Mail::SpamAssassin::Plugin::ReplaceTags=HASH(0x59084c0) implements 'finish_parsing_end', priority 0
feb 19 15:09:59.900 [20864] dbg: plugin: Mail::SpamAssassin::Plugin::FreeMail=HASH(0x4cda9f0) implements 'finish_parsing_end', priority 0
feb 19 15:09:59.900 [20864] dbg: replacetags: replacing tags
feb 19 15:09:59.900 [20864] dbg: replacetags: done replacing tags
feb 19 15:09:59.900 [20864] dbg: FreeMail: no freemail_domains entries defined, disabling plugin
feb 19 15:09:59.900 [20864] dbg: plugin: Mail::SpamAssassin::Plugin::Bayes=HASH(0x592b7e0) implements 'learner_new', priority 0
feb 19 15:09:59.900 [20864] dbg: bayes: learner_new self=Mail::SpamAssassin::Plugin::Bayes=HASH(0x592b7e0), bayes_store_module=Mail::SpamAssassin::BayesStore::MySQL
feb 19 15:09:59.938 [20864] dbg: bayes: using username: unknown
feb 19 15:09:59.938 [20864] dbg: bayes: learner_new: got store=Mail::SpamAssassin::BayesStore::MySQL=HASH(0x4cdaa38)
feb 19 15:09:59.938 [20864] dbg: plugin: Mail::SpamAssassin::Plugin::Bayes=HASH(0x592b7e0) implements 'learner_is_scan_available', priority 0
plugin: eval failed: install_driver(mysql) failed: Can't load 'runtime\lib/auto/DBD/mysql/mysql.xs.dll' for module DBD::mysql: load_file:Impossible to find the procedure specified at DynaLoader.pm line 193.

User avatar
katip
Senior user
Senior user
Posts: 671
Joined: 2006-12-22 07:58
Location: Istanbul

Re: Store and lookup SpamAssassin bayes in mySQL

Post by katip » 2018-02-19 16:52

find "mysql.xs.dll" in \strawberry\perl\site\lib\auto\DBD\mysql\
copy it to <Spamassassin folder>\runtime\lib\auto\DBD\mysql\
Katip
--
HMS 5.7.0-B2428-LTS-64-bit, MySQL 5.7.24, SA 3.4.2, ClamAV 0.101.2 + SaneS

keysteal
Normal user
Normal user
Posts: 91
Joined: 2011-03-25 00:18

Re: Store and lookup SpamAssassin bayes in mySQL

Post by keysteal » 2018-02-19 18:37

I already have a mysql.xs.dll, and I've tried to substitute it with a newer version, but it doesn't work.
An error says "this is not a valid Win32 application" (!!!)
I thought, it was most simple to use Mysql with Spamassassin :(

User avatar
jimimaseye
Moderator
Moderator
Posts: 8073
Joined: 2011-09-08 17:48

Re: Store and lookup SpamAssassin bayes in mySQL

Post by jimimaseye » 2018-02-19 18:39

keysteal wrote: I thought, it was most simple to use Mysql with Spamassassin :(
Out of interest, why exactly are you trying to do this? How do you intend to benefit from it (instead oif using the bayes database that comes as standard)?
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

keysteal
Normal user
Normal user
Posts: 91
Joined: 2011-03-25 00:18

Re: Store and lookup SpamAssassin bayes in mySQL

Post by keysteal » 2018-02-19 18:50

Someone wrote that has more performances, but overall if I can use it with Mysql, why can't I do it?
P.s.: I am already using the builtin database

User avatar
SorenR
Senior user
Senior user
Posts: 3169
Joined: 2006-08-21 15:38
Location: Denmark

Re: Store and lookup SpamAssassin bayes in mySQL

Post by SorenR » 2018-02-19 18:56

keysteal wrote:I already have a mysql.xs.dll, and I've tried to substitute it with a newer version, but it doesn't work.
An error says "this is not a valid Win32 application" (!!!)
I thought, it was most simple to use Mysql with Spamassassin :(
It works for me using ActiveState Perl... SA version 3.4.0, Bayes on MySQL since May 1. 2017... Windows 2K3 R2 SE.

viewtopic.php?p=195564#p195564

Ahem... I can't find mysql.xs.dll on my system (?)

One thing I noticed... My path.config file says:

DEF_RULES_DIR=./share/spamassassin
LOCAL_RULES_DIR=./etc/spamassassin
LOCAL_STATE_DIR=./share

sa-learn DEBUG says:
maj 1 17:09:22.318 [3432] dbg: generic: Perl 5.008009, PREFIX=., DEF_RULES_DIR=.\share\spamassassin, LOCAL_RULES_DIR=.\etc\spamassassin, LOCAL_STATE_DIR=C:\Perl\site/var/spamassassin

Seems like a previously hardcoded path was changed...
SørenR.

The quantum rule of insecurity which states that the act of observing how vulnerable a host or service is changes the insecurity level of the service.

User avatar
katip
Senior user
Senior user
Posts: 671
Joined: 2006-12-22 07:58
Location: Istanbul

Re: Store and lookup SpamAssassin bayes in mySQL

Post by katip » 2018-02-19 20:56

sorry, as OP i have no other clue.
i had 2 installations, one with Strawberry Perl and another one with ActiveState Perl. both were running fine.
after changing to Linux version i still keep them (disabled). i remember i had to copy some mysql related files here and there. one for sure: libmysql__.dll to SA folder in both setups.

BTW, it wasn't worth, there was no noticeable benefit. opening a database for 5-10MB text is anyway useless/excessive i think, unless you want some statistics. once SorenR posted an interesting thing about word stats.
Katip
--
HMS 5.7.0-B2428-LTS-64-bit, MySQL 5.7.24, SA 3.4.2, ClamAV 0.101.2 + SaneS

User avatar
SorenR
Senior user
Senior user
Posts: 3169
Joined: 2006-08-21 15:38
Location: Denmark

Re: Store and lookup SpamAssassin bayes in mySQL

Post by SorenR » 2018-02-19 23:07

katip wrote:find "mysql.xs.dll" in \strawberry\perl\site\lib\auto\DBD\mysql\
copy it to <Spamassassin folder>\runtime\lib\auto\DBD\mysql\
Perhaps this or it could trigger an idea ???
https://bugzilla.mozilla.org/show_bug.cgi?id=987742
SørenR.

The quantum rule of insecurity which states that the act of observing how vulnerable a host or service is changes the insecurity level of the service.

keysteal
Normal user
Normal user
Posts: 91
Joined: 2011-03-25 00:18

Re: Store and lookup SpamAssassin bayes in mySQL

Post by keysteal » 2018-02-20 00:51

Now I've this error, after following your link (copy of libmysql__.dll):
SA-LEARN.EXE - Entry Point Not Found
The procedure entry point mysql_get_parameters cound not be located in the dynamic link library libmysql__.dll
I've 2 choices now:
1) leave the configuration as it is with bayes spamassassin builtin database;
2) uninstall Strawberry Perl with PADRE, and try to install ActivePerl;
find "mysql.xs.dll" in \strawberry\perl\site\lib\auto\DBD\mysql\
copy it to <Spamassassin folder>\runtime\lib\auto\DBD\mysql\
I don't find mysql.xs.dll in that path, probably the Jam Software version
of Spamassassin previously installed it.

Post Reply