Error HM5158 While Indexing

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
User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Error HM5158 While Indexing

Post by dieseldon » 2014-08-08 17:14

Hello all,

A several months ago, I noticed in the performance tab not all my messages were indexed. Not thinking much of it, I disregarded this information. Recently, I've gotten several complaints of sluggish performance from the email server, and I watched system loads, etc and didn't find anything significant... but in digging through things I found less of my messages are indexed. (31129 / 51753)

So I enabled debug logging and waited. Yesterday HMS fired off an indexing call, and aborted the indexing.
I received an entry in the ERROR_LOG file, as well as in the hmailserver log file.

Code: Select all

"DEBUG"	3656	"2014-08-07 09:59:07.448"	"Starting message indexing thread..."

"DEBUG"	3728	"2014-08-07 09:59:07.458"	"Indexing messages..."

"ERROR"	3728	"2014-08-07 09:59:35.238"	"Severity: 3 (Medium), Code: HM5158, Source: MessageIndexer::DoWork, Description: An error occured while indexing messages. The indexing was aborted."
I checked Google and found a single thread with Bill back in 2010 stating the HM5158 is a generic error. Well that's not helpful. In that thread I did learn that HMS (at the time) had (has?) a hard coded 60 second limit on indexing. I may overlook the obvious at times, but it looks to me that the indexing only ran for 28 seconds.
I checked the DB tables for errors and ran an OPTIMIZE on all of them; but it doesn't look as though there was anything wrong.
I did some additional digging and it doesn't look as though it regularly tries to do a large indexing either?

Running HMS 5.4.1-B1951 with MySQL 5.5.27.

Ideas? Need more info?
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

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

Re: Error HM5158 While Indexing

Post by mattg » 2014-08-09 01:44

Have you tried turning indexing off and then on again, or even just 'refresh'??
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Re: Error HM5158 While Indexing

Post by dieseldon » 2014-08-09 04:58

A quick reply (as i am not at the server right now) and i only have web admin access... I tired a simple disable indexing and reenabling of it and watched the hmailserver log file and this popped up

Code: Select all

 "ERROR" 1188 "2014-08-08 22:53:02.110" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC S...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 388, 188100, '2014-08-02 15:46:46', '"Salary.com" ', ' "DEBUG" 1188 "2014-08-08 22:53:02.120" "Error saving the index."
Should I try nuking the metadata table?
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

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

Re: Error HM5158 While Indexing

Post by mattg » 2014-08-09 11:31

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: Error HM5158 While Indexing

Post by percepts » 2014-08-09 12:56

when you say nuking I would warn you that hMail does actually look for that table even if you aren't using it and will throw errors when you start hmail if it doesn't exist. So don't just drop it unless you have the sql to create it and AND its indexes.

hMAil won't automatically create it when you switch on indexing. It expects it to be there already.

PHPMyAdmin (or other sql tool) can drop its contents / repair / optimise it so you shouldn't need to drop the actual table.

using clear in hmail admin options should delete all from it.

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2014-08-09 13:54

percepts wrote:when you say nuking I would warn you that hMail does actually look for that table even if you aren't using it and will throw errors when you start hmail if it doesn't exist. So don't just drop it unless you have the sql to create it and AND its indexes.

hMAil won't automatically create it when you switch on indexing. It expects it to be there already.

PHPMyAdmin (or other sql tool) can drop its contents / repair / optimise it so you shouldn't need to drop the actual table.

using clear in hmail admin options should delete all from it.
When you clear the index (hMailAdmin -> Settings -> Advanced -> Performance -> Message Indexing -> [Clear]) the table hm_message_metadata is "nuked" ... as in cleared.
Disable indexing -> save -> enable indexing -> save - to rebuild the message index (and the hm_message_metadata table).
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Re: Error HM5158 While Indexing

Post by dieseldon » 2014-08-10 17:30

I see I got everyone worried about a generalized statement of 'nuking' ... what I should have stated was 'clearing with hmail admin' or using the TRUNCATE mysql statement if it came to that, however I was trying to be subtle of my work when I was out to dinner with my other boss :)

I have more info, and a possible exact scenario of whats going on here...

After clearing the metadata table (through hMail) I did exactly as SorenR said, disable, clear, enable...
then I sat watching the hmailserver log in real time, and waited.
I refreshed the message indexing page every so often and was watching the indexed messages count steadily rising..
Suddenly, out came some server phlegm as it spit out another error. Similar to the last one I posted. "WHY?!" I said.

I opened the phpMyAdmin and started tracking down as much information about this error prone email. I dug up the account, and found the message ID and located the actual file .. and opened it up in notepad.

The subject was encoded in UTF-8 with a long string of encoded text. Ah ha... I opened the message in outlook express to obtain the plaintext subject and edited the message file with the plaintext subject, and restarted the indexing again.

12 times I had to do this. The senders were primarily from three sources, facebook, Salary.com, and iOS mail client. Once I got everything corrected, every message was showing to be indexed. YAY!

Now the question is: why did hMail choke on these UTF-8 encoded subject lines? What can be done to mitigate these from causing issues again?
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

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

Re: Error HM5158 While Indexing

Post by percepts » 2014-08-10 17:48

well done for tracking down the cause.

I would post it as an issue at following link and provide a link back to this topic.

https://github.com/hmailserver/hmailser ... =is%3Aopen

User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Re: Error HM5158 While Indexing

Post by dieseldon » 2014-08-10 20:15

percepts wrote:well done for tracking down the cause.

I would post it as an issue at following link and provide a link back to this topic.

https://github.com/hmailserver/hmailser ... =is%3Aopen
..and done. :)

Also noted on the bug report page; it seems the unknown UTF-8 characters are being generated on/from apple OS's ..hrm...
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

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

Re: Error HM5158 While Indexing

