Mailing List Archive: DBMail: users

DBMAIL error on SELECT

Index | Next | Previous | View Threaded

randomfire at gmail

Sep 9, 2009, 5:33 PM

Post #1 of 17 (665 views)
Permalink

DBMAIL error on SELECT

Hi, I recently upgraded my dbmail to 2.2.11 and decided to create a new
email account/mailbox to make sure everything is working correctly. I sent
myself an email and retrieved it via outlook using dbmail-imapd. Below is
the error I received in dbmail.err

The email displays correctly, but wondering why I'm getting this error. My
email accounts that existed before I upgrade don't cause this error. I
should also mention that I also converted database type from SQL_ASCII to
UTF8.

Thanks,

/var/log/dbmail.err

Sep 10 00:39:49 node1.the.matrix dbmail-imapd[4957]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr, v.header
value, k.messageblk FROM dbmail_messageblks k JOIN dbmail_physmessage p ON
k.physmessage_id=p.id JOIN dbmail_messages m ON m.physmessage
_id=p.id JOIN dbmail_headervalue v ON v.physmessage_id=p.id WHERE
m.mailbox_idnr=53 AND m.status in (0,1) HAVING SUBSTRING(v.headervalue
,0,255) ILIKE '%multipart/encrypted%' OR k.messageblk ILIKE
'%multipart/encrypted%'] : [.ERROR: operator does not exist: bytea ~~* unkno
wn
LINE 1: ...55) ILIKE '%multipart/encrypted%' OR k.messageblk ILIKE '%mu...
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
]

Sep 10 00:39:49 node1.the.matrix dbmail-imapd[4957]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query

--
David

vulture at netvulture

Sep 9, 2009, 8:24 PM

Post #2 of 17 (643 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

What database server and version are you using? This seems like an older
database not able to handle that query.

-Jon

David Young wrote:
> Hi, I recently upgraded my dbmail to 2.2.11 and decided to create a
> new email account/mailbox to make sure everything is working
> correctly. I sent myself an email and retrieved it via outlook using
> dbmail-imapd. Below is the error I received in dbmail.err
>
> The email displays correctly, but wondering why I'm getting this
> error. My email accounts that existed before I upgrade don't cause
> this error. I should also mention that I also converted database type
> from SQL_ASCII to UTF8.
>
> Thanks,
>
> /var/log/dbmail.err
>
> Sep 10 00:39:49 node1.the.matrix dbmail-imapd[4957]: Error:[sql]
> dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr, v.header
> value, k.messageblk FROM dbmail_messageblks k JOIN dbmail_physmessage
> p ON k.physmessage_id=p.id <http://p.id/> JOIN dbmail_messages m ON
> m..physmessage
> _id=p.id <http://p.id/> JOIN dbmail_headervalue v ON
> v.physmessage_id=p.id <http://p.id/> WHERE m.mailbox_idnr=53 AND
> m.status in (0,1) HAVING SUBSTRING(v.headervalue
> ,0,255) ILIKE '%multipart/encrypted%' OR k.messageblk ILIKE
> '%multipart/encrypted%'] : [.ERROR: operator does not exist: bytea ~~*
> unkno
> wn
> LINE 1: ...55) ILIKE '%multipart/encrypted%' OR k.messageblk ILIKE '%mu...
> ^
> HINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
> ]
>
> Sep 10 00:39:49 node1.the.matrix dbmail-imapd[4957]: Error:[mailbox]
> dbmail-mailbox.c,mailbox_search(+1374): could not execute query
>
>
>
> --
> David
>
> --
> Scanned for viruses and dangerous content by *MailScanner*
> <http://www.mailscanner.info/>
> ------------------------------------------------------------------------
>
> _______________________________________________
> DBmail mailing list
> DBmail[at]dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>

--
Scanned for viruses and dangerous content by MailScanner

_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

randomfire at gmail

