Hi,
Mark Martinec schrieb:
>> since now I'm not using any SQL in AMaViSd / SpamAssassin.
>> I'm planning to go to bayes in SQL and to make use of the pen pal /
>> bounce killer feature with MySQL.
>> I'm not quite clear what tables the logging mechanism in AMaViSd uses
>> and which I have to define to use bounce killer. Any hint?
>
> You need tables maddr, msgs, msgrcpt, and quarantine,
> search for "R/W part of the dataset" in README.sql-mysql / README.sql-pg.
I installed the tables like this and send a few mails, which created
rows in the tables.
MySQL is 5.1.42 on SLES 11.
CREATE TABLE maddr (
partition_tag integer DEFAULT 0, -- see $sql_partition_tag
id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
email varbinary(255) NOT NULL, -- full mail address
domain varchar(255) NOT NULL, -- only domain part of the email
address
-- with subdomain fields in reverse
CONSTRAINT part_email UNIQUE (partition_tag,email)
) ENGINE=InnoDB;
CREATE TABLE msgs (
partition_tag integer DEFAULT 0, -- see $sql_partition_tag
mail_id varbinary(12) NOT NULL PRIMARY KEY, -- long-term unique
mail id
secret_id varbinary(12) DEFAULT '', -- authorizes release of mail_id
am_id varchar(20) NOT NULL, -- id used in the log
time_num integer unsigned NOT NULL, -- rx_time: seconds since Unix
epoch
time_iso TIMESTAMP NOT NULL DEFAULT 0,
sid bigint unsigned NOT NULL, -- sender: maddr.id
policy varchar(255) DEFAULT '', -- policy bank path (like macro %p)
client_addr varchar(255) DEFAULT '', -- SMTP client IP address (IPv4
or v6)
size integer unsigned NOT NULL, -- message size in bytes
content binary(1), -- content type: V/B/S/s/M/H/O/C:
--
virus/banned/spam(kill)/spammy(tag2)/bad-mime/bad-header/oversized/clean
-- is NULL on partially processed mail
-- use binary instead of char for case sensitivity ('S' != 's')
quar_type binary(1), -- quarantined as: ' '/F/Z/B/Q/M/L
-- none/file/zipfile/bsmtp/sql/
-- /mailbox(smtp)/mailbox(lmtp)
quar_loc varbinary(255) DEFAULT '', -- quarantine location (e.g. file)
dsn_sent char(1), -- was DSN sent? Y/N/q (q=quenched)
spam_level float, -- SA spam level (no boosts)
message_id varchar(255) DEFAULT '', -- mail Message-ID header field
from_addr varchar(255) DEFAULT '', -- mail From header field, UTF8
subject varchar(255) DEFAULT '', -- mail Subject header field, UTF8
host varchar(255) NOT NULL, -- hostname where amavisd is
running
FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE INDEX msgs_idx_sid ON msgs (sid);
CREATE INDEX msgs_idx_mess_id ON msgs (message_id); -- useful with pen pals
CREATE INDEX msgs_idx_time_num ON msgs (time_num);
CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
CREATE TABLE msgrcpt (
partition_tag integer DEFAULT 0, -- see $sql_partition_tag
mail_id varbinary(12) NOT NULL, -- (must allow duplicates)
rid bigint unsigned NOT NULL, -- recipient: maddr.id (dupl.
allowed)
ds char(1) NOT NULL, -- delivery status: P/R/B/D/T
--
pass/reject/bounce/discard/tempfail
rs char(1) NOT NULL, -- release status: initialized
to ' '
bl char(1) DEFAULT ' ', -- sender blacklisted by this
recip
wl char(1) DEFAULT ' ', -- sender whitelisted by this
recip
bspam_level float, -- spam level + per-recip boost
smtp_resp varchar(255) DEFAULT '', -- SMTP response given to MTA
FOREIGN KEY (rid) REFERENCES maddr(id) ON DELETE RESTRICT,
FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id);
CREATE INDEX msgrcpt_idx_rid ON msgrcpt (rid);
CREATE TABLE quarantine (
partition_tag integer DEFAULT 0, -- see $sql_partition_tag
mail_id varbinary(12) NOT NULL, -- long-term unique mail id
chunk_ind integer unsigned NOT NULL, -- chunk number, starting with 1
mail_text blob NOT NULL, -- store mail as chunks of octets
PRIMARY KEY (mail_id,chunk_ind),
FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
) ENGINE=InnoDB;
When I tried to go with partitions:
CREATE TABLE maddr (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
partition_tag int(11) NOT NULL DEFAULT '0',
email varbinary(255) NOT NULL,
domain varchar(255) NOT NULL,
PRIMARY KEY (id, partition_tag),
UNIQUE KEY email_key (partition_tag, email)
) ENGINE=InnoDB PARTITION BY LIST (partition_tag) (
PARTITION p0 VALUES IN (0,1,2,3,4,5),
PARTITION p1 VALUES IN (6,7,8,9,10,11),
PARTITION p2 VALUES IN (12,13,14,15,16,17),
PARTITION p3 VALUES IN (18,19,20,21,22,23),
PARTITION p4 VALUES IN (24,25,26,27,28,29),
PARTITION p5 VALUES IN (30,31,32,33,34,35),
PARTITION p6 VALUES IN (36,37,38,39,40,41),
PARTITION p7 VALUES IN (42,43,44,45,46,47),
PARTITION p8 VALUES IN (48,49,50,51,52,53)
);
This is created just fine.
The second one is not:
CREATE TABLE msgs (
partition_tag integer DEFAULT 0, -- see $sql_partition_tag
mail_id varbinary(12) NOT NULL PRIMARY KEY, -- long-term unique
mail id
secret_id varbinary(12) DEFAULT '', -- authorizes release of mail_id
am_id varchar(20) NOT NULL, -- id used in the log
time_num integer unsigned NOT NULL, -- rx_time: seconds since Unix
epoch
time_iso TIMESTAMP NOT NULL DEFAULT 0,
sid bigint unsigned NOT NULL, -- sender: maddr.id
policy varchar(255) DEFAULT '', -- policy bank path (like macro %p)
client_addr varchar(255) DEFAULT '', -- SMTP client IP address (IPv4
or v6)
size integer unsigned NOT NULL, -- message size in bytes
content binary(1), -- content type: V/B/S/s/M/H/O/C:
--
virus/banned/spam(kill)/spammy(tag2)/bad-mime/bad-header/oversized/clean
-- is NULL on partially processed mail
-- use binary instead of char for case sensitivity ('S' != 's')
quar_type binary(1), -- quarantined as: ' '/F/Z/B/Q/M/L
-- none/file/zipfile/bsmtp/sql/
-- /mailbox(smtp)/mailbox(lmtp)
quar_loc varbinary(255) DEFAULT '', -- quarantine location (e.g. file)
dsn_sent char(1), -- was DSN sent? Y/N/q (q=quenched)
spam_level float, -- SA spam level (no boosts)
message_id varchar(255) DEFAULT '', -- mail Message-ID header field
from_addr varchar(255) DEFAULT '', -- mail From header field, UTF8
subject varchar(255) DEFAULT '', -- mail Subject header field, UTF8
host varchar(255) NOT NULL, -- hostname where amavisd is
running
FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
) ENGINE=InnoDB PARTITION BY LIST (partition_tag) (
PARTITION p0 VALUES IN (0,1,2,3,4,5),
PARTITION p1 VALUES IN (6,7,8,9,10,11),
PARTITION p2 VALUES IN (12,13,14,15,16,17),
PARTITION p3 VALUES IN (18,19,20,21,22,23),
PARTITION p4 VALUES IN (24,25,26,27,28,29),
PARTITION p5 VALUES IN (30,31,32,33,34,35),
PARTITION p6 VALUES IN (36,37,38,39,40,41),
PARTITION p7 VALUES IN (42,43,44,45,46,47),
PARTITION p8 VALUES IN (48,49,50,51,52,53)
);
ERROR 1506 (HY000): Foreign key clause is not yet supported in
conjunction with partitioning
Even this the table schema without (see above) it does not work:
ERROR 1005 (HY000): Can't create table 'amavisd_log.msgs' (errno: 150)
shell> perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
How can I use all the tables above with partitioning or is this not
recommended?
Marc
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
