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: 232
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: 4583
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.

Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.

User avatar
bagu
Normal user
Normal user
Posts: 232
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: 9053
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?
5.7 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: 4583
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.

Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.

Post Reply