powered by Jive Software

Cannot create Packet Filter rules

When I attempt to create a PacketFilter rule (fill in the form and click Create Rule) nothing happens, it just takes me back to the empty list of Packet Filter rules and I get this in my error log. I noticed that the INSERT statement has ?s for values.

Any thoughts from anyone?

[EDIT]

Is there a way to create rules by inserting SQL directly? If so what values do I need to enter? I’d like one rule to allow a group to send messages to all and then another rule to reject messages from all to all.

2009.09.16 14:12:04 [org.jivesoftware.openfire.plugin.rules.DbRuleManager.addRule(DbRuleManager.jav a:253)
]
java.sql.SQLException: Attempt to insert null into a non-nullable column: column: ID table: OFPFRULES in statement [INSERT INTO ofPfRules(ruleorder,type,tojid,fromjid,rulef,disabled,log,description,sourcetyp e,desttype) VALUES(?,?,?,?,?,?,?,?,?,?)]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.execute(Unknown Source)
at sun.reflect.GeneratedMethodAccessor47.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.Statement$$EnhancerByProxool$$ae0a4a06.execute()
at org.jivesoftware.openfire.plugin.rules.DbRuleManager.addRule(DbRuleManager.java :248)
at org.jivesoftware.openfire.plugin.rules.DbRuleManager.addRule(DbRuleManager.java :207)
at org.jivesoftware.openfire.plugin.rules.RuleManagerProxy.addRule(RuleManagerProx y.java:34)
at org.jivesoftware.openfire.plugin.packetFilter.rule_002dform_jsp._jspService(rul e_002dform_jsp.java:192)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.jivesoftware.openfire.container.PluginServlet.handleJSP(PluginServlet.java: 229)
at org.jivesoftware.openfire.container.PluginServlet.service(PluginServlet.java:87 )
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1093)
at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:70)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:146)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
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.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:66)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingF ilter.java:42)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:70)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:146)
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:726)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollect ion.java:206)
at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:324)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.j ava:829)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:514)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)

Message was edited by: nbrowning

Everything runs smoothly here, with Openfire 3.6.4. Maybe you are using some special characters in rule form, or maybe in the groups names?

wroot,

I’m not entering any special characters, only alpha text, my group names are all normal too, no spaces or special characters.

What I think is the key is this line in my log:

java.sql.SQLException: Attempt to insert null into a non-nullable column: column: ID table: OFPFRULES in statement

Shouldn’t it automatically assign an ID or does HSQLDB not do that like MySQL? Why are there only question marks in my values?

My workaround question is: If I want to create rules by inserting SQL directly what values do I need? For instance, what would a SQL statement for a rule look like that allowed a group named OpenfireSenders to send to All. What about a rule to reject from All to All?

In my case i cant find anything being inserted into ofpfrules table in my openfire.script file (embedded HSQLDB). But rules are preserved after a restart, so they are stored somewhere, but i cant find where. Anyway, even if you’ll be able to find a workaround, something is still wrong with your server.

I’m using HSQL Database Manager so it’s easy for me to query the database. The fields of the ofpfrules table are:

ID RULEORDER TYPE TOJID FROMJID RULEF DISABLED LOG DESCRIPTION SOURCETYPE DESTTYPE

Most are easy to guess, I just don’t know what it’s expecting for TYPE, RULEF, SOURCETYPE, and DESTTYPE. I guessed at some but it didn’t work.

I’ve already tried removing the plug-in from Nate’s instructions and re-installing it cleanly but I get the same thing. It seems that the database doesn’t know that it should auto-increment the ID field. Unless someone has any more ideas I guess I’ll just re-install Openfire completely.

The schema is supposed to look like :

CREATE TABLE ofPfRules (
   id           BIGINT   IDENTITY,
   ruleorder    BIGINT      ,
   type         varchar(255)     ,
   tojid       varchar(255)    ,
   fromjid     varchar(255)    ,
   rulef         varchar(255)   ,
   disabled     boolean,
   log          boolean,
   description  varchar(255),
   sourcetype   varchar(255),
   desttype     varchar(255),
   CONSTRAINT  ofPfRules_pk PRIMARY KEY (id)
);

I did a full re-install and set everything up again and it works now. That’s not very helpful for fixing the problem but I’m up and running now.

Glad you’re up and running. Let me know if you run into any issues or have any questions.