Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: DBMail: users

Imap sort broken? (3.0.2)

 

 

DBMail users RSS feed   Index | Next | Previous | View Threaded


harald.leithner at itronic

Mar 28, 2012, 3:46 AM

Post #1 of 17 (1404 views)
Permalink
Imap sort broken? (3.0.2)

Hi,

I upgraded to DBmail 3.0.2 and it seams that

UID SORT (DATE) US-ASCII ALL

is broken. While

UID SORT (ARRIVAL) US-ASCII ALL

seams to work well.


-----> C= 1332931088 username CLIENT: sd [8]
A0006 UID SORT (ARRIVAL) US-ASCII ALL^


-----> C= 1332931088 username SERVER: sd [11]
* SORT 3228855 3228856 3463460^
A0006 OK UID SORT completed^

This seam to be right but with DATE it brings another order.

-----> C= 1332931093 username CLIENT: sd [9]
A0006 UID SORT (DATE) US-ASCII ALL^


-----> C= 1332931093 username SERVER: sd [11]
* SORT 3463460 3228855 3228856^
A0006 OK UID SORT completed^


--- Dates:
3228855: date: Wed, 17 Aug 2011 09:55:29 +0200
3228856: date: Thu, 15 Sep 2011 19:21:12 +0200
3463460: date: Sun, 29 Jan 2012 03:12:22 -0600

Paul did you have any idea? Is there any task I have to do after upgrading
to 3.0.2?

thx

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


paul at nfg

Mar 28, 2012, 5:35 AM

Post #2 of 17 (1357 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

On 03/28/2012 12:46 PM, ITronic Harald Leithner wrote:
> Hi,
>
> I upgraded to DBmail 3.0.2 and it seams that
>
> UID SORT (DATE) US-ASCII ALL
>
> is broken. While
>
> UID SORT (ARRIVAL) US-ASCII ALL
>
> seams to work well.
>
>
> -----> C= 1332931088 username CLIENT: sd [8]
> A0006 UID SORT (ARRIVAL) US-ASCII ALL^
>
>
> -----> C= 1332931088 username SERVER: sd [11]
> * SORT 3228855 3228856 3463460^
> A0006 OK UID SORT completed^
>
> This seam to be right but with DATE it brings another order.
>
> -----> C= 1332931093 username CLIENT: sd [9]
> A0006 UID SORT (DATE) US-ASCII ALL^
>
>
> -----> C= 1332931093 username SERVER: sd [11]
> * SORT 3463460 3228855 3228856^
> A0006 OK UID SORT completed^
>
>
> --- Dates:
> 3228855: date: Wed, 17 Aug 2011 09:55:29 +0200
> 3228856: date: Thu, 15 Sep 2011 19:21:12 +0200
> 3463460: date: Sun, 29 Jan 2012 03:12:22 -0600

Please not that ARRIVAL sorts on the envelope date (time of arrival),
while DATE sorts by date-time in the Date: header. So different results
are to be expected.

> Paul did you have any idea? Is there any task I have to do after
> upgrading to 3.0.2?

Yes. You need to cleanout and rebuild the headercache since for old
messages sorting and searching by date will be broken.

That shouldn't have gone into the stable branch without lots of
warnings. Sorry about that.





--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


harald.leithner at itronic

Mar 28, 2012, 5:48 AM

Post #3 of 17 (1353 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

Hi,

no Problem. I will do this tonight because this would take sometime to
rebuild 3 GB on header informations.

to be safe I just connect to mysql and do:
TRUNCATE `dbmail_header`;
TRUNCATE `dbmail_headername`;
TRUNCATE `dbmail_headervalue`;

after that I make.
dbmail-util -ay

correct?

thx

Harald

Am 28.03.2012, 14:35 Uhr, schrieb Paul J Stevens <paul [at] nfg>:

> On 03/28/2012 12:46 PM, ITronic Harald Leithner wrote:
>> Hi,
>>
>> I upgraded to DBmail 3.0.2 and it seams that
>>
>> UID SORT (DATE) US-ASCII ALL
>>
>> is broken. While
>>
>> UID SORT (ARRIVAL) US-ASCII ALL
>>
>> seams to work well.
>>
>>
>> -----> C= 1332931088 username CLIENT: sd [8]
>> A0006 UID SORT (ARRIVAL) US-ASCII ALL^
>>
>>
>> -----> C= 1332931088 username SERVER: sd [11]
>> * SORT 3228855 3228856 3463460^
>> A0006 OK UID SORT completed^
>>
>> This seam to be right but with DATE it brings another order.
>>
>> -----> C= 1332931093 username CLIENT: sd [9]
>> A0006 UID SORT (DATE) US-ASCII ALL^
>>
>>
>> -----> C= 1332931093 username SERVER: sd [11]
>> * SORT 3463460 3228855 3228856^
>> A0006 OK UID SORT completed^
>>
>>
>> --- Dates:
>> 3228855: date: Wed, 17 Aug 2011 09:55:29 +0200
>> 3228856: date: Thu, 15 Sep 2011 19:21:12 +0200
>> 3463460: date: Sun, 29 Jan 2012 03:12:22 -0600
>
> Please not that ARRIVAL sorts on the envelope date (time of arrival),
> while DATE sorts by date-time in the Date: header. So different results
> are to be expected.
>
>> Paul did you have any idea? Is there any task I have to do after
>> upgrading to 3.0.2?
>
> Yes. You need to cleanout and rebuild the headercache since for old
> messages sorting and searching by date will be broken.
>
> That shouldn't have gone into the stable branch without lots of
> warnings. Sorry about that.
>
>
>
>
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


