Mailing List Archive: DBMail: users

Repairing envelope cache generates double entries

Index | Next | Previous | View Threaded

bert at procurios

May 8, 2008, 11:56 PM

Post #1 of 10 (318 views)
Permalink

Repairing envelope cache generates double entries

Hello all,

Using a webmail client I insert messages in the database directly
(dbmail_messages, dbmail_physmessage, dbmail_messageblks). DBMail 2.2
has all kinds of (very useful) cache tables, like dbmail_envelope and
dbmail_datefield.

The dbmail_util program repairs these messages without cache entries:
dbmail-util -b. I did this manually, first dbmail-util -b and then
dbmail-util -by.

Repairing DBMAIL for cached envelopes...
Ok. Found [89] missing envelope values.

The problem is that now every physmessage has 2 (two!) dbmail_envelope
entries.

mysql> select * from dbmail_envelope where physmessage_id = '353694';
+----------------+--------+--------------------------------+
| physmessage_id | id | envelope |
+----------------+--------+--------------------------------+
| 353694 | 353651 | ("Thu, 08 May ... smail.net>") |
| 353694 | 353740 | ("Thu, 08 May ... smail.net>") |
+----------------+--------+--------------------------------+
2 rows in set (0.00 sec)

This results in messages being listed twice in our webmail overview.

I've three questions:
1. Is it a bug in dbmail-util that two entries in dbmail_envelope are
inserted?
2. Should I trust that dbmail_envelope has only one entry per
physmessage? This increases speed because no "GROUP BY physmessage_id"
has to be done.
3. Is it a problem if a message doesn't have cached headers/envelope for
normal imap access?

We use DBMail 2.2.10.

Kind regards,

Bert Slagter

_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

May 9, 2008, 12:40 AM

Post #2 of 10 (308 views)
Permalink

Re: Repairing envelope cache generates double entries [In reply to]

Bert Slagter wrote:
> Hello all,
>
> Using a webmail client I insert messages in the database directly
> (dbmail_messages, dbmail_physmessage, dbmail_messageblks). DBMail 2.2
> has all kinds of (very useful) cache tables, like dbmail_envelope and
> dbmail_datefield.
>
> The dbmail_util program repairs these messages without cache entries:
> dbmail-util -b. I did this manually, first dbmail-util -b and then
> dbmail-util -by.
>
> Repairing DBMAIL for cached envelopes...
> Ok. Found [89] missing envelope values.
>
> The problem is that now every physmessage has 2 (two!) dbmail_envelope
> entries.
>
> mysql> select * from dbmail_envelope where physmessage_id = '353694';
> +----------------+--------+--------------------------------+
> | physmessage_id | id | envelope |
> +----------------+--------+--------------------------------+
> | 353694 | 353651 | ("Thu, 08 May ... smail.net>") |
> | 353694 | 353740 | ("Thu, 08 May ... smail.net>") |
> +----------------+--------+--------------------------------+
> 2 rows in set (0.00 sec)
>
> This results in messages being listed twice in our webmail overview.
>
> I've three questions:
> 1. Is it a bug in dbmail-util that two entries in dbmail_envelope are
> inserted?

Not in dbmail-util, but in the schema. There should by a UNIQUE key on
the physmessage_id field.

Double entries could happen if two dbmail-util processes are running
simultaneously against the same database.

Dbmail-util -by does:
- collect a list of physmessages which do not have any values in the
_headervalue table. In that case assume no caching has been done for
that message.
- for each physmessage in that list: retrieve the message from the
database and re-do *all* headercaching.

In your case, I can't even be sure the messages where stored 'correctly'
to begin with, making it rather difficult to tell where this came from.

Please consider that dbmail-util -by is *not* designed to detect and fix
all inconsistencies in your cache tables. It was meant only as a
one-shot migration tool.

Inserting messages into the database directly (without respecting the
cache tables) is a /bad/ idea. I've said so before many times. It will
be near impossible in 2.4 with the single-instance storage paradigm.

