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?