Post by percepts » 2014-08-10 20:50

Thanks, it's down to Martin to have a look at it when he gets time. Busy with STARTTLS right now I think.

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2014-08-28 02:17

dieseldon wrote:
percepts wrote:well done for tracking down the cause.

I would post it as an issue at following link and provide a link back to this topic.

https://github.com/hmailserver/hmailser ... =is%3Aopen
..and done. :)

Also noted on the bug report page; it seems the unknown UTF-8 characters are being generated on/from apple OS's ..hrm...
Not unknown... They are 4 byte Unicode... aka EMOJI's... Used in iOS devices and Twitter emails :evil:

I suspect changing hm_message_metadata.metadata_subject from "utf8_general_ci" to "utf8mb4_general_ci" could do the trick. UTF8 is up to 3 byte per character and UTF8MB4 is up to 4 bytes per character.

My MySQL server is pre 5.5.3, so I can't test it.. You may want to wait for Martin to come back on this. I have added a note on GitHub.

http://dev.mysql.com/doc/refman/5.5/en/ ... -sets.html
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2014-08-28 02:50

Notes on how to change database to support 4 byte Unicode on MySQL 5.5.3 and onwards.

https://mathiasbynens.be/notes/mysql-utf8mb4
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Re: Error HM5158 While Indexing

Post by dieseldon » 2014-08-28 16:56

When checking my email I saw the github notification first.. and read your comments there, but posting here for continuity and archival purposes.

I am indeed running MySQL 5.5.27; and that you were correct the table collation was utf8_general_ci ... I changed the table collation to utf8mb4_general_ci on both the "hm_messages" and "hm_message_metadata" tables and will report back with findings.
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Re: Error HM5158 While Indexing

Post by dieseldon » 2014-08-29 01:47

Well reporting in, I adjusted MySQL my.ini file to adjust the collation to utf8mb4_general_ci to make it the default character set, changed the hmailserver database default collation to utf8mb4_general_ci, changed the hm_message_metadada table and the metadata_subject to utf8mb4_general_ci.
Restarted MySQL, and hmailserver.
Disabled message indexing, and cleared, saved, and enabled and saved. 53,000 messages in... and I'm hit with "HM5032" ... Well now I'm getting a different error.

What's more interesting, is even hmailserver, upon hitting these errors, cannot even log correctly, and the log entry line is truncated and doesn't contain a <CR> or <LF> at the end.. and the next error just gets added... I ended up with this on one line

Code: Select all

"ERROR" 3156    "2014-08-28 19:08:50.546"       "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC G...                       ' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_fro                       m, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191497, '2014-08-22 15:20:26', '"Salary.com" <no-reply@jobs.salary.com>', '"ERROR"     4056    "2014-08-28 19:13:32.792"       "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 192381, '2014-08-25 13:26:45', '"Salary.com" <no-reply@jobs.salary.com>', ' "ERROR"      536     "2014-08-28 19:18:12.534"       "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 192381, '2014-08-25 13:26:45', '"Salary.com" <no-reply@jobs.salary.com>', ' "ERROR"  2956    "2014-08-28 19:22:38.647"       "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC S...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191943, '2014-08-23 14:58:39', '"Salary.com" <no-reply@jobs.salary.com>', '"ERROR"       2692    "2014-08-28 19:23:38.403"       "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191226, '2014-08-21 14:31:57', '"Salary.com" <no-reply@jobs.salary.com>', ' "ERROR"  3280    "2014-08-28 19:24:00.214"       "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191226, '2014-08-21 14:31:57', '"Salary.com" <no-reply@jobs.salary.com>', ' "ERROR"      3540    "2014-08-28 19:24:11.200"       "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC S...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191943, '2014-08-23 14:58:39', '"Salary.com" <no-reply@jobs.salary.com>', '"ERROR"   3648    "2014-08-28 19:24:21.275"       "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC S...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191943, '2014-08-23 14:58:39', '"Salary.com" <no-reply@jobs.salary.com>', '"ERROR"       3400    "2014-08-28 19:24:31.449"       "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC S...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191943, '2014-08-23 14:58:39', '"Salary.com" <no-reply@jobs.salary.com>', '"ERROR"   2996    "2014-08-28 19:24:47.352"       "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191226, '2014-08-21 14:31:57', '"Salary.com" <no-reply@jobs.salary.com>', ' "ERROR"      2676    "2014-08-28 19:25:13.700"       "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191226, '2014-08-21 14:31:57', '"Salary.com" <no-reply@jobs.salary.com>', ' 
Beautiful isn't it?
Fixed:

Code: Select all

"ERROR" 3156 "2014-08-28 19:08:50.546" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC G...                       ' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_fro                       m, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191497, '2014-08-22 15:20:26', '"Salary.com" <no-reply@jobs.salary.com>', '

"ERROR" 4056 "2014-08-28 19:13:32.792" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 192381, '2014-08-25 13:26:45', '"Salary.com" <no-reply@jobs.salary.com>', '

"ERROR"  536 "2014-08-28 19:18:12.534" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 192381, '2014-08-25 13:26:45', '"Salary.com" <no-reply@jobs.salary.com>', '

"ERROR" 2956 "2014-08-28 19:22:38.647" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC S...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191943, '2014-08-23 14:58:39', '"Salary.com" <no-reply@jobs.salary.com>', '

"ERROR" 2692 "2014-08-28 19:23:38.403" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191226, '2014-08-21 14:31:57', '"Salary.com" <no-reply@jobs.salary.com>', '

"ERROR" 3280 "2014-08-28 19:24:00.214" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191226, '2014-08-21 14:31:57', '"Salary.com" <no-reply@jobs.salary.com>', '

