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 20:02 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? 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 ====================================================================== _______________________________________________ Dbmail-dev mailing list [email protected] http://twister.fastxs.net/mailman/listinfo/dbmail-dev
