Best before 23rd June
I will be soon out of date.

Openfire database integration changes

It seems that recently the way how database should be integrated for the use of the Openfire XMPP server, has changed. The config file needs to have the additional settings but after they also need to be hand edited at the server settings page of the admin panel, thus the configuration file becomes like one below:

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

<jive>
  <adminConsole>
    <port>9090</port>
    <securePort>9091</securePort>
  </adminConsole>
  <admin></admin>
  <locale>en</locale>
  <provider>
    <auth/>
    <user/>
    <group></group>
  </provider>
  <jdbcProvider/>
  <jdbcAuthProvider/>
  <jdbcUserProvider/>
  <jdbcGroupProvider></jdbcGroupProvider>
  <log>
    <debug>
      <enabled>true</enabled>
    </debug>
  </log>
  <connectionProvider>
    <className>org.jivesoftware.database.DefaultConnectionProvider</className>
  </connectionProvider>
  <database>
    <defaultProvider>
      <driver>com.mysql.jdbc.Driver</driver>
      <serverURL>jdbc:mysql://localhost:3306/openfire?useUnicode=true&;characterEncoding=UTF-8&;characterSetResults=UTF-8</serverURL>
      <username>openfire</username>
      <password>openfire</password>
      <testSQL>select 1</testSQL>
      <testBeforeUse>true</testBeforeUse>
      <testAfterUse>true</testAfterUse>
      <minConnections>5</minConnections>
      <maxConnections>25</maxConnections>
      <connectionTimeout>1.0</connectionTimeout>
    </defaultProvider>
  </database>
  <setup>true</setup>
</jive>

The database properties are set to the following:

ofProperty values
name propValue
admin.authorizedJIDs juga@paazmaya.fi
jdbcAuthProvider.passwordSQL SELECT password FROM users WHERE nickname = ? LIMIT 1
jdbcAuthProvider.passwordType sha1
jdbcGroupProvider.allGroupsSQL SELECT title FROM groups ORDER BY title ASC
jdbcGroupProvider.descriptionSQL SELECT description FROM groups WHERE title = ?
jdbcGroupProvider.groupCountSQL SELECT COUNT() FROM groups
jdbcGroupProvider.loadMembersSQL SELECT A.nickname FROM users A, group_user B, groups C WHERE C.title = ? AND A.id = B.user AND B.group = C.id
jdbcGroupProvider.userGroupsSQL SELECT A.title FROM groups A, group_user B, users C WHERE A.id = B.group AND B.user = C.id AND C.nickname = ?
jdbcProvider.connectionString jdbc:mysql://localhost:3306/tsuka_beta?user=tsuka_beta&password=tsuka_beta
jdbcProvider.driver com.mysql.jdbc.Driver
jdbcUserProvider.allUsersSQL SELECT nickname FROM users ORDER BY nickname ASC
jdbcUserProvider.emailField email
jdbcUserProvider.loadUserSQL SELECT nickname, email FROM users WHERE nickname = ? LIMIT 1
jdbcUserProvider.nameField nickname
jdbcUserProvider.searchSQL SELECT nickname FROM users WHERE
jdbcUserProvider.userCountSQL SELECT COUNT() FROM users
jdbcUserProvider.usernameField nickname
provider.admin.className org.jivesoftware.openfire.admin.DefaultAdminProvider
provider.auth.className org.jivesoftware.openfire.auth.JDBCAuthProvider
provider.group.className org.jivesoftware.openfire.group.JDBCGroupProvider
provider.lockout.className org.jivesoftware.openfire.lockout.DefaultLockOutProvider
provider.securityAudit.className org.jivesoftware.openfire.security.DefaultSecurityAuditProvider
provider.user.className org.jivesoftware.openfire.user.JDBCUserProvider
provider.vcard.className org.jivesoftware.openfire.vcard.DefaultVCardProvider
xmpp.domain paazmaya.fi

Remember to make sure that the domain of the admin user matches to the domain of the server.