Mailing List Archive: DBMail: users

Bug in dbmail-util - not cleaning un-cached physmessages, and making other mess

Index | Next | Previous | View Threaded

michael.monnerie at is

Mar 17, 2009, 1:29 AM

Post #1 of 6 (1419 views)
Permalink

Bug in dbmail-util - not cleaning un-cached physmessages, and making other mess

No matter how often I run dbmail-util -by, I always get:

Repairing DBMAIL for cached header values...
Ok. Found [3] un-cached physmessages.

In the code, this generates (in function db.c/db_icheck_headercache )
SELECT p.id FROM dbmail_physmessage p LEFT JOIN dbmail_headervalue h ON
p.id = h.physmessage_id WHERE h.physmessage_id IS NULL;
which really returns 3 id's.

Then function db.c/db_set_headercache should cleanup the mess, but
doesn't. That function only logs:
Mar 17 09:04:38 mailsrv2.i.zmi.at dbmail/maintenance[9101]: Message:
[message] dbmail-message.c,dbmail_message_cache_subjectfield(+1207): no
subject field value [1838862]
Mar 17 09:04:38 mailsrv2.i.zmi.at dbmail/maintenance[9101]: Message:
[message] dbmail-message.c,dbmail_message_cache_subjectfield(+1207): no
subject field value [1838861]
Mar 17 09:04:38 mailsrv2.i.zmi.at dbmail/maintenance[9101]: Message:
[message] dbmail-message.c,dbmail_message_cache_subjectfield(+1207): no
subject field value [1838858]

There doesn't seem to be an error, as there are no logs for that, but
still it's not working. Then I set TRACE_SYSLOG = 5 and re-ran dbmail-
util. See attached dbmail.log

From those logs, you can see that a dbmail_datefield and dbmail_envelope
with a date are inserted into the db. That's a mess already:
select * from dbmail_datefield where physmessage_id = 1838862;
physmessage_id | id | datefield
----------------+---------+---------------------
1838862 | 1307186 | 1970-01-01 00:00:00
1838862 | 1307099 | 1970-01-01 00:00:00
1838862 | 1306945 | 1970-01-01 00:00:00
1838862 | 1306701 | 1970-01-01 00:00:00
1838862 | 1306400 | 1970-01-01 00:00:00
1838862 | 1307612 | 1970-01-01 00:00:00
1838862 | 1307616 | 1970-01-01 00:00:00
1838862 | 1307564 | 1970-01-01 00:00:00
1838862 | 1307619 | 1970-01-01 00:00:00
1838862 | 1307350 | 1970-01-01 00:00:00
1838862 | 1307609 | 1970-01-01 00:00:00

Wouldn't a single physmessage_id entry be enough in dbmail_datefield? If
yes, the index dbmail_datefield_1, which currently is UNIQUE
(physmessage_id, id) could be changed to UNIQUE (physmessage_id).

And the same goes for dbmail_datefield I suppose, and maybe others also.
Paul, if you say I'm right I'd deliver SQL statements to cleanup maybe
existing mess, and change indices. But I could be wrong, so I wait for
your reply.

Back to the original search: There is no subjectfield value in those 3
messages, so no subject gets inserted. And that makes dbmail-util never
successful in its cleanup. Maybe a "" subject should be inserted for
messages that don't have it?

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .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: wwwkeys.eu.pgp.net Key-ID: 1C1209B4

Attachments:

dbmail.log (6.05 KB)

paul at nfg

Mar 17, 2009, 2:23 AM

Post #2 of 6 (1334 views)
Permalink

Re: Bug in dbmail-util - not cleaning un-cached physmessages, and making other mess [In reply to]

