Interesting.. if I try to import my old Berkely DB it fails right out. If I
process a message auto learn complains about the duplicate key again, but it
actually learns the tokens and I can see them with sa-learn --dump magic.
What key is duplicate? I'm still unclear on how to resolve this? I took the
schema syntax directly from the bayes mysql readme for 3.1.4.
>>> "Ryan Kather" <[EMAIL PROTECTED]> 08/21/06 04:28PM >>>
I am having a few problems converting from Berkely DB to MySQL w/ InnoDB. I
have created the DB, Tables, and updated the local.cf. Everything appears ok,
but when I attempt to restore my Berkely DB backup with sa-learn --restore
filename. I get the following errors.
[21508] dbg: bayes: using username: somuser
[21508] dbg: bayes: database connection established
[21508] dbg: bayes: found bayes db version 3
[21508] dbg: bayes: unable to initialize database for someuser, aborting!
[21508] dbg: config: score set 1 chosen.
[21508] dbg: bayes: database connection established
[21508] dbg: bayes: found bayes db version 3
[21508] dbg: bayes: unable to initialize database for someuser, aborting!
[21508] dbg: bayes: database connection established
[21508] dbg: bayes: found bayes db version 3
[21508] dbg: bayes: using userid: 1
[21508] dbg: bayes: _put_token: SQL error: Duplicate entry '1-' for key 1
[21508] dbg: bayes: error inserting token for line: t 648 899 1156175812
c0614089c0
[21508] dbg: bayes: _put_token: SQL error: Duplicate entry '1-' for key 1
[21508] dbg: bayes: error inserting token for line: t 253 160 1156151124
90775ea219
[21508] dbg: bayes: _put_token: SQL error: Duplicate entry '1-' for key 1
......
bayes: encountered too many errors (20) while parsing token line, reverting to
empty database and exiting
ERROR: Bayes restore returned an error, please re-run with -D for more
information
I'm using SpamAssassin 3.1.4 with MySQL 5.0.24 on Intel EM64T (Gentoo 2006.0).
I've tried reverting to MyISAM with no change. Any ideas?
AWL w/ SQL seems to be working. Thanks for any guidance.
Configuration:
-----------------
local.cf settings:
# SpamAssassin SQL Based Bayesian
bayes_store_module Mail::SpamAssassin::BayesStore::SQL
bayes_sql_dsn DBI:mysql:somedatabase:localhost
bayes_sql_username someuser
bayes_sql_password somepassword
bayes_sql_override_username someuser
# SpamAssassin SQL Based AWL
auto_whitelist_factory Mail::SpamAssassin::SQLBasedAddrList
user_awl_dsn DBI:mysql:somedatabase:localhost
user_awl_sql_username someuser
user_awl_sql_password somepassword
SQL Schema:
CREATE TABLE awl (
username varchar(100) NOT NULL default '',
email varchar(200) NOT NULL default '',
ip varchar(10) NOT NULL default '',
count int(11) default '0',
totscore float default '0',
PRIMARY KEY (username,email,ip)
) TYPE=InnoDB;
CREATE TABLE bayes_expire (
id int(11) NOT NULL default '0',
runtime int(11) NOT NULL default '0',
KEY bayes_expire_idx1 (id)
) TYPE=MyInnoDB;
CREATE TABLE bayes_global_vars (
variable varchar(30) NOT NULL default '',
value varchar(200) NOT NULL default '',
PRIMARY KEY (variable)
) TYPE=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)
) TYPE=InnoDB;
CREATE TABLE bayes_token (
id int(11) NOT NULL default '0',
token char(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 (token),
INDEX bayes_token_idx2 (id, atime)
) TYPE=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)
) TYPE=InnoDB;