paul at nfg

Mar 28, 2012, 5:57 AM

Post #4 of 17 (1351 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

On 03/28/2012 02:48 PM, ITronic Harald Leithner wrote:
> Hi,
>
> no Problem. I will do this tonight because this would take sometime to
> rebuild 3 GB on header informations.
>
> to be safe I just connect to mysql and do:
> TRUNCATE `dbmail_header`;
> TRUNCATE `dbmail_headername`;
> TRUNCATE `dbmail_headervalue`;

correct.

> after that I make.
> dbmail-util -ay

dbmail-util -by

is sufficient.


--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


h.reindl at thelounge

Mar 28, 2012, 11:04 AM

Post #5 of 17 (1351 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

Am 28.03.2012 14:57, schrieb Paul J Stevens:
> On 03/28/2012 02:48 PM, ITronic Harald Leithner wrote:
>> no Problem. I will do this tonight because this would take sometime to
>> rebuild 3 GB on header informations.
>>
>> to be safe I just connect to mysql and do:
>> TRUNCATE `dbmail_header`;
>> TRUNCATE `dbmail_headername`;
>> TRUNCATE `dbmail_headervalue`;
>
> correct.
>
>> after that I make.
>> dbmail-util -ay
>
> dbmail-util -by
>
> is sufficient

Hi

i just let this run on a copy of our replication slave
foreign keys have to be disabled for the operation!

a very long time with messages missing subjects
Mi 28. Mär 18:40:49 CEST 2012
Mi 28. Mär 19:54:48 CEST 2012

i think an extra cli-switch for "dbmail-util" which does
this message for message would be a good idea. loop
phymessages, remove all header-records for this message
and re-create them, this way only one message without
subject would appear at the same time
___________________________________________________________

BTW:
"dbmail_headervalue" is not cleaned up by "dbmail-util"
see the differnce between rebuilt snapshot to liveserver
after get rid around 20000 messages the last weeks

mysql> select count(*) from dbmail_headervalue;
+----------+
| count(*) |
+----------+
| 519747 |
+----------+
1 row in set (0.16 sec)

mysql> select count(*) from dbmail_headervalue;
+----------+
| count(*) |
+----------+
| 1603038 |
+----------+
1 row in set (1.80 sec)
___________________________________________________________

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `dbmail_header`;
TRUNCATE `dbmail_headername`;
TRUNCATE `dbmail_headervalue`;
SET FOREIGN_KEY_CHECKS=1;

dbmail-util -by
Attachments: signature.asc (0.26 KB)


harald.leithner at itronic

Mar 29, 2012, 1:34 AM

Post #6 of 17 (1353 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

Hi,

So I truncating the cache was successful, but I got duplicate error
message while rebuilding:

---
Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message]
insert_field_cache(+1775): insert referencesfield failed
[1d4e44360608081238p3dfdaea5xac3aabc057bd5f3c [at] mail]
..Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message]
insert_field_cache(+1773): SQLException: Duplicate entry
'221-44D97BCE.9070906 [at] domainname' for key 'physmessage_id'
Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message]
insert_field_cache(+1775): insert referencesfield failed
[44D97BCE.9070906 [at] domainname]
..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message]
insert_field_cache(+1773): SQLException: Duplicate entry
'220-44D97C5E.3 [at] domainname' for key 'physmessage_id'
Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message]
insert_field_cache(+1775): insert referencesfield failed
[44D97C5E.3 [at] domainname]
..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message]
insert_field_cache(+1773): SQLException: Duplicate entry
'219-44D97F0D.3070909 [at] domainname' for key 'physmessage_id'
Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message]
insert_field_cache(+1775): insert referencesfield failed
[44D97F0D.3070909 [at] domainname]
..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message]
insert_field_cache(+1773): SQLException: Duplicate entry
'218-44D97F9A.2080007 [at] domainname' for key 'physmessage_id'
Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message]
insert_field_cache(+1775): insert referencesfield failed
[44D97F9A.2080007 [at] domainname]
..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message]
insert_field_cache(+1773): SQLException: Duplicate entry
'217-44D983B3.8030007 [at] domainname' for key 'physmessage_id'
Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message]
insert_field_cache(+1775): insert referencesfield failed
[44D983B3.8030007 [at] domainname]
---

