[Fwd: maddr] unique id's but duplicate email

[Michael Scheidell <sc...@secnap.net>]

I sent this to our support group, but looks like there can be some  
issues, and maybe a better way.

while there cannot be a duplicate id:

(maddr id = 5) can't exist in partition_tag 23 AND 24,

the email address CAN exist in multiple partitions, is this the intended 
results?
seems that daily, weekly and monthly cleanups that rely on maddr id's, 
or reports that think they will get one id could fail.


starwoodhotelsresorts@starwood.delivery.net

select partition_tag,id from maddr where 
email='starwoodhotelsresorts@starwood.delivery.net';
+---------------+---------+
| partition_tag | id      |
+---------------+---------+
|             0 |  827430 |
|        200934 | 7454622 |
|        200938 | 9316892 |
+---------------+---------+


-------- Original Message --------
Subject: 	maddr
Date: 	Mon, 28 Sep 2009 16:13:35 -0400
From: 	Michael Scheidell <sc...@secnap.net>
To: 	SpammerTrap Support <su...@spammertrap.com>



it looks like, no matter what, you won't have duplicate maddr id's.
but, matching it with partition tags could get weird.

-- provide unique id for each e-mail address, avoids storing copies
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;

if you use mysql partitions, id (and partition tag) need to be part of 
the primary key.

this might be better then (if using 53 weeks) replicating much used 
maddr id's (sid and rid in msgrcpt).

could get

-- 
Michael Scheidell, CTO
Phone: 561-999-5000, x 1259
 > *| *SECNAP Network Security Corporation

    * Certified SNORT Integrator
    * 2008-9 Hot Company Award Winner, World Executive Alliance
    * Five-Star Partner Program 2009, VARBusiness
    * Best Anti-Spam Product 2008, Network Products Guide
    * King of Spam Filters, SC Magazine 2008


-- 
Michael Scheidell, CTO
Phone: 561-999-5000, x 1259
 > *| *SECNAP Network Security Corporation

    * Certified SNORT Integrator
    * 2008-9 Hot Company Award Winner, World Executive Alliance
    * Five-Star Partner Program 2009, VARBusiness
    * Best Anti-Spam Product 2008, Network Products Guide
    * King of Spam Filters, SC Magazine 2008


_________________________________________________________________________
This email has been scanned and certified safe by SpammerTrap(r). 
For Information please see http://www.spammertrap.com
_________________________________________________________________________

Re: [Fwd: maddr] unique id's but duplicate email

["McDonald, Dan" <Da...@austinenergy.com>]

On Mon, 2009-09-28 at 16:52 -0400, Michael Scheidell wrote:
> I sent this to our support group, but looks like there can be some
> issues, and maybe a better way.

Wrong list - this is an amavisd question....

> while there cannot be a duplicate id:
> 
> (maddr id = 5) can't exist in partition_tag 23 AND 24,

There can be, if you have your schema built properly.
> 
> the email address CAN exist in multiple partitions, is this the
> intended results?

Yes.  That way you can blithely purge an entire partition and maintain
referential integrity.
 
> seems that daily, weekly and monthly cleanups that rely on maddr id's,
> or reports that think they will get one id could fail.

They need to be altered for use with partitions.  That's the point of
partition tags....

-- 
Daniel J McDonald, CCIE # 2495, CISSP # 78281, CNX
www.austinenergy.com