Sep 9, 2009, 8:33 PM

Post #3 of 17 (646 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

Running CentOS 5.2, Postgres 8.3.7.

On Wed, Sep 9, 2009 at 8:24 PM, Jonathan Feally <vulture[at]netvulture.com>wrote:

> What database server and version are you using? This seems like an older
> database not able to handle that query.
>
> -Jon
>
> David Young wrote:
> > Hi, I recently upgraded my dbmail to 2.2.11 and decided to create a
> > new email account/mailbox to make sure everything is working
> > correctly. I sent myself an email and retrieved it via outlook using
> > dbmail-imapd. Below is the error I received in dbmail.err
> >
> > The email displays correctly, but wondering why I'm getting this
> > error. My email accounts that existed before I upgrade don't cause
> > this error. I should also mention that I also converted database type
> > from SQL_ASCII to UTF8.
> >
> > Thanks,
> >
> > /var/log/dbmail.err
> >
> > Sep 10 00:39:49 node1.the.matrix dbmail-imapd[4957]: Error:[sql]
> > dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr, v.header
> > value, k.messageblk FROM dbmail_messageblks k JOIN dbmail_physmessage
> > p ON k.physmessage_id=p.id <http://p.id/> JOIN dbmail_messages m ON
> > m..physmessage
> > _id=p.id <http://p.id/> JOIN dbmail_headervalue v ON
> > v.physmessage_id=p.id <http://p.id/> WHERE m.mailbox_idnr=53 AND
> > m.status in (0,1) HAVING SUBSTRING(v.headervalue
> > ,0,255) ILIKE '%multipart/encrypted%' OR k.messageblk ILIKE
> > '%multipart/encrypted%'] : [.ERROR: operator does not exist: bytea ~~*
> > unkno
> > wn
> > LINE 1: ...55) ILIKE '%multipart/encrypted%' OR k.messageblk ILIKE
> '%mu...
> > ^
> > HINT: No operator matches the given name and argument type(s). You
> > might need to add explicit type casts.
> > ]
> >
> > Sep 10 00:39:49 node1.the.matrix dbmail-imapd[4957]: Error:[mailbox]
> > dbmail-mailbox.c,mailbox_search(+1374): could not execute query
> >
> >
> >
> > --
> > David
> >
> > --
> > Scanned for viruses and dangerous content by *MailScanner*
> > <http://www.mailscanner.info/>
> > ------------------------------------------------------------------------
> >
> > _______________________________________________
> > DBmail mailing list
> > DBmail[at]dbmail.org
> > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
> >
>
>
> --
> Scanned for viruses and dangerous content by MailScanner
>
> _______________________________________________
> DBmail mailing list
> DBmail[at]dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>

--
David

michael.monnerie at is

Sep 10, 2009, 1:32 AM

Post #4 of 17 (645 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

On Donnerstag 10 September 2009 David Young wrote:
> Sep 10 00:39:49 node1.the.matrix dbmail-imapd[4957]: Error:[sql]
> dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
> v.header value, k.messageblk FROM dbmail_messageblks k JOIN
> dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
> ON m.physmessage _id=p.id JOIN dbmail_headervalue v ON
> v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
> HAVING SUBSTRING(v.headervalue ,0,255) ILIKE '%multipart/encrypted%'
> OR k.messageblk ILIKE
> '%multipart/encrypted%'] : [.ERROR: �operator does not exist: bytea
> ~~* unkno wn
> LINE 1: ...55) ILIKE '%multipart/encrypted%' OR k.messageblk ILIKE
> '%mu... ^ HINT: �No operator matches the given name and argument
> type(s). You might need to add explicit type casts.
> ]
>
> Sep 10 00:39:49 node1.the.matrix dbmail-imapd[4957]: Error:[mailbox]
> dbmail-mailbox.c,mailbox_search(+1374): could not execute query

