Mysql error in log

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
bagu
Normal user
Normal user
Posts: 211
Joined: 2005-06-17 03:08
Location: France
Contact:

Mysql error in log

Post by bagu » 2015-08-18 23:58

Hello,
I have the same error that back between one and two times every day, and I do not understand why .

Code: Select all

"ERROR"	4344	"2015-08-18 23:33:23.805"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x8D\x92 w...' 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 (84, 477, 484126, '2015-08-01 17:59:48', 'Populaire au sein de votre réseau <info@twitter.com>', 'MORTEN a tweeté : Unreal walking into @pacha 
Can you help me to solve this ?
I use HmailServer 5.6.4 build 2283 and Mariadb 10.0.21

Thanks
hMailServer 5.6.8 With SpamAssassin 3.4.2

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

Re: Mysql error in log

Post by SorenR » 2015-08-19 01:40

bagu wrote:Hello,
I have the same error that back between one and two times every day, and I do not understand why .

Code: Select all

"ERROR"	4344	"2015-08-18 23:33:23.805"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x8D\x92 w...' 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 (84, 477, 484126, '2015-08-01 17:59:48', 'Populaire au sein de votre réseau <info@twitter.com>', 'MORTEN a tweeté : Unreal walking into @pacha 
Can you help me to solve this ?
I use HmailServer 5.6.4 build 2283 and Mariadb 10.0.21

Thanks
EMOJI's... It's a utf8mb4 issue with "metadata_subject", "metadata_from", "metadata_to", "metadata_cc" in table "hm_message_metadata". Change the 4 columns from utf8_general_ci to utf8mb4_general_ci and varchar(255) to varchar(191). Disable message indexing, wait 5 minutes and enable message indexing again.
SørenR.

“With age comes wisdom, but sometimes age comes alone.”
- Oscar Wilde

User avatar
bagu
Normal user
Normal user
Posts: 211
Joined: 2005-06-17 03:08
Location: France
Contact:

Re: Mysql error in log

Post by bagu » 2015-08-19 11:52

Thanks a lot for your answer.
I have made changes and waiting to see if it happen again.
hMailServer 5.6.8 With SpamAssassin 3.4.2

User avatar
jimimaseye
Moderator
Moderator
Posts: 8175
Joined: 2011-09-08 17:48

Re: Mysql error in log

Post by jimimaseye » 2017-01-04 15:46

Presumably the MySQL commands will be:

Code: Select all

ALTER TABLE hm_message_metadata CONVERT TO CHARACTER SET utf8 COLLATE utf8mb4_general_ci;

ALTER TABLE hm_message_metadata MODIFY metadata_subject VARCHAR(191),
ALTER TABLE hm_message_metadata MODIFY metadata_from VARCHAR(191),
ALTER TABLE hm_message_metadata MODIFY metadata_to VARCHAR(191),
ALTER TABLE hm_message_metadata MODIFY metadata_cc VARCHAR(191);
(Not tried it, though, because I dont have the problem (I don't use indexing) and my MySQL version is too old for utf8mb4_general_ci).

Can anyone confirm?
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

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

Re: Mysql error in log

Post by SorenR » 2017-01-04 17:55

The Internet says...
ALTER TABLE table_name
CHANGE column_name column_name
VARCHAR(191)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
Not tested!

Anyways, UTF8 is contained within UTF8MB4 so no actual conversion is done except adding 1 byte address space per char, thus the reduction from 255 to 191 characters.
SørenR.

“With age comes wisdom, but sometimes age comes alone.”
- Oscar Wilde

Post Reply