"ERROR" 3540 "2014-08-28 19:24:11.200" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC S...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191943, '2014-08-23 14:58:39', '"Salary.com" <no-reply@jobs.salary.com>', '

"ERROR" 3648 "2014-08-28 19:24:21.275" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC S...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191943, '2014-08-23 14:58:39', '"Salary.com" <no-reply@jobs.salary.com>', '

"ERROR" 3400 "2014-08-28 19:24:31.449" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC S...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191943, '2014-08-23 14:58:39', '"Salary.com" <no-reply@jobs.salary.com>', '

"ERROR" 2996 "2014-08-28 19:24:47.352" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191226, '2014-08-21 14:31:57', '"Salary.com" <no-reply@jobs.salary.com>', '

"ERROR" 2676 "2014-08-28 19:25:13.700" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x92\xBC M...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (215, 332, 191226, '2014-08-21 14:31:57', '"Salary.com" <no-reply@jobs.salary.com>', '
I realize salary.com look fairly suspicious, however at this point in time, it's the only active offender, but there are other's out there causing these errors.

Thanks Soren for your help, and this was most certainly worth a shot ... and I was hopeful, but alas the solution eludes me still.

If there's something else I can do to aid in getting to the bottom of this, lemme know!

-DieselDon
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2014-08-29 13:10

You are not alone...
http://stackoverflow.com/questions/2367 ... 92-xbc-for

Anyways... Further digging shows this:
http://dba.stackexchange.com/questions/ ... to-utf8mb4

and going through 5.4.2-B1964 source code I found
MySQLInterface::Instance()->p_mysql_query(m_pDBConn, "SET NAMES utf8");
I have no idea if this is related or not, but it may explain why you still get the error (different code) with the modified collate.

HM5032:

Code: Select all

      // We've reached the maximum number of 
      // reties without success. Time to give up.
      ErrorManager::Instance()->ReportError(ErrorManager::High, 5032, "DALConnection::Execute", sErrorMessage);
It could be interesting to see your MySQL error log...
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2014-08-29 13:40

Eh...

I wonder if this could be a "quick and dirty" solution while waiting for Martin to look into it. I believe indexing is not done until message is delivered to the intended user, so until then the message can be altered...

I use the same function in my "DeliveryLog" script to remove 4 byte Unicode Emojis from Twitter emails. The preg_replace() originally comes from PHP and someone was kind enough to port it to VBScript. The RegEx'es comes from a link in a post in a Drupal forum that I unfortunately lost the link to... :roll:

Code: Select all

'  *******************************
'  ********** Functions **********
'  *******************************

   Private Function preg_init(find_re)
      Set preg_init = New RegExp
      With preg_init
         .Global = True
         If Left(find_re, 1) = "/" Then
            Dim pos: pos = InStrRev(find_re, "/")
            .Pattern = Mid(find_re, 2, pos - 2)
            .IgnoreCase = (InStr(pos, find_re, "i") > 0)
            .Multiline = (InStr(pos, find_re, "m") > 0)
         Else
            .Pattern = find_re
         End If
      End With
   End Function

   Function preg_replace(find_re, replace_arg, text)
      preg_replace = preg_init(find_re).Replace(text, replace_arg)
   End Function

'  *********************************
'  ********** Subroutines **********
'  *********************************

   Sub OnAcceptMessage(oClient, oMessage)

'
'     Place logic here to select specific sender !!
'

      Dim sSubject
      sSubject = Mid(oMessage.Subject, 1, 255)

      ' reject overly long 2 byte sequences, as well as characters above U+10000 and replace with ?
      sSubject = preg_replace("/[\x00-\x08\x10\x0B\x0C\x0E-\x19\x7F]|[\x00-\x7F][\x80-\xBF]+" &_
                 "|([\xC0\xC1]|[\xF0-\xFF])[\x80-\xBF]*|[\xC2-\xDF]((?![\x80-\xBF])|[\x80-\xBF]{2,})" &_
       "|[\xE0-\xEF](([\x80-\xBF](?![\x80-\xBF]))|(?![\x80-\xBF]{2})|[\x80-\xBF]{3,})/S", "?", sSubject)
 
      ' reject overly long 3 byte sequences and UTF-16 surrogates and replace with ?
      sSubject = preg_replace("/\xE0[\x80-\x9F][\x80-\xBF]|\xED[\xA0-\xBF][\x80-\xBF]/S", "?", sSubject)

      oMessage.Save

   End Sub
All existing emails from the "offending" sender will need to be altered - or deleted - or they will break indexing again.
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Re: Error HM5158 While Indexing

Post by dieseldon » 2014-08-29 15:15

SorenR wrote:
It could be interesting to see your MySQL error log...
That can be arranged :-p ... however, since I'm not in the office all day today, I'll get that setup and done and posted later today. (I don't have mySQL error logging enabled by default)
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2014-08-30 17:43

Was looking further into this...

Someone with phpBB were having problems with 4 byte emojis, he upgraded MySQL to 5.5.3+ and changed collate to utf8mb4... Still problems... He was then told to change "utf8" in /includes/db/mysqli.php line ~74 to "utf8mb4" ...

Now, I don't have phpBB but found a version of it on the 'net... Line 73 said:

@mysqli_query($this->db_connect_id, "SET NAMES 'utf8'");

Looks familiar :mrgreen:

From hMailServer source code... MySQLConnection.cpp line 92...

MySQLInterface::Instance()->p_mysql_query(m_pDBConn, "SET NAMES utf8");
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Re: Error HM5158 While Indexing

Post by dieseldon » 2014-08-30 18:33

I wonder if it really would be that easy of a fix... for this case scenario. I'm almost temped to fire up V Studio and try to compile the mysqlconnection and test.

Now, I'm not arguing your previous statements by any means, but either my understanding is wrong, or well, something...

But after looking through your post to https://mathiasbynens.be/notes/mysql-utf8mb4 ... there is a directive for character-set-client-handshake = FALSE; which by setting makes MySQL behave more like version 4.0 in the regard that it forces the client to use the server default, which in this case, is utf8mb4.
I see what you were getting at, with the similar lines from phpBB and hmail; but I am still curious as to why if hmail is being forced by the sql server to use utf8mb4 it's still getting these errors.

Maybe I'm a 90W light bulb in a 100W box, but I feel I'm missing something.

I didn't actually end up home until around 1am after work; so I wasn't able to enable logging, but after posting this reply I am going to do so; and when I get a couple I'll get them tossed up for further analysis.

*disclaimer - I just woke up, and have not had any coffee, so if I'm not making sense it's because I'm incoherent until my brain get's a caffeine jump start. If that previous statement is true, I will most likely address any nonsense in my next reply with the error logs.
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2014-08-30 19:16

dieseldon wrote:I wonder if it really would be that easy of a fix... for this case scenario. I'm almost temped to fire up V Studio and try to compile the mysqlconnection and test.

Now, I'm not arguing your previous statements by any means, but either my understanding is wrong, or well, something...

But after looking through your post to https://mathiasbynens.be/notes/mysql-utf8mb4 ... there is a directive for character-set-client-handshake = FALSE; which by setting makes MySQL behave more like version 4.0 in the regard that it forces the client to use the server default, which in this case, is utf8mb4.
I see what you were getting at, with the similar lines from phpBB and hmail; but I am still curious as to why if hmail is being forced by the sql server to use utf8mb4 it's still getting these errors.

Maybe I'm a 90W light bulb in a 100W box, but I feel I'm missing something.

I didn't actually end up home until around 1am after work; so I wasn't able to enable logging, but after posting this reply I am going to do so; and when I get a couple I'll get them tossed up for further analysis.

*disclaimer - I just woke up, and have not had any coffee, so if I'm not making sense it's because I'm incoherent until my brain get's a caffeine jump start. If that previous statement is true, I will most likely address any nonsense in my next reply with the error logs.
No worries... I fought my MySQL installation last night upgrading it from 5.0.45 to 5.1.73... Like you are supposed to do, in steps towards 5.5.x... That was the intention anyway... Ended up uninstalling MySQL completely, trying to install 5.1.73 over the remains of 5.0.45 so that I could keep my users/passwords.... I now have all the data but my userlist -> bitbucket!
By then it was around 5 AM (GMT+2), created the user for hMailServer, fired it all up and went to bed :roll:

From lessons learnt I will make a sqldump, uninstall and make a new install of 5.5.39 later to night. Build a version of hMailServer with utf8mb4 and have someone with an iPhone5 send me an emoji - IF I can stay awake ;-)
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Re: Error HM5158 While Indexing

Post by dieseldon » 2014-08-31 05:04

I know your pain with upgrading MySQL. Why I haven't upgraded from 5.5.27 yet lol ... I'm waiting to do the server hardware upgrade to upgrade my software. (Also doesn't help I am switching platforms from Win32 to linux)
But when I moved to 5.5.27, that's what I did, a mysqldump and import. Just saves SOOOOO much headache. Really isn't all that time intensive all things considered.

I had the same idea as you, but evidently I am a bit rusty at V-Studio; and I don't have all the requisites correct. (github client set up correctly and everything else.. kept running into errors with not finding modules)

SOO.... if you are more successful than I at compiling hmailserver with utf8mb4 .. let me know, I'll be more than happy to be guinea pig. You know... for science. :-p But seriously, if I can help I will; but if you do figure it out, and don't mind sharing your binaries, I'd also be grateful.

OH..update, I was looking through the MySQL error log, and it has shown nothing from the year 2014 except a server restart today (I never turned it back off like I thought I did) Upon discovering that, I turned on query logging, so once I get another error from hmailserver I'll track back through the query log, and see what I've gotten from hmail.
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

User avatar
martin
Developer
Developer
Posts: 6846
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Error HM5158 While Indexing

Post by martin » 2014-08-31 12:11

Latest dev build will use utf8mb4 for the connection if MySQL supports it. The columns must still be manually altered though.

User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Re: Error HM5158 While Indexing

Post by dieseldon » 2014-08-31 16:43

I can confirm this works. Having already modified the table collation, I simply compiled hmailserver and replaced the executable and ran an index. Completed with no errors. Replaced the executable with the original, cleared the index cache, and enabled indexing. About 21 minutes later, the server coughed up another error and the indexing thread stopped.

However, I did not use the actual latest build. I had already downloaded the source code for 5.4.1-B1951. So using github, I appended the changes listed under the commits then compiled.
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2014-08-31 17:07

iOS5 [U+1F44D]

Image
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

Re: Error HM5158 While Indexing

Post by mattg » 2014-09-05 04:23

OK

Upgraded to latest hMailserver Beta 5.5-B2074
Using MyPHPWebAdmin I :-
- Changed my hMailserver database to default to utf8mb4_general_ci
- Made the tables hm_Messages and hm_Message_Meteadata to be utf8mb4_general_ci

I'm already on MySQL 5.5.17

Restarted MySQL (and the dependant hMailserver) service

still getting heaps of these

Code: Select all

"ERROR"	14844	"2014-09-05 12:20:58.410"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF3\xBE\x8C\xAC\xF3\xBE...' for column 'metadata_subject' at row 1 (Additional info: INSERT INTO hm_message_metadata (metadata_accountid, metadata_folderid, metadata_messageid, metadata_dateutc, metadata_from, metadata_subject, metadata_to, metadata_cc) VALUES (81, 278, 442845, '2014-09-03 12:02:51', '"Facebook" <notification+kkkkkkkkkj1_-jki@facebookmail.com>', 'XXXXXXXX XXXXXXXX updated her status: "Suit for a tbh and rate
What step did I miss?
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Re: Error HM5158 While Indexing

Post by dieseldon » 2014-09-05 05:09

mattg wrote:OK

Upgraded to latest hMailserver Beta 5.5-B2074
Using MyPHPWebAdmin I :-
- Changed my hMailserver database to default to utf8mb4_general_ci
- Made the tables hm_Messages and hm_Message_Meteadata to be utf8mb4_general_ci

I'm already on MySQL 5.5.17

Restarted MySQL (and the dependant hMailserver) service


What step did I miss?
Did you make sure to change the collation of the field withing the table? (within phpmyadmin click on structure of the hm_message_metadata) click change on the metadata_subject and change that to utf8mb4 and change it to varchar191? (to account to for lost length due to the longer bit length)

Also did you edit my.conf (my.ini)?

Code: Select all

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
;character-set-client-handshake = FALSE (this line is optional; use only if you want MySQL to act like ver 4.0 and earlier - disable encoding handshakes with server and client ... forces server to use defaults always)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Restart MySQL.. then run your mysql client. It should return this and you'll know your SQL server is setup corrctly

Code: Select all

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)
It's not a bad idea to repair/optimize the tables you modified the collation either.
A fast, simple method:

Code: Select all

mysqlcheck -u root -p --auto-repair --optimize --all-databases

This should take care of it.. if not, report back!
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

User avatar
martin
Developer
Developer
Posts: 6846
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Error HM5158 While Indexing

Post by martin » 2014-09-05 09:52

I did not have to edit my my.ini to get it to work. I just had to change the type of the column. I guessing (haven't read the docs) the default-character-set settings only applies to new columns.

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

Re: Error HM5158 While Indexing

Post by percepts » 2014-09-05 14:01

I haven't been following this topic so apologies if this has already been mentioned.

If you change the databse/table/column charset then you MUST convert what is already put in there with the old charset.

for example:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci

so to change your hmailserver database you would need an alter statement for each table or column at a minimum

User avatar
dieseldon
Normal user
Normal user
Posts: 49
Joined: 2012-02-08 02:48
Location: S.W. Michigan
Contact:

Re: Error HM5158 While Indexing

Post by dieseldon » 2014-09-05 14:17

martin wrote:I did not have to edit my my.ini to get it to work. I just had to change the type of the column. I guessing (haven't read the docs) the default-character-set settings only applies to new columns.
I may and revert my settings and see what that does; I'm a defaults kind of guy after all :-p
percepts wrote: If you change the databse/table/column charset then you MUST convert what is already put in there with the old charset.

for example:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci

so to change your hmailserver database you would need an alter statement for each table or column at a minimum
As Mattg was using phpMyAdmin, I was trying to help him using a familiar interface... Using the structure screen, within the table column, clicking change issues an ALTER command to the column.
A screenshot to show function location within phpMyAdmin
Attachments
Clipboard01.jpg
When life hands you lemons, make lemonade; when life hands you melons, you're dyslexic.

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

Re: Error HM5158 While Indexing

Post by percepts » 2014-09-05 14:25

dieseldon wrote:Using the structure screen, within the table column, clicking change issues an ALTER command to the column.
But does it issue the convert ? I don't think so but I may be wrong. Wouldn't be the first time.
I'm just giving you guys a heads up that changing database/table/column charset definition can leave a table in a corrupt state if you haven't converted data already there to that charset. i.e. don't assume the definition change does the convert automatically.

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2014-09-05 14:45

percepts wrote:
dieseldon wrote:Using the structure screen, within the table column, clicking change issues an ALTER command to the column.
But does it issue the convert ? I don't think so but I may be wrong. Wouldn't be the first time.
I'm just giving you guys a heads up that changing database/table/column charset definition can leave a table in a corrupt state if you haven't converted data already there to that charset. i.e. don't assume the definition change does the convert automatically.
http://dev.mysql.com/doc/refman/5.5/en/ ... ading.html
In terms of table content, conversion from utf8 to utf8mb4 presents no problems:

For a BMP character, utf8 and utf8mb4 have identical storage characteristics: same code values, same encoding, same length.

For a supplementary character, utf8 cannot store the character at all, while utf8mb4 requires four bytes to store it. Since utf8 cannot store the character at all, you do not have any supplementary characters in utf8 columns and you need not worry about converting characters or losing data when upgrading utf8 data from older versions of MySQL.

In terms of table structure, the catch when converting from utf8 to utf8mb4 is that the maximum length of a column or index key is unchanged in terms of bytes. Therefore, it is smaller in terms of characters because the maximum length of a character is four bytes instead of three. For the CHAR, VARCHAR, and TEXT data types, watch for these things when converting your MySQL tables:

Check all definitions of utf8 columns and make sure they will not exceed the maximum length for the storage engine.

Check all indexes on utf8 columns and make sure they will not exceed the maximum length for the storage engine. Sometimes the maximum can change due to storage engine enhancements.

If the preceding conditions apply, you must either reduce the defined length of columns or indexes, or continue to use utf8 rather than utf8mb4.
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

Re: Error HM5158 While Indexing

Post by mattg » 2014-09-06 01:51

dieseldon wrote:Did you make sure to change the collation of the field withing the table?
No, that's what I missed.
Changed it to 'utf8mb4_unicode_ci'

Then tried to optimise, but optimise instead automatically 're-created' the table.

I'll keep an eye on it.
Thanks
dieseldon wrote:Also did you edit my.conf (my.ini)?
martin wrote:I guessing (haven't read the docs) the default-character-set settings only applies to new columns.
I agree with Martin.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Cariba
New user
New user
Posts: 19
Joined: 2008-04-14 18:50

Re: Error HM5158 While Indexing

Post by Cariba » 2015-02-09 15:15

Can I set this for all the tables? Or do I need to set this only on specific fields and tables? If so, which tables and fields?

If I try to change it globally I'm stuck on a few tables, e.g.:

Code: Select all

ALTER TABLE `hm_accounts` COLLATE='utf8mb4_unicode_ci', CONVERT TO CHARSET utf8mb4;
/* SQL Error (1071): Specified key was too long; max key length is 767 bytes */


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

Re: Error HM5158 While Indexing

Post by mattg » 2015-02-10 00:36

mattg wrote:- Made the tables hm_Messages and hm_Message_Meteadata to be utf8mb4_general_ci
Don't think it matters if you change all of them, but only these two are needed for 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

User avatar
tbrg78_hm
Normal user
Normal user
Posts: 55
Joined: 2013-12-17 19:58
Location: Berlin/Germany

Re: Error HM5158 While Indexing

Post by tbrg78_hm » 2015-12-11 01:59

Hi guys,

I'm running MySQL 5.6 and upgraded from 5.4. to the latest hMailserver 5.6.4 just an hour ago, mainly because of the security issues, but also because of these error log files created every day caused by this indexing error. After updating I cleared/disabled/saved the indexing settings and then enabled/saved them again, but it seems the error still occurs. Shouldn't this be fixed with the latest hMailserver version? I see you guys are still tinkering with collation settings, so I'm not so sure what to expect or do now.

I also installed the 4ByteUTF8-ScriptFix (viewtopic.php?f=9&t=27884) but this works for new incoming mails only I guess, so any advice is appreciated on how to get around the ever growing error log file collection. o)

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

Re: Error HM5158 While Indexing

Post by mattg » 2015-12-11 05:41

you still need to play with the collation settings for an existing database
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2015-12-11 16:49

tbrg78_hm wrote:Hi guys,

I'm running MySQL 5.6 and upgraded from 5.4. to the latest hMailserver 5.6.4 just an hour ago, mainly because of the security issues, but also because of these error log files created every day caused by this indexing error. After updating I cleared/disabled/saved the indexing settings and then enabled/saved them again, but it seems the error still occurs. Shouldn't this be fixed with the latest hMailserver version? I see you guys are still tinkering with collation settings, so I'm not so sure what to expect or do now.

I also installed the 4ByteUTF8-ScriptFix (viewtopic.php?f=9&t=27884) but this works for new incoming mails only I guess, so any advice is appreciated on how to get around the ever growing error log file collection. o)
The Script was a "quick-fix" for older MySQL databases. If your database support UTF8MB4 it should be the preferred solution as the script is not 100% effective :oops:

Anyways... Existing messages can be fixed by editing the .eml files with a text editor - it will fix the indexing errors until you upgrade & reconfigure the database... However you may break some privacy rules doing that :mrgreen:
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
tbrg78_hm
Normal user
Normal user
Posts: 55
Joined: 2013-12-17 19:58
Location: Berlin/Germany

Re: Error HM5158 While Indexing

Post by tbrg78_hm » 2015-12-12 00:04

Thanks for the clarification. o)
I hoped the database update during the hMailserver update would have taken care of altering the database accordingly, but ok! o)
So I've read on how to convert the table to the desired collation in the meantime, but I must admit, the process is not quite clear to me.

