Кодировка в Openfire (крукозябра в сообщениях)

Ранее, я описывал установку Openfire XMPP-сервера на операционной системе FreeBSD. В данной статье опишу решение проблемы неправильного отображения текста. В основном ошибка проявлялась в оффлайн сообщениях, так же её следы были найдены в веб панели управления сервером.

Ошибка выглядела в точности как на рисунке ниже

Находил в интернете решения, что достаточно изменить параметры конфига /usr/local/etc/openfire/openfire.xml

jdbc:mysql://localhost:3306/openfire?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8

Но данное решение мне не сильно облегчило жизнь и пришлось лезть в саму базу (openfire — название нашей базы)

use openfire;
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 = 'openfire';

Вывод в ответ на команду

ALTER TABLE openfire.fpAgent CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpAgentProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpAgentSession CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpChatSetting CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpDispatcher CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpDispatcherProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpOfflineSetting CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpQueue CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpQueueAgent CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpQueueGroup CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpQueueProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpRouteRule CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpSearchIndex CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpSession CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpSessionMetadata CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpSessionProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpSetting CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpWorkgroup CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpWorkgroupProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.fpWorkgroupRoster CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofBookmark CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofBookmarkPerm CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofBookmarkProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofConParticipant CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofConversation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofExtComponentConf CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofGroup CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofGroupProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofGroupUser CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofID CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofMessageArchive CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofMucAffiliation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofMucConversationLog CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofMucMember CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofMucRoom CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofMucRoomProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofMucService CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofMucServiceProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofOffline CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofPfRules CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofPresence CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofPrivacyList CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofPrivate CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofProperty CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofPubsubAffiliation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofPubsubDefaultConf CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofPubsubItem CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofPubsubNode CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofPubsubNodeGroups CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofPubsubNodeJIDs CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofPubsubSubscription CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofRRDs CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofRemoteServerConf CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofRoster CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofRosterGroups CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofSASLAuthorized CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofSecurityAuditLog CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofSipPhoneLog CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofSipUser CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofUser CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofUserFlag CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofUserProp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofVCard CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE openfire.ofVersion CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Выходим из консоли 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 = 'openfire';" > openfire.sql

Открываем выгруженную базу

ee openfire.sql

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

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

Загружаем базу обратно

mysql -u root -p < openfire.sql

Теперь переходим к настройкам конфигов сервера openfire

ee /usr/local/share/java/openfire/resources/security/openfire.xml

Находим и изменяем строку подключения к базе данных

jdbc:mysql://localhost:3306/openfire?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8

Правим файл

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

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

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

Правим файл

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

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

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

Рестартуем MySQL сервер

/usr/local/etc/rc.d/mysql-server restart

Рестартуем Open­Fire сервер

/usr/local/etc/rc.d/openfire restart