But I don't get this for all messages, is this a problem?

Btw webmail works perfect now.


About the Headervalues:

before rebuild I had about 1,9 Million entries after rebuild 1,7 Million
entries.

If I interpret the source of dbmail util correctly, it only delete
messages from messages table.
Thats good for all tables using constraints but the dbmail_headervalue
table has no constraints.
Only dmail_header has a constraint to dbmail_headervalue which cascade on
delete but does this every happen?
Would the other direction be better? Is this possible?

Another fix would be a simple query for clean up dbmail_headervalue (maybe
trigger?)
"delete from dbmail_headervalue where id not in
(dbmail_header.headervalue_id);" (not tested)

Regards,

Harald


Am 28.03.2012, 20:04 Uhr, schrieb Reindl Harald <h.reindl [at] thelounge>:

>
> Am 28.03.2012 14:57, schrieb Paul J Stevens:
>> On 03/28/2012 02:48 PM, ITronic Harald Leithner wrote:
>>> no Problem. I will do this tonight because this would take sometime to
>>> rebuild 3 GB on header informations.
>>>
>>> to be safe I just connect to mysql and do:
>>> TRUNCATE `dbmail_header`;
>>> TRUNCATE `dbmail_headername`;
>>> TRUNCATE `dbmail_headervalue`;
>>
>> correct.
>>
>>> after that I make.
>>> dbmail-util -ay
>>
>> dbmail-util -by
>>
>> is sufficient
>
> Hi
>
> i just let this run on a copy of our replication slave
> foreign keys have to be disabled for the operation!
>
> a very long time with messages missing subjects
> Mi 28. Mär 18:40:49 CEST 2012
> Mi 28. Mär 19:54:48 CEST 2012
>
> i think an extra cli-switch for "dbmail-util" which does
> this message for message would be a good idea. loop
> phymessages, remove all header-records for this message
> and re-create them, this way only one message without
> subject would appear at the same time
> ___________________________________________________________
>
> BTW:
> "dbmail_headervalue" is not cleaned up by "dbmail-util"
> see the differnce between rebuilt snapshot to liveserver
> after get rid around 20000 messages the last weeks
>
> mysql> select count(*) from dbmail_headervalue;
> +----------+
> | count(*) |
> +----------+
> | 519747 |
> +----------+
> 1 row in set (0.16 sec)
>
> mysql> select count(*) from dbmail_headervalue;
> +----------+
> | count(*) |
> +----------+
> | 1603038 |
> +----------+
> 1 row in set (1.80 sec)
> ___________________________________________________________
>
> SET FOREIGN_KEY_CHECKS=0;
> TRUNCATE `dbmail_header`;
> TRUNCATE `dbmail_headername`;
> TRUNCATE `dbmail_headervalue`;
> SET FOREIGN_KEY_CHECKS=1;
>
> dbmail-util -by
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


harald.leithner at itronic

Apr 2, 2012, 3:25 AM

Post #7 of 17 (1342 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

Paul,

any statement to the "referencesfield" Problem?

thx
Harald

Am 29.03.2012, 10:34 Uhr, schrieb ITronic Harald Leithner
<harald.leithner [at] itronic>:

