hMial server side imap sorting

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
nvvnravi
Normal user
Normal user
Posts: 90
Joined: 2013-06-04 16:53

hMial server side imap sorting

Post by nvvnravi » 2014-06-11 14:12

we are using server side sorting in our implementation.
does anybody know how it works on the server.currently we are seeing that sorting is not done properly on date.please see the attached pictures.
FYI, could this be due to data inconsistency in hm_message_metadata and hm_message tables?
initially we have multiple instances of hmail server pointing to the same DB. now it is removed but my question could this data inconsistency be causing the issue?
Attachments
1.png
1.png (6.29 KiB) Viewed 5349 times
2.png
2.png (5.13 KiB) Viewed 5349 times

User avatar
mattg
Moderator
Moderator
Posts: 20103
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: hMial server side imap sorting

Post by mattg » 2014-06-11 15:04

What mail client?


There is a few dates with emails...
There is the 'written date', the 'received date' and 'the date that is set as it passes through each mail server' (this is a header).
Some mail clients show one, not the other.

I think that the sort is on the 'header' date
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: hMial server side imap sorting

Post by percepts » 2014-06-11 15:56

It looks very much to me that in hm_messages the column messagecreatetime is the Date: header of the email which is created by the email client I think. It is the date with the timezone applied so is the date time of the client.

The hm_message_metatdata column metadata_dateutc is the date from the recieved header and being utc it does not have the time zone adjustment applied. So for example, in the UK where I am, we are on BST which is one hour ahead of GMT. The date in metadata_dateutc is the GMT datetime that hmail receives the message. In your country it will be your countries utc datetime.

Note date is not used for indexing by either hm_messages or hm_message_metadata. Messages are added chronologically into hm_message_metatadata as they are received and not by utc date so by default hm_message_metadata can appear out of date sequence if compared to hm_message date. They are different dates.

Hope that clarifies it a little.

nvvnravi
Normal user
Normal user
Posts: 90
Joined: 2013-06-04 16:53

Re: hMial server side imap sorting

Post by nvvnravi » 2014-06-12 01:27

Matt : I am using claros webmail which is java based.

Thank you for the info on database.

I still need to look at the database in more detail as to whether/how it is sending incorrect information. also need to see if webmail modifying it after it gets it from hmail.

nvvnravi
Normal user
Normal user
Posts: 90
Joined: 2013-06-04 16:53

Re: hMial server side imap sorting

Post by nvvnravi » 2014-06-13 02:17

we were able to solve it by clearing the message index feature under "performance" and then disabling & again enabling the indexing feature. we also had to restart the server since some of the messages were not able to index.
Hope this helps to somebody who has similar issues.

Bill48105
Developer
Developer
Posts: 6192
Joined: 2010-04-24 23:16
Location: Michigan, USA

Re: hMial server side imap sorting

Post by Bill48105 » 2014-06-13 17:02

nvvnravi wrote:we were able to solve it by clearing the message index feature under "performance" and then disabling & again enabling the indexing feature. we also had to restart the server since some of the messages were not able to index.
Hope this helps to somebody who has similar issues.
Sounds like the index was corrupted or out of sync. I guess you'll know if it happens again.
Bill
hMailServer build LIVE on my servers: 5.4-B2014050402
#hmailserver on FreeNode IRC https://webchat.freenode.net/?channels=#hmailserver
*** ABSENT FROM hMail! Those in IRC know how to find me if urgent. ***

nvvnravi
Normal user
Normal user
Posts: 90
Joined: 2013-06-04 16:53

Re: hMial server side imap sorting

Post by nvvnravi » 2014-06-19 19:50

Again i see similar issues in the database.

Is there any permanent fix for this issue. i am still seeing null values in many of the hm_metadata columns. looks liek index is again out of sync or corrupted.


Anybody seen/getting similar issues??

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: hMial server side imap sorting

Post by percepts » 2014-06-19 23:04

I suggest you take a look at your webmail program because no one else is reporting this problem as far as I know.
Does your webmail program conform to IMAP4 protocol ? Does it mess with dates? does it apply timezones correctly. There are many things it could be doing wrong so why are you so covinced that it's hmail which is at fault?

Suggest you trial another webmail program such as roundcube or squirrelmail on a specific user account. If that doesn't cause a problem then you can be pretty sure its your claros webmail software that is at fault.

nvvnravi
Normal user
Normal user
Posts: 90
Joined: 2013-06-04 16:53

Re: hMial server side imap sorting

Post by nvvnravi » 2014-06-23 18:42

