You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@spamassassin.apache.org by Michael <mi...@michi.su> on 2014/08/20 16:07:20 UTC
Need help with setting up MySQL storage for SA
Hi,
I'm using Spamassassin in a virtual user environment. To store
preferences like settings, Bayes and AWL for each user I'm trying to
set up a MySQL storage.
I created the MySQL tables according the instructions from the files
awl_mysql.sql, bayes_mysql.sql, README.awl, README.bayes, README and
userpref_mysql that came with my Spamassassin 3.4 installation on
Ubuntu 14.04.
The connection to the database seem to be working.
For me the debug output looks like if Spamassassin would expect to be
already some data in the tables. Where shall I get this data from? Do
I have to manually create entries for each user? What am I missing?
When calling "spamc -u test@michi.su < testmail.txt" I'm getting the
following debug output (shortened):
Aug 20 08:14:46.563 [16682] dbg: config: Conf::SQL: executing SQL:
select preference, value from userpref where username =
'test@michi.su' or username = '@GLOBAL' order by username asc
Aug 20 08:14:46.563 [16682] dbg: config: retrieving prefs for
test@michi.su from SQL server
Aug 20 08:14:46.564 [16682] dbg: info: user has changed
Aug 20 08:14:46.564 [16682] dbg: bayes: learner_new
self=Mail::SpamAssassin::Plugin::Bayes=HASH(0x30fdce0),
bayes_store_module=Mail::SpamAssassin::BayesStore::MySQL
Aug 20 08:14:46.564 [16682] dbg: bayes: using username: test@michi.su
Aug 20 08:14:46.564 [16682] dbg: bayes: learner_new: got
store=Mail::SpamAssassin::BayesStore::MySQL=HASH(0x3d1a768)
Aug 20 08:14:46.565 [16682] dbg: bayes: database connection established
Aug 20 08:14:46.566 [16682] dbg: bayes: found bayes db version 3
Aug 20 08:14:46.566 [16682] dbg: bayes: unable to initialize database
for test@michi.su user, aborting!
The MySQL relevant options that I added are:
user_scores_dsn DBI:mysql:spamassassin:localhost
user_scores_sql_username spamassassin
user_scores_sql_password pass
bayes_store_module Mail::SpamAssassin::BayesStore::MySQL
bayes_sql_dsn DBI:mysql:spamassassin:localhost
bayes_sql_username spamassassin
bayes_sql_password pass
auto_whitelist_factory Mail::SpamAssassin::SQLBasedAddrList
user_awl_dsn DBI:mysql:spamassassin:localhost
user_awl_sql_username spamassassin
user_awl_sql_password pass
Re: Need help with setting up MySQL storage for SA
Posted by Matteo Dessalvi <mt...@yahoo.it>.
On 21.08.2014 09:20, Michael wrote:
>
> So that means, that actually I do not have to do any action on newly
> created users. Once they retrain their first message, the Bayes entries
> are getting created. Before that, Bayes is not used for that user. Is
> that correct?
>
Yes, I would say it correct. To start using the bayesian filter SA needs
at least to classify 200 spam and ham email otherwise the BAYES_* rules
will not trigger
> What about the autolearn functionality? Where are these infos getting
> stored? Is it also stored in the Bayes tables? What happens, if they are
> not yet "initialised"?
>
About the autolearning feature you can read about that here:
http://spamassassin.apache.org/full/3.4.x/doc/Mail_SpamAssassin_Plugin_AutoLearnThreshold.html
And about the info stored into the DB by SA you can take a look here:
http://svn.apache.org/repos/asf/spamassassin/tags/spamassassin_current_release_3.4.x/sql/README.bayes
Regards,
Matteo
>
> Quoting Matteo Dessalvi <mt...@yahoo.it>:
>
>> Hi.
>>
>> I did test a similar configuration a while ago and had the same problem.
>> If you take a look at this thread on the mailing list:
>>
>> http://spamassassin.1065346.n5.nabble.com/Bayes-vars-records-on-MySQL-not-created-automatically-td104615.html
>>
>>
>> you'll see it was a problem of running 'sa-learn --sync' as the user
>> who is running the test.
>>
>> Best regards,
Re: Need help with setting up MySQL storage for SA
Posted by Michael <mi...@michi.su>.
Thanks for pointing me into the right direction. That helped me solve
the issue.
In fact there was no issue. I just had to run "sa-learn -u <user>
--spam < miss-classified-message.txt".
This command added some content into the Bayes tables and the warning
in the debug output was gone.
So that means, that actually I do not have to do any action on newly
created users. Once they retrain their first message, the Bayes
entries are getting created. Before that, Bayes is not used for that
user. Is that correct?
What about the autolearn functionality? Where are these infos getting
stored? Is it also stored in the Bayes tables? What happens, if they
are not yet "initialised"?
Thanks,
Michael
Quoting Matteo Dessalvi <mt...@yahoo.it>:
> Hi.
>
> I did test a similar configuration a while ago and had the same problem.
> If you take a look at this thread on the mailing list:
>
> http://spamassassin.1065346.n5.nabble.com/Bayes-vars-records-on-MySQL-not-created-automatically-td104615.html
>
> you'll see it was a problem of running 'sa-learn --sync' as the user
> who is running the test.
>
> Best regards,
> Matteo
>
> On 20.08.2014 16:07, Michael wrote:
>> Hi,
>> I'm using Spamassassin in a virtual user environment. To store
>> preferences like settings, Bayes and AWL for each user I'm trying to set
>> up a MySQL storage.
>>
>> I created the MySQL tables according the instructions from the files
>> awl_mysql.sql, bayes_mysql.sql, README.awl, README.bayes, README and
>> userpref_mysql that came with my Spamassassin 3.4 installation on Ubuntu
>> 14.04.
>>
>> The connection to the database seem to be working.
>> For me the debug output looks like if Spamassassin would expect to be
>> already some data in the tables. Where shall I get this data from? Do I
>> have to manually create entries for each user? What am I missing?
>>
>>
>>
>> When calling "spamc -u test@michi.su < testmail.txt" I'm getting the
>> following debug output (shortened):
>>
>> Aug 20 08:14:46.563 [16682] dbg: config: Conf::SQL: executing SQL:
>> select preference, value from userpref where username = 'test@michi.su'
>> or username = '@GLOBAL' order by username asc
>> Aug 20 08:14:46.563 [16682] dbg: config: retrieving prefs for
>> test@michi.su from SQL server
>> Aug 20 08:14:46.564 [16682] dbg: info: user has changed
>> Aug 20 08:14:46.564 [16682] dbg: bayes: learner_new
>> self=Mail::SpamAssassin::Plugin::Bayes=HASH(0x30fdce0),
>> bayes_store_module=Mail::SpamAssassin::BayesStore::MySQL
>> Aug 20 08:14:46.564 [16682] dbg: bayes: using username: test@michi.su
>> Aug 20 08:14:46.564 [16682] dbg: bayes: learner_new: got
>> store=Mail::SpamAssassin::BayesStore::MySQL=HASH(0x3d1a768)
>> Aug 20 08:14:46.565 [16682] dbg: bayes: database connection established
>> Aug 20 08:14:46.566 [16682] dbg: bayes: found bayes db version 3
>> Aug 20 08:14:46.566 [16682] dbg: bayes: unable to initialize database
>> for test@michi.su user, aborting!
>>
>>
>>
>> The MySQL relevant options that I added are:
>> user_scores_dsn DBI:mysql:spamassassin:localhost
>> user_scores_sql_username spamassassin
>> user_scores_sql_password pass
>>
>> bayes_store_module Mail::SpamAssassin::BayesStore::MySQL
>> bayes_sql_dsn DBI:mysql:spamassassin:localhost
>> bayes_sql_username spamassassin
>> bayes_sql_password pass
>>
>> auto_whitelist_factory Mail::SpamAssassin::SQLBasedAddrList
>> user_awl_dsn DBI:mysql:spamassassin:localhost
>> user_awl_sql_username spamassassin
>> user_awl_sql_password pass
>>
Re: Need help with setting up MySQL storage for SA
Posted by Matteo Dessalvi <mt...@yahoo.it>.
Hi.
I did test a similar configuration a while ago and had the same problem.
If you take a look at this thread on the mailing list:
http://spamassassin.1065346.n5.nabble.com/Bayes-vars-records-on-MySQL-not-created-automatically-td104615.html
you'll see it was a problem of running 'sa-learn --sync' as the user
who is running the test.
Best regards,
Matteo
On 20.08.2014 16:07, Michael wrote:
> Hi,
> I'm using Spamassassin in a virtual user environment. To store
> preferences like settings, Bayes and AWL for each user I'm trying to set
> up a MySQL storage.
>
> I created the MySQL tables according the instructions from the files
> awl_mysql.sql, bayes_mysql.sql, README.awl, README.bayes, README and
> userpref_mysql that came with my Spamassassin 3.4 installation on Ubuntu
> 14.04.
>
> The connection to the database seem to be working.
> For me the debug output looks like if Spamassassin would expect to be
> already some data in the tables. Where shall I get this data from? Do I
> have to manually create entries for each user? What am I missing?
>
>
>
> When calling "spamc -u test@michi.su < testmail.txt" I'm getting the
> following debug output (shortened):
>
> Aug 20 08:14:46.563 [16682] dbg: config: Conf::SQL: executing SQL:
> select preference, value from userpref where username = 'test@michi.su'
> or username = '@GLOBAL' order by username asc
> Aug 20 08:14:46.563 [16682] dbg: config: retrieving prefs for
> test@michi.su from SQL server
> Aug 20 08:14:46.564 [16682] dbg: info: user has changed
> Aug 20 08:14:46.564 [16682] dbg: bayes: learner_new
> self=Mail::SpamAssassin::Plugin::Bayes=HASH(0x30fdce0),
> bayes_store_module=Mail::SpamAssassin::BayesStore::MySQL
> Aug 20 08:14:46.564 [16682] dbg: bayes: using username: test@michi.su
> Aug 20 08:14:46.564 [16682] dbg: bayes: learner_new: got
> store=Mail::SpamAssassin::BayesStore::MySQL=HASH(0x3d1a768)
> Aug 20 08:14:46.565 [16682] dbg: bayes: database connection established
> Aug 20 08:14:46.566 [16682] dbg: bayes: found bayes db version 3
> Aug 20 08:14:46.566 [16682] dbg: bayes: unable to initialize database
> for test@michi.su user, aborting!
>
>
>
> The MySQL relevant options that I added are:
> user_scores_dsn DBI:mysql:spamassassin:localhost
> user_scores_sql_username spamassassin
> user_scores_sql_password pass
>
> bayes_store_module Mail::SpamAssassin::BayesStore::MySQL
> bayes_sql_dsn DBI:mysql:spamassassin:localhost
> bayes_sql_username spamassassin
> bayes_sql_password pass
>
> auto_whitelist_factory Mail::SpamAssassin::SQLBasedAddrList
> user_awl_dsn DBI:mysql:spamassassin:localhost
> user_awl_sql_username spamassassin
> user_awl_sql_password pass
>