Best way to guarantee unique mail_id across partitions when using mysql

[Mark Martinec via amavis-users]

Dave,

> What is the recommend way of ensuring mail_id is unique across 
> partitions
> when using MySQL ?
> We are using a week number for the partition_tag field.
> We are considering adding unique indicies on mail_id in cases.
> Is there a better or more correct way ?
> Can we just put one on the first table that amavisd writes to ?

The uniqueness of mail_id in SQL is tested by attempting to insert a
record about a new message by an SQL clause in $sql_clause{'ins_msg'},
which by default is:

   INSERT INTO msgs (partition_tag, mail_id, secret_id, am_id,
    time_num, time_iso, sid, policy, client_addr, size, host)
    VALUES (?,?,?,?,?,?,?,?,?,?,?)

If that succeeds, the generated mail_id is good and get used,
otherwise a new attempt is made with a new random id.

So it all boils down to how a primary key and a CONSTRAINT
is declared for a table 'msgs', e.g.:

   CONSTRAINT msgs_partition_mail UNIQUE (partition_tag,mail_id),
   PRIMARY KEY (partition_tag,mail_id)

If that is changed to enforce that just the 'mail_id' field is unique
(not paired with partition_tag), then this would achieve what you want.
On a constraint failure amavisd would retry the mail_id generation.


> Is the mail_id unique enough as it stands to do nothing about it ?

Unless you are a huge mail service provider and keeping a long archive,
I wouldn't worry.

For the default 12-character mail_id size, the following formula
still applies (from release notes):

   The mail_id carries 71.9 bits of information (subject to quality of 
sources
   of entropy). For a high-end system that keeps a year's worth of mail 
messages
   in evidence (e.g. in quarantine) and receives 10e6 messages each day
   (20..200 TB of yearly mail contents), the probability of a mail_id
   collision happening during one year (while gradually displacing an 
entire
   collection with a new set of IDs) is n^2/m = 0.3 %

   (10e6 * 365)^2 / (62 * (64^(12-2)) * 62) = (10e6 * 365)^2 / 2^71.9 = 
0.003


Mark