Emoji's not working in offline chat when using Openfire to chat between two iOS devices (using a MySQL db)

Question

I have a problem with Openfire and especially support for emoji characters. I've searched on internet found out that in order to support emoji, I have to change the encoding and collation of the database and tables to UTF-8 unicode (utf8mb4). I've done that using the following SQL commands:

SET NAMES utf8mb4;  
ALTER DATABASE openfire CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;  
ALTER TABLE ofOffline CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  

So in the statements above, I'm changing the character set and collation of the database and table. Next, I read that I should change the JDBC driver to support unicode. I have the following value for the "database.defaultProvider.serverURL" entry in my system properties (using the Openfire admin webpage):

jdbc:mysql://localhost:3306/openfire?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8

When I'm sending messages with emoji between two online users, it works flawless. When the recipient of the message is offline, the message is stored to the database and this is where it goes wrong: the emoji is not correctly stored in the database (it is stored as two question marks).

My CREATE TABLE statement looks as follows:

CREATE TABLE `ofMessageArchive` (
  `messageID` bigint(20) DEFAULT NULL,
  `conversationID` bigint(20) NOT NULL,
  `fromJID` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `fromJIDResource` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `toJID` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `toJIDResource` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sentDate` bigint(20) NOT NULL,
  `stanza` mediumtext COLLATE utf8mb4_unicode_ci,
  `body` mediumtext COLLATE utf8mb4_unicode_ci,
  KEY `ofMessageArchive_con_idx` (`conversationID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I tried fetching the stanza text (with the emoji in it) using the following query (note that message 73 contains only one character: the emoji):

SELECT stanza, HEX(stanza) FROM ofOffline WHERE messageID = 73

This gives me (left of the - sign the message, right the hex value):

😀 - F09F9880

When inserting the stanza in the database (in particular, the ofOffline table where messages are stored that are to be delivered to offline users), the following code gets executed:

String msgXML = message.getElement().asXML();

Connection con = null;
PreparedStatement pstmt = null;
try {
    con = DbConnectionManager.getConnection();
    pstmt = con.prepareStatement(INSERT_OFFLINE);
    pstmt.setString(1, username);
    pstmt.setLong(2, messageID);
    pstmt.setString(3, StringUtils.dateToMillis(new java.util.Date()));
    pstmt.setInt(4, msgXML.length());
    pstmt.setString(5, msgXML);
    pstmt.executeUpdate();
}

I'm not sure what I can try now or whether there are any other workarounds for this issue. Any help would be greatly appreciated.

Thanks in advance!

Answer 1

After some more investigation, I found out that the issue probably had to do with the JDBC driver that Openfire uses. It seemed as if my parameters for the connection string were not properly parsed and used so I changed the JDBC connection driver string in conf/openfire.xml and restarted the server. Now, when I inserted an emoji, I got the following error:

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x80</...' for column 'stanza' at row 1

After some searching for the above error, It was explained here that I should add the following to my MySQL config file:

[mysqld]
...
character-set-server = utf8mb4

Moreover, I can remove the characterEncoding parameter from the connection string. After I made these changes, restarted MySQL and openfire, the insertion of Emoji characters started working.