PostgreSQL 8.3 issue. They started to have tougher type casts. The
message explains that "You might need to add explicit type casts.". I
still don't have it, so I don't know exactly how to cast it. Maybe

OR k.messageblk::varchar ILIKE '%multipart/encrypted%'

would solve the problem, but you can't cast from bytea to varchar, at
least in 8.2. Asking on the PostgreSQL list should help, I'll do that.

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4

Attachments:

signature.asc (0.19 KB)

michael.monnerie at is

Sep 10, 2009, 8:23 AM

Post #5 of 17 (643 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

On Donnerstag 10 September 2009 Michael Monnerie wrote:
> PostgreSQL 8.3 issue

Interesting, I got this answer from a PG dev:
************
There has *never* been a Postgres release that accepted bytea ILIKE
something. I'm not sure what you were really doing before, but that
wasn't it.
************
So, this is the query you posted stripped down:
SELECT k.messageblk FROM dbmail_messageblks k WHERE
k.messageblk ILIKE '%multipart/encrypted%';

He's right, that query doesn't work on PG 8.1 either.

How did you make dbmail run such a query? Looks like via a search, but I
guess that never worked and would be a bug then. So please, how can I
reproduce that query?

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4

_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

randomfire at gmail

Sep 10, 2009, 9:56 AM

Post #6 of 17 (640 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

First I created a new user on dbmail with dbmail-user with the following
options (-a -s -w -p MD5-HASH). DBMAIL created the new user/mailbox
successfully.

Next, I created an IMAP mailbox using Outlook 2003 SP3. Connected to
dbmail-imapd. Outlook showed a single mailbox (Inbox). I sent an email to
the new mailbox. The error occurred when I selected the Inbox and the new
message.

David

On Thu, Sep 10, 2009 at 8:23 AM, Michael Monnerie <
michael.monnerie[at]is.it-management.at> wrote:

> On Donnerstag 10 September 2009 Michael Monnerie wrote:
> > PostgreSQL 8.3 issue
>
> Interesting, I got this answer from a PG dev:
> ************
> There has *never* been a Postgres release that accepted bytea ILIKE
> something. I'm not sure what you were really doing before, but that
> wasn't it.
> ************
> So, this is the query you posted stripped down:
> SELECT k.messageblk FROM dbmail_messageblks k WHERE
> k.messageblk ILIKE '%multipart/encrypted%';
>
> He's right, that query doesn't work on PG 8.1 either.
>
> How did you make dbmail run such a query? Looks like via a search, but I
> guess that never worked and would be a bug then. So please, how can I
> reproduce that query?
>
> mfg zmi
> --
> // Michael Monnerie, Ing.BSc ----- http://it-management.at
> // Tel: 0660 / 415 65 31 .network.your.ideas.
> // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
> // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
> // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
>
> _______________________________________________
> DBmail mailing list
> DBmail[at]dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>

--
David

randomfire at gmail

Sep 10, 2009, 11:21 AM

Post #7 of 17 (640 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

