Monitoring plugin and emoticons

Openfire Openfire Support
1

Hi everyone,

my company uses Openfire (With the latest Mysql as a database) extensively and we also use the monitoring plugin to retrieve and sync chat sessions across android and iOS devices.

We recently ran into a problem. When a user sends a message containing complex emoticons, such as iOS emojis which often requires the full 4 bytes of utf8 to be mapped correctly, the recipient at first correctly receives the emoticons (So we know it is not an inherent problem in the xml stream). However, the monitoring plugin fails to correctly save them to the database, resulting in a bunch of “?” being saved instead.

The issue can be reproduced by sending emojis from an iOS device and trying to retrieve them through the monitoring plugin.

We are using MySQL 5.5.4 as database, and from initial research the issue might be due to the MySQL implementation of the utf8 charset. Apparently MySQL only maps correctly utf8 characters requiring up to 3 bytes. The suggested solution appears to be to change charsets to utf8mb4. See link https://mathiasbynens.be/notes/mysql-utf8mb4 .

We have not solved the issue yet. Can anyone suggest a solution or is there an easy work around?

Paolo

2

Hi I hate to up this this way.
However, this appears to have been ignored for a very long time.
It is still a problem (4 years later I stumbled into the problem again, only to find I had already posted about this ages ago)
We have tried converting our latest openfire database to utf8mb4 still doesn’t solve the issue. Are we doing something fundamentally wrong?
Thanks in advance
Paolo

3

Hi Paolo,

I recently came across this issue and solved it by altering the database.

Alter your openfire database with below queries. This should solve your issue of emoticons.

ALTER TABLE ofMessageArchive MODIFY COLUMN body TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE ofMessageArchive MODIFY COLUMN stanza TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE ofOffline MODIFY COLUMN stanza TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Let me know if you still face the issue.

Regards,
Bipin

4

Hi Bipin,
eventually we did the same adding a script we run on openfire post installation to alter those table structures.
We also eventually resolved to alter the connection script and base collation for the mysql server installation altering the my.cnf as:

[client]
default-character-set=utf8mb4
 
[mysqld]
character-set-server=utf8mb4

And the connection string in openfire configuration to:

<serverURL>jdbc:mysql://localhost:3306/of?useUnicode=true&amp;rewriteBatchedStatements=true</serverURL>

I paste this here as it may be helpful to other people as well.

Thanks again,
Paolo Manili