General discussion on installation and configuration of SOGo

Text archives Help


Re: [SOGo] appointments from iPhone with emoticons


Chronological Thread 
  • From: "Chris Coleman" ( ) < >
  • To: "\"Andreas Blaha\" ( )" < >
  • Subject: Re: [SOGo] appointments from iPhone with emoticons
  • Date: Sat, 11 Feb 2017 15:02:49 -0500
  • Dmarc-filter: OpenDMARC Filter v1.2.0 mail.inverse.ca 8E74DCA8D1D

UTF-8 encoding fully supports emoticons; however, MySQL’s limited version of utf8 does not support emoticons!

MySQL’s utf8 can only store 3 byte characters, which are characters from the Basic Multilingual Plane - characters from the majority of modern languages and some symbols.  Emoticon characters exist in the Supplementary Multilingual Plane for which we need 4 bytes and so must use utf8mb4.

To save emoticons to a MySQL database we need to change the character set and collation properties, on the database connection, the database, the tables, and the columns, to use utf8mb4.  Not necessarily all the columns, a minimal solution has utf8mb4 on only the columns containing user-entered text.

Below find commands to change the character set and collation properties.  For pro users only.   Warning, backup your entire database before trying this because the risk of data loss is real:

SET NAMES utf8mb4;
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(140) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

** Bear in mind that changing from utf8 to utfmb4 affects the maximum lengths of columns and index keys. The maximum lengths of columns will be unchanged when modifying the tables with the commands discussed here; but in terms of actual characters the amount that can be stored is reduced. This is a consequence of changing from being able to store only 3 bytes up to 4! You may need to update these lengths. **

REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;

$ mysqlcheck -u root -p --auto-repair --optimize --all-databases

Finally to change the character set of the connection, that setting must be changed inside the SOGo application.

Hope this helps.


On 2/10/2017 9:21 AM, "Andreas Blaha" ( ) wrote:

Hi all,

strange request from a user:

when an appointment created on the iphone/ipad contains an emoticon (wherever: title, notes, location etc.) the appointment is visible and manageable on the iphone/ipad, but not in the web gui. It can be seen but does not give any details, cannot be edited or deleted from there, I cannot view the source text of the appointment, but I get the according reminders. The appointment is not visible at all in Outlook 201x/ActiveSync. This behaviour I can reproduce, both in 2.3.19 and 3.2.6a.

I only found an open bug referring to UTF8, not sure if this has anything to do with it.

I have no Android device at hand to check whether it is the same there or not.

 

Has anyone experienced this as well?

 

Best regards

Andy

--

https://inverse.ca/sogo/lists



Archive powered by MHonArc 2.6.18.

Top of page