Oracle database: all tables loaded?

Hi there,

I’‘ve upgraded Wildfire 3.0.1 to OpenFire 3.3.1. We’'re using Oracle10 as our database backend. The process was really simple and the product is just great. After that I decided to give a try to the Enterprise plugin , so I made the install and requested an evaluation license. So far so good.

Now I can see two new tabs in the administration web, one for the Enterprise/Corporate and the other for FastPath. The former seems to work really fine and I’‘ve found no problems up to this point. As for FastPath, I can create workgroups and queues, but whenever I add an agent to a workgroup, the username is never displayed. I know it is a correct user because otherwise there’'s an invalid user message. If I check the error log I can see a message “ORA error: Table or view does not exist”.

I tried to uninstall/reinstall the plugin ,but I find the same problem. I’‘ve been browsing the database creation *.sql files in both /opt/openfire/plugins/enterprise/database/enterprise_oracle.sql and deeper in the upgrade folder but I’'ve been unable to locate the correct script.

Could anyone provide me with some hints for this procedure? Maybe I’'m missing some steps.

Kind regards!

Message was edited by: jcarloscamargo

Can you include a little more of the error log? Normally it includes a table name as well.

Thanks for answering! Of course, this is what I get whenever I try to add an agent to a queue:

(…)

2007.06.07 07:38:51 org.jivesoftware.openfire.component.InternalComponentManager$2.error(InternalCom ponentManager.java:280)

java.sql.SQLException: ORA-00942: table or view does not exist

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 com.jivesoftware.xmpp.workgroup.RequestQueue.addAgentToDb(RequestQueue.java:751 )

at com.jivesoftware.xmpp.workgroup.RequestQueue.addMember(RequestQueue.java:516)

at org.jivesoftware.openfire.plugin.enterprise.fastpath.workgroup_002dqueue_002dag ents_jsp._jspService(workgroup_002dqueue_002dagents_jsp.java:147)

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

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

at org.jivesoftware.openfire.container.PluginServlet.handleJSP(PluginServlet.java: 234)

at org.jivesoftware.openfire.container.PluginServlet.service(PluginServlet.java:92 )

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

at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:491)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1074)

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

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1065)

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

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1065)

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:1065)

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

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1065)

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

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1065)

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

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1065)

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

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1065)

at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:365)

at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:185)

at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)

at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:689)

at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:391)

at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollect ion.java:146)

at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)

at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:139)

at org.mortbay.jetty.Server.handle(Server.java:285)

at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:457)

at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:765 )

at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:627)

at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:209)

at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:357)

at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:329)

at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:475)

I’'m back with the problem solved. The table was fpQueueAgent:

CREATE TABLE fpQueueAgent (

queueID INT NOT NULL,

objectID INT NOT NULL,

objectType INT NOT NULL,

administrator INT NULL,

CONSTRAINT jive_liveGroupQueue_pk PRIMARY KEY (queueID,objectID,objectType)

);

The constraint “jive_liveGroupQueue_pk” existed already on a table that wasnt removed properly from an older installation:

SQL> desc jlagroupqueue;

Nombre ?Nulo? Tipo




QUEUEID NOT NULL NUMBER(38)

OBJECTID NOT NULL NUMBER(38)

OBJECTTYPE NOT NULL NUMBER(38)

ADMINISTRATOR NUMBER(38)

Once this table is deleted, the fpQueueAgent can be created and hence agents get added succesfully.

My excuses for having bothered you and thanks for the guidelines, Kevin.

Regards!