I reproduced the problem again by doing the same thing.� Here's the
FULL error log. Sorry for the SPAM.

Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE '%multipart/encrypted%' OR
k.messageblk ILIKE '%multipart/encrypted%'] : [.ERROR: operator does
not exist: bytea ~~* unknown LINE 1: ...55) ILIKE
'%multipart/encrypted%' OR k.messageblk ILIKE '%mu...
^ HINT: No operator
matches the given name and argument type(s). You might need to add
explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE '%multipart/signed%' OR
k.messageblk ILIKE '%multipart/signed%'] : [.ERROR: operator does not
exist: bytea ~~* unknown LINE 1: ...0,255) ILIKE '%multipart/signed%'
OR k.messageblk ILIKE '%mu...
^ HINT: No operator matches the given name and
argument type(s). You might need to add explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE
'%application/x-pkcs7-mime%' OR k.messageblk ILIKE
'%application/x-pkcs7-mime%'] : [.ERROR: operator does not exist:
bytea ~~* unknown LINE 1: ...LIKE '%application/x-pkcs7-mime%' OR
k.messageblk ILIKE '%ap...
^ HINT: No operator matches the given name and
argument type(s). You might need to add explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE '%application/pkcs7-mime%'
OR k.messageblk ILIKE '%application/pkcs7-mime%'] : [.ERROR: operator
does not exist: bytea ~~* unknown LINE 1: ... ILIKE
'%application/pkcs7-mime%' OR k.messageblk ILIKE '%ap...
^ HINT: No operator
matches the given name and argument type(s). You might need to add
explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE '%* PGP%' OR k.messageblk
ILIKE '%* PGP%'] : [.ERROR: operator does not exist: bytea ~~* unknown
LINE 1: ...eadervalue,0,255) ILIKE '%* PGP%' OR k.messageblk ILIKE '%*
.... ^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE '%-----BEGIN PGP%' OR
k.messageblk ILIKE '%-----BEGIN PGP%'] : [.ERROR: operator does not
exist: bytea ~~* unknown LINE 1: ...e,0,255) ILIKE '%-----BEGIN PGP%'
OR k.messageblk ILIKE '%--...
^ HINT: No operator matches the given name and
argument type(s). You might need to add explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE
'%application/pgp-encrypted%' OR k.messageblk ILIKE
'%application/pgp-encrypted%'] : [.ERROR: operator does not exist:
bytea ~~* unknown LINE 1: ...IKE '%application/pgp-encrypted%' OR
k.messageblk ILIKE '%ap...
^ HINT: No operator matches the given name and
argument type(s). You might need to add explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE
'%application/pgp-signature%' OR k.messageblk ILIKE
'%application/pgp-signature%'] : [.ERROR: operator does not exist:
bytea ~~* unknown LINE 1: ...IKE '%application/pgp-signature%' OR
k.messageblk ILIKE '%ap...
^ HINT: No operator matches the given name and
argument type(s). You might need to add explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE '%-----BEGIN=20PGP%' OR
k.messageblk ILIKE '%-----BEGIN=20PGP%'] : [.ERROR: operator does not
exist: bytea ~~* unknown LINE 1: ...0,255) ILIKE '%-----BEGIN=20PGP%'
OR k.messageblk ILIKE '%--...
^ HINT: No operator matches the given name and
argument type(s). You might need to add explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE '%LS0tLS1CRUdJTiBQR1Ag%'
OR k.messageblk ILIKE '%LS0tLS1CRUdJTiBQR1Ag%'] : [.ERROR: operator
does not exist: bytea ~~* unknown LINE 1: ...5) ILIKE
'%LS0tLS1CRUdJTiBQR1Ag%' OR k.messageblk ILIKE '%LS...
^ HINT: No operator
matches the given name and argument type(s). You might need to add
explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE '%LS0tQkVHSU4gUEdQI%' OR
k.messageblk ILIKE '%LS0tQkVHSU4gUEdQI%'] : [.ERROR: operator does not
exist: bytea ~~* unknown LINE 1: ...,255) ILIKE '%LS0tQkVHSU4gUEdQI%'
OR k.messageblk ILIKE '%LS...
^ HINT: No operator matches the given name and
argument type(s). You might need to add explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[sql]
dbpgsql.c,db_query(+287): query failed [.SELECT m.message_idnr,
v.headervalue, k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages m
ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=53 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE '%LS0tLUJFR0lOIFBHUC%' OR
k.messageblk ILIKE '%LS0tLUJFR0lOIFBHUC%'] : [.ERROR: operator does
not exist: bytea ~~* unknown LINE 1: ...255) ILIKE
'%LS0tLUJFR0lOIFBHUC%' OR k.messageblk ILIKE '%LS...
^ HINT: No operator
matches the given name and argument type(s). You might need to add
explicit type casts. ]
Sep 10 19:07:27 dbmail/imap4d[16437]: Error:[mailbox]
dbmail-mailbox.c,mailbox_search(+1374): could not execute query

