Есть сервер openfire 3.7, работающий с базой данных mysql. По умолчанию таблицы в ней в кодировке latin1, кроме того сама админка Openfire по-умолчанию не дружит с UTF-8. Я использую FreeBSD, так что в Linux пути будут отличаться.

С базой данных в консоли mysql делаем вот что:

use наша-база;
alter database character set utf8;
alter database collate utf8_general_ci;

Теперь необходимо сделать массовое изменение кодировки таблиц. Формируем текст с нужными нам запросами:

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'наша-база';

Получаем вывод вида (это просто текст запросов, они не исполняются):

+------------------------------------------------------------------------------------------------------------------+
| CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;')     |
+------------------------------------------------------------------------------------------------------------------+
| ALTER TABLE наша-база.fpAgent CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                             |
| ALTER TABLE наша-база.fpAgentProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                         |
| ALTER TABLE наша-база.fpAgentSession CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                      |
| ALTER TABLE наша-база.fpChatSetting CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                       |
| ALTER TABLE наша-база.fpDispatcher CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                        |
| ALTER TABLE наша-база.fpDispatcherProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                    |
| ALTER TABLE наша-база.fpOfflineSetting CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                    |
| ALTER TABLE наша-база.fpQueue CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                             |
| ALTER TABLE наша-база.fpQueueAgent CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                        |
| ALTER TABLE наша-база.fpQueueGroup CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                        |
| ALTER TABLE наша-база.fpQueueProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                         |
| ALTER TABLE наша-база.fpRouteRule CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                         |
| ALTER TABLE наша-база.fpSearchIndex CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                       |
| ALTER TABLE наша-база.fpSession CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                           |
| ALTER TABLE наша-база.fpSessionMetadata CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                   |
| ALTER TABLE наша-база.fpSessionProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                       |
| ALTER TABLE наша-база.fpSetting CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                           |
| ALTER TABLE наша-база.fpWorkgroup CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                         |
| ALTER TABLE наша-база.fpWorkgroupProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                     |
| ALTER TABLE наша-база.fpWorkgroupRoster CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                   |
| ALTER TABLE наша-база.ofBookmark CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                          |
| ALTER TABLE наша-база.ofBookmarkPerm CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                      |
| ALTER TABLE наша-база.ofBookmarkProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                      |
| ALTER TABLE наша-база.ofConParticipant CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                    |
| ALTER TABLE наша-база.ofConversation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                      |
| ALTER TABLE наша-база.ofExtComponentConf 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 наша-база.ofMessageArchive 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 наша-база.ofPfRules 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 наша-база.ofRRDs 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 наша-база.ofSipPhoneLog CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;                       |
| ALTER TABLE наша-база.ofSipUser 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;                           |
+------------------------------------------------------------------------------------------------------------------+
64 rows in set (0.01 sec)

Выходим из консоли mysql, выполняем команду mysql с запросом и сохраняем вывод в файл:

mysql -u root -p -e "SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'наша-база';" > query.sql

Удаляем из файла запрос:

CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;')

Выполняем:

mysql -u root -p < query.sql

Ок, с базой разобрались. Редактируем настройки подключения в /usr/local/share/java/openfire/resources/security/openfire.xml:

<serverURL>jdbc:mysql://localhost:3306/наша-база?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8</serverURL>

Из-за автозамены, привожу скриншот:



Правим админку:

/usr/local/share/java/openfire/plugins/admin/webapp/WEB-INF/admin.tld:

Заменяем кодировку xml файла на UTF-8:

<?xml version="1.0" encoding="UTF-8"?>

/usr/local/share/java/openfire/plugins/admin/webapp/WEB-INF/web.xml

<?xml version="1.0" encoding="UTF-8"?>

Рестартуем mysql и openfire.