Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte (Page 1) — iRedMail Support

Topic: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

============ Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.8
- Linux/BSD distribution name and version: Ubuntu 16.04.4 LTS
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro?
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Hello,

Upgrade from 0.9.7 to 0.9.8 went perfect except the last step.
"Amavisd: Add new SQL column maddr.email_raw to store mail address without address extension"
I have removed "already exists" errors from log, because I have started script for the second time.

root@mail:~/iRedmail_0.9.9_upd# su - postgres
postgres@mail:~$ psql -U amavisd -d amavisd
psql (9.5.12)
Type "help" for help.
amavisd=>
amavisd=> sql> \i /home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql
psql:/home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql:3: ERROR:  invalid input syntax for type bytea
psql:/home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql:7: ERROR:  invalid input syntax for type bytea

What I can do with these "invalid input syntax for type bytea" errors? Do they affect on my e-mail server?

P.S. there is small typo in PostgreSQL command: "psql -d vmail < /root/iredmail.mysql"

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

Re: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

ZhangHuangbin wrote:

abcdzz wrote:

amavisd=> sql> \i /home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql

You should remove "sql>" in the command.
Also, can PostgreSQL daemon user access file under /home/mailserv/?

Thank you for your support.

I have made test dir, so everyone has access to this folder.
I have also removerd "sql>" in the command, but error remains the same.

root@mail:/# stat test
  File: 'test'
  Size: 4096            Blocks: 8          IO Block: 4096   directory
Device: fc00h/64512d    Inode: 8781825     Links: 2
Access: (0777/drwxrwxrwx)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2018-04-17 18:43:48.577440637 +0300
Modify: 2018-04-17 18:43:37.641281661 +0300
Change: 2018-04-17 18:43:44.593382716 +0300
 Birth: -
root@mail:/# su - postgres
postgres@mail:~$ psql -U amavisd -d amavisd
psql (9.5.12)
Type "help" for help.
amavisd=> \i /test/amavisd.pgsql
ALTER TABLE
psql:/test/amavisd.pgsql:3: ERROR:  invalid input syntax for type bytea
ALTER TABLE
ALTER TABLE
psql:/test/amavisd.pgsql:7: ERROR:  invalid input syntax for type bytea
ALTER TABLE
psql:/test/amavisd.pgsql:11: ERROR:  column "email_raw" of relation "maddr" already exists
psql:/test/amavisd.pgsql:14: ERROR:  relation "maddr_idx_email" already exists
psql:/test/amavisd.pgsql:15: ERROR:  relation "maddr_idx_email_raw" already exists
psql:/test/amavisd.pgsql:16: ERROR:  relation "maddr_idx_domain" already exists
CREATE FUNCTION
psql:/test/amavisd.pgsql:36: ERROR:  trigger "maddr_email_raw" for relation "maddr" already exists
amavisd=>

What else I could try to do?

Re: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

Could you try this SQL command instead?

\c amavisd;
ALTER TABLE msgs ALTER COLUMN subject TYPE bytea;
ALTER TABLE msgs ALTER COLUMN from_addr TYPE bytea;

Re: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

ZhangHuangbin wrote:

Could you try this SQL command instead?

\c amavisd;
ALTER TABLE msgs ALTER COLUMN subject TYPE bytea;
ALTER TABLE msgs ALTER COLUMN from_addr TYPE bytea;

Tried to use these commands and here is log:

amavisd=> \c amavisd;
You are now connected to database "amavisd" as user "amavisd".
amavisd=> ALTER TABLE msgs ALTER COLUMN subject TYPE bytea;
ERROR:  column "subject" cannot be cast automatically to type bytea
HINT:  You might need to specify "USING subject::bytea".
amavisd=> ALTER TABLE msgs ALTER COLUMN from_addr TYPE bytea;
ERROR:  column "from_addr" cannot be cast automatically to type bytea
HINT:  You might need to specify "USING from_addr::bytea".
amavisd=>

