Michael,
> Also found out the partition_tag has to be part or the primary,
> and or ALL unique keys. I got the following error otherwise:
> ERROR 1503 (HY000): A PRIMARY KEY must include all columns
> in the table's partitioning function
> Since partition_tag is NOT part of, or an index in anything but maddr,
> #1, can't be included in mysql partitioning anyway, and even without mysql
> partitioning
Just make a partition_tag part of keys,
this is what 2.6.1 made possible.
amavisd-new-2.6.1 release notes
- changed SQL default clauses in %sql_clause (upd_msg, sel_quar, sel_penpals)
to always join tables using both the partition_tag and the mail_id fields,
previously just the mail_id field was used in a join. The change has no
particular effect (and is not really necessary) on existing 2.6.0 databases
where a primary key is mail_id (it is just a redundant extra condition),
but saves a day when a primary key is a composite: (partition_tag,mail_id),
which may be a requirement of a SQL partitioning mechanism.
Thanks to Thomas Gelf for his testing of MySQL partitioning, reporting
deficiency in amavisd SQL schema (primary keys) which did not meet MySQL
requirements for partitioning, and suggestions;
[...]
Background information on UNIQUE constraint in table SQL msgs
Amavisd does not know and need not be aware of what is a primary
key or what are UNIQUE constraints in SQL table msgs. When generating
a mail_id for a message being processed, amavisd tries to INSERT
a record with a randomly generated mail_id into table msgs (using
SQL clause in $sql_clause{'ins_msg'}). If the operation fails,
another mail_id is generated and attempt repeated, until it eventually
succeeds. Thus it depends entirely on SQL's decision whether a
particular record is allowed or would break some UNIQUE constraint.
So, by only changing a declaration on table msgs (PRIMARY KEY or
adding a CONSTRAINT), it changes what keys amavisd will be allowed
to insert and what kind of duplicates would be allowed.
Classically the msgs.mail_id is a PRIMARY KEY and as such it is unique.
This was a requirement for versions of amavisd up to and including 2.6.0.
Starting with 2.6.1 the JOINs have been tightened to include a
partition_tag besides mail_id in a relation, which makes it possible
to loosen a unique requirement on msgs.mail_id and only require a
pair (partition_tag,mail_id) to be unique. In other words, this way
the mail_id is only needed to be unique within each partition tag value.
This change allows a partitioning scheme to meet requirements on
MySQL partitioning. For non-partitioned databases the change shouldn't
make any difference, and one is free to choose between having mail_id
unique across the entire table or just within each partition_tag value.
Changing a primary key to (partition_tag,mail_id) brings consequences
to quarantining, in particular to releasing from a SQL quarantine,
where it no longer suffices to specify mail_id=xxx in AM.PDP request,
but may be necessary to specify also a partition_tag=xx to distinguish
between SQL-quarantined messages which happen to have the same mail_id.
Mark
------------------------------------------------------------------------------
SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada.
The future of the web can't happen without you. Join us at MIX09 to help
pave the way to the Next Web now. Learn more and register at
http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/
_______________________________________________
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/