On Thu, Sep 10, 2009 at 9:56 AM, David Young <randomfire[at]gmail.com> wrote:
>
> First I created a new user on dbmail with dbmail-user with the following options (-a -s -w -p MD5-HASH).� DBMAIL created the new user/mailbox successfully.
>
> Next, I created an IMAP mailbox using Outlook 2003 SP3.� Connected to dbmail-imapd.� Outlook showed a single mailbox (Inbox).� I sent an email to the new mailbox.� The error occurred when I selected the Inbox and the new message.
>
>
> David
>
>
> On Thu, Sep 10, 2009 at 8:23 AM, Michael Monnerie <michael.monnerie[at]is.it-management.at> wrote:
>>
>> On Donnerstag 10 September 2009 Michael Monnerie wrote:
>> > PostgreSQL 8.3 issue
>>
>> Interesting, I got this answer from a PG dev:
>> ************
>> There has *never* been a Postgres release that accepted bytea ILIKE
>> something. �I'm not sure what you were really doing before, but that
>> wasn't it.
>> ************
>> So, this is the query you posted stripped down:
>> SELECT k.messageblk FROM dbmail_messageblks k WHERE
>> �k.messageblk ILIKE '%multipart/encrypted%';
>>
>> He's right, that query doesn't work on PG 8.1 either.
>>
>> How did you make dbmail run such a query? Looks like via a search, but I
>> guess that never worked and would be a bug then. So please, how can I
>> reproduce that query?
>>
>> mfg zmi
>> --
>> // Michael Monnerie, Ing.BSc � �----- � � �http://it-management.at
>> // Tel: 0660 / 415 65 31 � � � � � � � � � � �.network.your.ideas.
>> // PGP Key: � � � � "curl -s http://zmi.at/zmi.asc | gpg --import"
>> // Fingerprint: AC19 F9D5 36ED CD8A EF38 �500E CE14 91F7 1C12 09B4
>> // Keyserver: wwwkeys.eu.pgp.net � � � � � � � � �Key-ID: 1C1209B4
>>
>> _______________________________________________
>> DBmail mailing list
>> DBmail[at]dbmail.org
>> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>
>
> --
> David

--
David
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

cloos at jhcloos

Sep 10, 2009, 2:23 PM

Post #8 of 17 (640 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

>>>>> "Michael" == Michael Monnerie <michael.monnerie[at]is.it-management.at> writes:

Michael> How did you make dbmail run such a query? Looks like via a
Michael> search, but I guess that never worked and would be a bug
Michael> then. So please, how can I reproduce that query?

That block is in mailbox_search() (and the query is essentially the same
in both master and dbmail_2_2) and is for a "text" search. So, in an
imap session, after SELECTing a folder, something like:

C->S: 37331 SEARCH TEXT "multipart/encrypted"

should trigger the dbmail-imapd bug.

(I chose multipart/encrypted only to be similar the the search the OP's
client made.)

I tried that in a 'dbmail-imapd -n -v' session and got the same error:

dbmail-imapd[10679]: Debug:[sql] dbpgsql.c,db_query(+273): [.SELECT
m.message_idnr, v.headervalue, k.messageblk FROM dbmail_messageblks k
JOIN dbmail_physmessage p ON k.physmessage_id=p.id JOIN dbmail_messages
m ON m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.id WHERE m.mailbox_idnr=1 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE '%''multipart/encrypted''%'
OR k.messageblk ILIKE '%''multipart/encrypted''%']

