headers
Sergey Spiridonov | 2 Jun 2010 10:17

dbmail/maintenance error: Duplicate entry ... for key 2] [INSERT INTO dbmail_datefield

Hi

I get such errors each night. Database is mysql, dbmail version is
2.2.10-1+b1 from current stable Debian Lenny 5.0.4:

Jun  2 03:00:11 jupiter dbmail/maintenance[1076]: Error:[sql]
dbmysql.c,db_query(+290): [Duplicate entry '7503-1970-01-01 00:00:00'
for key 2] [INSERT INTO dbmail_datefield (physmessage_id, datefield)
VALUES (7503,'1970-01-01 00:00:00')]
Jun  2 03:00:11 jupiter dbmail/maintenance[1076]: Error:[message]
dbmail-message.c,insert_field_cache(+1105): insert datefield failed
[INSERT INTO dbmail_datefield (physmessage_id, datefield) VALUES
(7503,'1970-01-01 00:00:00')]
Jun  2 03:00:11 jupiter dbmail/maintenance[1076]: Error:[sql]
dbmysql.c,db_query(+290): [Duplicate entry '7446-1970-01-01 00:00:00'
for key 2] [INSERT INTO dbmail_datefield (physmessage_id, datefield)
VALUES (7446,'1970-01-01 00:00:00')]
Jun  2 03:00:11 jupiter dbmail/maintenance[1076]: Error:[message]
dbmail-message.c,insert_field_cache(+1105): insert datefield failed
[INSERT INTO dbmail_datefield (physmessage_id, datefield) VALUES
(7446,'1970-01-01 00:00:00')]
Jun  2 03:00:11 jupiter dbmail/maintenance[1076]: Error:[sql]
dbmysql.c,db_query(+290): [Duplicate entry '3335-1970-01-01 00:00:00'
for key 2] [INSERT INTO dbmail_datefield (physmessage_id, datefield)
VALUES (3335,'1970-01-01 00:00:00')]
Jun  2 03:00:11 jupiter dbmail/maintenance[1076]: Error:[message]
dbmail-message.c,insert_field_cache(+1105): insert datefield failed
[INSERT INTO dbmail_datefield (physmessage_id, datefield) VALUES
(3335,'1970-01-01 00:00:00')]
(Continue reading)

Permalink | Reply | 7 comments |
headers
Sergey Spiridonov | 2 Jun 2010 17:13

Re: dbmail/maintenance error: Duplicate entry ... for key 2] [INSERT INTO dbmail_datefield

I can reproduce this by running bmail-util -ay:

# dbmail-util -ay

Repairing DBMAIL messageblocks integrity...
Ok. Found [0] unconnected messageblks.

Repairing DBMAIL physmessage integrity...
Ok. Found [0] unconnected physmessages.

Repairing DBMAIL message integrity...
Ok. Found [0] unconnected messages.

Repairing DBMAIL mailbox integrity...
Ok. Found [0] unconnected mailboxes.

Repairing DBMAIL for NULL messages...
Ok. Found [0] NULL messages.

Checking DBMAIL for NULL physmessages...
Ok. Found [0] physmessages without messageblocks.

Deleting messages with DELETE status...
Ok. [0] messages deleted.

Repairing DBMAIL for rfcsize field...
Ok. Found [0] missing rfcsize values.

Repairing DBMAIL for incorrect is_header flags...
Ok. Found [0] incorrect is_header flags.
(Continue reading)

Permalink | Reply | 1 comment |
headers
N Sj | 2 Jun 2010 18:43
Picon
Favicon

Re: dbmail/maintenance error: Duplicate entry ... for key 2] [INSERT INTO dbmail_datefield

you can try deleting the duplicate records from dbmail cache in mysql DB and the rerun 'dbmail-util -ay'

Here is an example to remove these records:

DELETE FROM dbmail_ccfield WHERE physmessage_id='3335';
DELETE FROM dbmail_datefield WHERE physmessage_id='3335';
DELETE FROM dbmail_envelope WHERE physmessage_id='3335';
DELETE FROM dbmail_fromfield WHERE physmessage_id='3335';
DELETE FROM dbmail_headervalue WHERE physmessage_id='3335';
DELETE FROM dbmail_referencesfield WHERE physmessage_id='3335';
DELETE FROM dbmail_replytofield WHERE physmessage_id='3335';
DELETE FROM dbmail_subjectfield WHERE physmessage_id='3335';
DELETE FROM dbmail_tofield WHERE physmessage_id='3335';

----- Original Message ----
From: Sergey Spiridonov <sena <at> hurd.homeunix.org>
To: dbmail <at> dbmail.org
Sent: Wed, June 2, 2010 8:13:58 AM
Subject: Re: [Dbmail] dbmail/maintenance error: Duplicate entry ... for key 2] [INSERT INTO dbmail_datefield

I can reproduce this by running bmail-util -ay:

# dbmail-util -ay

Repairing DBMAIL messageblocks integrity...
Ok. Found [0] unconnected messageblks.

Repairing DBMAIL physmessage integrity...
Ok. Found [0] unconnected physmessages.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Sergey Spiridonov | 2 Jun 2010 18:09

Re: dbmail/maintenance error: Duplicate entry ... for key 2] [INSERT INTO dbmail_datefield

Hi

Well, i think, I understand, what is the problem. I have 3 messages
without entries in dbmail_headervalue:

mysql> select p.id from dbmail_physmessage p LEFT JOIN
dbmail_headervalue h ON p.id = h.physmessage_id WHERE h.physmessage_id
IS NULL;
+------+
| id   |
+------+
| 3335 |
| 7446 |
| 7503 |
+------+
3 rows in set (0.06 sec)

So, dbmail tryes to create them. But this messages are very small and do
not have date field:

mysql> select * from dbmail_physmessage where id=3335;
+------+-------------+---------+---------------------+
| id   | messagesize | rfcsize | internal_date       |
+------+-------------+---------+---------------------+
| 3335 |          12 |       2 | 2007-11-07 13:50:41 |
+------+-------------+---------+---------------------+
1 row in set (0.00 sec)

mysql> select * from dbmail_physmessage where id=7446;
+------+-------------+---------+---------------------+
(Continue reading)

Permalink | Reply | 4 comments |
headers
Paul J Stevens | 2 Jun 2010 20:30
Picon
Favicon
Gravatar

Re: dbmail/maintenance error: Duplicate entry ... for key 2] [INSERT INTO dbmail_datefield

> mysql> select * from dbmail_physmessage where id=3335;
> +------+-------------+---------+---------------------+
> | id   | messagesize | rfcsize | internal_date       |
> +------+-------------+---------+---------------------+
> | 3335 |          12 |       2 | 2007-11-07 13:50:41 |
> +------+-------------+---------+---------------------+
> 1 row in set (0.00 sec)

No way those are valid messages. I would simply delete them.

> How can I workadound this easely? Is this an old known bug?

I remember someone having the same problem. Iirc, deleting those broken
physmessage entries was the solution. Since the internal_date is quite
old, and the rfcsize indicates a NULL message I would expect them to be
bogus data anyway.

--

-- 
  ________________________________________________________________
  Paul Stevens                                      paul at nfg.nl
  NET FACILITIES GROUP                     GPG/PGP: 1024D/11F8CD31
  The Netherlands________________________________http://www.nfg.nl
Permalink | Reply | 3 comments |
headers
Sergey Spiridonov | 4 Jun 2010 09:55

Re: dbmail/maintenance error: Duplicate entry ... for key 2] [INSERT INTO dbmail_datefield

Hi

Paul J Stevens wrote:
>> mysql> select * from dbmail_physmessage where id=3335;
>> +------+-------------+---------+---------------------+
>> | id   | messagesize | rfcsize | internal_date       |
>> +------+-------------+---------+---------------------+
>> | 3335 |          12 |       2 | 2007-11-07 13:50:41 |
>> +------+-------------+---------+---------------------+
>> 1 row in set (0.00 sec)
> 
> No way those are valid messages. I would simply delete them.

What is the easiest way to delete message knowing only
message_id/physmessage_id? From which tables should I delete?
--

-- 
Best regards, Sergey Spiridonov
Permalink | Reply | 0 comments |
headers
Sergey Spiridonov | 4 Jun 2010 10:35

Re: dbmail/maintenance error: Duplicate entry ... for key 2] [INSERT INTO dbmail_datefield

Hi

> No way those are valid messages. I would simply delete them.

<Looking at entity-relationship-model>: is it enough to

DELETE FROM dbmail_messages where physmessage_id=335;
DELETE FROM dbmail_physmessage where id=3335;
DELETE FROM dbmail_messageblks where physmessage_id=3335;
DELETE FROM dbmail_envelope where physmessage_id=3335;
DELETE FROM dbmail_headervalue where physmessage_id=3335;
DELETE FROM dbmail_ccfield WHERE physmessage_id='3335';
DELETE FROM dbmail_datefield WHERE physmessage_id='3335';
DELETE FROM dbmail_envelope WHERE physmessage_id='3335';
DELETE FROM dbmail_fromfield WHERE physmessage_id='3335';
DELETE FROM dbmail_headervalue WHERE physmessage_id='3335';
DELETE FROM dbmail_referencesfield WHERE physmessage_id='3335';
DELETE FROM dbmail_replytofield WHERE physmessage_id='3335';
DELETE FROM dbmail_subjectfield WHERE physmessage_id='3335';
DELETE FROM dbmail_tofield WHERE physmessage_id='3335';

?

--

-- 
Best regards, Sergey Spiridonov
Permalink | Reply | 1 comment |
headers
Paul J Stevens | 4 Jun 2010 11:32
Picon
Favicon
Gravatar

Re: dbmail/maintenance error: Duplicate entry ... for key 2] [INSERT INTO dbmail_datefield

Sergey Spiridonov wrote:
> Hi
> 
>> No way those are valid messages. I would simply delete them.
> 
> <Looking at entity-relationship-model>: is it enough to
> 
> 
> DELETE FROM dbmail_messages where physmessage_id=335;
> DELETE FROM dbmail_physmessage where id=3335;
> DELETE FROM dbmail_messageblks where physmessage_id=3335;
> DELETE FROM dbmail_envelope where physmessage_id=3335;
> DELETE FROM dbmail_headervalue where physmessage_id=3335;
> DELETE FROM dbmail_ccfield WHERE physmessage_id='3335';
> DELETE FROM dbmail_datefield WHERE physmessage_id='3335';
> DELETE FROM dbmail_envelope WHERE physmessage_id='3335';
> DELETE FROM dbmail_fromfield WHERE physmessage_id='3335';
> DELETE FROM dbmail_headervalue WHERE physmessage_id='3335';
> DELETE FROM dbmail_referencesfield WHERE physmessage_id='3335';
> DELETE FROM dbmail_replytofield WHERE physmessage_id='3335';
> DELETE FROM dbmail_subjectfield WHERE physmessage_id='3335';
> DELETE FROM dbmail_tofield WHERE physmessage_id='3335';
> 
> ?
> 

just do:

DELETE FROM dbmail_physmessage where id=3335;
(Continue reading)

Permalink | Reply | 0 comments |
headers
Sergey Spiridonov | 4 Jun 2010 12:04

suppress duplicates, duplicate mesages and all, all, all

Hi

I found on dbmail wiki following passage:

> Ability to store only a single copy of duplicated mail messages to
> save storage. (update: this was done already in 2.2 and trunk. It's
> called suppress_duplicates in dbmail.conf)

AFAIK, suppress_duplicates works only when same message is delivered to
the same mailbox. If same mail is delivered (or copied) to another
mailbox, it is copied (and this is good) and not suppressed. But what
author of the original message meant, that 2 identical messages inside 2
different mailboxes should not take double space. This is same idea as
hardlinks on filesystem.

Is there something like this in dbmail?

BTW, suppressing duplicates in a way it is implemented now is sometimes
misleading. Try, for example, to delete a message and add it again when
suppress_duplicates is enabled - message will not be added. This happens
because message is still in database and is only marked for deletion,
but for enduser this is not so clear.
--

-- 
Best regards, Sergey Spiridonov
Permalink | Reply | 2 comments |
headers
Paul J Stevens | 4 Jun 2010 12:24
Picon
Favicon
Gravatar

Re: suppress duplicates, duplicate mesages and all, all, all

Sergey Spiridonov wrote:
> Hi
> 
> I found on dbmail wiki following passage:
> 
>> Ability to store only a single copy of duplicated mail messages to
>> save storage. (update: this was done already in 2.2 and trunk. It's
>> called suppress_duplicates in dbmail.conf)
> 
> AFAIK, suppress_duplicates works only when same message is delivered to
> the same mailbox. If same mail is delivered (or copied) to another
> mailbox, it is copied (and this is good) and not suppressed. But what
> author of the original message meant, that 2 identical messages inside 2
> different mailboxes should not take double space. This is same idea as
> hardlinks on filesystem.
> 
> Is there something like this in dbmail?

Yes. But it's in the 2.3 codebase only. It's called single-instance
storage, and it applies to mime-parts and header-values.

> 
> BTW, suppressing duplicates in a way it is implemented now is sometimes
> misleading. Try, for example, to delete a message and add it again when
> suppress_duplicates is enabled - message will not be added. This happens
> because message is still in database and is only marked for deletion,
> but for enduser this is not so clear.

Sounds like a bug. The support_duplicates functionality should check for
message status. Please file a ticket in the bugtracker.
(Continue reading)

Permalink | Reply | 1 comment |

Gmane