Some questions arised:
1) Do I need to convert the data within the affected tables before or after changing the collation to utf8mb4_general_ci/utf8mb4_unicode_ci?
2) Is utf8mb4_general_ci the desired collation or is it utf8mb4_unicode_ci?
3) What about the length of all the VARCHAR columns, do I need to alter them from 255 to xxx?
4) If column lengths is changed, won't HM choke one day, as the lengths are all different from what it expects?
5) Is that procedure needed anytime I install a fresh hMailserver? I see utf8 ist still the default for all created tables in the MySql scripts (hu?).

Thanks for pointing out, that the installed vb-script is not perfect, one more reason to get it right in the db anytime soon.
I would appreciate if you could give me some more advice, as I'm everything but a db-expert. I can drop tables though. o)

Thanks and have a nice weekend! o)

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

Re: Error HM5158 While Indexing

Post by mattg » 2015-12-12 02:17

tbrg78_hm wrote:I hoped the database update during the hMailserver update would have taken care of altering the database accordingly, but ok! o)
That's tricky.

Not all databases supported by hMailsevrer handle this collation
Not all versions of MySQL handle this collation.
Not all users with MySQL that can handle this collation, want or need this change.
tbrg78_hm wrote:So I've read on how to convert the table to the desired collation in the meantime, but I must admit, the process is not quite clear to me.