Michael Monnerie wrote:
> No matter how often I run dbmail-util -by, I always get:
>
> Repairing DBMAIL for cached header values...
> Ok. Found [3] un-cached physmessages.
>
> In the code, this generates (in function db.c/db_icheck_headercache )
> SELECT p.id FROM dbmail_physmessage p LEFT JOIN dbmail_headervalue h ON
> p.id = h.physmessage_id WHERE h.physmessage_id IS NULL;
> which really returns 3 id's.
>
> Then function db.c/db_set_headercache should cleanup the mess, but
> doesn't. That function only logs:
> Mar 17 09:04:38 mailsrv2.i.zmi.at dbmail/maintenance[9101]: Message:
> [message] dbmail-message.c,dbmail_message_cache_subjectfield(+1207): no
> subject field value [1838862]
> Mar 17 09:04:38 mailsrv2.i.zmi.at dbmail/maintenance[9101]: Message:
> [message] dbmail-message.c,dbmail_message_cache_subjectfield(+1207): no
> subject field value [1838861]
> Mar 17 09:04:38 mailsrv2.i.zmi.at dbmail/maintenance[9101]: Message:
> [message] dbmail-message.c,dbmail_message_cache_subjectfield(+1207): no
> subject field value [1838858]
>
> There doesn't seem to be an error, as there are no logs for that, but
> still it's not working. Then I set TRACE_SYSLOG = 5 and re-ran dbmail-
> util. See attached dbmail.log
>
> From those logs, you can see that a dbmail_datefield and dbmail_envelope
> with a date are inserted into the db. That's a mess already:
> select * from dbmail_datefield where physmessage_id = 1838862;
> physmessage_id | id | datefield
> ----------------+---------+---------------------
> 1838862 | 1307186 | 1970-01-01 00:00:00
> 1838862 | 1307099 | 1970-01-01 00:00:00
> 1838862 | 1306945 | 1970-01-01 00:00:00
> 1838862 | 1306701 | 1970-01-01 00:00:00
> 1838862 | 1306400 | 1970-01-01 00:00:00
> 1838862 | 1307612 | 1970-01-01 00:00:00
> 1838862 | 1307616 | 1970-01-01 00:00:00
> 1838862 | 1307564 | 1970-01-01 00:00:00
> 1838862 | 1307619 | 1970-01-01 00:00:00
> 1838862 | 1307350 | 1970-01-01 00:00:00
> 1838862 | 1307609 | 1970-01-01 00:00:00
>
> Wouldn't a single physmessage_id entry be enough in dbmail_datefield? If
> yes, the index dbmail_datefield_1, which currently is UNIQUE
> (physmessage_id, id) could be changed to UNIQUE (physmessage_id).

Sounds good.

>
> And the same goes for dbmail_datefield I suppose, and maybe others also.
> Paul, if you say I'm right I'd deliver SQL statements to cleanup maybe
> existing mess, and change indices. But I could be wrong, so I wait for
> your reply.

Please do.

> Back to the original search: There is no subjectfield value in those 3
> messages, so no subject gets inserted. And that makes dbmail-util never
> successful in its cleanup. Maybe a "" subject should be inserted for
> messages that don't have it?

Correct, a perfectly valid empty subject shouldn't lead to any kind of
failure.

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

michael.monnerie at is

Mar 17, 2009, 6:08 AM

Post #3 of 6 (1337 views)
Permalink

Re: Bug in dbmail-util - not cleaning un-cached physmessages, and making other mess [In reply to]

On Dienstag 17 M�rz 2009 Paul J Stevens wrote:
> > Wouldn't a single physmessage_id entry be enough in
> > dbmail_datefield? If yes, the index dbmail_datefield_1, which
> > currently is UNIQUE (physmessage_id, id) could be changed to UNIQUE
> > (physmessage_id).
>
> Sounds good.
>
> > And the same goes for dbmail_datefield I suppose, and maybe others
> > also. Paul, if you say I'm right I'd deliver SQL statements to
> > cleanup maybe existing mess, and change indices. But I could be
> > wrong, so I wait for your reply.
>
> Please do.

Do you think there will ever be a patch in the 2.2 series to apply my
changes? I think no, as 2.2 is end-of-life from devs pov. So I wonder if
it's worth the effort at all. But that changes could help find bugs like
this one, as the db will complain on duplicate indices.

