powered by Jive Software

Emoticons support for Amazon RDS mysql db

We have integrated openfire with mysql db on Amazon RDS. I guess there are problems with emoticons or some special characters.
We have the following error logs in the openfire’s error.log file

2016.03.14 01:26:04 org.jivesoftware.openfire.OfflineMessageStore - Internal server error

java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x8E\x89\xF0\x9F…’ 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.GeneratedMethodAccessor20.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja va:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

at com.mysql.jdbc.Statement$$EnhancerByProxool$$87a9b307.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:114)

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(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnable.java:51)

at java.lang.Thread.run(Thread.java:745)

2016.03.14 01:26:22 org.jivesoftware.openfire.archive.ConversationManager - Incorrect string value: ‘\xF0\x9F\x8E\x89\xF0\x9F…’ for column ‘body’ at row 1

java.sql.BatchUpdateException: Incorrect string value: ‘\xF0\x9F\x8E\x89\xF0\x9F…’ 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(DelegatingMethodAccessorImpl.ja va:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

at com.mysql.jdbc.Statement$$EnhancerByProxool$$87a9b307.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(Executors.java:471)

at java.util.concurrent.FutureTask.run(FutureTask.java:262)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at java.lang.Thread.run(Thread.java:745)

Caused by: java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x8E\x89\xF0\x9F…’ 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

2016.03.14 17:58:22 org.jivesoftware.openfire.archive.ConversationManager - Incorrect string value: ‘\xF0\x9F\x98\x8F’ for column ‘body’ at row 1

java.sql.BatchUpdateException: Incorrect string value: ‘\xF0\x9F\x98\x8F’ 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(DelegatingMethodAccessorImpl.ja va:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

at com.mysql.jdbc.Statement$$EnhancerByProxool$$87a9b307.executeBatch()

at org.jivesoftware.openfire.archive.ConversationManager$ArchivingTask.run(Convers ationManager.java:1002)

at org.jivesoftware.openfire.archive.ConversationManager$1.run(ConversationManager .java:189)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)

at java.util.concurrent.FutureTask.run(FutureTask.java:262)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at java.lang.Thread.run(Thread.java:745)

Caused by: java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x98\x8F’ 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

2016.03.14 18:47:05 org.jivesoftware.openfire.OfflineMessageStore - Internal server error

Following to few posts over stack overflow we have done charset as utf8mb4 and and collation to utf8mb4_unicode_ci. However the errors still persists.
Any suggestions?

Hi Sagar,

Not sure if you are still facing the issue. 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