Some questions arised:
1) Do I need to convert the data within the affected tables before or after changing the collation to utf8mb4_general_ci/utf8mb4_unicode_ci?
2) Is utf8mb4_general_ci the desired collation or is it utf8mb4_unicode_ci?
3) What about the length of all the VARCHAR columns, do I need to alter them from 255 to xxx?
4) If column lengths is changed, won't HM choke one day, as the lengths are all different from what it expects?
5) Is that procedure needed anytime I install a fresh hMailserver? I see utf8 ist still the default for all created tables in the MySql scripts (hu?).
1. no, just change the collation
2. I have utf8mb4_general_ci on my system
3. Mine are ALL still varchar(255). I'd leave yours at that unless you have a SPECIFIC reason to change
4. Yes, that's why don't change them
5. Likely to be the case. I just build a new production machine and I had to do this again. See my answers about the types of databases above. I'll look at the code and suggest a change if I can see how to do this.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
tbrg78_hm
Normal user
Normal user
Posts: 55
Joined: 2013-12-17 19:58
Location: Berlin/Germany

Re: Error HM5158 While Indexing

Post by tbrg78_hm » 2015-12-12 14:07

Thanks matt! This answered my questions and I guess I was able to successfully change the collations of the tables/columns.
The indexing error does not occur anymore at least and there are no more error log files since then, great! o)

