Openfire 3.4.1 and Custom Database

Openfire Openfire Support
1

Hello,

I have a pretty simple question.

Has anyone here been able to setup openfire version 3.4.1 to use a custom MySQL database?

I have read through the documentation as well as various threads and it simply will not work.

I always get the same error.

2007.11.27 16:28:34 org.jivesoftware.openfire.auth.JDBCAuthProvider.getPasswordValue(JDBCAuthProvid er.java:254) Exception in JDBCAuthProvider

java.sql.SQLException: The url cannot be null

at java.sql.DriverManager.getConnection(DriverManager.java:554)

at java.sql.DriverManager.getConnection(DriverManager.java:207)

at org.jivesoftware.openfire.auth.JDBCAuthProvider.getPasswordValue(JDBCAuthProvid er.java:240)

at org.jivesoftware.openfire.auth.JDBCAuthProvider.authenticate(JDBCAuthProvider.j ava:116)

at org.jivesoftware.openfire.auth.AuthFactory.authenticate(AuthFactory.java:149)

at org.jivesoftware.openfire.admin.login_jsp._jspService(login_jsp.java:150)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1093)

at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:39)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:65)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingF ilter.java:41)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:69)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:98)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)

at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)

at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)

at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:712)

at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)

at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollect ion.java:211)

at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)

at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:139)

at org.mortbay.jetty.Server.handle(Server.java:313)

at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:506)

at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:844 )

at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:644)

at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)

at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:381)

at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:396)

at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:442)

So if you are able to use Openfire 3.4.1 with MySQL custom database let me know, so that I can hace patience

to keep trying.

2

What do you mean by MySQL custom database? I’m using it with MySQL, but I don’t know what you mean by custom.

3

I mean this kind of setup.

http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/db-integ ration-guide.html

4

aha! Well that’s very cool. =)

Either way, the error “appears” to be indicating that the table referenced has a url field that can not be null, and yet is being set to null.

Either that or the uri of the mysql database itself isn’t set in all of the right places?

If you wouldn’t mind, would you share your config so we can look over it? (with passwords and sensitive data X’d out)

5

Here’s the config.

If I remove everything below the line everything works fine.

If I try to include the custom db settings everything breaks.

<jive>
  <adminConsole>
    <port>9090</port>
    <securePort>9091</securePort>
  </adminConsole>
  <admin>
    <authorizedUsernames>me</authorizedUsernames>
  </admin>
  <locale>en</locale>
    <network>
        <interface></interface>
    </network>
  <connectionProvider>
    <className>org.jivesoftware.database.DefaultConnectionProvider</className>
  </connectionProvider>
  <database>
    <defaultProvider>
      <driver>com.mysql.jdbc.Driver</driver>
      <serverURL>jdbc:mysql://db.domain.com:3306/openfire</serverURL>
      <username>XXX</username>
      <password>XXX</password>
      <minConnections>5</minConnections>
      <maxConnections>15</maxConnections>
      <connectionTimeout>1.0</connectionTimeout>
    </defaultProvider>
  </database>
  <setup>true</setup>
  <jdbcProvider>
    <driver>org.mysql.jdbc.Driver</driver>
    <connectionString>jdbc:mysql://db.domain.com:3306/sigma?user=XXX&amp;password=XXX</connectionString>
  </jdbcProvider>
<provider>
    <auth>
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
    </auth>
    <user>
      <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>
    </user>
  </provider>
  <jdbcAuthProvider>
    <passwordSQL>SELECT pword FROM staff WHERE login=?</passwordSQL>
    <passwordType>plain</passwordType>
  </jdbcAuthProvider>
  <jdbcUserProvider>
    <loadUserSQL>SELECT fname FROM staff WHERE login=?</loadUserSQL>
    <userCountSQL>SELECT COUNT(*) FROM staff</userCountSQL>
    <allUsersSQL>SELECT login FROM staff</allUsersSQL>
    <searchSQL>SELECT login FROM staff WHERE</searchSQL>
    <usernameField>login</usernameField>
    <nameField>fname</nameField>
    <emailField>email</emailField>
  </jdbcUserProvider>
</jive>