dbmail-imapd[10679]: Error:[sql] dbpgsql.c,db_query(+281): query failed
[.SELECT m.message_idnr, v.headervalue, k.messageblk FROM
dbmail_messageblks k JOIN dbmail_physmessage p ON k.physmessage_id=p.id
JOIN dbmail_messages m ON m.physmessage_id=p.id JOIN dbmail_headervalue
v ON v.physmessage_id=p.id WHERE m.mailbox_idnr=1 AND m.status in (0,1)
HAVING SUBSTRING(v.headervalue,0,255) ILIKE '%''multipart/encrypted''%'
OR k.messageblk ILIKE '%''multipart/encrypted''%'] : [.ERROR: operator
does not exist: bytea ~~* unknown

LINE 1: ...ILIKE '%''multipart/encrypted''%' OR k.messageblk ILIKE '%''...
^

HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.
]

I don't know whether it is possible to do full text searches on a BYTEA
w/o retrieving the whole thing; perhaps one of the extensions might add
a function which can do it?

-JimC
--
James Cloos <cloos[at]jhcloos.com> OpenPGP: 1024D/ED7DAEA6
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

michael.monnerie at is

Sep 15, 2009, 9:27 PM

Post #9 of 17 (623 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

On Donnerstag 10 September 2009 Michael Monnerie wrote:
> So, this is the query you posted stripped down:
> SELECT k.messageblk FROM dbmail_messageblks k WHERE
> k.messageblk ILIKE '%multipart/encrypted%';
>
> He's right, that query doesn't work on PG 8.1 either.

OK, this is a bug in dbmail, which can be fixed in PostgreSQL 8.3 and
up:
http://www.postgresql.org/docs/8.4/interactive/functions-string.html

I would have thought convert_from, possibly combined with convert,
would do it.
And this should really do the magic, but I'm on 8.1 today, will test on
8.3 soon.

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4

_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

cloos at jhcloos

Sep 16, 2009, 11:45 AM

Post #10 of 17 (622 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

>>>>> "Michael" == Michael Monnerie <michael.monnerie[at]is.it-management.at> writes:

Michael> I would have thought convert_from, possibly combined with
Michael> convert, would do it. And this should really do the magic,
Michael> but I'm on 8.1 today, will test on 8.3 soon.

Indeed, convert_from() can be used to coerce the messageblk to text,
but only if dbmail knows how the blk is encoded.

This query in psql does give me the header of a message sent to this
group:

,----
| select physmessage_id from dbmail_messageblks where is_header = 1
| and convert_from(messageblk,'SQL_ASCII') ~ 'dbmail-dev' limit 1;
`----

It is, however, as one might expect quite slow.

If one blindly uses 'SQL_ASCII', octets other than those from in /[\n\t -~]/
aka /\n\t\x20-\x7E/ seem to be output as C-style backslash escapes.
That may or may not be useful for imap search.

There is also the question of whether imap search is supposed to search
the over-the-wire format of the mail or the as-viewed-in-an-MUA format.
Ie, how imap search and mime are supposed to interact.

This query would get all of the mail which have 'dmail-dev' in their
bodies, ordered by physmessage_id. But it is wildly disk intensive,
as psql has to read through every messageblk in dbmail_messageblks.

,----
| select convert_from(messageblk,'SQL_ASCII') from dbmail_messageblks
| where physmessage_id in ( select physmessage_id
| from dbmail_messageblks
| where is_header=0 and
| convert_from(messageblk,'SQL_ASCII') ~ 'dbmail-dev' )
| order by physmessage_id, is_header desc;
`----

Generating an index of the output of convert_from(messageblk,'SQL_ASCII')
would be painful at best.

-JimC
--
James Cloos <cloos[at]jhcloos.com> OpenPGP: 1024D/ED7DAEA6
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

paul at nfg

Sep 17, 2009, 5:13 AM

Post #11 of 17 (615 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

