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
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [4] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Info:[db]
dbmodule.c,db_query(+151): slow query [SELECT p.id FROM dbmail_physmessage p
LEFT JOIN dbmail_headervalue h ON p.id = h.physmessage_id WHERE
h.physmessage_id IS NULL] took [4] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [SELECT messageblk, is_header FROM dbmail_messageblks
WHERE physmessage_id = 1838862 AND is_header = '1']
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[message]
dbmail-message.c,_set_content_from_stream(+425): parse message
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [BEGIN]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [INSERT INTO dbmail_datefield (physmessage_id,
datefield) VALUES (1838862,'1970-01-01 00:00:00')]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Message:[message]
dbmail-message.c,dbmail_message_cache_subjectfield(+1207): no subject field
value [1838862]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [INSERT INTO dbmail_envelope (physmessage_id,
envelope) VALUES (1838862,'("Thu, 01 Jan 1970 00:00:00 +0000" NIL NIL NIL NIL
NIL NIL NIL NIL NIL)')]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [COMMIT]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
db.c,db_commit_transaction(+214): last transaction took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [SELECT messageblk, is_header FROM dbmail_messageblks
WHERE physmessage_id = 1838861 AND is_header = '1']
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[message]
dbmail-message.c,_set_content_from_stream(+425): parse message
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [BEGIN]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [INSERT INTO dbmail_datefield (physmessage_id,
datefield) VALUES (1838861,'1970-01-01 00:00:00')]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Message:[message]
dbmail-message.c,dbmail_message_cache_subjectfield(+1207): no subject field
value [1838861]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [INSERT INTO dbmail_envelope (physmessage_id,
envelope) VALUES (1838861,'("Thu, 01 Jan 1970 00:00:00 +0000" NIL NIL NIL NIL
NIL NIL NIL NIL NIL)')]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [COMMIT]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
db.c,db_commit_transaction(+214): last transaction took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [SELECT messageblk, is_header FROM dbmail_messageblks
WHERE physmessage_id = 1838858 AND is_header = '1']
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[message]
dbmail-message.c,_set_content_from_stream(+425): parse message
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [BEGIN]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [INSERT INTO dbmail_datefield (physmessage_id,
datefield) VALUES (1838858,'1970-01-01 00:00:00')]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Message:[message]
dbmail-message.c,dbmail_message_cache_subjectfield(+1207): no subject field
value [1838858]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [INSERT INTO dbmail_envelope (physmessage_id,
envelope) VALUES (1838858,'("Thu, 01 Jan 1970 00:00:00 +0000" NIL NIL NIL NIL
NIL NIL NIL NIL NIL)')]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[sql]
dbpgsql.c,db_query(+279): [COMMIT]
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
dbmodule.c,db_query(+145): last query took [0] seconds
Mar 17 09:08:02 mailsrv2.i.zmi.at dbmail/maintenance[9114]: Debug:[db]
db.c,db_commit_transaction(+214): last transaction took [0] seconds
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