Now I still wonder why the indexing stops at message #542 of about 30k messages in total (it goes there within seconds, then stalls). Before clearing, disabling and re-enabling the indexer, the status bar was all to the right, nearly all messages indexed. Is the stopping some kind of error-indicator or does it just take some time/more operations over time to build the index? I cannot see any errors in the logs, indexer just starts and stops.

I also had the impression that listing folders is slower after changing the table/column collations, but since I did not measure scientifically before/after, my mind probably just plays tricks on me. Not sure how an uncomplete index plays in here though, maybe it is related to that?

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2015-12-12 18:05

The InnoDB storage engine has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8 columns with indexes longer than 191 characters, you will need to index a smaller number of characters when using utf8mb4.
https://mathiasbynens.be/notes/mysql-utf8mb4

or alternative..

http://dev.mysql.com/doc/refman/5.5/en/ ... rge_prefix
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
tbrg78_hm
Normal user
Normal user
Posts: 55
Joined: 2013-12-17 19:58
Location: Berlin/Germany

Re: Error HM5158 While Indexing

Post by tbrg78_hm » 2015-12-12 20:35

Sören, I guess you quoted and posted the links, because there are additional things to do?

I've read and come across the 255 vs. 191 characters notice, that's why I asked wether it's necessary to truncate the columns.
But as a developer myself, I wondered if that's a good idea as long as HM expects columns to be 255 characters long.

