Offline message lost when sending emojicons

Openfire Openfire Dev
1

When user A (online) tring to send messages to user B (offline) all messages which contains emojicon code are lost.

Openfire 3.9.3

Asmack-android-8-source-4.0.0-rc2

Message

**
**

SENT (1): ��4aa406d9-2074-4fa1-a29b-f4c5f70598ac</mess age>

Opentfire Log

**
**

at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:187)

at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandl er.java:181)
at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived (AbstractIoFilterChain.java:570)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)
at org.apache.mina.common.IoFilterAdapter.messageReceived(IoFilterAdapter.java:80)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)
at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimplePr otocolDecoderOutput.java:58)
at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecF ilter.java:185)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)
at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java :239)
at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(Execut orFilter.java:283)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
2014.06.01 13:59:54 org.jivesoftware.openfire.archive.ConversationManager - Incorrect string value: ‘\xF0\x9F\x98\xA6’ for column ‘body’ at row 1
java.sql.BatchUpdateException: Incorrect string value: ‘\xF0\x9F\x98\xA6’ for column ‘body’ at row 1
at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1 760)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1382)
at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at $java.sql.PreparedStatement$$EnhancerByProxool$$9b80a39c.executeBatch()
at org.jivesoftware.openfire.archive.ConversationManager$ArchivingTask.run(Convers ationManager.java:997)
at org.jivesoftware.openfire.archive.ConversationManager$1.run(ConversationManager .java:189)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x98\xA6’ for column ‘body’ at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)
at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1 714)
… 14 more

2

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)
2014.06.01 15:40:36 org.jivesoftware.openfire.OfflineMessageStore - Internal server error
java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x98\x8D</…’ for column ‘stanza’ at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)
at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at $java.sql.PreparedStatement$$EnhancerByProxool$$9b80a39c.executeUpdate()
at org.jivesoftware.openfire.OfflineMessageStore.addMessage(OfflineMessageStore.ja va:156)
at org.jivesoftware.openfire.OfflineMessageStrategy.store(OfflineMessageStrategy.j ava:154)
at org.jivesoftware.openfire.OfflineMessageStrategy.storeOffline(OfflineMessageStr ategy.java:110)
at org.jivesoftware.openfire.MessageRouter.routingFailed(MessageRouter.java:256)
at org.jivesoftware.openfire.spi.RoutingTableImpl.routePacket(RoutingTableImpl.jav a:257)
at org.jivesoftware.openfire.MessageRouter.route(MessageRouter.java:138)
at org.jivesoftware.openfire.spi.PacketRouterImpl.route(PacketRouterImpl.java:80)
at org.jivesoftware.openfire.net.StanzaHandler.processMessage(StanzaHandler.java:3 66)
at org.jivesoftware.openfire.net.ClientStanzaHandler.processMessage(ClientStanzaHa ndler.java:107)
at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:220)
at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:187)
at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandl er.java:181)
at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived (AbstractIoFilterChain.java:570)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)
at org.apache.mina.common.IoFilterAdapter.messageReceived(IoFilterAdapter.java:80)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)
at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimplePr otocolDecoderOutput.java:58)
at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecF ilter.java:185)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)
at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java :239)
at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(Execut orFilter.java:283)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

3

OF-815

4

Looks like it was already reported earlier (and filed by me…) OF-695

5

I’ve tried what’s suggested in the link without any luck, the same error.

java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x98\x9C</…’ for column ‘stanza’ at row 1

ALTER TABLE ofOffline MODIFY stanza Longtext CHARACTER SET utf8 collate utf8_unicode_ci;

ALTER TABLE ofOffline MODIFY stanza Longtext CHARACTER SET utf8mb4;

ALTER TABLE ofOffline MODIFY stanza Longtext;

Edit:

I don’t have an access to the server right now to try character_set_server=utf8mb4 so I will try it later.

6

I see that you have edited your post but i don’t see the final post here. So, can i add this as a possible solution to the ticket?

ALTER DATABASE openfiredb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE ofOffline MODIFY stanza Longtext CHARACTER SET utf8 collate utf8_unicode_ci;

7

Sorry it’s my fault I thought it was fixed. I believe that using utf8mb4 will solve it but I think I need to change the connection string and restart MySQL server I will try it tommorow.

You may find more about it here:

http://www.codeproject.com/Articles/684717/Emoji-and-web-application

http://mathiasbynens.be/notes/mysql-utf8mb4

8

Thanks a lot . I really appreciate your help.

Now I’m confident to tell that the following sql solved the problem:

ALTER DATABASE openfire_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE ofOffline CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE ofOffline CHANGE stanza stanza longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Edit:

query: select * from ofoffline

username
messageID
creationDate
messageSize
stanza
username
770
001401834906312
248

9

I have posted your fix as a comment for OF-695. Hopefully one of the devs will take a look and include this for 3.9.4.