> Hi,
>
> So I truncating the cache was successful, but I got duplicate error
> message while rebuilding:
>
> ---
> Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040]
> Error:[message] insert_field_cache(+1775): insert referencesfield failed
> [1d4e44360608081238p3dfdaea5xac3aabc057bd5f3c [at] mail]
> ..Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040]
> Error:[message] insert_field_cache(+1773): SQLException: Duplicate entry
> '221-44D97BCE.9070906 [at] domainname' for key 'physmessage_id'
> Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040]
> Error:[message] insert_field_cache(+1775): insert referencesfield failed
> [44D97BCE.9070906 [at] domainname]
> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040]
> Error:[message] insert_field_cache(+1773): SQLException: Duplicate entry
> '220-44D97C5E.3 [at] domainname' for key 'physmessage_id'
> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040]
> Error:[message] insert_field_cache(+1775): insert referencesfield failed
> [44D97C5E.3 [at] domainname]
> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040]
> Error:[message] insert_field_cache(+1773): SQLException: Duplicate entry
> '219-44D97F0D.3070909 [at] domainname' for key 'physmessage_id'
> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040]
> Error:[message] insert_field_cache(+1775): insert referencesfield failed
> [44D97F0D.3070909 [at] domainname]
> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040]
> Error:[message] insert_field_cache(+1773): SQLException: Duplicate entry
> '218-44D97F9A.2080007 [at] domainname' for key 'physmessage_id'
> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040]
> Error:[message] insert_field_cache(+1775): insert referencesfield failed
> [44D97F9A.2080007 [at] domainname]
> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040]
> Error:[message] insert_field_cache(+1773): SQLException: Duplicate entry
> '217-44D983B3.8030007 [at] domainname' for key 'physmessage_id'
> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040]
> Error:[message] insert_field_cache(+1775): insert referencesfield failed
> [44D983B3.8030007 [at] domainname]
> ---
>
> But I don't get this for all messages, is this a problem?
>
> Btw webmail works perfect now.
>
>
> About the Headervalues:
>
> before rebuild I had about 1,9 Million entries after rebuild 1,7 Million
> entries.
>
> If I interpret the source of dbmail util correctly, it only delete
> messages from messages table.
> Thats good for all tables using constraints but the dbmail_headervalue
> table has no constraints.
> Only dmail_header has a constraint to dbmail_headervalue which cascade
> on delete but does this every happen?
> Would the other direction be better? Is this possible?
>
> Another fix would be a simple query for clean up dbmail_headervalue
> (maybe trigger?)
> "delete from dbmail_headervalue where id not in
> (dbmail_header.headervalue_id);" (not tested)
>
> Regards,
>
> Harald
>
>
> Am 28.03.2012, 20:04 Uhr, schrieb Reindl Harald <h.reindl [at] thelounge>:
>
>>
>> Am 28.03.2012 14:57, schrieb Paul J Stevens:
>>> On 03/28/2012 02:48 PM, ITronic Harald Leithner wrote:
>>>> no Problem. I will do this tonight because this would take sometime to
>>>> rebuild 3 GB on header informations.
>>>>
>>>> to be safe I just connect to mysql and do:
>>>> TRUNCATE `dbmail_header`;
>>>> TRUNCATE `dbmail_headername`;
>>>> TRUNCATE `dbmail_headervalue`;
>>>
>>> correct.
>>>
>>>> after that I make.
>>>> dbmail-util -ay
>>>
>>> dbmail-util -by
>>>
>>> is sufficient
>>
>> Hi
>>
>> i just let this run on a copy of our replication slave
>> foreign keys have to be disabled for the operation!
>>
>> a very long time with messages missing subjects
>> Mi 28. Mär 18:40:49 CEST 2012
>> Mi 28. Mär 19:54:48 CEST 2012
>>
>> i think an extra cli-switch for "dbmail-util" which does
>> this message for message would be a good idea. loop
>> phymessages, remove all header-records for this message
>> and re-create them, this way only one message without
>> subject would appear at the same time
>> ___________________________________________________________
>>
>> BTW:
>> "dbmail_headervalue" is not cleaned up by "dbmail-util"
>> see the differnce between rebuilt snapshot to liveserver
>> after get rid around 20000 messages the last weeks
>>
>> mysql> select count(*) from dbmail_headervalue;
>> +----------+
>> | count(*) |
>> +----------+
>> | 519747 |
>> +----------+
>> 1 row in set (0.16 sec)
>>
>> mysql> select count(*) from dbmail_headervalue;
>> +----------+
>> | count(*) |
>> +----------+
>> | 1603038 |
>> +----------+
>> 1 row in set (1.80 sec)
>> ___________________________________________________________
>>
>> SET FOREIGN_KEY_CHECKS=0;
>> TRUNCATE `dbmail_header`;
>> TRUNCATE `dbmail_headername`;
>> TRUNCATE `dbmail_headervalue`;
>> SET FOREIGN_KEY_CHECKS=1;
>>
>> dbmail-util -by
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


h.reindl at thelounge

Apr 2, 2012, 3:28 AM

Post #8 of 17 (1341 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

this is not really a problem and is triggered
by the fact the we truncated only the header-tables
while the references table had already data

more a problem is that headervalues is growing and
growing over the time and after few months it
uses more index-space as the rest of the database

