Romaric,
> I'm running amavisd-new 2.4.2 + postgresql 8.4 with debian.
>
> Most of the SPAM are quarantined with no problem, but sometime I catch
> these errors :
>
> /usr/sbin/amavisd-new[29177]: (29177-02) TROUBLE in check_mail:
> quar+notif FAILED: temporarily unable to quarantine: 451 4.5.0 Storing
> to sql db as mail_id sDxh-Adfb11z failed: writing mail text to SQL
> failed: Error closing, flush: sql inserting text failed, sql exec:
> err=7, 22P02, DBD::Pg::st execute failed: ERREUR: Syntaxe en entrée
> invalide pour le type bytea at (eval 52) line 177, <GEN41> line 268. at
> (eval 52) line 68, <GEN41> line 268. at (eval 52) line
> 293., id=29177-02 at /usr/sbin/amavisd-new line 8682.
>
> or
>
> /usr/sbin/amavisd-new[29103]: (29103-04) writing mail text to SQL
> failed: Error closing, flush: sql inserting text failed, sql exec:
> err=7, 22P02, DBD::Pg::st execute failed: ERREUR: Syntaxe en entrée
> invalide pour le type bytea at (eval 52) line 177, <GEN32> line 454. at
> (eval 52) line 68, <GEN32> line 454.
> I think the SQL request try to insert text data in bytea field.
True, amavisd was using incorrect data type when supplying data for a field.
> I read a lot of documentation on internet, and I think I could solve
> this problem with upgrading my version of amavis to 2.4.5.
Indeed. This was fixed in amavisd-new-2.4.4. From release notes:
- PostgreSQL: when storing mail text to a quarantine use pg_type=PG_BYTEA
attribute on a field 'quarantine.mail_text'; previously the following
error could be reported:
451 4.5.0 Storing to sql db as mail_id ... failed:
writing mail text to SQL failed: Error closing, flush:
sql inserting text failed,
sql exec: err=7, 22P02, DBD::Pg::st execute failed:
ERROR: invalid input syntax for type bytea
- updated documentation in README.sql to suggest using data type 'bytea'
instead of inappropriate data type 'text' for a field quarantine.mail_text
To convert an existing table (when quarantining to SQL) please use:
ALTER TABLE quarantine ALTER mail_text TYPE bytea
USING decode(replace(mail_text,'\\','\\\\'),'escape');
If conversion of data type for 'quarantine.mail_text' is not done,
the following error will be reported when storing a message to a SQL
quarantine is attempted:
TROUBLE in check_mail: quar+notif FAILED:
temporarily unable to quarantine:
451 4.5.0 Storing to sql db as mail_id ... failed:
writing mail text to SQL failed: Error closing, flush:
sql inserting text failed, sql exec: err=7, DBD::Pg::st execute failed:
ERROR: column "mail_text" is of type text but expression is of type bytea
HINT: You will need to rewrite or cast the expression
If converting quarantine table is not desirable or possible in a short term,
it is possible to continue use existing SQL quarantine table without
conversion by specifying the following in amavisd.conf:
$sql_clause{'ins_quar'} =
"INSERT INTO quarantine (mail_id, chunk_ind, mail_text)".
" VALUES (?,?,encode(?,'escape'))";
$sql_clause{'sel_quar'} =
"SELECT decode(mail_text,'escape') FROM quarantine".
" WHERE mail_id=? ORDER BY chunk_ind";
This will allow PostgreSQL to convert data types on-the-fly, converting
octets (any byte) into escaped text, and vice versa when releasing from
a quarantine;
Problem reported by Justin Hillyard, correct data type suggested by
Nikola Milutinovic;
- MySQL: updated documentation in README.sql to suggest using data
type 'blob' instead of inappropriate data type 'text' for a field
quarantine.mail_text. To convert an existing table please use:
ALTER TABLE quarantine CHANGE mail_text mail_text blob;
Seems like MySQL does not complain on incompatibility between provided
data type and a data type of a field in table, but there are reports that
MySQL may silently truncate data which it finds violating character set
constraints, so conversion to 'blob' is highly recommended. Truncation
of quarantined message at an 8-bit character reported by Lubor Kolar.
> The problem is :
> the most recent package of amavis for debian is 2.4.2 :(
> (in Testing and Unstable too...)
>
> How can I fix this problem without upgrading ?
The change was nontrivial. Trying to backport it is more error-prone
and time consuming than upgrading amavisd (at least to 2.4.5).
Note that we are at 2.5.1 by now, looks like the world is leaving you
behind. Perhaps you should be looking for a more agile distribution :)
> I got another problem, when I release mail (ham) with amavisd-release,
> they come back in the spam quarantine. I use Mailzu 0.8RC1 to manage
> them but the probleme is not with mailzu.
> If I wait 1 hour or more, I can finally release them. Why ?
It could also be that all your amavisd services are busy and
have an active session with Postfix smtp services. In this case
(to facilitate testing and/or releasing from a quarantine),
you may want to run one additional amavisd child process
(set $max_servers in amavisd.conf to one more than is maxproc
in master.cf for a smtp service that is feeding amavisd,
typically named 'smtp-amavis' or 'amavisfeed' in more recent
documentation).
Mark
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