> > Back to the original search: There is no subjectfield value in
> > those 3 messages, so no subject gets inserted. And that makes
> > dbmail-util never successful in its cleanup. Maybe a "" subject
> > should be inserted for messages that don't have it?
>
> Correct, a perfectly valid empty subject shouldn't lead to any kind
> of failure.

Does that mean it will be worked on? Should I file a bug report?

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .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: wwwkeys.eu.pgp.net Key-ID: 1C1209B4

Attachments:

signature.asc (0.19 KB)

josh at worldhosting

Mar 18, 2009, 3:41 PM

Post #4 of 6 (1331 views)
Permalink

Re: Bug in dbmail-util - not cleaning un-cached physmessages, and making other mess [In reply to]

> > Wouldn't a single physmessage_id entry be enough in dbmail_datefield? If
> > yes, the index dbmail_datefield_1, which currently is UNIQUE
> > (physmessage_id, id) could be changed to UNIQUE (physmessage_id).

This is interesting. I'm using MySQL here and the UNIQUE is keyed off
the physmessage_id and the datefield. So when I see this problem I get
errors inserting the date due to the duplicate. Again, I get this
repeatedly as the real problem (in this case missing headers - from, to,
subject etc) is not repaired due to it halting on the first error.

Maybe the INSERT INTO here can be a REPLACE INTO to avoid duplicates,
otherwise ignore any duplicate errors as obviously that particular
header already exists.

Regards,
Josh.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

josh at worldhosting

Mar 18, 2009, 3:47 PM

Post #5 of 6 (1331 views)
Permalink

Re: Bug in dbmail-util - not cleaning un-cached physmessages, and making other mess [In reply to]

On Thu, 2009-03-19 at 08:41 +1000, Josh Marshall wrote:
> Maybe the INSERT INTO here can be a REPLACE INTO to avoid duplicates,
> otherwise ignore any duplicate errors as obviously that particular
> header already exists.

^^^ Or do a SELECT just prior to INSERT so that an error doesn't have to
be processed. Since this is only done during the maintenance time it
shouldn't impact speed too much

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

michael.monnerie at is

Mar 19, 2009, 2:38 AM

Post #6 of 6 (1329 views)
Permalink

Re: Bug in dbmail-util - not cleaning un-cached physmessages, and making other mess [In reply to]

On Mittwoch 18 M�rz 2009 Josh Marshall wrote:
> This is interesting. I'm using MySQL here and the UNIQUE is keyed off
> the physmessage_id and the datefield.

Weird. There's a bug somewhere. I guess the db indices shouldn't be
different between SQL flavors.
Can someone confirm that mysql & postgresql have different indices here?
That should be a bug, right?
And then: what would be the right definition of dbmail_datefield_1?
In his last mail, Paul confirmed my idea:

>> Wouldn't a single physmessage_id entry be enough in dbmail_datefield?
>> If yes, the index dbmail_datefield_1, which currently is UNIQUE
>> (physmessage_id, id) could be changed to UNIQUE (physmessage_id).
> Sounds good.

But then we didn't know about the mysql case. So now I'm not sure if
that index should be about (physmessage_id) only or
(physmessage_id,date). Doesn't sound too bad also. I still think the
single (physmessage_id) would be enough, as we don't want two dates for
a single physmessage at all, right?

> So when I see this problem I
> get errors inserting the date due to the duplicate. Again, I get this
> repeatedly as the real problem (in this case missing headers - from,
> to, subject etc) is not repaired due to it halting on the first
> error.
>
> Maybe the INSERT INTO here can be a REPLACE INTO to avoid duplicates,
> otherwise ignore any duplicate errors as obviously that particular
> header already exists.

The right thing to do is to not insert duplicates, not even try it. The
question is: why does dbmail try to insert a value that exists already?
"dbmail-util -by" should only cache missing physmessages. The code
should be fixed there. If that needs a SELECT prior to INSERT or
whatever is in the coders hand ;-)

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .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: wwwkeys.eu.pgp.net Key-ID: 1C1209B4

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads