I have a problem with Openfire and especially emoji character support. I searched the Internet, found out that to support emoji I need to change the encoding and mapping of the database and tables in unicode UTF-8 (utf8mb4). I did this 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 instructions above, I am changing the character set and sorting of the database and table. Then I read that I have to change the JDBC driver to support unicode. I have the following value for writing "database.defaultProvider.serverURL" in my system properties (using the Openfire admin web page):
jdbc:mysql://localhost:3306/openfire?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8
When I send emoji messages between two users online, it works flawlessly. When the recipient of the message is offline, the message is stored in the database, and in this case it is erroneous: emoji is not stored correctly in the database (it is stored as two question marks).
My statement is CREATE TABLEas 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 to extract the stanza text (with emoji in it) using the following query (note that message 73 contains only one character: emoji):
SELECT stanza, HEX(stanza) FROM ofOffline WHERE messageID = 73
This gives me (to the left of the sign of the -message, to the right of the hexadecimal value):
😀 - F09F9880
When inserting a stanza into a database (in particular, into a table ofOfflinewhere messages are stored that should be delivered to offline users), the following code is 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();
}
, , . .
!