|
»
DBMAIL error on SELECT
|
View:
New views
17 Messages
—
Rating Filter:
Alert me
|
 |
DBMAIL error on SELECT
by David Young-17
::
Rate this Message:
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 _______________________________________________ DBmail mailing list DBmail@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by Jonathan Feally
::
Rate this Message:
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@... > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail > -- Scanned for viruses and dangerous content by MailScanner _______________________________________________ DBmail mailing list DBmail@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by David Young-17
::
Rate this Message:
Running CentOS 5.2, Postgres 8.3.7.
On Wed, Sep 9, 2009 at 8:24 PM, Jonathan Feally <vulture@...> wrote: What database server and version are you using? This seems like an older -- David _______________________________________________ DBmail mailing list DBmail@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by Michael Monnerie-5
::
Rate this Message:
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 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 _______________________________________________ DBmail mailing list DBmail@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by Michael Monnerie-5
::
Rate this Message:
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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by David Young-17
::
Rate this Message:
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@...> wrote: On Donnerstag 10 September 2009 Michael Monnerie wrote: -- David _______________________________________________ DBmail mailing list DBmail@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by David Young-17
::
Rate this Message:
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@...> 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@...> 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@... >> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail > > > > -- > David -- David _______________________________________________ DBmail mailing list DBmail@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by James Cloos-9
::
Rate this Message:
>>>>> "Michael" == Michael Monnerie <michael.monnerie@...> 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@...> OpenPGP: 1024D/ED7DAEA6 _______________________________________________ DBmail mailing list DBmail@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by Michael Monnerie-5
::
Rate this Message:
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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by James Cloos-9
::
Rate this Message:
>>>>> "Michael" == Michael Monnerie <michael.monnerie@...> 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@...> OpenPGP: 1024D/ED7DAEA6 _______________________________________________ DBmail mailing list DBmail@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by Paul J Stevens
::
Rate this Message:
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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by Michael Monnerie-5
::
Rate this Message:
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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by Jonathan Feally
::
Rate this Message:
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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by Michael Monnerie-5
::
Rate this Message:
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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by Aaron Stone
::
Rate this Message:
On Thu, 17 Sep 2009 23:52:24 +0200, Michael Monnerie
<michael.monnerie@...> 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@... > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail DBmail mailing list DBmail@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by Paul J Stevens
::
Rate this Message:
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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
|
|
Re: DBMAIL error on SELECT
by Paul J Stevens
::
Rate this Message:
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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail |
signature.asc (204 bytes) | Free embeddable forum powered by Nabble | Forum Help |
