> > CREATE TABLE msgrcpt (
> > ...
> > PRIMARY KEY (partition_tag)
> > ) ENGINE=InnoDB PARTITION BY LIST (partition_tag) ( ...
>
> Yes, this is wrong. The table msgrcpt does not have a primary key,
> nor even any unique key.
>
> If a primary key is necessary for partitioning or for some other
> database distribution / optimization purpose, it needs to be
> invented. I could add for example a recipient sequence number
> (counts recipients within a single message) to a table msgrcpt,
> which would make it possible to define a unique key as
> (partition_tag,mail_id,rind).
Making a primary key for table msgrcpt:
A field msgrcpt.rseqnum needs to be inserted into table msgrcpt,
and the patch below applied (it will go into the next release).
The field msgrcpt.rseqnum uniquely identifies recipients of each message,
typically by assigning them sequential numbers starting with 1. The only
purpose of this field is to make it possible to define a primary key for
the table msgrcpt, which may be needed for some clustering/partitioning
purposes. Amavisd itself does not need a primary key on this table.
The following SQL directives can be used to add the new field:
ALTER TABLE msgrcpt ADD rseqnum integer DEFAULT 0;
If a primary key on table msgrcpt is needed for some reason,
try something like the following (MySQL):
UPDATE msgrcpt SET rseqnum=1+floor(999999999*rand()) WHERE rseqnum=0;
ALTER TABLE msgrcpt ADD PRIMARY KEY (partition_tag,mail_id,rseqnum);
A schema could look like:
CREATE TABLE msgrcpt (
partition_tag integer DEFAULT 0, -- see $partition_tag
mail_id varbinary(12) NOT NULL, -- (must allow duplicates)
rseqnum integer DEFAULT 0, -- recipient count within one message
rid bigint unsigned NOT NULL, -- recipient: maddr.id (dupl. allowed)
...
PRIMARY KEY (partition_tag,mail_id,rseqnum),
) ...
The patch against 2.6.4:
--- amavisd~ 2009-06-25 14:39:01.000000000 +0200
+++ amavisd 2010-04-21 20:21:22.071491197 +0200
@@ -1190,6 +1190,7 @@
' WHERE partition_tag=? AND mail_id=?',
'ins_rcp' =>
- 'INSERT INTO msgrcpt (partition_tag, mail_id, rid,'.
+ 'INSERT INTO msgrcpt (partition_tag, mail_id, rseqnum, rid,'.
' ds, rs, bl, wl, bspam_level, smtp_resp) VALUES (?,?,?,?,?,?,?,?,?)',
+ ' ds, rs, bl, wl, bspam_level, smtp_resp) VALUES (?,?,?,?,?,?,?,?,?,?)',
# 'INSERT INTO msgrcpt (partition_tag, mail_id, rid,'.
# ' ds, rs, content, bl, wl, bspam_level, sql_policy_id, smtp_resp)'.
@@ -19706,5 +19707,7 @@
CC_OVERSIZED,'O', CC_MTA,'t', CC_CLEAN,'C', CC_CATCHALL,'?');
# insert per-recipient records into table msgrcpt
+ my($r_seq_num) = 0; # can serve as a component of a primary key
for my $r (@{$msginfo->per_recip_data}) {
+ $r_seq_num++;
my($rid) = $r->recip_maddr_id;
next if !defined $rid; # e.g. always_bcc, or table 'maddr' is disabled
@@ -19721,5 +19724,5 @@
$resp =~ s/[^\040-\176]/?/gs; # just in case, only need 7 bit printbl
$conn_h->execute($ins_rcp,
- $msginfo->partition_tag, $mail_id, untaint($rid),
+ $msginfo->partition_tag, $mail_id, $r_seq_num, untaint($rid),
# int($msginfo->rx_time),
substr($d,0,1), ' ',
Mark
------------------------------------------------------------------------------
_______________________________________________
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/