Am 02.04.2012 12:25, schrieb ITronic Harald Leithner:
> Paul,
>
> any statement to the "referencesfield" Problem?
>
> thx
> Harald
>
> Am 29.03.2012, 10:34 Uhr, schrieb ITronic Harald Leithner <harald.leithner [at] itronic>:
>
>> Hi,
>>
>> So I truncating the cache was successful, but I got duplicate error message while rebuilding:
>>
>> ---
>> Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] insert_field_cache(+1775): insert
>> referencesfield failed [1d4e44360608081238p3dfdaea5xac3aabc057bd5f3c [at] mail]
>> ..Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] insert_field_cache(+1773):
>> SQLException: Duplicate entry '221-44D97BCE.9070906 [at] domainname' for key 'physmessage_id'
>> Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] insert_field_cache(+1775): insert
>> referencesfield failed [44D97BCE.9070906 [at] domainname]
>> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] insert_field_cache(+1773):
>> SQLException: Duplicate entry '220-44D97C5E.3 [at] domainname' for key 'physmessage_id'
>> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] insert_field_cache(+1775): insert
>> referencesfield failed [44D97C5E.3 [at] domainname]
>> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] insert_field_cache(+1773):
>> SQLException: Duplicate entry '219-44D97F0D.3070909 [at] domainname' for key 'physmessage_id'
>> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] insert_field_cache(+1775): insert
>> referencesfield failed [44D97F0D.3070909 [at] domainname]
>> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] insert_field_cache(+1773):
>> SQLException: Duplicate entry '218-44D97F9A.2080007 [at] domainname' for key 'physmessage_id'
>> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] insert_field_cache(+1775): insert
>> referencesfield failed [44D97F9A.2080007 [at] domainname]
>> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] insert_field_cache(+1773):
>> SQLException: Duplicate entry '217-44D983B3.8030007 [at] domainname' for key 'physmessage_id'
>> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] insert_field_cache(+1775): insert
>> referencesfield failed [44D983B3.8030007 [at] domainname]
>> ---
>>
>> But I don't get this for all messages, is this a problem?
>>
>> Btw webmail works perfect now.
>>
>>
>> About the Headervalues:
>>
>> before rebuild I had about 1,9 Million entries after rebuild 1,7 Million entries.
>>
>> If I interpret the source of dbmail util correctly, it only delete messages from messages table.
>> Thats good for all tables using constraints but the dbmail_headervalue table has no constraints.
>> Only dmail_header has a constraint to dbmail_headervalue which cascade on delete but does this every happen?
>> Would the other direction be better? Is this possible?
>>
>> Another fix would be a simple query for clean up dbmail_headervalue (maybe trigger?)
>> "delete from dbmail_headervalue where id not in (dbmail_header.headervalue_id);" (not tested)
>>
>> Regards,
>>
>> Harald
>>
>>
>> Am 28.03.2012, 20:04 Uhr, schrieb Reindl Harald <h.reindl [at] thelounge>:
>>
>>>
>>> Am 28.03.2012 14:57, schrieb Paul J Stevens:
>>>> On 03/28/2012 02:48 PM, ITronic Harald Leithner wrote:
>>>>> no Problem. I will do this tonight because this would take sometime to
>>>>> rebuild 3 GB on header informations.
>>>>>
>>>>> to be safe I just connect to mysql and do:
>>>>> TRUNCATE `dbmail_header`;
>>>>> TRUNCATE `dbmail_headername`;
>>>>> TRUNCATE `dbmail_headervalue`;
>>>>
>>>> correct.
>>>>
>>>>> after that I make.
>>>>> dbmail-util -ay
>>>>
>>>> dbmail-util -by
>>>>
>>>> is sufficient
>>>
>>> Hi
>>>
>>> i just let this run on a copy of our replication slave
>>> foreign keys have to be disabled for the operation!
>>>
>>> a very long time with messages missing subjects
>>> Mi 28. Mär 18:40:49 CEST 2012
>>> Mi 28. Mär 19:54:48 CEST 2012
>>>
>>> i think an extra cli-switch for "dbmail-util" which does
>>> this message for message would be a good idea. loop
>>> phymessages, remove all header-records for this message
>>> and re-create them, this way only one message without
>>> subject would appear at the same time
>>> ___________________________________________________________
>>>
>>> BTW:
>>> "dbmail_headervalue" is not cleaned up by "dbmail-util"
>>> see the differnce between rebuilt snapshot to liveserver
>>> after get rid around 20000 messages the last weeks
>>>
>>> mysql> select count(*) from dbmail_headervalue;
>>> +----------+
>>> | count(*) |
>>> +----------+
>>> | 519747 |
>>> +----------+
>>> 1 row in set (0.16 sec)
>>>
>>> mysql> select count(*) from dbmail_headervalue;
>>> +----------+
>>> | count(*) |
>>> +----------+
>>> | 1603038 |
>>> +----------+
>>> 1 row in set (1.80 sec)
>>> ___________________________________________________________
>>>
>>> SET FOREIGN_KEY_CHECKS=0;
>>> TRUNCATE `dbmail_header`;
>>> TRUNCATE `dbmail_headername`;
>>> TRUNCATE `dbmail_headervalue`;
>>> SET FOREIGN_KEY_CHECKS=1;
>>>
>>> dbmail-util -by
Attachments: signature.asc (0.26 KB)


paul at nfg

