powered by Jive Software

Cannot delete or edit PacketFilter rules using PostgreSQL as DB

Hi folks,

I’m using Openfire with a PostgreSQL 8.4.0 database as a messaging server for a Call Center. Everything was going fine when I added some PacketFilter rules but if I tried to edit or delete them the actions never complete, because of this errors:

  • When I try to Edit:

**2009.09.24 14:39:14 [org.jivesoftware.openfire.plugin.rules.DbRuleManager.updateRule(DbRuleManager. java:405)] **
**org.postgresql.util.PSQLException: ERROR: la columna «disabled» es de tipo boolean pero la expresión es de tipo ****smallint
Hint: Necesitará reescribir la expresión o aplicarle una conversión de tipo.
Position: 68 **
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl .java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java: 1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java :479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2State ment.java:367)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statemen t.java:321)
at sun.reflect.GeneratedMethodAccessor42.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 org.postgresql.PGStatement$$EnhancerByProxool$$6b3840b8.executeUpdate()
at org.jivesoftware.openfire.plugin.rules.DbRuleManager.updateRule(DbRuleManager.j ava:400)
at org.jivesoftware.openfire.plugin.rules.DbRuleManager.updateRule(DbRuleManager.j ava:370)
at org.jivesoftware.openfire.plugin.rules.RuleManagerProxy.updateRule(RuleManagerP roxy.java:73)
at org.jivesoftware.openfire.plugin.packetFilter.rule_002dedit_002dform_jsp._jspSe rvice(rule_002dedit_002dform_jsp.java:268)
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)

When I try to delete:

**
**

**2009.09.24 14:38:55 [org.jivesoftware.openfire.plugin.rules.DbRuleManager.deleteRule(DbRuleManager. java:278)]
org.postgresql.util.PSQLException: ERROR: el operador no existe: integer = character varying
Hint: Ningún operador coincide con el nombre y el tipo de los argumentos. Puede desear agregar conversiones explícitas de tipos.
Position: 31 **
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl .java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java: 1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java :479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2State ment.java:367)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java :360)
at sun.reflect.GeneratedMethodAccessor52.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 org.postgresql.PGStatement$$EnhancerByProxool$$6b3840b8.execute()
at org.jivesoftware.openfire.plugin.rules.DbRuleManager.deleteRule(DbRuleManager.j ava:273)
at org.jivesoftware.openfire.plugin.rules.RuleManagerProxy.deleteRule(RuleManagerP roxy.java:40)
at org.jivesoftware.openfire.plugin.packetFilter.delete_002drule_jsp._jspService(d elete_002drule_jsp.java:83)
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)

Seems like the methods that generate the queries need some fixing.

Thanks!

Hi,

Which version of PostgreSQL are you using?

daryl

I’m using PostgreSQL 8.4.0 with the latest Openfire 3.6.4 and latest PacketFilter 2.0.2 plugin. To make Openfire work with this version of postgres, I’ve replaced the bundled PostgreSQL JDBC that came with Openfire, with the latest JDBC that is compatible with the PostgreSQL 8.4 database. But finally the errors are not related to the version but to the types of some fields and the queries that adds, updates or deletes the rules in the table.

What field type do you have for the disabled field? If its an int I would try chaning it to a boolean. If its a boolean try and int I remeber having some issues with how various combos of dbs and jdbc drivers handle this kind of thing.

Cheers,

Nate

Same problem here with pgsql. I have to edit/remov my rules with sql and restart openfire to apply changes…