Upgrade from 3.5.2 to 3.6.2 oracle table problem

I finally upgraded from 3.5.2 to 3.6.2 with oracle 10g as the db after following Daniel’s advice. I now am getting these errors, which seem to be affecting the MUC:

2009.01.03 20:44:44 [org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.createPublishedItem( PubSubPersistenceManager.java:1029)]

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

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatemen t.java:3368)

at sun.reflect.GeneratedMethodAccessor10.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja va:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

at $java.sql.PreparedStatement$$EnhancerByProxool$$638aade7.executeUpdate()

at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.createPublishedItem(P ubSubPersistenceManager.java:1024)

at org.jivesoftware.openfire.pubsub.PublishedItemTask.run(PublishedItemTask.java:7 2)

at java.util.TimerThread.mainLoop(Timer.java:512)

at java.util.TimerThread.run(Timer.java:462)

I can no longer connect to our MUC and when I try to create a new service from the admin frontend, I get:

Exception:

java.lang.AbstractMethodError at org.jivesoftware.openfire.component.InternalComponentManager.notifyComponentRegistered(InternalComponentManager.java:178) at org.jivesoftware.openfire.component.InternalComponentManager.addComponent(InternalComponentManager.java:143) at org.jivesoftware.openfire.muc.MultiUserChatManager.registerMultiUserChatService(MultiUserChatManager.java:131) at org.jivesoftware.openfire.muc.MultiUserChatManager.createMultiUserChatService(MultiUserChatManager.java:191) at org.jivesoftware.openfire.admin.muc_002dservice_002dedit_002dform_jsp._jspService(muc_002dservice_002dedit_002dform_jsp.java:115) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97) 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.java:1093) at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:118) at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:52) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:66) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:42) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:70) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:146) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java: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(ContextHandlerCollection.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.content(HttpConnection.java:843) at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:648) 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)

I took a look at the sql db upgrade scripts, and 17 and 19 seem to muck about with pubsub and muc tables pretty heavily. Unfortunately, the errors came so fast and furious that the initial error.log was aged out just 20 minutes or so after bringing the server back up

Any advice as to what I can do to create the tables? Everything else seems to work fine…

Thanks!

Hi,

do you need the old pubsub tables and contents?

If you do not need the data you can create the table with

“CREATE TABLE ofPubsubItem (
serviceID VARCHAR2(100) NOT NULL,
nodeID VARCHAR2(100) NOT NULL,
id VARCHAR2(100) NOT NULL,
jid VARCHAR2(1024) NOT NULL,
creationDate CHAR(15) NOT NULL,
payload VARCHAR(4000) NULL,
CONSTRAINT ofPubsubItem_pk PRIMARY KEY (serviceID, nodeID, id)
);” as this seems to be the missing table.

Take a look at resources/database/openfire_oracle.sql - all the tables and indexes in it should be in your database.

When everything runs fine again you may want to delete the “jive…” tables if there are still some in your Openfire schema.

LG

OK, I just looked at ofversion and found:

openfire 18
monitoring 0
gateway 8
enterprise 10

so it seems that the upgrade got messed up at 18. I also have no ofpubsub* tables, just a bunch of pubsub* tables. I see that all the renaming of the tables from pubsub* to ofpubsub* happened in 19 I will try running the 19 and 20 sql upgrade scripts and seeing what happens…

Thanks!