1 Topic by mrkimile

  • mrkimile
  • Member
  • Offline
  • Registered: 2014-03-12
  • Posts: 9

Topic: ERROR: invalid input syntax for type bytea (iRedMail-0.9.9 fresh inst

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 0.9.9 PGSQL edition.
- Deployed with downloadable installer
- Ubuntu 18.04 LTS
- Store mail accounts in PGSQL
- Apache
- No iRedAadmin-Pro
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

I've done a fresh install of a iReadMail-0.9.9 via the downloadable installer. On some of the messages I receive I get errors like this in a /var/log/mail.log file:

Apr 28 19:13:22 iredmail amavis[7830]: (07830-11) (!)WARN save_info_final: sql exec: err=7, 22P02, DBD::Pg::st execute failed: ERROR:  invalid input syntax for type bytea at (eval 108) line 173.
Apr 28 19:13:25 iredmail amavis[7830]: (07830-11) (!)WARN save_info_final: sql exec: err=7, 22P02, DBD::Pg::st execute failed: ERROR:  invalid input syntax for type bytea at (eval 108) line 173.
Apr 28 19:13:30 iredmail amavis[7830]: message repeated 3 times: [ (07830-11) (!)WARN save_info_final: sql exec: err=7, 22P02, DBD::Pg::st execute failed: ERROR:  invalid input syntax for type bytea at (eval 108) line 173.]
Apr 28 19:13:30 iredmail amavis[7830]: (07830-11) (!!)ERROR sql_storage: too many retries on storing final, info not saved

I have enabled full query logging in postgres, and here is what the error looks like in Postgres:

2019-04-28 19:13:30.687 UTC [18235] amavisd@amavisd LOG:  duration: 0.103 ms  statement: SET NAMES 'utf8'
2019-04-28 19:13:30.687 UTC [18235] amavisd@amavisd LOG:  duration: 0.023 ms  statement: begin
2019-04-28 19:13:30.688 UTC [18235] amavisd@amavisd LOG:  duration: 0.551 ms  parse <unnamed>: INSERT INTO msgrcpt (partition_tag, mail_id, rseqnum, rid, is_local, content, ds, rs, bl, wl, bspam_level, smtp_resp) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
2019-04-28 19:13:30.689 UTC [18235] amavisd@amavisd LOG:  duration: 1.414 ms  bind <unnamed>: INSERT INTO msgrcpt (partition_tag, mail_id, rseqnum, rid, is_local, content, ds, rs, bl, wl, bspam_level, smtp_resp) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
2019-04-28 19:13:30.689 UTC [18235] amavisd@amavisd DETAIL:  parameters: $1 = '0', $2 = 'jd_gsiZ8ne8O', $3 = '1', $4 = '2', $5 = 'Y', $6 = 'C', $7 = 'P', $8 = ' ', $9 = 'N', $10 = 'N', $11 = '-1', $12 = '250 2.0.0 from MTA(smtp:[127.0.0.1]:10025): 250 2.0.0 Ok: queued as 44scrf47MXzGgVH'
2019-04-28 19:13:30.690 UTC [18235] amavisd@amavisd LOG:  duration: 0.320 ms  execute <unnamed>: INSERT INTO msgrcpt (partition_tag, mail_id, rseqnum, rid, is_local, content, ds, rs, bl, wl, bspam_level, smtp_resp) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
2019-04-28 19:13:30.690 UTC [18235] amavisd@amavisd DETAIL:  parameters: $1 = '0', $2 = 'jd_gsiZ8ne8O', $3 = '1', $4 = '2', $5 = 'Y', $6 = 'C', $7 = 'P', $8 = ' ', $9 = 'N', $10 = 'N', $11 = '-1', $12 = '250 2.0.0 from MTA(smtp:[127.0.0.1]:10025): 250 2.0.0 Ok: queued as 44scrf47MXzGgVH'
2019-04-28 19:13:30.691 UTC [18235] amavisd@amavisd LOG:  duration: 0.440 ms  parse <unnamed>: UPDATE msgs SET content=$1, quar_type=$2, quar_loc=$3, dsn_sent=$4, spam_level=$5, message_id=$6, from_addr=$7, subject=$8, client_addr=$9, originating=$10 WHERE partition_tag=$11 AND mail_id=$12
2019-04-28 19:13:30.691 UTC [18235] amavisd@amavisd ERROR:  invalid input syntax for type bytea
2019-04-28 19:13:30.691 UTC [18235] amavisd@amavisd STATEMENT:  UPDATE msgs SET content=$1, quar_type=$2, quar_loc=$3, dsn_sent=$4, spam_level=$5, message_id=$6, from_addr=$7, subject=$8, client_addr=$9, originating=$10 WHERE partition_tag=$11 AND mail_id=$12
2019-04-28 19:13:30.692 UTC [18235] amavisd@amavisd LOG:  duration: 0.022 ms  statement: rollback

Now, this seems like an issue with Amavis that is not compabible with postgres10 that is in Bionic. I will look more into this, but still wanted to share this issue here on the forum (with the hope that someone might already have a solution for this! big_smile )

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: ERROR: invalid input syntax for type bytea (iRedMail-0.9.9 fresh inst

Do you have a sample email which can steadily trigger this error? so that i can test it locally and hopefully fix it.

3 Reply by mrkimile

  • mrkimile
  • Member
  • Offline
  • Registered: 2014-03-12
  • Posts: 9

Re: ERROR: invalid input syntax for type bytea (iRedMail-0.9.9 fresh inst

Hi, Zhang. I do, I can email it to you, but it's actually a message from psql-hackers mailing list, this particular one:

https://www.postgresql.org/message-id/a … 815@lancre

The body has funky characters (building that table), but the body is not inserted into the database. When I look trough postgres logs, I see this, again:

2019-04-29 06:48:38.722 UTC [31315] amavisd@amavisd LOG:  duration: 0.017 ms  statement: rollback
2019-04-29 06:48:39.732 UTC [31316] amavisd@amavisd LOG:  duration: 0.101 ms  statement: SET NAMES 'utf8'
2019-04-29 06:48:39.733 UTC [31316] amavisd@amavisd LOG:  duration: 0.022 ms  statement: begin
2019-04-29 06:48:39.733 UTC [31316] amavisd@amavisd LOG:  duration: 0.553 ms  parse <unnamed>: INSERT INTO msgrcpt (partition_tag, mail_id, rseqnum, rid, is_local, content, ds, rs, bl, wl, bspam_level, smtp_resp) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
2019-04-29 06:48:39.733 UTC [31316] amavisd@amavisd LOG:  duration: 0.124 ms  bind <unnamed>: INSERT INTO msgrcpt (partition_tag, mail_id, rseqnum, rid, is_local, content, ds, rs, bl, wl, bspam_level, smtp_resp) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
2019-04-29 06:48:39.733 UTC [31316] amavisd@amavisd DETAIL:  parameters: $1 = '0', $2 = 'XcZp8EstISXq', $3 = '1', $4 = '2', $5 = 'Y', $6 = 'C', $7 = 'P', $8 = ' ', $9 = 'N', $10 = 'N', $11 = '-1', $12 = '250 2.0.0 from MTA(smtp:[127.0.0.1]:10025): 250 2.0.0 Ok: queued as 44swGk4PdNzGgb4'
2019-04-29 06:48:39.734 UTC [31316] amavisd@amavisd LOG:  duration: 0.328 ms  execute <unnamed>: INSERT INTO msgrcpt (partition_tag, mail_id, rseqnum, rid, is_local, content, ds, rs, bl, wl, bspam_level, smtp_resp) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
2019-04-29 06:48:39.734 UTC [31316] amavisd@amavisd DETAIL:  parameters: $1 = '0', $2 = 'XcZp8EstISXq', $3 = '1', $4 = '2', $5 = 'Y', $6 = 'C', $7 = 'P', $8 = ' ', $9 = 'N', $10 = 'N', $11 = '-1', $12 = '250 2.0.0 from MTA(smtp:[127.0.0.1]:10025): 250 2.0.0 Ok: queued as 44swGk4PdNzGgb4'
2019-04-29 06:48:39.735 UTC [31316] amavisd@amavisd LOG:  duration: 0.306 ms  parse <unnamed>: UPDATE msgs SET content=$1, quar_type=$2, quar_loc=$3, dsn_sent=$4, spam_level=$5, message_id=$6, from_addr=$7, subject=$8, client_addr=$9, originating=$10 WHERE partition_tag=$11 AND mail_id=$12
2019-04-29 06:48:39.735 UTC [31316] amavisd@amavisd ERROR:  invalid input syntax for type bytea
2019-04-29 06:48:39.735 UTC [31316] amavisd@amavisd STATEMENT:  UPDATE msgs SET content=$1, quar_type=$2, quar_loc=$3, dsn_sent=$4, spam_level=$5, message_id=$6, from_addr=$7, subject=$8, client_addr=$9, originating=$10 WHERE partition_tag=$11 AND mail_id=$12
2019-04-29 06:48:39.735 UTC [31316] amavisd@amavisd LOG:  duration: 0.018 ms  statement: rollback


So, the first INSERT goes ok. Insert is populating the msgrcpt table, where only bytea column is mail_id.
Field $12 is smtp_resp column, and the value saved there is: "250 2.0.0 from MTA(smtp:[127.0.0.1]:10025): 250 2.0.0 Ok: queued as 44swGk4PdNzGgb4"

Now, the UPDATE right after updates table msgs. $12 is still that same string as in INSERT, but it is passed to WHERE part of the UPDATE, for mail_id. As mail_id is bytea, postgres won't do conversion from text, and it yields an error.
I see that the mail_id XcZp8EstISXq, which is passed as $2 in the INSERT statement should actually be $12 in the UPDATE satement.

Let me know if you still need the full message file from the mailbox on my server.

4 Reply by mrkimile

  • mrkimile
  • Member
  • Offline
  • Registered: 2014-03-12
  • Posts: 9

Re: ERROR: invalid input syntax for type bytea (iRedMail-0.9.9 fresh inst

Hm, actually, what I wrote is not correct. The first list of parameters is related to 'bind' and the second one is related to 'execute'. I don't have the list of UPDATE parameters here.

I will try to hack in extra debugging into amavisd-new and see what exactly causes this postgres issue. It's not happening very often though.

5 Reply by mrkimile

  • mrkimile
  • Member
  • Offline
  • Registered: 2014-03-12
  • Posts: 9

Re: ERROR: invalid input syntax for type bytea (iRedMail-0.9.9 fresh inst

Found it!

So, I hacked amavisd to show me parameters of commands being executed. I also noticed that all the errors I get are from emails that have "\dt+'" in subject.

And really, look:

amavisd=# select 'Re: [PATCH v5] Show detailed table persistence in \dt+'::bytea;
ERROR:  invalid input syntax for type bytea
LINE 1: select 'Re: [PATCH v5] Show detailed table persistence in \d...
               ^
amavisd=#


This is postgres10. Happens on xenial with postgres9.5 too, also with postgres11.

It's easy to reproduce this, just send an email that has backslash in the subject, followed by something (like \w, or \dt, or...).

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: ERROR: invalid input syntax for type bytea (iRedMail-0.9.9 fresh inst

mrkimile wrote:

It's easy to reproduce this, just send an email that has backslash in the subject, followed by something (like \w, or \dt, or...).

Could you give me a sample subject which can steadily trigger this issue?

7 Reply by mrkimile

  • mrkimile
  • Member
  • Offline
  • Registered: 2014-03-12
  • Posts: 9

Re: ERROR: invalid input syntax for type bytea (iRedMail-0.9.9 fresh inst

Any subject that has \  (backslash) in it.

8 Reply by mrkimile

  • mrkimile
  • Member
  • Offline
  • Registered: 2014-03-12
  • Posts: 9

Re: ERROR: invalid input syntax for type bytea (iRedMail-0.9.9 fresh inst

I think I fixed this - I changed the column type for msgs.subject from bytea to text, and now I no longer get this error.

When full query is enabled in postgres, now I can see that the UPDATE that was failing before - finishes:


2019-04-30 13:43:54.499 UTC [7856] amavisd@amavisd LOG:  duration: 0.221 ms  bind dbdpg_p6755_9: UPDATE msgs SET content=$1, quar_type=$2, quar_loc=$3, dsn_sent=$4, spam_level=$5, message_id=$6, from_addr=$7, subject=$8, client_addr=$9, originating=$10 WHERE partition_tag=$11 AND mail_id=$12
2019-04-30 13:43:54.499 UTC [7856] amavisd@amavisd DETAIL:  parameters: $1 = 'C', $2 = ' ', $3 = '', $4 = 'N', $5 = '0', $6 = '<20190430134353.57846A09B2@www.ubuntu-hr.org>', $7 = 'mmike@ubuntu-hr.org (Mario Splivalo)', $8 = 'Wrong subject \dt+', $9 = '161.53.50.215', $10 = 'N', $11 = '0', $12 = 'njS69kEwxaOe'
2019-04-30 13:43:54.499 UTC [7856] amavisd@amavisd LOG:  duration: 0.202 ms  execute dbdpg_p6755_9: UPDATE msgs SET content=$1, quar_type=$2, quar_loc=$3, dsn_sent=$4, spam_level=$5, message_id=$6, from_addr=$7, subject=$8, client_addr=$9, originating=$10 WHERE partition_tag=$11 AND mail_id=$12
2019-04-30 13:43:54.499 UTC [7856] amavisd@amavisd DETAIL:  parameters: $1 = 'C', $2 = ' ', $3 = '', $4 = 'N', $5 = '0', $6 = '<20190430134353.57846A09B2@www.ubuntu-hr.org>', $7 = 'mmike@ubuntu-hr.org (Mario Splivalo)', $8 = 'Wrong subject \dt+', $9 = '161.53.50.215', $10 = 'N', $11 = '0', $12 = 'njS69kEwxaOe'
2019-04-30 13:43:54.501 UTC [7856] amavisd@amavisd LOG:  duration: 1.159 ms  statement: commit

I've also checked amavisd documentation and initialization files, it uses varchar(255) in both postgres and mysql for msgs.sub ject column. Also, I see that with iRedMail some columns are varbinary (mysql) or bytea (postgres), where in upstream amavisd those are vachar.
I see that the change was made to accomodate for Unicode emojis, but I also tried sending an email with emojis in subject, and 'text' colum type treated them as expected:

amavisd=# select subject from msgs where mail_id = 'UJVAglBP4HPf';
                   subject                   
---------------------------------------------
Emoji subject: ð\u009F\u0098\u0088ð\u009F¤ 
(1 row)

Is this ok?

9 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: ERROR: invalid input syntax for type bytea (iRedMail-0.9.9 fresh inst

Seems fine. I will try to merge this in next iRedMail release.

But i think this is a bug of Amavisd (PGSQL relevant), could you help try with original Amavisd sql column type and report this to Amavisd mailing list (if you can reproduce it)?