Mailing List Archive: DBMail: users

duplicate key on dbmail_headername_1

Index | Next | Previous | View Threaded

michael.monnerie at it-management

Apr 9, 2008, 2:59 AM

Post #1 of 13 (483 views)
Permalink

duplicate key on dbmail_headername_1

Error:[sql] dbpgsql.c,db_query(+287): query failed [INSERT INTO
dbmail_headername (headername) VALUES
('x-valkarton-mailscanner-spamscore')] : [FEHLER: duplizierter
Schlüssel verletzt Unique-Constraint ťdbmail_headername_1Ť ]

How could this happen? I did a REINDEX now, there are no problems.

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666 .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: www.keyserver.net Key-ID: 1C1209B4

Attachments:

signature.asc (0.19 KB)

paul at nfg

Apr 9, 2008, 3:06 AM

Post #2 of 13 (469 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

Michael Monnerie wrote:
> Error:[sql] dbpgsql.c,db_query(+287): query failed [INSERT INTO
> dbmail_headername (headername) VALUES
> ('x-valkarton-mailscanner-spamscore')] : [FEHLER: duplizierter
> Schlüssel verletzt Unique-Constraint ťdbmail_headername_1Ť ]
>
> How could this happen? I did a REINDEX now, there are no problems.

This could be a race condition where two dbmail-lmtpd or dbmail-smtp processes
try to store the same headername at the same time.

Solving this type of concurrency issue is a driver for 2.3+ development.

--
________________________________________________________________
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

michael.monnerie at it-management

Apr 9, 2008, 3:41 AM

Post #3 of 13 (470 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

On Mittwoch, 9. April 2008 Paul J Stevens wrote:
> This could be a race condition where two dbmail-lmtpd or dbmail-smtp
> processes try to store the same headername at the same time.
>
> Solving this type of concurrency issue is a driver for 2.3+
> development.

I thought about this too. A simple quick solution would be not to break
the transaction because of this, in order to make a successful
delivery. But as it's not too often, it can be ignored, I guess. What
happens to the e-mail whose transaction is aborted? Lost, or thrown
back at postfix which retries again later? Horrible would be an error
back to the sender (looks unprofessional).

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666 .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: www.keyserver.net Key-ID: 1C1209B4

Attachments:

signature.asc (0.19 KB)

daniel at gosi

Apr 9, 2008, 3:49 AM

Post #4 of 13 (469 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

Paul J Stevens schrieb:
> Michael Monnerie wrote:
>> Error:[sql] dbpgsql.c,db_query(+287): query failed [INSERT INTO
>> dbmail_headername (headername) VALUES
>> ('x-valkarton-mailscanner-spamscore')] : [FEHLER: duplizierter
>> Schlüssel verletzt Unique-Constraint ťdbmail_headername_1Ť ]
>>
>> How could this happen? I did a REINDEX now, there are no problems.
>
> This could be a race condition where two dbmail-lmtpd or dbmail-smtp processes
> try to store the same headername at the same time.
>
> Solving this type of concurrency issue is a driver for 2.3+ development.
>

Paul I have two questions here:

I suppose this problem is not much of an issue or does it cause further
damage?

And why do you store the headername and headervalue for an email in an
extra table? is that for better performance for the IMAP daemon when
just fetching a few flags or has that another purpose?

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

paul at nfg

Apr 9, 2008, 3:56 AM

Post #5 of 13 (469 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

Daniel Urstöger wrote:
>
> Paul J Stevens schrieb:
>> Michael Monnerie wrote:
>>> Error:[sql] dbpgsql.c,db_query(+287): query failed [INSERT INTO
>>> dbmail_headername (headername) VALUES
>>> ('x-valkarton-mailscanner-spamscore')] : [FEHLER: duplizierter
>>> Schlüssel verletzt Unique-Constraint ťdbmail_headername_1Ť ]
>>>
>>> How could this happen? I did a REINDEX now, there are no problems.
>>
>> This could be a race condition where two dbmail-lmtpd or dbmail-smtp
>> processes
>> try to store the same headername at the same time.
>>
>> Solving this type of concurrency issue is a driver for 2.3+ development..
>>
>
> Paul I have two questions here:
>
> I suppose this problem is not much of an issue or does it cause further
> damage?

It will abort the header-caching.

