Cannot create Packet Filter rules

Openfire Plugins Packet Filter
1

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

2

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?

3

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?

4

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.

5

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.

6

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)
);
7

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.

8

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