A NOTE has been added to this issue.
======================================================================
http://www.dbmail.org/mantis/view.php?id=688
======================================================================
Reported By: m2000
Assigned To:
======================================================================
Project: DBMail
Issue ID: 688
Category: Database layer
Reproducibility: sometimes
Severity: minor
Priority: normal
Status: new
target:
======================================================================
Date Submitted: 10-Mar-08 19:55 CET
Last Modified: 13-Mar-08 22:10 CET
======================================================================
Summary: re 0000486: duplicate key errors in dbmail_datefield
Description:
Repairing DBMAIL for cached header values...
Ok. Found [1] un-cached physmessages.
Mar 10 19:38:21 pigeon dbmail-util[22372]: Error:[sql]
dbmysql.c,db_query(+290): [Duplicate entry '728249-1970-01-01 00:00:00'
for key 2] [INSERT INTO dbmail_datefield (physmessage_id, datefield)
VALUES (728249,'1970-01-01 00:00:00')]
Mar 10 19:38:21 pigeon dbmail-util[22372]: Error:[message]
dbmail-message.c,insert_field_cache(+1100): insert datefield failed
[INSERT INTO dbmail_datefield (physmessage_id, datefield) VALUES
(728249,'1970-01-01 00:00:00')]
mysql> SELECT * FROM dbmail_datefield WHERE physmessage_id=728249;
+----------------+--------+---------------------+
| physmessage_id | id | datefield |
+----------------+--------+---------------------+
| 728249 | 728221 | 1970-01-01 00:00:00 |
+----------------+--------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM dbmail_physmessage WHERE id=728249;
+--------+-------------+---------+---------------------+
| id | messagesize | rfcsize | internal_date |
+--------+-------------+---------+---------------------+
| 728249 | 410805 | 416350 | 2004-11-22 02:10:24 |
+--------+-------------+---------+---------------------+
1 row in set (0.00 sec)
======================================================================
----------------------------------------------------------------------
paul - 12-Mar-08 22:32
----------------------------------------------------------------------
Is this problem persistent? If so, try clearing out all cache tables for
the physmessages involved in the duplicate key errors.
If a physmessage doesn't have any entries in the headervalue table it is
assumed not to have been cached at all. If a physmessage is missing from
the headervalue table, but _does_ have entries in one of the other tables,
such duplicate key collisions are to be expected.
Regarding the problem with your replication: what do you mean by never
seeing the messae on the US side? Are you looking in the tables, or
looking from IMAP? Anyway it's unrelated.
----------------------------------------------------------------------
m2000 - 12-Mar-08 22:50
----------------------------------------------------------------------
Looking from IMAP.
----------------------------------------------------------------------
m2000 - 12-Mar-08 22:51
----------------------------------------------------------------------
Problem is persistent, insofar as it happens continually. Stupid question,
how do I clear the caches?
----------------------------------------------------------------------
m2000 - 13-Mar-08 18:44
----------------------------------------------------------------------
fwiw, i did DELETE FROM dbmail_datefield WHERE physmessage_id=728249, ran
dbmail-util -by, and then re-ran it b/c it said to do so.
The first run no error (said it fixed one entry). Ran it again, and it
gave the same key-collision.
Am I supposed to clear out more than just that one table? Is there a
quickie way to do that w/o doing it from the mysql console?
----------------------------------------------------------------------
paul - 13-Mar-08 19:33
----------------------------------------------------------------------
You need to clear out all the cache tables:
| dbmail_ccfield |
| dbmail_datefield |
| dbmail_envelope |
| dbmail_fromfield |
| dbmail_headervalue |
| dbmail_referencesfield |
| dbmail_replytofield |
| dbmail_subjectfield |
| dbmail_tofield |
all are keyed to the physmessage_id. There is no way to do this other than
the console.
----------------------------------------------------------------------
m2000 - 13-Mar-08 19:48
----------------------------------------------------------------------
START TRANSACTION;
DELETE FROM dbmail_datefield WHERE physmessage_id=728249; DELETE FROM
dbmail_ccfield WHERE physmessage_id=728249; DELETE FROM dbmail_fromfield
WHERE physmessage_id=728249; DELETE FROM dbmail_referencesfield WHERE
physmessage_id=728249; DELETE FROM dbmail_replytofield WHERE
physmessage_id=728249; DELETE FROM dbmail_subjectfield WHERE
physmessage_id=728249; DELETE FROM dbmail_tofield WHERE
physmessage_id=728249; DELETE FROM dbmail_headervalue WHERE
physmessage_id=728249; DELETE FROM dbmail_envelope WHERE
physmessage_id=728249;
COMMIT;
Ran dbmail-util -by, ran it again b/c it said so.
Repairing DBMAIL for cached header values...
Ok. Found [1] un-cached physmessages.
Mar 13 11:47:59 perch dbmail-util[31434]: Error:[sql]
dbmysql.c,db_query(+290): [Duplicate entry '728249-1970-01-01 00:00:00'
for key 2] [INSERT INTO dbmail_datefield (physmessage_id, datefield)
VALUES (728249,'1970-01-01 00:00:00')]
Mar 13 11:47:59 perch dbmail-util[31434]: Error:[message]
dbmail-message.c,insert_field_cache(+1100): insert datefield failed
[INSERT INTO dbmail_datefield (physmessage_id, datefield) VALUES
(728249,'1970-01-01 00:00:00')]
----------------------------------------------------------------------
m2000 - 13-Mar-08 19:49
----------------------------------------------------------------------
Is it just me, or is someone doing a "is date zero" check and deciding it's
wrong?
----------------------------------------------------------------------
m2000 - 13-Mar-08 19:58
----------------------------------------------------------------------
The relevant physmessage has only 2 messageblks. No apparent headers. Maybe
corrupted message, transferred from another mail server?
I ran the DELETE commands again, and 1 datefield row was removed and 3
envelope rows, all other tables were empty. I think before it was 8
headervalue or 8 envelope (don't remember for sure). I rolled back the
transaction this time.
----------------------------------------------------------------------
paul - 13-Mar-08 20:02
----------------------------------------------------------------------
if the message is corrupt why not simply delete that message completely?
----------------------------------------------------------------------
m2000 - 13-Mar-08 20:23
----------------------------------------------------------------------
b/c presumably the user is still able to access it and may desire to
continue to do so.
Messages do sometimes get inserted that may be invalid from a strict point
of view, esp if it may have come from a non-email source (Lotus Notes
doesn't so much keep email as 'documents'). The email itself appears to be
from 2004, and presumably has [badly] survived the transition from a MacOS
(Cyrus) email solution to Lotus Notes, and now to dbmail.
Despite the message appearing to be missing most of its headers, it looks
like the MIME blocks themselves appear to be intact, and even containing
the proper end of MIME block footers.
dbmail_envelope for this message is below
mysql> SELECT * FROM dbmail_envelope WHERE (physmessage_id=728249)\G
*************************** 1. row ***************************
physmessage_id: 728249
id: 1157049
envelope: ("Thu, 01 Jan 1970 00:00:00 +0000" NIL NIL NIL NIL NIL NIL
NIL NIL NIL)
*************************** 2. row ***************************
physmessage_id: 728249
id: 1157053
envelope: ("Thu, 01 Jan 1970 00:00:00 +0000" NIL NIL NIL NIL NIL NIL
NIL NIL NIL)
*************************** 3. row ***************************
physmessage_id: 728249
id: 1157057
envelope: ("Thu, 01 Jan 1970 00:00:00 +0000" NIL NIL NIL NIL NIL NIL
NIL NIL NIL)
3 rows in set (0.00 sec)
----------------------------------------------------------------------
paul - 13-Mar-08 22:10
----------------------------------------------------------------------
Those duplicate envelopes are most likely just a side effect of the failure
to cache the headers.
You can disable header-cache checks when running dbmail-util if the errors
bug you, but dbmail's storage model is only tested against mime messages
with at least some measure of compliancy.
If you want you might provide 'steps to reproduce' this behaviour and
someone like me might pick this up and do a fix.
Issue History
Date Modified Username Field Change
======================================================================
10-Mar-08 19:55 m2000 New Issue
12-Mar-08 22:32 paul Note Added: 0002510
12-Mar-08 22:50 m2000 Note Added: 0002511
12-Mar-08 22:51 m2000 Note Added: 0002512
13-Mar-08 18:44 m2000 Note Added: 0002514
13-Mar-08 19:33 paul Note Added: 0002515
13-Mar-08 19:48 m2000 Note Added: 0002517
13-Mar-08 19:49 m2000 Note Added: 0002518
13-Mar-08 19:58 m2000 Note Added: 0002519
13-Mar-08 20:02 paul Note Added: 0002520
13-Mar-08 20:23 m2000 Note Added: 0002522
13-Mar-08 22:10 paul Note Added: 0002523
======================================================================
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
