Emoji problem

[OBG-IT]

Hello,
I recently migrated our otrs system to otobo and now we noticed when a new ticket arrives that contains a smiley emoji the ticket is not created and there is an error.

Has anyone here had the same problem and also got it solved?

The error shown is:

DBD::mysql::db do failed: Incorrect string value: '\xF0\x9F\x98\x8A\x0A\x0A...' for column `otobo`.`article_data_mime`.`a_body` at row 1 at /data/otobo/Kernel/System/DB.pm line 556.
ERROR: OTOBO-otobo.Console.pl-Maint::PostMaster::Read-10 Perl: 5.34.0 OS: linux Time: Fri Aug 19 13:20:46 2022

 Message: Incorrect string value: '\xF0\x9F\x98\x8A\x0A\x0A...' for column `otobo`.`article_data_mime`.`a_body` at row 1, SQL: '
            INSERT INTO article_data_mime (
                article_id, a_from, a_reply_to, a_to, a_cc, a_bcc, a_subject, a_message_id,
                a_message_id_md5, a_in_reply_to, a_references, a_content_type, a_body,
                incoming_time, content_path, create_time, create_by, change_time, change_by)
            VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '2022-08-19 13:20:46', ?, '2022-08-19 13:20:46', ?
            )
        '

Kind regards Alex

[bschmalhofer]

Hello Alex,

first, these kind of support questions should initially be posted on the OTOBO Forum at https://otobo.de/en/forums/otobo/otobo-forum/. The reason is that we want to keep the GitHub issues dedicated to bugs and new features. On the forum there already was a similar question, albeit in German: https://otobo.de/en/forums/topic/problem-mails/#post-10072

Your incoming data looks sensible:
$ uni -x F09F988A
😊 - U+1F60A - SMILING FACE WITH SMILING EYES

This indicates that there is a charset issue with your database. Based on the error message I gather that the database is either MySQL or MariaDB. Could you check the default charset of the database and the charset of the table? For the database you can execute: show variables like "character_set_database" . For the table you can use use otobo; SHOW CREATE TABLE article_data_mime. More options are at https://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-mysql-database-table-column-is .

Both charsets should be utf8mb4.

Best regards,
Bernhard

[OBG-IT]

Hello Bernhard,
okay sorry I did not know.

yes the question in the forum I have already read, but had not helped me with my problem.

However, your answer has helped me. :)
Through your command SHOW CREATE TABLE article_data_mime I found out that character was still on utf8.

mysql> SHOW CREATE TABLE article_data_mime \G *************************** 1. row *************************** Table: article_data_mime Create Table: CREATE TABLE article_data_mime(idbigint(20) NOT NULL AUTO_INCREMENT,a_frommediumtext CHARACTER SET utf8,a_reply_tomediumtext CHARACTER SET utf8,a_tomediumtext CHARACTER SET utf8,a_ccmediumtext CHARACTER SET utf8,a_subjecttext CHARACTER SET utf8,a_message_idtext CHARACTER SET utf8,a_message_id_md5varchar(32) CHARACTER SET utf8 DEFAULT NULL,a_in_reply_tomediumtext CHARACTER SET utf8,a_referencesmediumtext CHARACTER SET utf8,a_content_typevarchar(250) CHARACTER SET utf8 DEFAULT NULL,a_bodymediumtext CHARACTER SET utf8,incoming_timeint(11) NOT NULL,content_pathvarchar(250) CHARACTER SET utf8 DEFAULT NULL,create_timedatetime NOT NULL,create_byint(11) NOT NULL,change_timedatetime NOT NULL,change_byint(11) NOT NULL,article_idbigint(20) NOT NULL,a_bcc mediumtext CHARACTER SET utf8, PRIMARY KEY (id), KEY article_data_mime_message_id_md5 (a_message_id_md5), KEY FK_article_data_mime_article_id_id (article_id), KEY FK_article_data_mime_create_by_id (create_by), KEY FK_article_data_mime_change_by_id (change_by), CONSTRAINT FK_article_data_mime_article_id_id FOREIGN KEY (article_id) REF ERENCES article (id), CONSTRAINT FK_article_data_mime_change_by_id FOREIGN KEY (change_by) REFER ENCES users (id), CONSTRAINT FK_article_data_mime_create_by_id FOREIGN KEY (create_by) REFER ENCES users (id) ) ENGINE=InnoDB AUTO_INCREMENT=55358 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)

One last question here.
would this be the right command to change the character set here or how would you go about it?
alter table article_data_mime convert to character set utf8mb4 collate utf8mb4_general_ci;

Best regards,
Alex

[bschmalhofer]

Hi Alex,
ah, having the charset utf8 declared on the individual columns is an interesting case.

First, let's sanity check that charset utf8 vs. utf8mb4 is really the problem. The problematic emoji is indeed encoded in four bytes:

$ uni -8 😊
😊 - U+1F60A - F0 9F 98 8A - SMILING FACE WITH SMILING EYES

So, this theory is confirmed.

I assume that you used the streamlined migration method for migrating the data from OTRS to OTOBO:
https://doc.otobo.org/manual/installation/10.0/en/content/migration-from-otrs-6.html#optional-step-streamlined-migration-of-the-database .
Checking the script scripts/backup.pl, I found that there is an oversight. The charset on the tables is adapted and the COLLATE on columns is removed in the subroutine MySQLBackupForMigrateFromOTRS(), see https://github.com/RotherOSS/otobo/blob/rel-10_1/scripts/backup.pl#L578. But the CHARACTER SET utf8 is not removed. This oversight seems to be the root cause of the problem.

Now for the fix. The statement alter table article_data_mime convert to character set utf8mb4 collate utf8mb4_general_ci; is likely not enough, as the charset set of the columns have higher priority than the default charset of the table. So the relevant statement is more like:

For each column:

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
See https://mathiasbynens.be/notes/mysql-utf8mb4 .

My preferred solution would be to redo the migration. Either with a fixed script scripts/backup.pl or with using the standard migration method.

Best regards,
Bernhard

[OBG-IT]

Hello Bernhard,
thank you very much for your help.
Through your link I was able to fix my problem.

Tickets with smileys are now created.

kind regards
Alex