Ryan,
I just did this myself the first time in the last week; Be sure that all your operations on the DB are done as the user who is going to be accessing it; ie: Spamassassin spamuser etc. Not knowing the history of your install; In your Spamassassin local.cf file you should have these lines, COMMENTED OUT for now... You want spamassassin to use the berkely db for the moment. # bayes_store_module Mail::SpamAssassin::BayesStore::MySQL # bayes_sql_dsn DBI:mysql:bayes:localhost:3306 # bayes_sql_username spamassassin # bayes_sql_password spampassword # bayes_sql_override_username spamassassin First be sure that your B-DB is actually a vs 3.x by doing 'sa-learn --sync' This will ensure that the b-db format is 3.x compatible. Next, do a sa-learn --backup >backup.txt. Create the bayes DB in mysql, and then apply the tables using the templates ( that you obviously have ). In mysql (as root) be sure to do : - grant all privileges on bayes.* to [EMAIL PROTECTED] identified by 'spampassword' Uncomment the bayse_* lines from Spamassassin local.cf, then su back as spamassassin ( or whatever user is going to be accessing the db ) run 'sa-learn --restore ./backup.txt' this places all the entries from backup.txt into the mysql db. This should take a few minutes. >From your errors, it looks like the import process into your db got messed up. As root, go into mysql> and drop the bayes db and start again.... Good luck... Michael Grey -----Original Message----- From: Ryan Kather [mailto:[EMAIL PROTECTED] Sent: Monday, August 21, 2006 1:29 PM To: <users@spamassassin.apache.org Subject: Bayes SQL Errors 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;