The index-status bar of HM admin shows some more messages have been indexed in the meantime, just a few, but that index is, as far as my understanding goes, a total different story to the internal index of MySql on specific tables, isn't it? So it seems I currently screwed both indexing systems!? o) Maybe somebody likes to share some more details on how to get this straight again and wether the stuck status bar of the HM indexer has anything to do with the changed collation.

Thanks in advance.. o)

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2015-12-12 22:47

tbrg78_hm wrote:Sören, I guess you quoted and posted the links, because there are additional things to do?

I've read and come across the 255 vs. 191 characters notice, that's why I asked wether it's necessary to truncate the columns.
But as a developer myself, I wondered if that's a good idea as long as HM expects columns to be 255 characters long.

The index-status bar of HM admin shows some more messages have been indexed in the meantime, just a few, but that index is, as far as my understanding goes, a total different story to the internal index of MySql on specific tables, isn't it? So it seems I currently screwed both indexing systems!? o) Maybe somebody likes to share some more details on how to get this straight again and wether the stuck status bar of the HM indexer has anything to do with the changed collation.

Thanks in advance.. o)
Did you check the source ??

If I'm correct then HMS (I'm using 5.4.2) will only store 100 chars in metadata_subject.
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

Re: Error HM5158 While Indexing

Post by mattg » 2015-12-13 02:20

My metadata_subject is set to 255 chars

Github shows ALL instances of 'metadata_subject' in current build are set to 255 chars >> https://github.com/hmailserver/hmailser ... ta_subject
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Error HM5158 While Indexing

Post by SorenR » 2015-12-13 15:43

mattg wrote:My metadata_subject is set to 255 chars

Github shows ALL instances of 'metadata_subject' in current build are set to 255 chars >> https://github.com/hmailserver/hmailser ... ta_subject
Source (code)... Not SQL statements...

https://github.com/hmailserver/hmailser ... a.cpp#L182
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
tbrg78_hm
Normal user
Normal user
Posts: 55
Joined: 2013-12-17 19:58
Location: Berlin/Germany

Re: Error HM5158 While Indexing

Post by tbrg78_hm » 2015-12-26 12:02

Thanks Sören for digging the sources, interesting! o)

