Re: [dev] Wrong database collation / utf8mb4 encoding for emoticons in subject line

Hello Markus, the emails can be stored, but any characters that cannot be represented by a 3-byte Unicode sequence will be replaced with the »Unknown Character« Unicode symbol (�) by our MySQL backend. Best regards, Martin Gruner Team Lead R&D OTRS AG Bahnhofplatz 1a 94315 Straubing T: +49 (0)6172 681988 0 F: +49 (0)9421 56818 18 I: https://www.otrs.com/ Geschäftssitz: Bad Homburg, Amtsgericht: Bad Homburg, HRB 10751, USt-Nr.: DE256610065 Aufsichtsratsvorsitzender: Burchard Steinbild, Vorstand: André Mindermann (Vorsitzender), Christopher Kuhn, Sabine Riedel ____________________________________________________ Customer Service, ITSM oder Corporate Security – OTRS hat die passende Lösung. Erfahren Sie mehr: https://otrs.com/de/otrs-loesungen/

Am 20.06.2018 um 13:12 schrieb OTRS-Administration :

Hi Michiel, dear list

thank you for your quick answer. So i changed the encoding in to utf8 and after a restart of the MySQL-DB the installer encountered no problem any more. I hope this setting will work out the case that email with emojis will enter the newly setup ORTS system.

Can you please refer to a proper howto just in case the newly setup OTRS system can't handle the incoming emoticons in subject line?

Best regards,

Markus

On 06/20/2018 11:58 AM, Michiel Beijen wrote:

...

Hi,

utf8mb4 is the only character set which can contain all UTF8 characters, especially 4-byte characters such as emojis can't be stored in 'utf8'. See https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e...

So it seems like this should be the collation to use in OTRS. However there is one index which is too large for use on utf8mb4 collation, which is why OTRS developers chose for now to stick with utf8. See this bug report: https://bugs.otrs.org/show_bug.cgi?id=12361

of course, you could also just drop this index (probably, you won't need it :D) and use utf8mb4. Most probably, you'll be fine...

-- Michiel

On Wed, Jun 20, 2018 at 10:59 AM, OTRS-Administration wrote:

...

Dear list members

we've recently installed OTRS version 6.0.8 (Community Edition) on a Debian Linux system9.4 with MySQL-/Maria-DB 15.1 and Apache Webserver 2.4.25 as well as Perl 5, version 24. The installation process went fine until we reached the point to initialize OTRS' database with CGI-Skript installer.pl. The installer reports:

"Wrong database collation (character_set_database is utf8mb4, but it needs to be utf8)."

The default character encoding on MySQL-/Maria-DB is:

MariaDB [(none)]> 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_general_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci | +--------------------------+--------------------+

Do we get rid of this error by setting character_set_database to utf8 only?

In the past we encountered problems with new e-mail that comes into the OTRS ticket system and contains nasty emoticons in the subject line: as a result the OTRS system refuses to import any email anymore. So we do think it is useful to keep the utf8mb4 encoding in the database because with this encoding the database is capable to handle the incoming emoticons in subject line.

Best regards,

Markus

-- OTRS-Administration Universität Konstanz Kommunikations-, Informations-, Medienzentrum (KIM) Abteilungen KIM-Basisdienste, KIM-Support 78457 Konstanz, Deutschland

_______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/mailman/listinfo/dev

---

OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/mailman/listinfo/dev

-- OTRS-Administration Universität Konstanz Kommunikations-, Informations-, Medienzentrum (KIM) Abteilungen KIM-Basisdienste, KIM-Support 78457 Konstanz, Deutschland

_______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/mailman/listinfo/dev