powered by Jive Software

Possible bug in SQL Server setup script - Openfire 3.8.2

I have just installed Openfire 3.8.2 on CentOS, using an Microsoft SQL Server as the database. The SQL Server is a windows 2008 R2 server running Microsoft SQL Server 2008 R2. I am having no issues with the actual Openfire installation, but did notice a lot of these messages in the warn.log file:

2013.08.13 14:11:07 org.jivesoftware.openfire.security.DefaultSecurityAuditProvider - Error trying to insert a new row in ofSecurityAuditLog:

java.sql.SQLException: Cannot insert the value NULL into column ‘node’, table ‘openfire-predev.dbo.ofSecurityAuditLog’; column does not allow nulls. INSERT fails.

    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)

    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)

    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)

    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)

    at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)

    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)

    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedState ment.java:505)

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

    at java.lang.reflect.Method.invoke(Unknown Source)

    at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

    at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

    at $java.sql.Wrapper$$EnhancerByProxool$$6a645ac9.executeUpdate(<generated>)

    at org.jivesoftware.openfire.security.DefaultSecurityAuditProvider.logEvent(Defaul tSecurityAuditProvider.java:79)

    at org.jivesoftware.openfire.security.SecurityAuditManager.logEvent(SecurityAuditM anager.java:138)

    at org.jivesoftware.util.WebManager.logEvent(WebManager.java:138)

    at org.jivesoftware.openfire.admin.server_002drestart_jsp._jspService(server_002dr estart_jsp.java:85)

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

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

    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:547)

    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1359)

    at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:11 8)

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

    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1330)

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

    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1330)

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

    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1330)

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

    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1330)

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

    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1330)

    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:478)

    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)

    at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:520)

    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:22 7)

    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:94 1)

    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:409)

    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:186 )

    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:875 )

    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)

    at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandler Collection.java:250)

    at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.jav a:149)

    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:110)

    at org.eclipse.jetty.server.Server.handle(Server.java:349)

    at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:441)

    at org.eclipse.jetty.server.HttpConnection$RequestHandler.headerComplete(HttpConne ction.java:919)

    at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:582)

    at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:218)

    at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:51 )

    at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.jav a:586)

    at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java :44)

    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:598 )

    at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:533)

    at java.lang.Thread.run(Unknown Source)

To fix this, I had to drop and recreate the table ofSecurityAuditLog, but with the ‘node’ column supporting 'NULL’. Now that I have done that, it works great.

This is a very simple fix. I simply used Microsoft SQL Management Studio to first script recreating the table, then dropped the table, and recreated it work ‘allow NULL’ on the node column. To prevent this in the first place, the ‘openfire_sqlserver.sql’ which comes with Openfire should be altered slightly. This is the current excerpt of that file which creates this particular table:

CREATE TABLE ofSecurityAuditLog (

msgID INTEGER NOT NULL,

username NVARCHAR(64) NOT NULL,

entryStamp BIGINT NOT NULL,

summary NVARCHAR(255) NOT NULL,

node NVARCHAR(255) NOT NULL,

details NTEXT,

CONSTRAINT ofSecurityAuditLog_pk PRIMARY KEY (msgID)

);

All that needs modifying is one word, such that the ‘openfire_sqlserver.sql’ file looks like this instead:

CREATE TABLE ofSecurityAuditLog (

msgID INTEGER NOT NULL,

username NVARCHAR(64) NOT NULL,

entryStamp BIGINT NOT NULL,

summary NVARCHAR(255) NOT NULL,

node NVARCHAR(255) NULL,

details NTEXT,

CONSTRAINT ofSecurityAuditLog_pk PRIMARY KEY (msgID)

);

I don’t know how bugs get filed and processed, but could you put this into the queue?

Thanks,

  • Erik

Thanks for the report. Filed as OF-692.