I am sure it is hMail because after looking the hm_messages_metadata and hm_messages tables i see that the message id's in both the tables are inconsistent . there are metadata_message in the metadata that are not in messages table and vice versa.

It went away last time by doing the clearing and reindexing. but it came again and i want know what could be the cause and possible solutions.

User avatar
mattg
Moderator
Moderator
Posts: 20103
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: hMial server side imap sorting

Post by mattg » 2014-06-23 22:24

nvvnravi wrote:... i see that the message id's in both the tables are inconsistent .
I think you may have some database corruption going on.

What database are you using?
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

nvvnravi
Normal user
Normal user
Posts: 90
Joined: 2013-06-04 16:53

Re: hMial server side imap sorting

Post by nvvnravi » 2014-06-23 23:45

ms sql server 2008.

do you know how we can reindex the tabels. atleast between message & message_metadata.

Also i am not seeing any relationship between tabels in the database?

User avatar
mattg
Moderator
Moderator
Posts: 20103
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: hMial server side imap sorting

Post by mattg » 2014-06-24 02:08

SQL management studio comes with tools that can re-index
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

nvvnravi
Normal user
Normal user
Posts: 90
Joined: 2013-06-04 16:53

Re: hMial server side imap sorting

Post by nvvnravi » 2014-06-24 16:57

before i proceed with re indexing.
i want to confirm whether the relationship are being maintained in hmail server or not. since i am not seeing any relationships being maintained at database level. i see that all the tables are standalone.

is this true?

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: hMial server side imap sorting

Post by percepts » 2014-06-24 16:59

yes there is no ddl relationships defined but they do exist logically in the data.

Before you do anything perhaps you should tell us where and what those logical relationships are so that we know you are not making wrong assumptions.

nvvnravi
Normal user
Normal user
Posts: 90
Joined: 2013-06-04 16:53

Re: hMial server side imap sorting

Post by nvvnravi » 2014-06-24 17:29

Thank you percepts for the quick response.

Based on what i saw in the DB hm_messages table has a dependency with hm_accounts(accountid) & hm_imapfolders(folderid).
similarly hm_messagaes_metadata table has a dependency with hm_accounts(accountid) , hm_imapfolders(folderid) and hm_messages (messageid).
This is just my observation. let me know if i have missed anything here.

I am planning to do the following steps. please let me know if this look good or you guys have any recommendation.
1) backup hm_messages & hm_message_metadata table and "data" folder
2) remove indexing from hmail admin (under performace)
3) delete all messages from all accounts using hmail admin.
4) confirm that there are no records in both the tables.
5) run reindex on the both the tabels and make sure it is initialized
6) copy back the data folder
7) run the datadirectory synchronizer
and enable the indexing form hmail admin under Performance.


I have a question though
what is the use of messageuid column in hm_messages table

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: hMial server side imap sorting

Post by percepts » 2014-06-24 17:58

I would go about it differently.

I would clear the indexing and then switch it off.

I would then run DSS

I would then do a complete SQL Dump which should include drop tables and create tables.

Then a full sql restore which will also re-index everything.

then switch on indexing.

Note that becasue hmail uses auto-increment values for uniqueness, those key values will be retained and infact MUST be retained because they are used for referential integrity across tables.

you have

hm_messages.messageid
hm_messages.messageaccountid
hm_messages.messagefolderid

hm_message_metadata.metadata_id
hm_message_metadata.metadata_accountid
hm_message_metadata.metadata_folderid
hm_message_metadata.metadata_messageid

the relationships as I see them are

hm_messages.messageid = hm_message_metadata.metadata_messageid
hm_messages.messageaccountid = hm_message_metadata.metadata_accountid
hm_messages.messagefolderid = hm_message_metadata.metadata_folderid

AND

I refer you back to what I posted about dates. They will be different.

AND

what is the use of messageuid column in hm_messages table.

answer I don't know without looking through DB and working it. That's upto you.

nvvnravi
Normal user
Normal user
Posts: 90
Joined: 2013-06-04 16:53

Re: hMial server side imap sorting

Post by nvvnravi » 2014-06-24 18:32

Thank you percepts for the steps you provided.
i have few clarification which i need to ask.

should i keep the messages and the "data" folder as it is or should i clear those also?

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: hMial server side imap sorting

Post by percepts » 2014-06-24 18:42

you should leave data folder as it is. DDS will do some cleanup if required. You should never manually delete anything from Data folder, there are references to its content in the db !!!

AND

do a complete backup of everything before you start.

Post Reply