Cannot insert NULL into ("OPENFIRE2"."OFGROUPPROP"."PROPVALUE")

We have Openfire 3.6.0a integrated with Clearspace 2.5.2. Our user connect with a branded version of Spark 2.5.8. We are seeing the following exceptions being thrown in the openfire log when Spark users connect and was wondering if anyone had any ideas?

java.sql.SQLException: ORA-01400: cannot insert NULL into (“OPENFIRE2”.“OFGROUPPROP”.“PROPVALUE”)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.jav a:955)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:11 68)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatem ent.java:3316)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatemen t.java:3400)
at sun.reflect.GeneratedMethodAccessor12.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja va:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at oracle.jdbc.internal.OracleStatement$$EnhancerByProxool$$95a06b85.executeUpdate ()
at org.jivesoftware.openfire.group.Group.insertProperty(Group.java:621)
at org.jivesoftware.openfire.group.Group.(Group.java:164)
at org.jivesoftware.openfire.clearspace.ClearspaceGroupProvider.translateGroup(Cle arspaceGroupProvider.java:220)
at org.jivesoftware.openfire.clearspace.ClearspaceGroupProvider.getGroup(Clearspac eGroupProvider.java:49)
at org.jivesoftware.openfire.group.GroupManager.getGroup(GroupManager.java:278)
at org.jivesoftware.openfire.group.GroupManager.getGroup(GroupManager.java:257)
at org.jivesoftware.openfire.roster.RosterItem.getSharedGroups(RosterItem.java:404 )
at org.jivesoftware.openfire.roster.Roster.getReset(Roster.java:523)
at org.jivesoftware.openfire.handler.IQRosterHandler.manageRoster(IQRosterHandler. java:202)
at org.jivesoftware.openfire.handler.IQRosterHandler.handleIQ(IQRosterHandler.java :105)
at org.jivesoftware.openfire.handler.IQHandler.process(IQHandler.java:49)
at org.jivesoftware.openfire.IQRouter.handle(IQRouter.java:351)
at org.jivesoftware.openfire.IQRouter.route(IQRouter.java:101)
at org.jivesoftware.openfire.spi.PacketRouterImpl.route(PacketRouterImpl.java:68)
at org.jivesoftware.openfire.net.StanzaHandler.processIQ(StanzaHandler.java:311)
at org.jivesoftware.openfire.net.ClientStanzaHandler.processIQ(ClientStanzaHandler .java:79)
at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:276)
at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:175)
at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandl er.java:133)
at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived (AbstractIoFilterChain.java:570)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)
at org.apache.mina.common.IoFilterAdapter.messageReceived(IoFilterAdapter.java:80)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)
at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimplePr otocolDecoderOutput.java:58)
at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecF ilter.java:185)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)
at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java :239)
at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(Execut orFilter.java:283)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java: 885)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnable.java:51)
at java.lang.Thread.run(Thread.java:619)

This Problem still exists in oracle 11.2.0.2.

The reason ist, that oracle saves empty Strings as NULL so the NOT NULL constraint on the column PROPVALUE is violated.

It can be fixed with:

ALTER TABLE ofGroupProp MODIFY propValue VARCHAR2(4000) NULL;

But i think it should also be done in the database scripts.

I can make a merge request with a fix:

  1. simply update the existing scripts
  2. update the init script and add a update script.

Which one do you prefer? I prefer 1. because this Thread is from 2008 and existing installations with oracle may have fixed them by hand. And existing installations with a repaired database will get the following error, if the update script will be applied: SQL-Fehler [1451] [72000]: ORA-01451: column to be modified to NULL cannot be modified to NULL

2019.08.01 06:47:08.148 ERROR [Jetty-QTP-AdminConsole-51] (org.jivesoftware.openfire.group.DefaultGroupPropertyMap:475) - ORA-01400: cannot insert NULL into ("SYSTEM"."OFGROUPPROP"."PROPVALUE")

