Load users from existing database

Hello !

I am trying to make openfire :

  1. Authenticate users against existing mysql database
  2. Search users in the existing mysql database

I followed this guide : http://download.igniterealtime.org/openfire/docs/latest/documentation/db-integration-guide.html

And read this discussion : Own user-database which seems to be a bit old (wildfire).

What I’ve done so far :

Step 1: installed openfire with default database
Step 2: created two users from the web admin console
Step 3: didn’t want to create the other 23 from the web admin console but to rather “import” them or “load” them from an existing mysql database I use for e-mail
Step 4: changed the openfire.xml config file to add the required lines of XML configuration, according to the documentation
Step 5: openfire still runs, but I see no new users in the web admin console
Step 6: comment out the internal HSQL section in the openfire.xml file and restart. Here’s the config file :

Step 7: Openfire doesn’t restart, here’s what’s in the error.log : (server restarted for the last time at 16:54)

Any help appreciated.

EDIT :

New users are allowed to post 2 links only, so I need to copy/paste log and config file.

1/ The config file :

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

<!--
    This file stores bootstrap properties needed by Openfire.
    Property names must be in the format: "prop.name.is.blah=value"
    That will be stored as:
        <prop>
            <name>
                <is>
                    <blah>value</blah>
                </is>
            </name>
        </prop>

    Most properties are stored in the Openfire database. A
    property viewer and editor is included in the admin console.
-->
<!-- root element, all properties must be under this element -->
<jive> 
  <adminConsole> 
    <!-- Disable either port by setting the value to -1 -->  
    <port>9090</port>  
    <securePort>9091</securePort> 
  </adminConsole>  
  <locale>en</locale>  
  <!-- Network settings. By default, Openfire will bind to all network interfaces.
      Alternatively, you can specify a specific network interfaces that the server
      will listen on. For example, 127.0.0.1. This setting is generally only useful
       on multi-homed servers. -->  
  <!--
    <network>
        <interface></interface>
    </network>
    -->  
  <!-- SPDY  Protocol is npn.
        (note: npn does not work with Java 8)
        add -Xbootclasspath/p:/OPENFIRE_HOME/lib/npn-boot.jar to .vmoptions file    -->  
  <!--
    <spdy>
        <protocol>npn</protocol>
    </spdy>
    -->  
  <!-- XEP-0198 properties -->  
  <stream> 
    <management> 
      <!-- Whether stream management is offered to clients by server. -->  
      <active>true</active>  
      <!-- Number of stanzas sent to client before a stream management
                 acknowledgement request is made. -->  
      <requestFrequency>5</requestFrequency> 
    </management> 
  </stream>



  <!-- WON'T START IF COMMENTED OUT -->
  <connectionProvider> 
    <className>org.jivesoftware.database.EmbeddedConnectionProvider</className> 
  </connectionProvider>
  <!-- WON'T START IF COMMENTED OUT -->


  


  
  <setup>true</setup>


  <!-- SEEMS TO BE IGNORED -->
  <!-- DATABASE PARAMS -->  
  <jdbcProvider> 
    <driver>com.mysql.jdbc.Driver</driver>  
    <!-- CNX URL -->  
    <connectionString>jdbc:mysql://10.10.10.200/mail?user=openfire&password=passwd</connectionString> 
  </jdbcProvider>  
  <!-- AUTHENTICATION AND USER INTEGRATION -->  
  <provider> 
    <auth> 
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className> 
    </auth>  
    <user> 
      <className>org.jivesoftware.openfire.user.JDBCUserProvider</className> 
    </user> 
  </provider>  
  <jdbcAuthProvider> 
    <!-- PASSWORD VERIFICATION-->  
    <passwordSQL>SELECT password FROM user WHERE email=?</passwordSQL>  
    <passwordType>sha512</passwordType> 
  </jdbcAuthProvider>  
  <jdbcUserProvider> 
    <!-- USER SELECTION-->  
    <loadUserSQL>SELECT name,email FROM user WHERE email=?</loadUserSQL>  
    <!-- USER COUNT-->  
    <userCountSQL>SELECT COUNT(*) FROM user</userCountSQL>  
    <!-- USER LISTING-->  
    <allUsersSQL>SELECT email FROM user</allUsersSQL>  
    <!-- USER SEARCHING-->  
    <searchSQL>SELECT email FROM user WHERE</searchSQL>  
    <!-- USER FIELDS-->  
    <usernameField>email</usernameField>  
    <nameField>name</nameField>  
    <emailField>email</emailField> 
  </jdbcUserProvider>
  <!-- SEEMS TO BE IGNORED -->  
