SpamAssassin MySQL Plugin

emal011 · September 30, 2010, 11:48:01 AM

Quote from: LMSSML;29860HI there,

It disappeard the problem with version 0.3 stable from this site:

JohnDoh's RoundCube-Plugin-SpamAssassin-User-Prefs-SQL at 0.3-stable - GitHub

Has John know .

THe problem here is that I got an error saving the settings for the spam.

See attach

Here the same problem...

SpamAssassin User Prefs (SQL) version 1.5

I can not change nothing!

roentgen · January 11, 2011, 04:42:51 PM

Hi

If I read Mail::SpamAssassin::Conf - SpamAssassin configuration file correctly ok_locales may take only en ja ko ru th zh values.

ok_languages is another story which seems to correspond to the real language.

Sorry if I understood something wrong.

JohnDoh · January 12, 2011, 04:19:33 PM

yes I think you might be right, I will try and correct the plugin

LMSSML · February 03, 2011, 05:26:08 PM

HI there,

I saw on log that evry time I try to save something on spam assassin user prefereneces it become with an error on roundcube.

and on the log it become with:
Feb 3 22:22:04 mailserver roundcube: MDB2 Error: syntax error (-2): _doQuery: [Error message: Could not execute statement]#012[Last executed query: SELECT , #012#011#011 FROM #012#011#011 WHERE = '[email protected]'#012#011#011 AND <> 'whitelist_from'#012#011#011 AND <> 'blacklist_from'#012#011#011 AND <> 'whitelist_to';]#012[Native code: 1064]#012[Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' #012#011#011 FROM #012#011#011 WHERE = '[email protected]'
Feb 3 22:22:04 mailserver roundcube: [03-Feb-2011 22:22:04 +0000]: DB Error: MDB2 Error: syntax error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: SELECT , FROM WHERE = '[email protected]' AND <> 'whitelist_from' AND <> 'blacklist_from' AND <> 'whitelist_to';] [Native code: 1064] [Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' FROM WHERE = '[email protected]' AND 'whi' at line 1] in /var/www/rc4/program/include/rcube_mdb2.php on line 644 (POST /rc4/?_task=settings&_action=plugin.sauserprefs.save)
Feb 3 22:22:04 mailserver roundcube: PHP Warning: array_merge() [function.array-merge]: Argument #1 is not an array in /var/www/rc4/plugins/sauserprefs/sauserprefs.php on line 65
Feb 3 22:22:04 mailserver roundcube: PHP Warning: Invalid argument supplied for foreach() in /var/www/rc4/plugins/sauserprefs/sauserprefs.php on line 125
Feb 3 22:22:04 mailserver roundcube: PHP Warning: array_flip() [function.array-flip]: The argument should be an array in /var/www/rc4/plugins/sauserprefs/sauserprefs.php on line 498

JohnDoh · February 04, 2011, 03:03:38 AM

That error shows there are alot of bit missing from the SQL. Check that you have set

Code Select

// spamassassin database settings
$rcmail_config['sauserprefs_db_dsnw'] = 'mysql://username:password@localhost/database';

// table that holds user prefs
$rcmail_config['sauserprefs_sql_table_name'] = &quot;userpref&quot;;

// name of the username field in the user prefs table
$rcmail_config['sauserprefs_sql_username_field'] = &quot;username&quot;;

// name of the preference field in the user prefs table, holds the name of the preference
$rcmail_config['sauserprefs_sql_preference_field'] = &quot;preference&quot;;

// name of the value field in the user prefs table, holds the value of the preference
$rcmail_config['sauserprefs_sql_value_field'] = &quot;value&quot;;

// username of the &quot;global&quot; or default settings user in the database, normaly $GLOBAL or @GLOBAL
$rcmail_config['sauserprefs_global_userid'] = &quot;\$GLOBAL&quot;;

correctly in your config and that you remember to rename it from config.inc.php.dist to config.inc.php

LMSSML · February 04, 2011, 01:10:45 PM

Hi there

The config.inc.php goes like this.

/*
+-----------------------------------------------------------------------+
| SAUserPrefs configuration file |
| |
| This file is part of the RoundCube Webmail client |
| Copyright (C) 2005-2009, RoundCube Dev. - Switzerland |
| Licensed under the GNU GPL |
| |
+-----------------------------------------------------------------------+

*/

$sauserprefs_config = array();

// spamassassin database settings
//$sauserprefs_config['db_dsnw'] = 'mysql://roundcube:password@localhost/roundcube';

// PEAR database DSN for read only operations (if empty write database will be used)
// useful for database replication
$sauserprefs_config['db_dsnr'] = '';

// use persistent db-connections
// beware this will not "always" work as expected
// see: PHP: Persistent Database Connections - Manual
$sauserprefs_config['db_persistent'] = FALSE;

// table that holds user prefs
$sauserprefs_config['sql_table_name'] = "userpref";

// name of the username field in the user prefs table
$sauserprefs_config['sql_username_field'] = "username";

// name of the preference field in the user prefs table, holds the name of the preference
$sauserprefs_config['sql_preference_field'] = "preference";

// name of the value field in the user prefs table, holds the value of the preference
$sauserprefs_config['sql_value_field'] = "value";

// enable the whitelists synchronisation
// NOTE: To use this your SpamAssassin prefs table must include a field in which the contact_id from the RC address book can be stored
// check README for more information
$sauserprefs_config['whitelist_sync'] = FALSE;

// don't allow these settings to be overriden by the user
// eg. $sauserprefs_config['dont_override'] = array('required_score','rewrite_header Subject');
$sauserprefs_config['dont_override'] = array();

// default settings
// these are overridden by $GLOBAL and user settings from the database
$sauserprefs_config['default_prefs'] = array(
"required_score" => "5",
"rewrite_header Subject" => "*****SPAM*****",
"ok_languages" => "all",
"ok_locales" => "all",
"fold_headers" => "1",
"add_header all Level" => "_STARS(*)_",
"use_razor1" => "0",
"use_razor2" => "1",
"use_pyzor" => "1",
"use_dcc" => "1",
"use_bayes" => "1",
"skip_rbl_checks" => "0",
"report_safe" => "1",
"bayes_auto_learn" => "1",
"bayes_auto_learn_threshold_nonspam" => "0.1",
"bayes_auto_learn_threshold_spam" => "12.0",
"use_bayes_rules" => "1"
);

// spam score increment - increment values in the the score threshold drop down by this, from 0 to 10
$sauserprefs_config['score_inc'] = 1;

// delete user bayesian data stored in database
// the query can contain the following macros that will be expanded as follows:
// %u is replaced with the username (from the session info)
// use an array to run multiple queries
// eg. $sauserprefs_config['bayes_table'] = array(
// 'DELETE FROM bayes_seen WHERE id IN (SELECT id FROM bayes_va$
// 'DELETE FROM bayes_token WHERE id IN (SELECT id FROM bayes_v$

$sauserprefs_config['bayes_delete_query'] = '';

// define languages
$sauserprefs_config['languages'] = array(
"af" => "Afrikaans",
"sq" => "Albanian",
"am" => "Amharic",
"ar" => "Arabic",
"hy" => "Armenian",
"eu" => "Basque",
"bs" => "Bosnian",
"bg" => "Bulgarian",
"be" => "Byelorussian",
"ca" => "Catalan",
"zh" => "Chinese",
"hr" => "Croatian",
"cs" => "Czech",
"da" => "Danish",
"nl" => "Dutch",
"en" => "English",
"eo" => "Esperanto",
"et" => "Estonian",
"fi" => "Finnish",
"fr" => "French",
"fy" => "Frisian",
"ka" => "Georgian",
"de" => "German",
"el" => "Greek",
"he" => "Hebrew",
"hi" => "Hindi",
"hu" => "Hungarian",
"is" => "Icelandic",
"id" => "Indonesian",
"ga" => "Irish Gaelic",
"it" => "Italian",
"ja" => "Japanese",
"ko" => "Korean",
"la" => "Latin",

$rcmail_config['sauserprefs_db_dsnw'] = 'mysql://roundcube:password@localhost/roundcube?new_link=true';

JohnDoh · February 05, 2011, 04:22:40 AM

and there is nothing in the error log about not being able to read it or anything like that?

fritz001 · February 08, 2011, 06:03:03 PM

Hei John, can you add an option to enable edit of rules in (Address Rules ) instead of just Create/Delete ?
Also option "ok_languages" => "all" from config.inc.php ..?

JohnDoh · February 09, 2011, 03:24:13 AM

Quotecan you add an option to enable edit of rules in (Address Rules ) instead of just Create/Delete ?

It's only 1 peice of information so editing is the same as delete+create, I'm not sure its really nessecarry, I'll put it on the wish list.

QuoteAlso option "ok_languages" => "all" from config.inc.php ..?

What about it?

jimmi · April 12, 2011, 07:18:27 AM

It could be only cosmetic, but looks weird.

I set in my database in $GLOBAL config the score 4.5, but when I go into the settings of RC interface the proposed score is still 5, only in the dropdown box I have the last entry "Others [4.5]".

Using spamc anyhow the required score is 4.5: is it correct?

JohnDoh · April 12, 2011, 09:37:34 AM

by default the scores available in the box are incremented by 1 from 0 to 10, if you want to use a different increment that can be changed from the config file.

a.l · April 17, 2011, 12:00:53 PM

Hi

I am really a noob. :-)

I'm trying to setup a mailserver where I want a configuration like this:
Postfix+dovecot+roundcube+SpamAssassin MySQL Plugin

I have some very basic postfix and dovecot setup up and running on a virtual machine before I go implement it on the real target server.
I have also installed the newest roundcube found in the beta of ubuntu 11.04 - however on the target server i will install roundcube from source to do stuff right (I can see the ubuntu dpkg installer does not ask all normally necessary questions.)

Now, because I would like to make it as user friendly as possible for the users of our local housing association (non-profit) then I would like to make use of the SA MySQL plugin.
(Besides that and because there is no Danish translation of this plugin I will make a translation right after I get this plugin to work and post it here. I already translated some of the strings however I would like to see the result before submitting.)

But there is a rock on the road that makes me stop!
It states in: http://www.tehinterweb.co.uk/roundcube/plugins/sauserprefs.README.txt
, that I should follow: UsingSQL - Spamassassin Wiki
, but when I read the section about "Testing your Setup" in the bottom of the page I kind of get lost!

I am lost because I do not know how to call Postfix with "spamc -u " and the right username (e.g./perhaps making a script to decide that and call spamc)?

I am also a little lost about how to understand the concept of the username?

Thank you all very much in advance!

JohnDoh · April 18, 2011, 03:26:42 AM

Hi, I think you might need to go ask the Postfix people. I don't know anything about Postfix though there could be other people around the forum who do and can post the answer I would guess a specialist postfix forum/list is what you want.

a.l · May 09, 2011, 04:31:31 PM

Quote from: JohnDoh;34341Hi, I think you might need to go ask the Postfix people. I don't know anything about Postfix though there could be other people around the forum who do and can post the answer I would guess a specialist postfix forum/list is what you want.

Thanks :-)

In the meantime i have been testing the plugin.

It seems to work (I havent tested it with spamassassin yet due to other work.)
The plugin works fine - I can set the different user preferences and the MySQL tables are updated.
However when I change a preference on the 'Generel Settings' section and try to save then only the first time I get "Successfully changed spam settings" on all attempts afterwards I get "Error: Cannot change spam settings" however they are _indeed_ changed in the MySQL table. (The other sections e.g. Internet Based Spam Tests and Bayes Settings do not have this problem.)

I have tried to wipe the tables and then it works again the first time and following attempts come with the error, but the preferences get changed.

JohnDoh · May 10, 2011, 02:53:14 AM

QuoteHowever when I change a preference on the 'Generel Settings' section and try to save then only the first time I get "Successfully changed spam settings" on all attempts afterwards I get "Error: Cannot change spam settings" however they are _indeed_ changed in the MySQL table.

That means one of the updates failed. It works fine for me. Could you look in the RC error log and see if there are any SQL errors please?