> And why do you store the headername and headervalue for an email in an
> extra table? is that for better performance for the IMAP daemon when
> just fetching a few flags or has that another purpose?

This was discussed at great length when header caching was first conceived. It
was determined that storing headernames and values in separate tables would
reduce storage and speed up lookups quite substantially.

It is not used for storing flags. System flags are stored as INT columns in the
messages table, keywords are stored in a separate table (in 2.3+) that has a key
constraint on the messages table.

--
________________________________________________________________
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

marc at electronics-design

Apr 9, 2008, 3:59 AM

Post #6 of 13 (469 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

> Lost, or thrown
> back at postfix which retries again later? Horrible would be an error
> back to the sender (looks unprofessional).

How will that look more unprofessional than loosing an email?

Case a) sender gets note message was not delivered and tries again.
Case b) sender calls reciptient "did you not get this message?"
recipient calls you "why did I not get this message?" your response?
"My mailserver had a bug, and your message was lost?"

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

paul at nfg

Apr 9, 2008, 4:15 AM

Post #7 of 13 (469 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

Michael Monnerie wrote:
> On Mittwoch, 9. April 2008 Paul J Stevens wrote:
>> This could be a race condition where two dbmail-lmtpd or dbmail-smtp
>> processes try to store the same headername at the same time.
>>
>> Solving this type of concurrency issue is a driver for 2.3+
>> development.
>
> I thought about this too. A simple quick solution would be not to break
> the transaction because of this, in order to make a successful
> delivery. But as it's not too often, it can be ignored, I guess. What
> happens to the e-mail whose transaction is aborted? Lost, or thrown
> back at postfix which retries again later? Horrible would be an error
> back to the sender (looks unprofessional).

Iirc, the only transaction that is broken is the cache insertion, not the
message insertion perse.

I've tried to solve this using two different approaches. Using savepoints and
rollbacks to the savepoint on failure works great for postgres. However, mysql
doesnt support savepoints in a useful fashion: it will abort the whole
transaction, even when a savepoint was set.

The second approach was in the trunk for a supplement for savepoint (or
alternative in case of for mysql and sqlite). I had the cache insertion packed
in a simple retry loop: If it fails, sleep a while, and try again.

I've experimented with retry loops a lot during 2.3.0 developement, and they
worked just great, but nowadays preforking is gone, libevent and libzdb ruleth,
and threading is in progress. So I removed those loops for now to clean up the
picture and allow me to focus on core-stability under high concurrencies.

The cache insertion is now done on a connection that is pulled fresh from the
pool. This means there is no longer a massive big transaction lock around the
whole insertion chain.

If the cache insertion fails it raises an exception. At this moment, such an
exception will still abort the header caching sequence, but allowing it to
continue would be trivial (by simply changing the return value from TRUE to
FALSE). Once this proves to work out ok, I can re-instate the retry loop or come
up with an alternative, but only if I observe deadlocks or race conditions in
the code.

--
________________________________________________________________
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

daniel at gosi

Apr 9, 2008, 4:17 AM

Post #8 of 13 (469 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

> It will abort the header-caching.

so not much of an issue, right?

>> And why do you store the headername and headervalue for an email in an
>> extra table? is that for better performance for the IMAP daemon when
>> just fetching a few flags or has that another purpose?
>
> This was discussed at great length when header caching was first conceived. It
> was determined that storing headernames and values in separate tables would
> reduce storage and speed up lookups quite substantially.
>
> It is not used for storing flags. System flags are stored as INT columns in the
> messages table, keywords are stored in a separate table (in 2.3+) that has a key
> constraint on the messages table.

okay, I think I got that. One thing I wonder about though: the complete
header is still stored within dbmail_messageblks right?

Thanks for your time!

greetings,
Daniel

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

paul at nfg

Apr 9, 2008, 4:20 AM

Post #9 of 13 (469 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

Marc Dirix wrote:
>> Lost, or thrown
>> back at postfix which retries again later? Horrible would be an error
>> back to the sender (looks unprofessional).
>
> How will that look more unprofessional than loosing an email?

Failure to do header caching for a message will not result in lost mail.

Message insertion is separated from cache insertions. If the message insertion
fails an error is back-propagaged to the sender, but failure during cache
insertion is silent apart from the errors in the logs. Messages without cached
headers can still be retrieved through IMAP or POP3, though in the case of IMAP
they will look funny in the list-pane, and look just fine in the preview pane.