The only supported injection pathways are: dbmail-smtp (aka
dbmail-deliver in 2.4), dbmail-lmtpd, and IMAP APPEND.

In your case: please use IMAP APPEND or dbmail-smtp.

> 2. Should I trust that dbmail_envelope has only one entry per
> physmessage? This increases speed because no "GROUP BY physmessage_id"
> has to be done.

GROUP BY is not used by the dbmail-imap server. If you talk to the
database yourself, you should add the UNIQUE key as I said above.

> 3. Is it a problem if a message doesn't have cached headers/envelope for
> normal imap access?

It is a problem. Those tables are used by and for normal IMAP access.

x FETCH 1:* (ENVELOPE) hits the _envelope table
x FETCH 1:* (BODY.PEEK[HEADER.FIELDS (From To Subject Date)]) hits the
_headervalue table
x SEARCH 1.* (BEFORE 1-Jan-2008 SENDER paul[at]nfg.nl) hits the
_headervalue tables

etc....

--
________________________________________________________________
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
https://mailman.fastxs.nl/mailman/listinfo/dbmail

bert at procurios

May 9, 2008, 6:12 AM

Post #3 of 10 (307 views)
Permalink

Re: Repairing envelope cache generates double entries [In reply to]

Paul J Stevens wrote:
> Inserting messages into the database directly (without respecting the
> cache tables) is a /bad/ idea. I've said so before many times. It will
> be near impossible in 2.4 with the single-instance storage paradigm.
>
> The only supported injection pathways are: dbmail-smtp (aka
> dbmail-deliver in 2.4), dbmail-lmtpd, and IMAP APPEND.
>
> In your case: please use IMAP APPEND or dbmail-smtp.
>
Hi Paul,

Thanks for your extensive reply. It clears things up and confirms some
expectations :).

My conclusion is that we'll have to switch to IMAP APPEND as soon as
possible. One big issue for us now is that we don't know a user's
password to login to imap, only the user_idnr from dbmail_users and the
md5 password hash. Do you have any ideas on that, or do we have to store
the plain text passwords ourself? User authentication to webmail is now
handled separately by our own system.

Thanks,
Bert

_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

May 9, 2008, 7:46 AM

Post #4 of 10 (306 views)
Permalink

Re: Repairing envelope cache generates double entries [In reply to]

Bert Slagter wrote:
>
> Paul J Stevens wrote:
>> Inserting messages into the database directly (without respecting the
>> cache tables) is a /bad/ idea. I've said so before many times. It will
>> be near impossible in 2.4 with the single-instance storage paradigm.
>>
>> The only supported injection pathways are: dbmail-smtp (aka
>> dbmail-deliver in 2.4), dbmail-lmtpd, and IMAP APPEND.
>>
>> In your case: please use IMAP APPEND or dbmail-smtp.
>>
> Hi Paul,
>
> Thanks for your extensive reply. It clears things up and confirms some
> expectations :).
>
> My conclusion is that we'll have to switch to IMAP APPEND as soon as
> possible. One big issue for us now is that we don't know a user's
> password to login to imap, only the user_idnr from dbmail_users and the
> md5 password hash. Do you have any ideas on that, or do we have to store
> the plain text passwords ourself? User authentication to webmail is now
> handled separately by our own system.

If you want to use IMAP APPEND without knowing the user's password you'll have
to use the acl tables to grant access to those mailboxes to a special user.
Major maintenance hassle. It would be nicer if dbmail supported a special admin
account with full access to all users and all mailboxes via the #Users
namespace. Patches are most welcome :-)

In your case: Why not use dbmail-smtp? Perhaps through a 'super' wrapper, or
something similar.

Actually dbmail really needs a xmlrpc interface for stuff like this ... Perhaps
that would be a nice project after I stabilize 2.4. It would make life a *lot*
easier for webapps.

#!/usr/bin/python
import xmlrpclib
username = "admin"
password = "admin"