James Cloos wrote:
> This query in psql does give me the header of a message sent to this
> group:
>
> ,----
> | select physmessage_id from dbmail_messageblks where is_header = 1
> | and convert_from(messageblk,'SQL_ASCII') ~ 'dbmail-dev' limit 1;
> `----
>
> It is, however, as one might expect quite slow.
>
> If one blindly uses 'SQL_ASCII', octets other than those from in /[\n\t -~]/
> aka /\n\t\x20-\x7E/ seem to be output as C-style backslash escapes.
> That may or may not be useful for imap search.
>
> There is also the question of whether imap search is supposed to search
> the over-the-wire format of the mail or the as-viewed-in-an-MUA format.
> Ie, how imap search and mime are supposed to interact.

The RFC is very clear on that one:

Server implementations MAY exclude [MIME-IMB] body parts with
terminal content media types other than TEXT and MESSAGE from
consideration in SEARCH matching.

So we don't need to match base64 encoded data, etc.

Also consider this:

In all search keys that use strings, a message matches the key if
the string is a substring of the field. The matching is
case-insensitive.

We try to avoid regexp matching for (possibly) historical performance
reasons, hence the use of ILIKE.

> This query would get all of the mail which have 'dmail-dev' in their
> bodies, ordered by physmessage_id. But it is wildly disk intensive,
> as psql has to read through every messageblk in dbmail_messageblks.

No way around that without full text indexing.

--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

michael.monnerie at is

Sep 17, 2009, 5:48 AM

Post #12 of 17 (612 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

On Donnerstag 17 September 2009 Paul J Stevens wrote:
> No way around that without full text indexing.

Any chance to have FTI support? PostgreSQL 8.3 has that built-in AFAIK,
so it could be interesting. Dbmail could support it if the DB supports
it, I just don't know how much effort that would be. Maybe just a
configuration line, or auto-detection?

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4

_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

vulture at netvulture

Sep 17, 2009, 9:51 AM

Post #13 of 17 (615 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

Michael Monnerie wrote:
> On Donnerstag 17 September 2009 Paul J Stevens wrote:
>
>> No way around that without full text indexing.
>>
>
> Any chance to have FTI support? PostgreSQL 8.3 has that built-in AFAIK,
> so it could be interesting. Dbmail could support it if the DB supports
> it, I just don't know how much effort that would be. Maybe just a
> configuration line, or auto-detection?
>
> mfg zmi
>

FTI would help searching greatly, however on MySQL the default is to
only index words 4 chars or longer. Thus it makes it unreliable when
searching for a small word of 3 chars or less. You can change it to
index 3 chars, which would probably be sufficient, but since this is a
global change on the whole database server and would require a restart,
not everyone would be able to use it (hosted databases). I am unsure of
SQLite FTI. I really can't see support of SQLite remaining beyond 2.4
because of the multi-server over a network requirement of 2.5 Hydra.

Do you know what PostgreSQL does in as far as what is indexed?

-Jon

--
Scanned for viruses and dangerous content by MailScanner

_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

michael.monnerie at is

Sep 17, 2009, 2:52 PM

Post #14 of 17 (614 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

On Donnerstag 17 September 2009 Jonathan Feally wrote:
> FTI would help searching greatly, however on MySQL the default is to
> only index words 4 chars or longer. Thus it makes it unreliable when
> searching for a small word of 3 chars or less. You can change it to
> index 3 chars, which would probably be sufficient, but since this is
> a global change on the whole database server and would require a
> restart, not everyone would be able to use it (hosted databases). I
> am unsure of SQLite FTI. I really can't see support of SQLite
> remaining beyond 2.4 because of the multi-server over a network
> requirement of 2.5 Hydra.

That's why I said it maybe should be configurable. There's always
somebody/something not wanting/supporting that feature, but it could
help those with the capabilities to use it.

> Do you know what PostgreSQL does in as far as what is indexed?

No. I just read on the pg-users ML that 8.3 has that, and we are on 8.4
already. So maybe support is even better there. But I never used it, not
having a program using it....

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4

_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

aaron at serendipity

Sep 17, 2009, 3:08 PM

Post #15 of 17 (613 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

On Thu, 17 Sep 2009 23:52:24 +0200, Michael Monnerie
<michael.monnerie[at]is.it-management.at> wrote:
> On Donnerstag 17 September 2009 Jonathan Feally wrote:
>> FTI would help searching greatly, however on MySQL the default is to
>> only index words 4 chars or longer. Thus it makes it unreliable when
>> searching for a small word of 3 chars or less. You can change it to
>> index 3 chars, which would probably be sufficient, but since this is
>> a global change on the whole database server and would require a
>> restart, not everyone would be able to use it (hosted databases). I
>> am unsure of SQLite FTI.

I don't think any database's own full text index will really do what we
want for IMAP. I'd love to bolt on Sphinx or something like it and have it
manage the search index out of band. http://www.sphinxsearch.com/

>> I really can't see support of SQLite
>> remaining beyond 2.4 because of the multi-server over a network
>> requirement of 2.5 Hydra.
>
> That's why I said it maybe should be configurable. There's always
> somebody/something not wanting/supporting that feature, but it could
> help those with the capabilities to use it.

Supporting SQLite is a great feature; it's important to be able to
configure for a single shard and remain on a single host.

>> Do you know what PostgreSQL does in as far as what is indexed?
>
> No. I just read on the pg-users ML that 8.3 has that, and we are on 8.4
> already. So maybe support is even better there. But I never used it, not

> having a program using it....
>
> mfg zmi
> --
> // Michael Monnerie, Ing.BSc ----- http://it-management.at
> // Tel: 0660 / 415 65 31 .network.your.ideas.
> // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
> // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
> // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
>
> _______________________________________________
> DBmail mailing list
> DBmail[at]dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

paul at nfg

Sep 18, 2009, 12:30 AM

Post #16 of 17 (605 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

Jonathan Feally wrote:
> Michael Monnerie wrote:
>> On Donnerstag 17 September 2009 Paul J Stevens wrote:
>>
>>> No way around that without full text indexing.
>>>
>> Any chance to have FTI support? PostgreSQL 8.3 has that built-in AFAIK,
>> so it could be interesting. Dbmail could support it if the DB supports
>> it, I just don't know how much effort that would be. Maybe just a
>> configuration line, or auto-detection?
>>
>> mfg zmi
>>
>
> FTI would help searching greatly, however on MySQL the default is to
> only index words 4 chars or longer. Thus it makes it unreliable when
> searching for a small word of 3 chars or less. You can change it to
> index 3 chars, which would probably be sufficient, but since this is a
> global change on the whole database server and would require a restart,
> not everyone would be able to use it (hosted databases).

I'd rather investigate the viability of an external indexer like
lucene/solr, extending the earlier design work I did:

http://www.dbmail.org/dokuwiki/doku.php/bodysearch

> I am unsure of
> SQLite FTI. I really can't see support of SQLite remaining beyond 2.4
> because of the multi-server over a network requirement of 2.5 Hydra.

I beg to differ here. SQLite kicks ass. One main use case for me for
developing hydra would be kinda like what Geo did a couple of years ago
when he donated the original sqlite driver: give each user his own
sqlite database.

But then again, our ideas of what hydra should be are not yet set in stone.

--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

paul at nfg

Sep 18, 2009, 12:41 AM

Post #17 of 17 (606 views)
Permalink

Re: DBMAIL error on SELECT [In reply to]

Aaron Stone wrote:
> I don't think any database's own full text index will really do what we
> want for IMAP. I'd love to bolt on Sphinx or something like it and have it
> manage the search index out of band. http://www.sphinxsearch.com/

Dude, you read my mind again? (or did I read yours...)

I wasn't aware of sphinx. Cool. Despite my lucene reference, I'm *not* a
fan of java bloat, brrr.

--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact lists@gossamer-threads.com