</jive>

2/ The error log

2018.11.21 16:18:23 org.jivesoftware.openfire.XMPPServer - java.sql.SQLException: ConnectionManager.getConnection() failed to obtain a connection after 11 retries. The exception from the last attempt is as follows: java.sql.SQLException: org.logicalcobwebs.proxool.ProxoolException: Couldn't load class null
java.lang.IllegalArgumentException: java.sql.SQLException: ConnectionManager.getConnection() failed to obtain a connection after 11 retries. The exception from the last attempt is as follows: java.sql.SQLException: org.logicalcobwebs.proxool.ProxoolException: Couldn't load class null
	at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:820)
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:543)
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:163)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at java.lang.Class.newInstance(Class.java:442)
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:102)
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:53)
Caused by: java.sql.SQLException: ConnectionManager.getConnection() failed to obtain a connection after 11 retries. The exception from the last attempt is as follows: java.sql.SQLException: org.logicalcobwebs.proxool.ProxoolException: Couldn't load class null
	at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:152)
	at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:810)
	... 9 more
2018.11.21 16:54:03 org.jivesoftware.util.JiveProperties - 
java.lang.NullPointerException
	at java.util.Hashtable.put(Hashtable.java:460)
	at java.util.Properties.setProperty(Properties.java:166)
	at org.jivesoftware.database.DefaultConnectionProvider.start(DefaultConnectionProvider.java:100)
	at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectionManager.java:594)
	at org.jivesoftware.database.DbConnectionManager.ensureConnectionProvider(DbConnectionManager.java:103)
	at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:117)
	at org.jivesoftware.util.JiveProperties.loadProperties(JiveProperties.java:413)
	at org.jivesoftware.util.JiveProperties.init(JiveProperties.java:89)
	at org.jivesoftware.util.JiveProperties.getInstance(JiveProperties.java:65)
	at org.jivesoftware.util.JiveGlobals.getProperty(JiveGlobals.java:544)
	at org.jivesoftware.util.cache.CacheFactory.<clinit>(CacheFactory.java:88)
	at org.jivesoftware.openfire.XMPPServer.initialize(XMPPServer.java:311)
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:535)
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:163)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at java.lang.Class.newInstance(Class.java:442)
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:102)
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:53)
2018.11.21 16:54:03 org.logicalcobwebs.proxool.openfire - Couldn't load class null
java.lang.ClassNotFoundException: null
	at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:264)
	at org.logicalcobwebs.proxool.ConnectionPool.setDefinition(ConnectionPool.java:740)
	at org.logicalcobwebs.proxool.ConnectionPool.<init>(ConnectionPool.java:115)
	at org.logicalcobwebs.proxool.ConnectionPoolManager.createConnectionPool(ConnectionPoolManager.java:93)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:109)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:97)
	at org.logicalcobwebs.proxool.ProxoolDriver.connect(ProxoolDriver.java:77)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConnectionProvider.java:82)
	at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:125)
	at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:810)
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:543)
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:163)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at java.lang.Class.newInstance(Class.java:442)
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:102)
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:53)
2018.11.21 16:54:03 org.logicalcobwebs.proxool.ProxoolDriver - Problem
org.logicalcobwebs.proxool.ProxoolException: Couldn't load class null
	at org.logicalcobwebs.proxool.ConnectionPool.setDefinition(ConnectionPool.java:743)
	at org.logicalcobwebs.proxool.ConnectionPool.<init>(ConnectionPool.java:115)
	at org.logicalcobwebs.proxool.ConnectionPoolManager.createConnectionPool(ConnectionPoolManager.java:93)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:109)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:97)
	at org.logicalcobwebs.proxool.ProxoolDriver.connect(ProxoolDriver.java:77)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConnectionProvider.java:82)
	at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:125)
	at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:810)
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:543)
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:163)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at java.lang.Class.newInstance(Class.java:442)
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:102)
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:53)
2018.11.21 16:54:03 org.logicalcobwebs.proxool.openfire - Couldn't load class null
java.lang.ClassNotFoundException: null
	at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:264)
	at org.logicalcobwebs.proxool.ConnectionPool.setDefinition(ConnectionPool.java:740)
	at org.logicalcobwebs.proxool.ConnectionPool.<init>(ConnectionPool.java:115)
	at org.logicalcobwebs.proxool.ConnectionPoolManager.createConnectionPool(ConnectionPoolManager.java:93)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:109)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:97)
	at org.logicalcobwebs.proxool.ProxoolDriver.connect(ProxoolDriver.java:77)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConnectionProvider.java:82)
	at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:125)
	at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:810)
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:543)
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:163)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at java.lang.Class.newInstance(Class.java:442)
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:102)
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:53)
2018.11.21 16:54:03 org.logicalcobwebs.proxool.ProxoolDriver - Problem
org.logicalcobwebs.proxool.ProxoolException: Couldn't load class null
	at org.logicalcobwebs.proxool.ConnectionPool.setDefinition(ConnectionPool.java:743)
	at org.logicalcobwebs.proxool.ConnectionPool.<init>(ConnectionPool.java:115)
	at org.logicalcobwebs.proxool.ConnectionPoolManager.createConnectionPool(ConnectionPoolManager.java:93)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:109)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:97)
	at org.logicalcobwebs.proxool.ProxoolDriver.connect(ProxoolDriver.java:77)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConnectionProvider.java:82)
	at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:125)
	at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:810)
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:543)
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:163)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at java.lang.Class.newInstance(Class.java:442)
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:102)
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:53)
2018.11.21 16:54:04 org.logicalcobwebs.proxool.openfire - Couldn't load class null
java.lang.ClassNotFoundException: null
	at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:264)
	at org.logicalcobwebs.proxool.ConnectionPool.setDefinition(ConnectionPool.java:740)
	at org.logicalcobwebs.proxool.ConnectionPool.<init>(ConnectionPool.java:115)
	at org.logicalcobwebs.proxool.ConnectionPoolManager.createConnectionPool(ConnectionPoolManager.java:93)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:109)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:97)
	at org.logicalcobwebs.proxool.ProxoolDriver.connect(ProxoolDriver.java:77)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConnectionProvider.java:82)
	at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:125)
	at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:810)
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:543)
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:163)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at java.lang.Class.newInstance(Class.java:442)
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:102)
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:53)
2018.11.21 16:54:04 org.logicalcobwebs.proxool.ProxoolDriver - Problem
org.logicalcobwebs.proxool.ProxoolException: Couldn't load class null
	at org.logicalcobwebs.proxool.ConnectionPool.setDefinition(ConnectionPool.java:743)
	at org.logicalcobwebs.proxool.ConnectionPool.<init>(ConnectionPool.java:115)
	at org.logicalcobwebs.proxool.ConnectionPoolManager.createConnectionPool(ConnectionPoolManager.java:93)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:109)
	at org.logicalcobwebs.proxool.ProxoolFacade.registerConnectionPool(ProxoolFacade.java:97)
	at org.logicalcobwebs.proxool.ProxoolDriver.connect(ProxoolDriver.java:77)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConnectionProvider.java:82)
	at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:125)
	at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:810)
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:543)
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:163)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at java.lang.Class.newInstance(Class.java:442)
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:102)
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:53)