I have also tried to use the HINT, but is was also unsuccessful.

amavisd=> ALTER TABLE msgs ALTER COLUMN subject TYPE bytea USING subject::bytea;
ERROR:  default for column "subject" cannot be cast automatically to type bytea
amavisd=> ALTER TABLE msgs ALTER COLUMN from_addr TYPE bytea USING from_addr::bytea;
ERROR:  default for column "from_addr" cannot be cast automatically to type bytea

Re: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

I'm also experiencing the exact same issue with postgresql 8.4.20 under RHEL6. The latest suggestion did not work:

amavisd=# ALTER TABLE msgs ALTER COLUMN subject TYPE bytea USING subject::bytea DEFAULT '';
ERROR: syntax error at or near "DEFAULT"
LINE 1: ...ER COLUMN subject TYPE bytea USING subject::bytea DEFAULT ''...

Re: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

Try this:

ALTER TABLE msgs ALTER COLUMN subject DROP DEFAULT;
ALTER TABLE msgs ALTER COLUMN subject TYPE bytea USING decode(subject,'escape');
ALTER TABLE msgs ALTER COLUMN subject SET DEFAULT '';
ALTER TABLE msgs ALTER COLUMN from_addr DROP DEFAULT;
ALTER TABLE msgs ALTER COLUMN from_addr TYPE bytea USING decode(from_addr, 'escape');
ALTER TABLE msgs ALTER COLUMN from_addr SET DEFAULT '';

Re: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

abcdzz wrote:

============ Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.8
- Linux/BSD distribution name and version: Ubuntu 16.04.4 LTS
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro?
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Hello,

Upgrade from 0.9.7 to 0.9.8 went perfect except the last step.
"Amavisd: Add new SQL column maddr.email_raw to store mail address without address extension"
I have removed "already exists" errors from log, because I have started script for the second time.
root@mail:~/iRedmail_0.9.9_upd# su - postgres
postgres@mail:~$ psql -U amavisd -d amavisd
psql (9.5.12)
Type "help" for help.
amavisd=>
amavisd=> sql> \i /home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql
psql:/home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql:3: ERROR:  invalid input syntax for type bytea
psql:/home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql:7: ERROR:  invalid input syntax for type bytea
What I can do with these "invalid input syntax for type bytea" errors? Do they affect on my e-mail server?

P.S. there is small typo in PostgreSQL command: "psql -d vmail < /root/iredmail.mysql"

hi dwbotsch,
my iremail system environment is same as you(postgresql+ubuntu16.04), recently i upgrade from 0.9.7 to 0.9.8.
bug I didn't have the same problem.

if you backup sql database before you upgrade. Recover it, and try again.

if you not backup sql database, can you show current `msgs` table SQL structure ? use below command:
```
su - postgres
psql -U amavisd -d amavisd
\d msgs;
```