sock_c = xmlrpclib.ServerProxy("http://dbmail:9143/xmlrpc/common")
uid = sock_c.login(user=username, password=password)
sock = xmlrpclib.ServerProxy("http://dbmail:9143/xmlrpc/object")

# get all users:
allusers = sock.execute(uid,password,"users","list")

# mailboxes for a user
user_id = sock.execute(uid, password, "users", "find",
{'userid':'someusername'})
mailboxes = sock.execute(uid, password, "mailboxes", "find",
{'owner_idnr': user_id})

etc, etc....

--
________________________________________________________________
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
https://mailman.fastxs.nl/mailman/listinfo/dbmail

bert at procurios

May 13, 2008, 4:49 AM

Post #5 of 10 (276 views)
Permalink

Re: Repairing envelope cache generates double entries [In reply to]

Paul J Stevens wrote:
> Bert Slagter wrote:
>
>> My conclusion is that we'll have to switch to IMAP APPEND as soon as
>> possible. One big issue for us now is that we don't know a user's
>> password to login to imap, only the user_idnr from dbmail_users and the
>> md5 password hash. Do you have any ideas on that, or do we have to store
>> the plain text passwords ourself? User authentication to webmail is now
>> handled separately by our own system.
>>
> If you want to use IMAP APPEND without knowing the user's password you'll have
> to use the acl tables to grant access to those mailboxes to a special user.
> Major maintenance hassle. It would be nicer if dbmail supported a special admin
> account with full access to all users and all mailboxes via the #Users
> namespace. Patches are most welcome :-)
>
Sounds like a good idea. I'm sorry we don't have the expertise to
contribute with code. But I'd love to be guinea pig :).
> In your case: Why not use dbmail-smtp? Perhaps through a 'super' wrapper, or
> something similar.
>
As far as I understand from the man pages dbmail-smtp allows me to let
dbmail deliver a message to a user. So I'm afraid this doesn't really
solve my problem. For example, I need to save a message in a specific
folder (like Sent or Drafts) and need to set specific flags (like seen).
And, I don't want sieve scripts to execute when I save this message. As
I can see this is not possible with dbmail-smtp. Or am I missing something?

So unless I don't understand dbmail-smtp yet, an xml/json-interface or
IMAP APPEND with a super user would be good solutions for me.
> Actually dbmail really needs a xmlrpc interface for stuff like this ... Perhaps
> that would be a nice project after I stabilize 2.4. It would make life a *lot*
> easier for webapps.
> [snip]
>
>
That would be great. JSON-RPC would be even better for PHP apps :).

Kind regards,
Bert
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

May 13, 2008, 6:01 AM

Post #6 of 10 (278 views)
Permalink

Re: Repairing envelope cache generates double entries [In reply to]

Bert Slagter wrote:

> As far as I understand from the man pages dbmail-smtp allows me to let
> dbmail deliver a message to a user. So I'm afraid this doesn't really
> solve my problem. For example, I need to save a message in a specific
> folder (like Sent or Drafts) and need to set specific flags (like seen).
> And, I don't want sieve scripts to execute when I save this message. As
> I can see this is not possible with dbmail-smtp. Or am I missing something?

dbmail-smtp does allow you to store messages into specific mailboxes. But it
does not support setting flags.

--
________________________________________________________________
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
https://mailman.fastxs.nl/mailman/listinfo/dbmail

aaron at serendipity

May 13, 2008, 11:59 AM

Post #7 of 10 (274 views)
Permalink

Re: Repairing envelope cache generates double entries [In reply to]

On May 13, 2008, at 6:01 AM, Paul J Stevens wrote:
> Bert Slagter wrote:
>
>> As far as I understand from the man pages dbmail-smtp allows me to
>> let
>> dbmail deliver a message to a user. So I'm afraid this doesn't really
>> solve my problem. For example, I need to save a message in a specific
>> folder (like Sent or Drafts) and need to set specific flags (like
>> seen).
>> And, I don't want sieve scripts to execute when I save this
>> message. As
>> I can see this is not possible with dbmail-smtp. Or am I missing
>> something?

