HM5032 error on column hm_message_metadata.metadata_subject

Use this forum if you want to discuss a problem or ask a question related to a hMailServer beta release.
Post Reply
mihaibarboi
New user
New user
Posts: 2
Joined: 2018-04-28 09:58

HM5032 error on column hm_message_metadata.metadata_subject

Post by mihaibarboi » 2018-04-28 10:21

Hello !

My server is kind of stuck in a retrial loop for some older messages.

I use: hMailServer 5.6.7-B2425 (db version 5601), MySql Server 5.7, Windows 10 - x64


While the messages are present in table hm_messages (and in .eml data files) but when it tries to insert them in hm_message_metadata it gets error :
(I have got ~200MB of hMailServer logs with this message(s), since 2017-05-30)

Code: Select all

"2018-04-27 23:52:11.669"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x8C\xA1Ce...' 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 (1, 1, 449, '2017-07-21 10:19:48', 'Domino''s Pizza <office@dominos-pizza.ro>', '"ERROR"	4160

"2018-04-27 23:49:04.582"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x93\x88 \xC3...' 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 (1, 1, 501, '2017-08-28 07:16:44', 'Domino''s Pizza <office@dominos-pizza.ro>', '"ERROR"	4160

"2018-04-27 23:42:50.395"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x8E\x89 \xC3...' 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 (1, 1, 540, '2017-10-02 09:08:51', 'Domino''s Pizza <office@dominos-pizza.ro>', '"ERROR"	4160

"2018-04-27 23:14:46.571"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x8E\x81 D...' 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 (1, 1, 515, '2017-09-04 07:58:41', 'Domino''s Pizza <office@dominos-pizza.ro>', '"ERROR"	4160
I've attached a sample (.eml) file with one of the above messages.

Etc,
Thx !

PS: in the end, meaning today (2018-04-28) i have deleted the messages manually from DB/data files
Attachments
sample.zip
(15.78 KiB) Downloaded 375 times

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

Re: HM5032 error on column hm_message_metadata.metadata_subject

Post by mattg » 2018-04-28 23:32

This is due to an emoji in the subject of the message (ie message is probably from facebook or from an Apple iDevice)

You need to set the collation of the metadtat_subject column in the hm_message_metadata to utf8mb4_general_ci

Which database do you use? You need to find some management tool and change the collation of this field.
(I'm guessing that you have upgraded to get to 5.6.7. Newer versions create the tables that way when they are created).
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

mihaibarboi
New user
New user
Posts: 2
Joined: 2018-04-28 09:58

Re: HM5032 error on column hm_message_metadata.metadata_subject

Post by mihaibarboi » 2018-04-29 16:48

Thx for the reply!

The column was using table's default which is simply "UTF8";
I've altered columns' collation to utf8mb4_general_ci as you specified.

I've moved the database between computers(win7 to win8 to win10); i had upgraded a couple of times, it has been a couple of years since I first started to use hMailServer so I don't have the older logs anymore.

Anyway, thank you :)

User avatar
ras07
Normal user
Normal user
Posts: 228
Joined: 2010-03-11 08:51

Re: HM5032 error on column hm_message_metadata.metadata_subject

Post by ras07 » 2020-01-06 02:46

I simultaneously moved from Win7-32 to Win10-64 and from MySQL to MariaDB, and ended up with this problem (although I'm not exactly sure why, because I didn't change hMS versions and the messages were all old - I imagine something didn't go quite right when rebuilding the database). The following fixed the problem:

Code: Select all

USE DATABASE hmailserver;
ALTER TABLE hm_message_metadata MODIFY metadata_subject VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE hm_message_metadata MODIFY metadata_from VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE hm_message_metadata MODIFY metadata_to VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE hm_message_metadata MODIFY metadata_cc VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Post Reply