```

Re: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

abcdzz wrote:

============ Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.8
- Linux/BSD distribution name and version: Ubuntu 16.04.4 LTS
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro?
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Hello,

Upgrade from 0.9.7 to 0.9.8 went perfect except the last step.
"Amavisd: Add new SQL column maddr.email_raw to store mail address without address extension"
I have removed "already exists" errors from log, because I have started script for the second time.
root@mail:~/iRedmail_0.9.9_upd# su - postgres
postgres@mail:~$ psql -U amavisd -d amavisd
psql (9.5.12)
Type "help" for help.
amavisd=>
amavisd=> sql> \i /home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql
psql:/home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql:3: ERROR:  invalid input syntax for type bytea
psql:/home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql:7: ERROR:  invalid input syntax for type bytea
What I can do with these "invalid input syntax for type bytea" errors? Do they affect on my e-mail server?

P.S. there is small typo in PostgreSQL command: "psql -d vmail < /root/iredmail.mysql"

another way, you can delete amavis.msgs table.(data in this table is unimportant).then create this table use new sql script in 0.9.8 version. the create table sql script in this url

https://bitbucket.org/zhb/iredmail/src/ … es-153:206

Re: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

Just dropping the msgs table is going to be problematic b.c. of dependencies:

amavisd=# drop table msgs;
ERROR:  cannot drop table msgs because other objects depend on it
DETAIL:  constraint msgrcpt_mail_id_fkey on table msgrcpt depends on table msgs
constraint quarantine_mail_id_fkey on table quarantine depends on table msgs
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Not sure I really want to go there and try to recreate all the right pieces.

vzer.zhang wrote:

abcdzz wrote:
============ Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.8
- Linux/BSD distribution name and version: Ubuntu 16.04.4 LTS
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro?
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Hello,

Upgrade from 0.9.7 to 0.9.8 went perfect except the last step.
"Amavisd: Add new SQL column maddr.email_raw to store mail address without address extension"
I have removed "already exists" errors from log, because I have started script for the second time.
root@mail:~/iRedmail_0.9.9_upd# su - postgres
postgres@mail:~$ psql -U amavisd -d amavisd
psql (9.5.12)
Type "help" for help.
amavisd=>
amavisd=> sql> \i /home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql
psql:/home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql:3: ERROR:  invalid input syntax for type bytea
psql:/home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql:7: ERROR:  invalid input syntax for type bytea
What I can do with these "invalid input syntax for type bytea" errors? Do they affect on my e-mail server?

P.S. there is small typo in PostgreSQL command: "psql -d vmail < /root/iredmail.mysql"
another way, you can delete amavis.msgs table.(data in this table is unimportant).then create this table use new sql script in 0.9.8 version. the create table sql script in this url

https://bitbucket.org/zhb/iredmail/src/ … es-153:206

Re: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

This is what amavisd.msgs currently looks like:

amavisd=# \d msgs;
                           Table "public.msgs"
    Column     |           Type           |           Modifiers           
---------------+--------------------------+-------------------------------
 partition_tag | integer                  | default 0
 mail_id       | character varying(12)    | not null
 secret_id     | character varying(12)    | default ''::character varying
 am_id         | character varying(20)    | not null
 time_num      | integer                  | not null
 time_iso      | timestamp with time zone | not null
 sid           | integer                  | not null
 policy        | character varying(255)   | default ''::character varying
 client_addr   | character varying(255)   | default ''::character varying
 size          | integer                  | not null
 content       | character(1)             | 
 quar_type     | character(1)             | 
 quar_loc      | character varying(255)   | default ''::character varying
 dsn_sent      | character(1)             | 
 spam_level    | real                     | 
 message_id    | character varying(255)   | default ''::character varying
 from_addr     | character varying(255)   | default ''::character varying
 subject       | character varying(255)   | default ''::character varying
 host          | character varying(255)   | not null
 originating   | character(1)             | not null default ' '::bpchar
Indexes:
    "msgs_pkey" PRIMARY KEY, btree (mail_id)
    "idx_msgs_quar_type" btree (quar_type)
    "msgs_idx_mess_id" btree (message_id)
    "msgs_idx_sid" btree (sid)
    "msgs_idx_spam_level" btree (spam_level)
    "msgs_idx_time_iso" btree (time_iso)
    "msgs_idx_time_num" btree (time_num)
Check constraints:
    "msgs_sid_check" CHECK (sid >= 0)
    "msgs_size_check" CHECK (size >= 0)
    "msgs_time_num_check" CHECK (time_num >= 0)
Foreign-key constraints:
    "msgs_sid_fkey" FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
Referenced by:
    TABLE "msgrcpt" CONSTRAINT "msgrcpt_mail_id_fkey" FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
    TABLE "quarantine" CONSTRAINT "quarantine_mail_id_fkey" FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE

vzer.zhang wrote:

abcdzz wrote:
============ Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.8
- Linux/BSD distribution name and version: Ubuntu 16.04.4 LTS
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro?
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Hello,

Upgrade from 0.9.7 to 0.9.8 went perfect except the last step.
"Amavisd: Add new SQL column maddr.email_raw to store mail address without address extension"
I have removed "already exists" errors from log, because I have started script for the second time.
root@mail:~/iRedmail_0.9.9_upd# su - postgres
postgres@mail:~$ psql -U amavisd -d amavisd
psql (9.5.12)
Type "help" for help.
amavisd=>
amavisd=> sql> \i /home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql
psql:/home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql:3: ERROR:  invalid input syntax for type bytea
psql:/home/mailserv/iRedmail_0.9.9_upd/amavisd.pgsql:7: ERROR:  invalid input syntax for type bytea
What I can do with these "invalid input syntax for type bytea" errors? Do they affect on my e-mail server?

P.S. there is small typo in PostgreSQL command: "psql -d vmail < /root/iredmail.mysql"
hi dwbotsch,
my iremail system environment is same as you(postgresql+ubuntu16.04), recently i upgrade from 0.9.7 to 0.9.8.
bug I didn't have the same problem.

if you backup sql database before you upgrade. Recover it, and try again.

if you not backup sql database, can you show current `msgs` table SQL structure ? use below command:
```
su - postgres
psql -U amavisd -d amavisd
\d msgs;
```

below， is `msgs` table SQL structure in 0.9.7 version:
```
amavisd=> \d msgs;
Table "public.msgs"
Column | Type | Modifiers
---------------+--------------------------+-------------------------------
partition_tag | integer | not null default 0
mail_id | bytea | not null
secret_id | bytea | default ''::bytea
am_id | character varying(20) | not null
time_num | integer | not null
time_iso | timestamp with time zone | not null
sid | integer | not null
policy | character varying(255) | default ''::character varying
client_addr | character varying(255) | default ''::character varying
size | integer | not null
originating | character(1) | not null default ' '::bpchar
content | character(1) |
quar_type | character(1) |
quar_loc | character varying(255) | default ''::character varying
dsn_sent | character(1) |
spam_level | real |
message_id | character varying(255) | default ''::character varying
from_addr | character varying(255) | default ''::character varying
subject | character varying(255) | default ''::character varying
host | character varying(255) | not null
Indexes:
"msgs_pkey" PRIMARY KEY, btree (partition_tag, mail_id)
"msgs_idx_content" btree (content)
"msgs_idx_mail_id" btree (mail_id)
"msgs_idx_mess_id" btree (message_id)
"msgs_idx_quar_type" btree (quar_type)
"msgs_idx_sid" btree (sid)
"msgs_idx_spam_level" btree (spam_level)
"msgs_idx_time_iso" btree (time_iso)
"msgs_idx_time_num" btree (time_num)
Check constraints:
"msgs_sid_check" CHECK (sid >= 0)
"msgs_size_check" CHECK (size >= 0)
"msgs_time_num_check" CHECK (time_num >= 0)

```

Re: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

ZhangHuangbin wrote:

Will it be easier to recreate all tables in amavisd sql db?

I don't know. I just cleared out years of data with the tools / amavisd clean script. So maybe there is some data in there that did not translate.

I don't want to just break amavisd ... is there a procedure for (re) creating all the amavisd tables?

Thanks.

Re: Upgrade to 0.9.8 Amavisd: ERROR: invalid input syntax for type byte

- Download amavisd sql db template file: https://bitbucket.org/zhb/iredmail/src/ … s/amavisd/
- Drop all tables in Amavisd db manually.
- Import the template file to amavisd db as the "amavisd" user.
- Check the ownership of sql tables with PGSQL command "\d", make sure all tables are owned by "amavisd" user. If not, use command like below to change it.

ALTER TABLE <table-name> OWNER TO amavisd;

Generated in 0.012 seconds (77% PHP - 23% DB) with 8 queries