Re: [Mailzu-users] DB Error: unknown error
Brought to you by:
trilexcom
From: Mark M. <Mar...@ij...> - 2010-09-28 14:32:48
|
On Monday 27 September 2010 19:51:34 Matt Hayes wrote: > On 9/27/2010 12:55 PM, Brian Evans wrote: > > On 9/27/2010 12:40 PM, Matt Hayes wrote: > >> This is what I get when I run the large query: > >> > >> ERROR 1052 (23000): Column 'content' in field list is ambiguous > > > > This error is the real problem. > > > > The query in question uses the msgs and msgrcpt tables. > > Only the msgs table should have a content field. This is no longer true with amavisd-new-2.7.0 . > I reread what you said and started looking at my tables. > msgs and msgrcpt both had content fields. > I just went through the queries in the DBEngine queries and set it from > content to msgs.content and all is well. the 2.7.0-pre* release notes: - when using SQL for logging/penpals: two fields need to be added to the table msgrcpt: msgrcpt.content and msgrcpt.rseqnum. Semantics of msgrcpt.content is similar to msgs.content, but reflects individual recipient's settings (e.g. when a message is both banned and spam, a recipient with banning tests disabled will see a message as spam, while other recipient of the same message will consider it banned). The added field may also simplify queries by third party applications. The field msgrcpt.rseqnum uniquely identifies recipients of each message, typically by assigning them sequential numbers starting with 1. The only purpose of this field is to make it possible to define a primary key for the table msgrcpt, which may be needed for some clustering/partitioning purposes. Amavisd itself does not need a primary key on this table. The following SQL directives can be used to add the two new fields: ALTER TABLE msgrcpt ADD content char(1) DEFAULT ' '; ALTER TABLE msgrcpt ADD rseqnum integer DEFAULT 0; If a primary key on table msgrcpt is needed for some reason, try something like the following: *MySQL: UPDATE msgrcpt SET rseqnum=1+floor(999999999*rand()) WHERE rseqnum=0; ALTER TABLE msgrcpt ADD PRIMARY KEY (partition_tag,mail_id,rseqnum); *PostgreSQL: UPDATE msgrcpt SET rseqnum=1+floor(999999999*random()) WHERE rseqnum=0; CREATE UNIQUE INDEX msgrcpt_idx_primary ON msgrcpt (partition_tag,mail_id,rseqnum); If keeping a customized copy of %sql_clause in a configuration file, the entry 'ins_rcp' will need to be adjusted accordingly. - SQL fields msgs.content and msgrcpt.content used to encode a content type CC_SPAMMY as 's', and CC_MTA as 't'. With default case-insensitive queries on a data type char it was not possible to distinguish between lowercase 's' (= CC_SPAMMY) and uppercase 'S' (= CC_SPAM), so the CC_SPAMMY is now encoded as 'Y', and CC_MTA as 'T' (just in case). Please adjust your management tools if necessary. Mark |