>
> Case a) sender gets note message was not delivered and tries again.
> Case b) sender calls reciptient "did you not get this message?"
> recipient calls you "why did I not get this message?" your response?
> "My mailserver had a bug, and your message was lost?"
>
>
> _______________________________________________
> DBmail mailing list
> DBmail[at]dbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>

--
________________________________________________________________
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

paul at nfg

Apr 9, 2008, 4:24 AM

Post #10 of 13 (469 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

Daniel Urstöger wrote:
>
>> It will abort the header-caching.
>
> so not much of an issue, right?

Correct.

> okay, I think I got that. One thing I wonder about though: the complete
> header is still stored within dbmail_messageblks right?

The messageblks table (or the mimeparts table in 2.3+) will always contain the
complete message as-received. Only exception: in case of 2.3+ the mime-part
boundaries are *not* stored as part of the blobs.

The caching tables are meant for improving IMAP performance (mostly: FETCH,
SEARCH, SORT, THREAD).

--
________________________________________________________________
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

rabbit+list at rabbit

Apr 9, 2008, 4:42 AM

Post #11 of 13 (469 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

Paul J Stevens wrote:
> Messages without cached
> headers can still be retrieved through IMAP or POP3, though in the case of IMAP
> they will look funny in the list-pane, and look just fine in the preview pane.

Isn't this a potential mail-loss scenario? A user opens his email client, sees
weird subjects and deletes them without looking inside. Is it possible to add
a layer of translucency to the cache table? So if the cached header for a
message is not present, the real physmessage is parsed instead?
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

Apr 9, 2008, 6:31 AM

Post #12 of 13 (470 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

Peter Rabbitson wrote:
> Paul J Stevens wrote:
>> Messages without cached
>> headers can still be retrieved through IMAP or POP3, though in the
>> case of IMAP
>> they will look funny in the list-pane, and look just fine in the
>> preview pane.
>
> Isn't this a potential mail-loss scenario? A user opens his email
> client, sees weird subjects and deletes them without looking inside. Is
> it possible to add a layer of translucency to the cache table? So if the
> cached header for a message is not present, the real physmessage is
> parsed instead?

That would defeat the purpose of the cache tables.

Imagine where an IMAP client requests a list of headers:

x UID FETCH 1:* (BODY.PEEK[HEADER.FIELDS (References X-Ref X-Priority
X-MSMail-Priority X-MSOESRec Newsgroups)])

(this is from the outlook fetch command). It is perfectly valid for an imap
client to request headers that may not be there. So if you have a mailbox with
messages, none of which have for example the X-MSOESRec header, dbmail would
have to retrieve each of those messages in full to do a double check for the
non-existence of a header that is (correctly) not in the caching tables.

--
________________________________________________________________
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

rabbit+list at rabbit

Apr 9, 2008, 9:18 AM

Post #13 of 13 (469 views)
Permalink

Re: duplicate key on dbmail_headername_1 [In reply to]

Paul J Stevens wrote:
> Peter Rabbitson wrote:
>> Paul J Stevens wrote:
>>> Messages without cached
>>> headers can still be retrieved through IMAP or POP3, though in the
>>> case of IMAP
>>> they will look funny in the list-pane, and look just fine in the
>>> preview pane.
>> Isn't this a potential mail-loss scenario? A user opens his email
>> client, sees weird subjects and deletes them without looking inside. Is
>> it possible to add a layer of translucency to the cache table? So if the
>> cached header for a message is not present, the real physmessage is
>> parsed instead?
>
> That would defeat the purpose of the cache tables.
>
> Imagine where an IMAP client requests a list of headers:
>
> x UID FETCH 1:* (BODY.PEEK[HEADER.FIELDS (References X-Ref X-Priority
> X-MSMail-Priority X-MSOESRec Newsgroups)])
>
> (this is from the outlook fetch command). It is perfectly valid for an imap
> client to request headers that may not be there. So if you have a mailbox with
> messages, none of which have for example the X-MSOESRec header, dbmail would
> have to retrieve each of those messages in full to do a double check for the
> non-existence of a header that is (correctly) not in the caching tables.
>

Good point. However I still think that caching (and subsequently losing) the
subjet is inherently bad. Probably the Subject needs preferential treatment,
just like From/To as they seem to not be affected by cache presence?
_______________________________________________
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