[... REPEATED 11 TIMES OR SO...]

2018.11.21 16:54:41 org.jivesoftware.openfire.XMPPServer - Database could not be accessed
java.sql.SQLException: ConnectionManager.getConnection() failed to obtain a connection after 11 retries. The exception from the last attempt is as follows: java.sql.SQLException: org.logicalcobwebs.proxool.ProxoolException: Couldn't load class null
	at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:152)
	at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:810)
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:543)
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:163)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at java.lang.Class.newInstance(Class.java:442)
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:102)
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:53)
2018.11.21 16:54:41 org.jivesoftware.openfire.XMPPServer - java.sql.SQLException: ConnectionManager.getConnection() failed to obtain a connection after 11 retries. The exception from the last attempt is as follows: java.sql.SQLException: org.logicalcobwebs.proxool.ProxoolException: Couldn't load class null
java.lang.IllegalArgumentException: java.sql.SQLException: ConnectionManager.getConnection() failed to obtain a connection after 11 retries. The exception from the last attempt is as follows: java.sql.SQLException: org.logicalcobwebs.proxool.ProxoolException: Couldn't load class null
	at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:820)
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:543)
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:163)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at java.lang.Class.newInstance(Class.java:442)
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:102)
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:53)
Caused by: java.sql.SQLException: ConnectionManager.getConnection() failed to obtain a connection after 11 retries. The exception from the last attempt is as follows: java.sql.SQLException: org.logicalcobwebs.proxool.ProxoolException: Couldn't load class null
	at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:152)
	at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:810)
	... 9 more

