Offline messages with non US-ASCII text do not get saved in offline storage (= lost messages)

Openfire is 3.3.2, client in use is Spark 2.5.6. MySQL server is 5.0.48 (according to logs seems to have happened in earlier versions too). MySQL JDBC library is the one that comes with Openfire.

Attempting to send the following (just “åäö”) to an offline user results in the following being printed to error.log and the message silently being discarded (lost). It does not matter if you send it separately or embedded into something another string of characters.

2007.09.17 21:22:15 org.jivesoftware.openfire.OfflineMessageStore.addMessage(OfflineMessageStore.jav a:139) Internal server error

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column ‘message’ at row 1

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2973)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)

at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.ja va:1125)

at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement. java:677)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1357)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1274)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1259)

at org.jivesoftware.openfire.OfflineMessageStore.addMessage(OfflineMessageStore.ja va:135)

at org.jivesoftware.openfire.OfflineMessageStrategy.store(OfflineMessageStrategy.j ava:138)

at org.jivesoftware.openfire.OfflineMessageStrategy.storeOffline(OfflineMessageStr ategy.java:98)

at org.jivesoftware.openfire.MessageRouter.routeToBareJID(MessageRouter.java:164)

at org.jivesoftware.openfire.MessageRouter.route(MessageRouter.java:100)

at org.jivesoftware.openfire.spi.PacketRouterImpl.route(PacketRouterImpl.java:71)

at org.jivesoftware.openfire.net.StanzaHandler.processMessage(StanzaHandler.java:3 23)

at org.jivesoftware.openfire.net.ClientStanzaHandler.processMessage(ClientStanzaHa ndler.java:91)

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

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

at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandl er.java:132)

at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived (AbstractIoFilterChain.java:703)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:362)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:54)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:800)

at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimplePr otocolDecoderOutput.java:62)

at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecF ilter.java:200)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:362)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:54)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:800)

at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java :266)

at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(Execut orFilter.java:326)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java: 885)

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

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

I do have the following configured in openfire.xml:

which according to my understanding (and reading this site + lots of googling) should Just Make Things Work.

I looked at the jiveOffline and any message with special characters (in our case å, ä, ö, Å, Ä, Ö in the Finnish alphabet) is not stored there.

Also does not seem to make a difference if runs ALTER TABLE jiveOffline DEFAULT CHARACTER SET=UTF8.

If the server has been configured to store offline messages and is unable to store it should IMHO return something to the user indicating things got out of whack.

Any ideas on fixing, though?

Kaj

Since the tables were originally as LATIN1, they need to be changed. The following stanza helps:

alter database default character set utf8;

alter table gatewayPseudoRoster default charset=utf8;

alter table gatewayRegistration default charset=utf8;

alter table gatewayRestrictions default charset=utf8;

alter table jiveExtComponentConf default charset=utf8;

alter table jiveGroup default charset=utf8;

alter table jiveGroupProp default charset=utf8;

alter table jiveGroupUser default charset=utf8;

alter table jiveID default charset=utf8;

alter table jiveOffline default charset=utf8;

alter table jivePresence default charset=utf8;

alter table jivePrivacyList default charset=utf8;

alter table jivePrivate default charset=utf8;

alter table jiveProperty default charset=utf8;

alter table jiveRemoteServerConf default charset=utf8;

alter table jiveRoster default charset=utf8;

alter table jiveRosterGroups default charset=utf8;

alter table jiveSASLAuthorized default charset=utf8;

alter table jiveUser default charset=utf8;

alter table jiveUserProp default charset=utf8;

alter table jiveVCard default charset=utf8;

alter table jiveVersion default charset=utf8;

alter table mucAffiliation default charset=utf8;

alter table mucConversationLog default charset=utf8;

alter table mucMember default charset=utf8;

alter table mucRoom default charset=utf8;

alter table mucRoomProp default charset=utf8;

alter table phoneDevice default charset=utf8;

alter table phoneServer default charset=utf8;

alter table phoneUser default charset=utf8;

alter table pubsubAffiliation default charset=utf8;

alter table pubsubDefaultConf default charset=utf8;

alter table pubsubItem default charset=utf8;

alter table pubsubNode default charset=utf8;

alter table pubsubNodeGroups default charset=utf8;

alter table pubsubNodeJIDs default charset=utf8;

alter table pubsubSubscription default charset=utf8;

jdbc:mysql://127.0.0.1:3306/openfire

true

Then it’s time to restart openfire and suddenly all is well.

service openfire restart

Is there a reason why the tables are created with LATIN1 encoding instead of UTF-8? (Would it be possible to get this changed in a future release so everybody who needs UTF-8 with MySQL wouldn’t have to go through the same obstacle course). The modification makes the local installation deviate from the openfire’s default.

I do still dislike the fact that messages are simply lost, from a user perspective it does not build confidence (and from an admin point of view complaining users need to be made happy somehow).

Kaj

If you look at the sql creation scripts, there is no encoding mentioned. Tables inherit the encoding of the database they are created in.