Mark,
Mark Martinec schrieb:
>> ERROR 1506 (HY000): Foreign key clause is not yet supported in
>> conjunction with partitioning
>>
>> Even this the table schema without (see above) it does not work:
>> ERROR 1005 (HY000): Can't create table 'amavisd_log.msgs' (errno: 150)
>>
>> shell> perror 150
>> MySQL error code 150: Foreign key constraint is incorrectly formed
>>
>> How can I use all the tables above with partitioning or is this not
>> recommended?
>
> Try removing all FOREIGN KEY constraints, these are not essential.
Just for the record, here is the complete SQL set for MySQL 5.1:
CREATE TABLE maddr (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
partition_tag int(11) NOT NULL DEFAULT '0',
email varbinary(255) NOT NULL,
domain varchar(255) NOT NULL,
PRIMARY KEY (id, partition_tag),
UNIQUE KEY email_key (partition_tag, email)
) ENGINE=InnoDB PARTITION BY LIST (partition_tag) (
PARTITION p0 VALUES IN (0,1,2,3,4,5),
PARTITION p1 VALUES IN (6,7,8,9,10,11),
PARTITION p2 VALUES IN (12,13,14,15,16,17),
PARTITION p3 VALUES IN (18,19,20,21,22,23),
PARTITION p4 VALUES IN (24,25,26,27,28,29),
PARTITION p5 VALUES IN (30,31,32,33,34,35),
PARTITION p6 VALUES IN (36,37,38,39,40,41),
PARTITION p7 VALUES IN (42,43,44,45,46,47),
PARTITION p8 VALUES IN (48,49,50,51,52,53)
);
CREATE TABLE msgs (
partition_tag integer DEFAULT 0, -- see $sql_partition_tag
mail_id varbinary(12) NOT NULL, -- long-term unique mail id
secret_id varbinary(12) DEFAULT '', -- authorizes release of mail_id
am_id varchar(20) NOT NULL, -- id used in the log
time_num integer unsigned NOT NULL, -- rx_time: seconds since Unix
epoch
time_iso TIMESTAMP NOT NULL DEFAULT 0,
sid bigint unsigned NOT NULL, -- sender: maddr.id
policy varchar(255) DEFAULT '', -- policy bank path (like macro %p)
client_addr varchar(255) DEFAULT '', -- SMTP client IP address (IPv4
or v6)
size integer unsigned NOT NULL, -- message size in bytes
content binary(1), -- content type: V/B/S/s/M/H/O/C:
--
virus/banned/spam(kill)/spammy(tag2)/bad-mime/bad-header/oversized/clean
-- is NULL on partially processed mail
-- use binary instead of char for case sensitivity ('S' != 's')
quar_type binary(1), -- quarantined as: ' '/F/Z/B/Q/M/L
-- none/file/zipfile/bsmtp/sql/
-- /mailbox(smtp)/mailbox(lmtp)
quar_loc varbinary(255) DEFAULT '', -- quarantine location (e.g. file)
dsn_sent char(1), -- was DSN sent? Y/N/q (q=quenched)
spam_level float, -- SA spam level (no boosts)
message_id varchar(255) DEFAULT '', -- mail Message-ID header field
from_addr varchar(255) DEFAULT '', -- mail From header field, UTF8
subject varchar(255) DEFAULT '', -- mail Subject header field, UTF8
host varchar(255) NOT NULL,
PRIMARY KEY (mail_id, partition_tag)
) ENGINE=InnoDB PARTITION BY LIST (partition_tag) (
PARTITION p0 VALUES IN (0,1,2,3,4,5),
PARTITION p1 VALUES IN (6,7,8,9,10,11),
PARTITION p2 VALUES IN (12,13,14,15,16,17),
PARTITION p3 VALUES IN (18,19,20,21,22,23),
PARTITION p4 VALUES IN (24,25,26,27,28,29),
PARTITION p5 VALUES IN (30,31,32,33,34,35),
PARTITION p6 VALUES IN (36,37,38,39,40,41),
PARTITION p7 VALUES IN (42,43,44,45,46,47),
PARTITION p8 VALUES IN (48,49,50,51,52,53)
);
CREATE INDEX msgs_idx_sid ON msgs (sid);
CREATE INDEX msgs_idx_mess_id ON msgs (message_id); -- useful with pen pals
CREATE INDEX msgs_idx_time_num ON msgs (time_num);
CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
CREATE TABLE msgrcpt (
partition_tag integer DEFAULT 0, -- see $sql_partition_tag
mail_id varbinary(12) NOT NULL, -- (must allow duplicates)
rid bigint unsigned NOT NULL, -- recipient: maddr.id (dupl.
allowed)
ds char(1) NOT NULL, -- delivery status: P/R/B/D/T
--
pass/reject/bounce/discard/tempfail
rs char(1) NOT NULL, -- release status: initialized
to ' '
bl char(1) DEFAULT ' ', -- sender blacklisted by this
recip
wl char(1) DEFAULT ' ', -- sender whitelisted by this
recip
bspam_level float, -- spam level + per-recip boost
smtp_resp varchar(255) DEFAULT '', -- SMTP response given to MTA
PRIMARY KEY (partition_tag)
) ENGINE=InnoDB PARTITION BY LIST (partition_tag) (
PARTITION p0 VALUES IN (0,1,2,3,4,5),
PARTITION p1 VALUES IN (6,7,8,9,10,11),
PARTITION p2 VALUES IN (12,13,14,15,16,17),
PARTITION p3 VALUES IN (18,19,20,21,22,23),
PARTITION p4 VALUES IN (24,25,26,27,28,29),
PARTITION p5 VALUES IN (30,31,32,33,34,35),
PARTITION p6 VALUES IN (36,37,38,39,40,41),
PARTITION p7 VALUES IN (42,43,44,45,46,47),
PARTITION p8 VALUES IN (48,49,50,51,52,53)
);
CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id);
CREATE INDEX msgrcpt_idx_rid ON msgrcpt (rid);
CREATE TABLE quarantine (
partition_tag integer DEFAULT 0, -- see $sql_partition_tag
mail_id varbinary(12) NOT NULL, -- long-term unique mail id
chunk_ind integer unsigned NOT NULL, -- chunk number, starting with 1
mail_text blob NOT NULL, -- store mail as chunks of octets
PRIMARY KEY (mail_id,chunk_ind,partition_tag)
) ENGINE=InnoDB PARTITION BY LIST (partition_tag) (
PARTITION p0 VALUES IN (0,1,2,3,4,5),
PARTITION p1 VALUES IN (6,7,8,9,10,11),
PARTITION p2 VALUES IN (12,13,14,15,16,17),
PARTITION p3 VALUES IN (18,19,20,21,22,23),
PARTITION p4 VALUES IN (24,25,26,27,28,29),
PARTITION p5 VALUES IN (30,31,32,33,34,35),
PARTITION p6 VALUES IN (36,37,38,39,40,41),
PARTITION p7 VALUES IN (42,43,44,45,46,47),
PARTITION p8 VALUES IN (48,49,50,51,52,53)
);
Marc
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
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/