EDIT2 : only 60 000 characters allowed, so can’t copy/paste the whole the log file, I cut a little through it (repeated messages were ignored)

OK, I think there is a slight mismatch between the documentation and reality. It looks to me like you need to place the settings directly in system properties; so instead of

<jdbcAuthProvider><passwordSQL>SELECT password FROM user WHERE email=?</passwordSQL></jdbcAuthProvider>

Set a system property (via the admin UI)

jdbcAuthProvider.passwordSQL to the value SELECT password FROM user WHERE email=? - repeat this using all the jdbcAuthProvider properties and jdbcUserProvider properties.

HTH,

Greg

https://issues.igniterealtime.org/browse/OF-1643

1 Like

Thanks for the feedback Greg. Is there anything else I need to do to tell openfire to use the properties to list users in the admin console > users/groups > users > Users summary Page ? and also perform the searches on this database in admin console > users/groups > users > User search page ?

It seems it’s not making any connexions to the DB I configured. And the admin console > Server > Server Manager > Database page seems to be read only (can’t change anything there).

It should just be a case of restarting.

Remember there are now two separate DB configurations;

The details you see on Server > Server Manager > Database are where Openfire keeps it’s core configuration details. You can’t change those on the admin screens - it’s necessary to edit openfire.xml for that.

You’ve should re-configured Openfire to use a separate DB connection for retrieving user details; this uses a different bunch of settings that you’ve defined with your jdbcAuth/jdbcUser settings.

I say “should” because you haven’t changed settings for either
provider.auth.className (should be org.jivesoftware.openfire.auth.JDBCAuthProvider) or
provider.user.className (should be org.jivesoftware.openfire.user.JDBCUserProvider) - (check http://download.igniterealtime.org/openfire/docs/latest/documentation/db-integration-guide.html again) but be careful with these as get them wrong and you’ll lock yourself out (it is possible to recover, but can be a PITA).

Greg

Hi @all,

and sorry for picking up this old topic but :slight_smile:
I don’t get it. I’m trying to do the same thing. I’m a bit irritated by the documentation as the OP also mentions. Why is there a “howto” to modify the openfire.xml but in fact you should just add the properties through the admin website.

And at the last post of @gdt he mentioned that it is necessary to edit the openfire.xml.

But why? And how?
Would it be possible to post a complete and a supported way to connect to an external users db?
And maybe remove outdated information?

This would be a great help to me!

Best Regards,

Alex

a) If you need to change the database from where Openfire stores it’s basic configuration information, it’s easiest to shutdown Openfire, edit conf/openfire.xml, change <setup>true</setup> to <setup>false</setup> and restart. The admin UI will then guide you through the setup procces again, allowing you to update the DB information.

b) If you want to use your own database to store user details, then the documentation for this is embedded in the code at:

Greg

1 Like