Apr 2, 2012, 3:45 AM

Post #9 of 17 (1343 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

On 04/02/2012 12:28 PM, Reindl Harald wrote:
> this is not really a problem and is triggered
> by the fact the we truncated only the header-tables
> while the references table had already data
>

Correct.

> more a problem is that headervalues is growing and
> growing over the time and after few months it
> uses more index-space as the rest of the database

Prune it!

http://blog.dbmail.eu/archives/12-Pruning-the-dbmail_headervalue-table.html


--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


h.reindl at thelounge

Apr 2, 2012, 3:54 AM

Post #10 of 17 (1345 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

Am 02.04.2012 12:45, schrieb Paul J Stevens:
> On 04/02/2012 12:28 PM, Reindl Harald wrote:
>> this is not really a problem and is triggered
>> by the fact the we truncated only the header-tables
>> while the references table had already data
>>
>
> Correct.
>
>> more a problem is that headervalues is growing and
>> growing over the time and after few months it
>> uses more index-space as the rest of the database
>
> Prune it!
>
> http://blog.dbmail.eu/archives/12-Pruning-the-dbmail_headervalue-table.html

hm - don't get me wrong but should dbmail not
avoid to insert unused records there?

the more problem currently is that it contains
even headers of long time deleted messages and
will not be cleaned at all
Attachments: signature.asc (0.26 KB)


h.reindl at thelounge

Apr 2, 2012, 4:21 AM

Post #11 of 17 (1341 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

Am 02.04.2012 12:45, schrieb Paul J Stevens:
> On 04/02/2012 12:28 PM, Reindl Harald wrote:
>> this is not really a problem and is triggered
>> by the fact the we truncated only the header-tables
>> while the references table had already data
>>
>
> Correct.
>
>> more a problem is that headervalues is growing and
>> growing over the time and after few months it
>> uses more index-space as the rest of the database
>
> Prune it!
>
> http://blog.dbmail.eu/archives/12-Pruning-the-dbmail_headervalue-table.html

however - how is this supposed to work?
DELETE FROM dbmail_headername WHERE headername='received' or headername='recieved' or headername='user-agent' or
headername='Received';

this will remove the 'headername'-records
but not anything in `dbmail_headervalue` which is the table with the hughe record-count
______________________________


CREATE TABLE `dbmail_header` (
`physmessage_id` bigint(20) unsigned NOT NULL,
`headername_id` bigint(20) unsigned NOT NULL,
`headervalue_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`physmessage_id`,`headername_id`,`headervalue_id`),
KEY `physmessage_id` (`physmessage_id`),
KEY `headername_id` (`headername_id`),
KEY `headervalue_id` (`headervalue_id`),
KEY `physmessage_id_headername_id` (`physmessage_id`,`headername_id`),
KEY `physmessage_id_headervalue_id` (`physmessage_id`,`headervalue_id`),
KEY `headername_id_headervalue_id` (`headername_id`,`headervalue_id`),
CONSTRAINT `dbmail_header_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_header_ibfk_2` FOREIGN KEY (`headername_id`) REFERENCES `dbmail_headername` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_header_ibfk_3` FOREIGN KEY (`headervalue_id`) REFERENCES `dbmail_headervalue` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

CREATE TABLE `dbmail_headername` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`headername` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `headername` (`headername`)
) ENGINE=InnoDB AUTO_INCREMENT=1936 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

CREATE TABLE `dbmail_headervalue` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`hash` varchar(255) NOT NULL,
`headervalue` text NOT NULL,
`sortfield` varchar(255) DEFAULT NULL,
`datefield` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `hash` (`hash`),
KEY `headervalue` (`headervalue`(255)),
KEY `sortfield` (`sortfield`),
KEY `datefield` (`datefield`)
) ENGINE=InnoDB AUTO_INCREMENT=568824 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
Attachments: signature.asc (0.26 KB)


harald.leithner at itronic

Apr 2, 2012, 5:03 AM

Post #12 of 17 (1339 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

With this query you will find all unconnected headervalues:

select * from dbmail_headervalue where id not in (select headervalue_id
from dbmail_header);

In my case there are 245,257 unused values while purging 4000 messages, so
I could do a cronjob with:

delete from dbmail_headervalue where id not in (select headervalue_id
from dbmail_header);

daily or once a week, but this should be done by dbmail-util in my
opinion, in or after db_icheck_physmessages.




Am 02.04.2012, 13:21 Uhr, schrieb Reindl Harald <h.reindl [at] thelounge>:

