Le vendredi 12 décembre 2008 10:52:07, Mark Martinec a écrit :
> 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/
A stupid question, what advantages do I get if I use partition_tag with
amavisd+mysql?
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
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/