I also had some reading about that sysvar_innodb_large_prefix setting, I think I understand its use, but I actually cannot understand why you suggested to enable it. If I look at the indexes created by the MySQL db-scripts for the tables hm_messages and hm_message_metadata, there are no columns involved that could exceed the new character limit of 191 on utf8mb4 columns. Not a single varchar(255) column is part of the indexes created. So, that sysvar_innodb_large_prefix setting would not have any effect (on these two tables at least, don't know about the others), am I wrong?

But, after having changed the varchar(255) column collations to utf8mb4, creating the index for the data directory synchronizer (as stated in the manual https://www.hmailserver.com/documentati ... rguide_dds) fails with "Error Code: 1071. Specified key was too long; max key length is 767 bytes". Which is expected I guess, because that index *does* sit on a varchar(255) column.

So enabling sysvar_innodb_large_prefix would still be necessary for this to work, but since I also need to play with other database settings like innodb_file_format=barracuda and innodb_file_per_table=true I really leave terrain and expect to mess up the whole system than to help general performance (unless you tell me it's safe to do?! o).

This far, changing the collation removed the indexing error from the logs successfuly.
Now if hms really stores/retrieves only 100 chars from the varchar(255) columns everytime (I checked, subjects are all cut at 100 chars in the db), then one could truncate them to 191 chars without expecting further trouble, which then might get us around enabling sysvar_innodb_large_prefix, right? o)

On the other hand, I don't know if it's sensible to use only 100 chars from the subject.
Is that actually intentional, since you Matt, were not very aware of that?

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

Re: Error HM5158 While Indexing

Post by mattg » 2015-12-27 05:37

OK...

So I ran this SQL statement on my MYSQL database

Code: Select all

SELECT metadata_id, metadata_subject, LENGTH(metadata_subject) AS mlen
FROM    hm_message_metadata
where LENGTH(metadata_subject) >100
ORDER BY
        mlen DESC
And It returned 225 database entries...

Changing to

Code: Select all

SELECT metadata_id, metadata_subject, CHAR_LENGTH(metadata_subject) AS mlen
FROM    hm_message_metadata
where CHAR_LENGTH(metadata_subject) >100
ORDER BY `mlen`  DESC
returns an empty data set

SO the 100 char limit on the Metadata_subject that is stored is different to the 255 limit which is number of BYTES in the subject that is stored in the database (and searched). This is NOT the subject line that is saved in the message file.

When I open the message file for the largest LENGTH(metadata_subject) on my system = 141 the RAW subject is

Code: Select all

Subject: =?UTF-8?Q?skyasi_just_went_live_with_=2210HR_LIVE_STREAM_HYPE?= =?UTF-8?Q?_MadPack_2_=28_=CD=A1~_=CD=9C=CA=96_=CD=A1=C2=B0=29?=
 =?UTF-8?Q?_=E1=B4=B5=E1=B5=97=CB=A2_=E1=B5=83?= =?UTF-8?Q?_=CB=A2=E1=B5=89=E1=B6=9C=CA=B3=E1=B5=89=E1=B5=97?=
 =?UTF-8?Q?_=E1=B5=97=E1=B5=92?= =?UTF-8?Q?_=E1=B5=89=E1=B5=9B=E1=B5=89=CA=B3=CA=B8=E1=B5=87=E1=B5=92=E1=B5=88=CA=B8=22?=
 =?UTF-8?Q?_on_Twitch?=
which reads as this in an email client
'skyasi just went live with "10HR LIVE STREAM HYPE MadPack 2 ( ͡~ ͜ʖ ͡°) ᴵᵗˢ ᵃ ˢᵉᶜʳᵉᵗ ᵗᵒ ᵉᵛᵉʳʸᵇᵒᵈʸ" on Twitch'
tbrg78_hm wrote:Now if hms really stores/retrieves only 100 chars from the varchar(255) columns everytime (I checked, subjects are all cut at 100 chars in the db), then one could truncate them to 191 chars without expecting further trouble, which then might get us around enabling sysvar_innodb_large_prefix, right? o)
I think so, but there is still potential that a subject with 100 chars has more than 191 bytes, but I guess the same potential exists for 255 bytes. A bit tricky as there is no fixed conversion except on a character by character basis. My language is English and I'd expect that someone who uses a non latin character set for their written language will have much larger numbers of bytes used per subject. Perhaps a subject line with 100 chars but more than 255 bytes throws an error. I'm not sure. If so, I'd expect the same to occur (only more frequently) with a 191 BYTE limit.
tbrg78_hm wrote:Is that actually intentional, since you Matt, were not very aware of that?
Probably. Martin doesn't do stuff unintentionally in my experience.

Does it matter?
Not that I can tell.

I have some 4245 messages on my system that have 100 chars saved in the Metadata database. This is ONLY what is indexed, not what is shown when the message is loaded.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

sgorbach
New user
New user
Posts: 8
Joined: 2013-01-11 13:01

Re: Error HM5158 While Indexing

Post by sgorbach » 2016-04-11 01:18

Since editing the collation is still the official fix for this issue, specially for those of us with pre-existing (and probably big) DBs, I'd like to note that just modifying metadata_subject is probably not enough anymore.

I've seen many UTF8-encoded strings in the From and even some in the To/CC fields, and those cause the same errors (HM5032) to be logged. I first started seeing those in the Subject line like most of you did, but now I see them in all the other fields.

Manual editing the .eml is not the proper solution because 1) it's not a fix, many more will come with utf8-weirdness 2) it's time consuming and 3) it shouldn't be done in the first place, specially for emails stored for anyone but ourselves.

Changing the default configuration for MySQL will be problematic for some, I can think of a couple of scenarios: the mysql instance is shared with other software (and while it SHOULD be seamless, going utf8 to utf8mb4 should require proper testing for that software as well), maybe the instance is not under our control and changing the INI or defaults is not an option (unlikely but posible), and so on.

So for those of you going to just edit the collation for the columns I recomend doing it for metadata_subject, metadata_from, metadata_to and metadata_cc, not just for subject. Of course this requires further testing, but I've seen no issues on my side so far with the 4 columns updated. As said, before the change I saw an increasing number of UTF8 From-fields and some To-fields that raised MH5032 logs.

As a side note, I host emails for mostly spanish-speaking users but the language seems to be completely unrelated to the issue as most if not all the lines that raise problems are emojis or weird characters used to stand out in the inbox.

Any feedback on this recomendation is of course welcome!

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

Re: Error HM5158 While Indexing

Post by mattg » 2016-04-11 02:13

sgorbach wrote:So for those of you going to just edit the collation for the columns I recomend doing it for metadata_subject, metadata_from, metadata_to and metadata_cc, not just for subject.
I did exactly this too, when I changed my collation in Sept 2014.

It's survived a few updates since then, and certainly not been an issue to me either
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

leshan
New user
New user
Posts: 19
Joined: 2015-04-13 16:39

Re: Error HM5158 While Indexing

Post by leshan » 2016-11-29 04:42

mattg wrote:
sgorbach wrote:So for those of you going to just edit the collation for the columns I recomend doing it for metadata_subject, metadata_from, metadata_to and metadata_cc, not just for subject.
I did exactly this too, when I changed my collation in Sept 2014.

It's survived a few updates since then, and certainly not been an issue to me either
Are these columns should be changed from utf8_general_ci to utf8mb4_general_ci?

hm_message_metadata(metadata_subject, metadata_from, metadata_to and metadata_cc)
hm_messages(messagefilename,messagefrom)

Is it safe to make the changes?

Thanks

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

Re: Error HM5158 While Indexing

Post by mattg » 2016-11-29 05:14

Yes - those columns
and yes this is safe (but do a backup first just in case)
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Post Reply