>
>
> Am 02.04.2012 12:45, schrieb Paul J Stevens:
>> On 04/02/2012 12:28 PM, Reindl Harald wrote:
>>> this is not really a problem and is triggered
>>> by the fact the we truncated only the header-tables
>>> while the references table had already data
>>>
>>
>> Correct.
>>
>>> more a problem is that headervalues is growing and
>>> growing over the time and after few months it
>>> uses more index-space as the rest of the database
>>
>> Prune it!
>>
>> http://blog.dbmail.eu/archives/12-Pruning-the-dbmail_headervalue-table.html
>
> however - how is this supposed to work?
> DELETE FROM dbmail_headername WHERE headername='received' or
> headername='recieved' or headername='user-agent' or
> headername='Received';
>
> this will remove the 'headername'-records
> but not anything in `dbmail_headervalue` which is the table with the
> hughe record-count
> ______________________________
>
>
> CREATE TABLE `dbmail_header` (
> `physmessage_id` bigint(20) unsigned NOT NULL,
> `headername_id` bigint(20) unsigned NOT NULL,
> `headervalue_id` bigint(20) unsigned NOT NULL,
> PRIMARY KEY (`physmessage_id`,`headername_id`,`headervalue_id`),
> KEY `physmessage_id` (`physmessage_id`),
> KEY `headername_id` (`headername_id`),
> KEY `headervalue_id` (`headervalue_id`),
> KEY `physmessage_id_headername_id` (`physmessage_id`,`headername_id`),
> KEY `physmessage_id_headervalue_id`
> (`physmessage_id`,`headervalue_id`),
> KEY `headername_id_headervalue_id` (`headername_id`,`headervalue_id`),
> CONSTRAINT `dbmail_header_ibfk_1` FOREIGN KEY (`physmessage_id`)
> REFERENCES `dbmail_physmessage` (`id`) ON DELETE
> CASCADE ON UPDATE CASCADE,
> CONSTRAINT `dbmail_header_ibfk_2` FOREIGN KEY (`headername_id`)
> REFERENCES `dbmail_headername` (`id`) ON DELETE
> CASCADE ON UPDATE CASCADE,
> CONSTRAINT `dbmail_header_ibfk_3` FOREIGN KEY (`headervalue_id`)
> REFERENCES `dbmail_headervalue` (`id`) ON DELETE
> CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
>
> CREATE TABLE `dbmail_headername` (
> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
> `headername` varchar(255) NOT NULL DEFAULT '',
> PRIMARY KEY (`id`),
> UNIQUE KEY `headername` (`headername`)
> ) ENGINE=InnoDB AUTO_INCREMENT=1936 DEFAULT CHARSET=utf8
> ROW_FORMAT=COMPRESSED;
>
> CREATE TABLE `dbmail_headervalue` (
> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
> `hash` varchar(255) NOT NULL,
> `headervalue` text NOT NULL,
> `sortfield` varchar(255) DEFAULT NULL,
> `datefield` datetime DEFAULT NULL,
> PRIMARY KEY (`id`),
> KEY `hash` (`hash`),
> KEY `headervalue` (`headervalue`(255)),
> KEY `sortfield` (`sortfield`),
> KEY `datefield` (`datefield`)
> ) ENGINE=InnoDB AUTO_INCREMENT=568824 DEFAULT CHARSET=utf8
> ROW_FORMAT=COMPRESSED;
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


h.reindl at thelounge

Apr 2, 2012, 5:55 AM

Post #13 of 17 (1346 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

cool - this seems to work perfectly
shouldn't it be a part of dbmail-util?

Am 02.04.2012 14:03, schrieb ITronic Harald Leithner:
>
> With this query you will find all unconnected headervalues:
>
> select * from dbmail_headervalue where id not in (select headervalue_id from dbmail_header);
>
> In my case there are 245,257 unused values while purging 4000 messages, so I could do a cronjob with:
>
> delete from dbmail_headervalue where id not in (select headervalue_id from dbmail_header);
>
> daily or once a week, but this should be done by dbmail-util in my opinion, in or after db_icheck_physmessages.
>
>
> Am 02.04.2012, 13:21 Uhr, schrieb Reindl Harald <h.reindl [at] thelounge>:
>
>>
>>
>> Am 02.04.2012 12:45, schrieb Paul J Stevens:
>>> On 04/02/2012 12:28 PM, Reindl Harald wrote:
>>>> this is not really a problem and is triggered
>>>> by the fact the we truncated only the header-tables
>>>> while the references table had already data
>>>>
>>>
>>> Correct.
>>>
>>>> more a problem is that headervalues is growing and
>>>> growing over the time and after few months it
>>>> uses more index-space as the rest of the database
>>>
>>> Prune it!
>>>
>>> http://blog.dbmail.eu/archives/12-Pruning-the-dbmail_headervalue-table.html
>>
>> however - how is this supposed to work?
>> DELETE FROM dbmail_headername WHERE headername='received' or headername='recieved' or headername='user-agent' or
>> headername='Received';
>>
>> this will remove the 'headername'-records
>> but not anything in `dbmail_headervalue` which is the table with the hughe record-count
>> ______________________________
>>
>>
>> CREATE TABLE `dbmail_header` (
>> `physmessage_id` bigint(20) unsigned NOT NULL,
>> `headername_id` bigint(20) unsigned NOT NULL,
>> `headervalue_id` bigint(20) unsigned NOT NULL,
>> PRIMARY KEY (`physmessage_id`,`headername_id`,`headervalue_id`),
>> KEY `physmessage_id` (`physmessage_id`),
>> KEY `headername_id` (`headername_id`),
>> KEY `headervalue_id` (`headervalue_id`),
>> KEY `physmessage_id_headername_id` (`physmessage_id`,`headername_id`),
>> KEY `physmessage_id_headervalue_id` (`physmessage_id`,`headervalue_id`),
>> KEY `headername_id_headervalue_id` (`headername_id`,`headervalue_id`),
>> CONSTRAINT `dbmail_header_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE
>> CASCADE ON UPDATE CASCADE,
>> CONSTRAINT `dbmail_header_ibfk_2` FOREIGN KEY (`headername_id`) REFERENCES `dbmail_headername` (`id`) ON DELETE
>> CASCADE ON UPDATE CASCADE,
>> CONSTRAINT `dbmail_header_ibfk_3` FOREIGN KEY (`headervalue_id`) REFERENCES `dbmail_headervalue` (`id`) ON DELETE
>> CASCADE ON UPDATE CASCADE
>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
>>
>> CREATE TABLE `dbmail_headername` (
>> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
>> `headername` varchar(255) NOT NULL DEFAULT '',
>> PRIMARY KEY (`id`),
>> UNIQUE KEY `headername` (`headername`)
>> ) ENGINE=InnoDB AUTO_INCREMENT=1936 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
>>
>> CREATE TABLE `dbmail_headervalue` (
>> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
>> `hash` varchar(255) NOT NULL,
>> `headervalue` text NOT NULL,
>> `sortfield` varchar(255) DEFAULT NULL,
>> `datefield` datetime DEFAULT NULL,
>> PRIMARY KEY (`id`),
>> KEY `hash` (`hash`),
>> KEY `headervalue` (`headervalue`(255)),
>> KEY `sortfield` (`sortfield`),
>> KEY `datefield` (`datefield`)
>> ) ENGINE=InnoDB AUTO_INCREMENT=568824 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

--

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm
Attachments: signature.asc (0.26 KB)


paul at nfg

Apr 2, 2012, 6:15 AM

Post #14 of 17 (1339 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

On 04/02/2012 02:55 PM, Reindl Harald wrote:
>> delete from dbmail_headervalue where id not in (select headervalue_id from dbmail_header);


correct me if I'm wrong but last I checked neither MySQL nor PostgreSQL
support deleting rows selected in a sub-query.



--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


harald.leithner at itronic

Apr 2, 2012, 6:21 AM

Post #15 of 17 (1340 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

I only know it for MySQL, you can't delete rows from the same table (this
would only work with sub-sub query).

In this Query I take the values from another table.

So this would not work:

delete from dbmail_headervalue b where id in (select id from
dbmail_headervalue a where id=1);

but this would work:

delete from dbmail_headervalue c where id in (select id from (select id
from dbmail_headervalue a where id=1) b);

the last query force the creation of a temporary table.

Am 02.04.2012, 15:15 Uhr, schrieb Paul J Stevens <paul [at] nfg>:

> On 04/02/2012 02:55 PM, Reindl Harald wrote:
>>> delete from dbmail_headervalue where id not in (select headervalue_id
>>> from dbmail_header);
>
>
> correct me if I'm wrong but last I checked neither MySQL nor PostgreSQL
> support deleting rows selected in a sub-query.
>
>
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


paul at nfg

Apr 2, 2012, 7:30 AM

Post #16 of 17 (1342 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

On 04/02/2012 03:21 PM, ITronic Harald Leithner wrote:
> I only know it for MySQL, you can't delete rows from the same table

that's what I meant. Misread the query.
--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


ahodgson at simkin

Apr 2, 2012, 11:17 AM

Post #17 of 17 (1339 views)
Permalink
Re: Imap sort broken? (3.0.2) [In reply to]

On Monday, April 02, 2012 03:15:07 PM Paul J Stevens wrote:
> On 04/02/2012 02:55 PM, Reindl Harald wrote:
> >> delete from dbmail_headervalue where id not in (select
> >> headervalue_id from dbmail_header);
> correct me if I'm wrong but last I checked neither MySQL nor PostgreSQL
> support deleting rows selected in a sub-query.

PostgreSQL does.

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

DBMail users RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.