upgrade amavisd เป็น version 2.7.0 แล้วเกิดปัญหา query DB ไม่ได้ [solved]

[golfreeze]

มีปัญหาเรื่องการ query ข้อมูล หลังจาก upgrade amavisd เป็น version 2.7.0
เพราะมีการเพิ่ม filed ใหม่ลงไปยัง table

ALTER TABLE msgrcpt ADD rseqnum     integer  DEFAULT 0   NOT NULL;
ALTER TABLE msgrcpt ADD content     char(1)  DEFAULT ' ' NOT NULL;
ALTER TABLE msgrcpt ADD is_local    char(1)  DEFAULT ' ' NOT NULL;
ALTER TABLE msgs    ADD originating char(1)  DEFAULT ' ' NOT NULL;

ดังนี้ครับผม ส่งผลให้ เวลา summary ข้อมูลใน mailzu (quarantine app) ไม่สามารถใช้งานได้ขึ้น error
"There was an error executing your query: "


Reference จาก : http://lists.amavis.org/pipermail/amavis-users/2011-November/001054.html


Gary,

> I updated an SQL database per release notes in 2.7.0
> http://www.amavis.org/release-notes.txt :
>
>  The following SQL directives can be used to add these new fields:
>    ALTER TABLE msgrcpt ADD rseqnum     integer  DEFAULT 0   NOT NULL;
>    ALTER TABLE msgrcpt ADD content     char(1)  DEFAULT ' ' NOT NULL;
>    ALTER TABLE msgrcpt ADD is_local    char(1)  DEFAULT ' ' NOT NULL;
>    ALTER TABLE msgs    ADD originating char(1)  DEFAULT ' ' NOT NULL;
>
> and now when initially logging into Malzu, I was getting an error on
> the Summary page "There was an error executing your query:". I looked
> in the mailzu log and executed the query manually and got:
>
> ERROR 1052 (23000): Column 'content' in field list is ambiguous
>
> So, for the benefit of others,  the fix was to make it unambiguous in
> DBEngine.class.php - also one other small fix is included here:

Thanks!

> cp DBEngine.class.php DBEngine.class.php.original
>
> sed -i 's/dbtype/dbType/' DBEngine.class.php
> sed -i 's/COUNT(content)/COUNT(msgs.content)/' DBEngine.class.php
> sed -i 's/WHERE content=/WHERE msgs.content=/' DBEngine.class.php

In case of multi-recipient mail where some recipients are spam lovers
or have different tag2_level, it may happen that the same message
is for example classified spam for one, and clean for somebody else.
In this case not all of msgrcpt.content will have the same value
as per-message content summary in msgs.content.

When giving per-recipient queries, the more appropriate field to test
is msgrcpt.content.  When giving a summarized per-message view
or statistics, it may be easier to use the msgs.content field.

> Any query where 'content' is used will give the error in MySQL. I
> noticed there are a lot of examples of queries that use the plain
> 'content' field in both http://www.amavis.org/README.sql-mysql.txt and
> http://www.amavis.org/README.sql-pg.txt. It appears that neither of
> these documents were updated to reflect the changes mentioned in
> release notes.

Thanks, fixed.  In the first two SELECT examples the 'content'
should be replaced by 'msgrcpt.content'.

For other examples which do not join tables msgs and msgrcpt,
the qualification is not necessary, as a field name 'content'
is unambiguous in a context of a single table.

> Possibly an unfortunate choice of field name was added
> to msgrcpt?

The choice was intentional as this is mostly the same kind of
information, but at different levels (per-message vs. per-recipient).
But yes, I forgot to update the examples.

  Mark