Use the -M option to skip scripts, quotas and permissions when
inserting a message into a particular mailbox.

> dbmail-smtp does allow you to store messages into specific
> mailboxes. But it
> does not support setting flags.

Flags, eh. Think that would be a feature worth adding, if I have time
to code it up?

Aaron

_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

May 13, 2008, 1:46 PM

Post #8 of 10 (270 views)
Permalink

Re: Repairing envelope cache generates double entries [In reply to]

Aaron Stone wrote:
>
> On May 13, 2008, at 6:01 AM, Paul J Stevens wrote:
>> Bert Slagter wrote:
>>
>>> As far as I understand from the man pages dbmail-smtp allows me to let
>>> dbmail deliver a message to a user. So I'm afraid this doesn't really
>>> solve my problem. For example, I need to save a message in a specific
>>> folder (like Sent or Drafts) and need to set specific flags (like seen).
>>> And, I don't want sieve scripts to execute when I save this message. As
>>> I can see this is not possible with dbmail-smtp. Or am I missing
>>> something?
>
> Use the -M option to skip scripts, quotas and permissions when inserting
> a message into a particular mailbox.
>
>> dbmail-smtp does allow you to store messages into specific mailboxes.
>> But it
>> does not support setting flags.
>
> Flags, eh. Think that would be a feature worth adding, if I have time to
> code it up?

the imap parser is already hooked up into dbmail-export. Shouldn't be
too hard to do the same for dbmail-smtp/dbmail-deliver and support:
dbmail-deliver --flags '(\Seen \Deleted $Junk)' -M mailbox -u user

--
________________________________________________________________
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
https://mailman.fastxs.nl/mailman/listinfo/dbmail

aaron at serendipity

May 13, 2008, 2:20 PM

Post #9 of 10 (274 views)
Permalink

Re: Repairing envelope cache generates double entries [In reply to]

On May 13, 2008, at 1:46 PM, Paul J Stevens wrote:
> Aaron Stone wrote:
>>
>> On May 13, 2008, at 6:01 AM, Paul J Stevens wrote:
>>> Bert Slagter wrote:
>>>
>>>> As far as I understand from the man pages dbmail-smtp allows me
>>>> to let
>>>> dbmail deliver a message to a user. So I'm afraid this doesn't
>>>> really
>>>> solve my problem. For example, I need to save a message in a
>>>> specific
>>>> folder (like Sent or Drafts) and need to set specific flags (like
>>>> seen).
>>>> And, I don't want sieve scripts to execute when I save this
>>>> message. As
>>>> I can see this is not possible with dbmail-smtp. Or am I missing
>>>> something?
>>
>> Use the -M option to skip scripts, quotas and permissions when
>> inserting
>> a message into a particular mailbox.
>>
>>> dbmail-smtp does allow you to store messages into specific
>>> mailboxes.
>>> But it
>>> does not support setting flags.
>>
>> Flags, eh. Think that would be a feature worth adding, if I have
>> time to
>> code it up?
>
> the imap parser is already hooked up into dbmail-export. Shouldn't be
> too hard to do the same for dbmail-smtp/dbmail-deliver and support:
> dbmail-deliver --flags '(\Seen \Deleted $Junk)' -M mailbox -u user

$Junk? Did you build support for arbitrary flag names in 2.3?

Aaron
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

May 14, 2008, 1:07 AM

Post #10 of 10 (272 views)
Permalink

Re: Repairing envelope cache generates double entries [In reply to]

Aaron Stone wrote:

>> the imap parser is already hooked up into dbmail-export. Shouldn't be
>> too hard to do the same for dbmail-smtp/dbmail-deliver and support:
>> dbmail-deliver --flags '(\Seen \Deleted $Junk)' -M mailbox -u user
>
> $Junk? Did you build support for arbitrary flag names in 2.3?

Most certainly. It's in 2.3.2 already.

--
________________________________________________________________
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
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Index | Next | Previous | View Threaded

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