Just wanted to pass along a thank you to those who helped out here and provide
a few notes, on my experience, that may help anyone else that is looking at
this.
By the way, converting to MySQL did alleviate the problems that I was seeing
when attempting to apply updates to AWL ... processes that were taking hours
are now taking seconds!
Along the way I discovered a few things:
1) We have been running SA for several years and the AWL was far too large
for any of the scripts, that I downloaded for conversion.
2) The time to load the MySQL database, with per-row insert logic was
daunting.
Note: I made a few passes at this before hitting on a working solution.
I found it, much, faster, to unload the existing DB to a delimited file, and
use "mysql load" to load the AWL from that file.
What I wound up with was a scrit to:
. Unload the DB (formatting a flat" delimited file):
This was created from the "convert_awl_dbm_to_sql" script downloaded
from the spamassassin/tools. Substituting output, to stdout, for the
mysql insert statement, and modifying the loop to use "each" rather than
attempting to build and array of "@k" as well as adding some trim
functionality:
# my @k = grep(!/totscore$/,keys(%h));
# for my $key (@k) {
our $rowsread;
our $rowsinsert;
print stderr "Processing: $db \n";
while(my ($key, $v) = each %h)
{
next if $key =~ /totscore$/; # Skip totscore entries
$rowsread++;
next if ($v < 2); # skip one off entries.
.
.
.
$rowsinsert++;
print '"',$opt{'username'},'",','"',$email,'",','"',$ip,'",',
$count,',',$totscore,"\n";
.
.
.
. Drop and create the AWL table (the drop becuase of earlier experiments).
drop table awl;
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',
lastupdate timestamp(14) NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (username,email,ip)
) TYPE=MyISAM;
. load the MySQL database:
LOAD DATA INFILE '/home/larrys/sa_tools/awl.load'
REPLACE
INTO TABLE awl
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(username, email, ip, count, totscore)
SET lastupdate = CURRENT_TIMESTAMP;
Following this it was a simple matter of sliding the new "local.cf" into place
and restarting processesses.
So far everything seems to be running well.
Thanks again
--
Larry G. Starr - lar...@fullcompass.com or sta...@globaldialog.com
Software Engineer: Full Compass Systems LTD.
Phone: 608-831-7330 x 1347 FAX: 608-831-6330
===================================================================
There are only three sports: bullfighting, mountaineering and motor
racing, all the rest are merely games! - Ernest Hemmingway
