Linux: Openfire Finetuning

This is to describe how to deal with incorrect coding of roster contacts in Openfire when using MySQL…

Openfire is an instant messaging (IM) and groupchat server that uses XMPP server written in Java and licensed under the Apache License 2.0.

Openfire and UTF-8 in MySQL

Unfortunalely, from some clumsy reasons, the Openfire server does not store rosters of jabber users in UTF8. Therefore, instead of some specific characters, exclamation marks (?) occur. Some clumsy information is available here, but this is how to make it properly.

First, start Openfire, log into the MySQL database and execute the following commands:

ALTER DATABASE `openfire` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `openfire`;
SHOW TABLE STATUS FROM openfire;
ALTER TABLE `ofExtComponentConf` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofGatewayAvatars` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofGatewayPseudoRoster` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofGatewayRegistration` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofGatewayRestrictions` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofGatewayVCards` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofGroup` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofGroupProp` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofGroupUser` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofID` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofMucAffiliation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofMucConversationLog` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofMucMember` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofMucRoom` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofMucRoomProp` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofMucService` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofMucServiceProp` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofOffline` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofPresence` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofPrivacyList` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofPrivate` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofProperty` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofPubsubAffiliation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofPubsubDefaultConf` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofPubsubItem` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofPubsubNode` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofPubsubNodeGroups` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofPubsubNodeJIDs` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofPubsubSubscription` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofRemoteServerConf` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofRoster` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofRosterGroups` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofSASLAuthorized` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofSecurityAuditLog` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofUser` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofUserFlag` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofUserProp` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofVCard` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `ofVersion` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
SHOW TABLE STATUS FROM openfire;

The tables should be all converted to utf8_general_ci.

Now edit the openfile.xml file

nano /opt/openfire/conf/openfire.xml

and replace

rewriteBatchedStatements=true

with

useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8

Start Openfire again.

Openfire and “Empty” Contact List

Sometimes, when trying to connect using my Psi client to the Openfire server, it appears that the connection was correctly established, the client is online, but not a single contact appers in my contact list (in other words, all seem to be offline). It cannot be said that this is a general solution for everybody, but it helped me. Thus basically, switch off the compression on the server, and restart the jabber client. To make sure, the client is not trying to compress the traffic, I switched off the compression support in the client as well.

Advertisements
This entry was posted in Linux, Server and tagged , , , , , , . Bookmark the permalink.