Paul
Here is the create statement for the dbmail_header (it was created using
the MySQL script from the 2=> 3.0.2 upgrade)
Regards
Daniel
DROP TABLE IF EXISTS `dbmail`.`dbmail_header`;
CREATE TABLE `dbmail`.`dbmail_header` (
`physmessage_id` bigint(20) unsigned NOT NULL,
`headername_id` bigint(20) unsigned NOT NULL,
`headervalue_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`physmessage_id`,`headername_id`,`headervalue_id`),
KEY `physmessage_id` (`physmessage_id`),
KEY `headername_id` (`headername_id`),
KEY `headervalue_id` (`headervalue_id`),
KEY `physmessage_id_headername_id` (`physmessage_id`,`headername_id`),
KEY `physmessage_id_headervalue_id`
(`physmessage_id`,`headervalue_id`),
KEY `headername_id_headervalue_id` (`headername_id`,`headervalue_id`),
CONSTRAINT `dbmail_header_ibfk_1` FOREIGN KEY (`physmessage_id`)
REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE,
CONSTRAINT `dbmail_header_ibfk_2` FOREIGN KEY (`headername_id`)
REFERENCES `dbmail_headername` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE,
CONSTRAINT `dbmail_header_ibfk_3` FOREIGN KEY (`headervalue_id`)
REFERENCES `dbmail_headervalue` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
If it helps here is the subject view schema
DROP VIEW IF EXISTS `dbmail`.`dbmail_subjectfield`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `dbmail`.`dbmail_subjectfield` AS select
`m`.`physmessage_id` AS `physmessage_id`,`v`.`headervalue` AS
`subjectfield` from (((`dbmail_messages` `m` join `dbmail_header` `h`
on((`m`.`physmessage_id` = `h`.`physmessage_id`))) join
`dbmail_headername` `n` on((`h`.`headername_id` = `n`.`id`))) join
`dbmail_headervalue` `v` on((`h`.`headervalue_id` = `v`.`id`))) where
(`n`.`headername` = 'subject');
--
Daniel Schütze
CWA International
Balmoral House
9 John St
London
WC1N 2ES
Tel +44 (0) 7242 8444
(E) [email protected]
On 28-08-13 12:53, Daniel Schütze wrote:
Paul
Out of "idle" curiosity when I run a query on the views I get multiple
rows with the same physmessage_id which strikes me as peculiar but may
be expected.
If so then your schema is missing an index. Most likely the
dbmail_header table is missing the primary key.
Can you provide the schema for that table?
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