java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("SYSTEM"."OFGROUPPROP"."PROPVALUE")

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) ~[ojdbc6.zip:11.2.0.4.0]
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6.zip:11.2.0.4.0]
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) ~[ojdbc6.zip:11.2.0.4.0]
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) ~[ojdbc6.zip:11.2.0.4.0]
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) ~[ojdbc6.zip:11.2.0.4.0]
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[ojdbc6.zip:11.2.0.4.0]
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208) ~[ojdbc6.zip:11.2.0.4.0]
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046) ~[ojdbc6.zip:11.2.0.4.0]
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336) ~[ojdbc6.zip:11.2.0.4.0]
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613) ~[ojdbc6.zip:11.2.0.4.0]
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3694) ~[ojdbc6.zip:11.2.0.4.0]
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354) ~[ojdbc6.zip:11.2.0.4.0]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) ~[commons-dbcp2-2.5.0.jar:2.5.0]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) ~[commons-dbcp2-2.5.0.jar:2.5.0]
	at org.jivesoftware.openfire.group.DefaultGroupPropertyMap.insertProperty(DefaultGroupPropertyMap.java:472) [xmppserver-4.3.3.jar:4.3.3]
	at org.jivesoftware.openfire.group.DefaultGroupPropertyMap.put(DefaultGroupPropertyMap.java:78) [xmppserver-4.3.3.jar:4.3.3]
	at org.jivesoftware.openfire.group.DefaultGroupPropertyMap.put(DefaultGroupPropertyMap.java:89) [xmppserver-4.3.3.jar:4.3.3]
	at org.jivesoftware.openfire.group.GroupManager$1.groupCreated(GroupManager.java:99) [xmppserver-4.3.3.jar:4.3.3]
	at org.jivesoftware.openfire.event.GroupEventDispatcher.dispatchEvent(GroupEventDispatcher.java:121) [xmppserver-4.3.3.jar:4.3.3]
	at org.jivesoftware.openfire.group.GroupManager.createGroup(GroupManager.java:324) [xmppserver-4.3.3.jar:4.3.3]
	at org.jivesoftware.openfire.admin.group_002dcreate_jsp._jspService(group_002dcreate_jsp.java:191) [xmppserver-4.3.3.jar:4.3.3]
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) [apache-jsp-8.5.24.2.jar:2.3]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) [javax.servlet-api-3.1.0.jar:3.1.0]
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:865) [jetty-servlet-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1655) [jetty-servlet-9.4.12.v20180830.jar:9.4.12.v20180830]
	at com.opensymphony.sitemesh.webapp.SiteMeshFilter.obtainContent(SiteMeshFilter.java:129) [sitemesh-2.4.2.jar:?]
	at com.opensymphony.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:77) [sitemesh-2.4.2.jar:?]
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642) [jetty-servlet-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:73) [xmppserver-4.3.3.jar:4.3.3]
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642) [jetty-servlet-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:49) [xmppserver-4.3.3.jar:4.3.3]
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642) [jetty-servlet-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:226) [xmppserver-4.3.3.jar:4.3.3]
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642) [jetty-servlet-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:216) [xmppserver-4.3.3.jar:4.3.3]
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1634) [jetty-servlet-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:533) [jetty-servlet-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:146) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548) [jetty-security-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:257) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1340) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473) [jetty-servlet-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1242) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:220) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:126) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.Server.handle(Server.java:503) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:364) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260) [jetty-server-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305) [jetty-io-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103) [jetty-io-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118) [jetty-io-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333) [jetty-util-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310) [jetty-util-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168) [jetty-util-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126) [jetty-util-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366) [jetty-util-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765) [jetty-util-9.4.12.v20180830.jar:9.4.12.v20180830]
	at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683) [jetty-util-9.4.12.v20180830.jar:9.4.12.v20180830]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_222]

Thanks, please PR option 1.

ok done https://github.com/igniterealtime/Openfire/pull/1436

Filed as OF-1828