4.0.3 to 4.2.3 Upgrade Issues (Schema)

[Spacegoose]

I’m trying to upgrade from 4.0.3 to 4.2.3 (on Solaris) and followed the usual (openfire/documentation/upgrade-guide.html) instructions … we’re using Oracle (I think 11g) and LDAP for authorization …

In the 4.0.3 install I had ojdbc6.jar and ojdbc7.jar files within openfire/lib, first I copied just ojdbc7.jar for the 4.2.3 install … and received the error below. I went to download a new ojdbc jar from Oracle for 11g (because our DB was upgraded at some point during our running of 4.0.3), the readme inside the new jdbc jar bundle for 11g said to use ojdbc6.jar for Java 1.8 and 11g, so I tried it again with this new ojdbc6.jar in openfire/lib and received the same errors. I am not sure if this is driver related and am also not currently certain of our Oracle DB version, but 11g is the last known version (searching my emails), it is perhaps higher now.

Saw this:

Openfire 4.2.0 Login Issues? Check your database schema

Update 8 Dec 2017: Openfire 4.2.1 has been released, which should contain a fix that allows Openfire to correctly update its schema at first launch. The workarounds below should no longer be necessary. Please open new discussion threads with any issues you have. We have noticed a number of users report login issues after upgrading to Openfire 4.2.0. Please review this message to see if you are hitting this bug. Openfire Bug OF-1254 Openfire uses a version value set within the o…

but it doesn’t seem to apply …

I was able to revert to 4.0.3 successfully (both times e.g. when trying with original 4.0.3 ojdbc7 and new ojdbc6 jars) by replacing the bits.

2018.06.19 18:10:55 INFO  [main]: org.jivesoftware.openfire.XMPPServer - Registering shutdown hook (standalone mode)
2018.06.19 18:10:56 INFO  [main]: org.logicalcobwebs.proxool.ProxoolFacade - Proxool 0.9.0RC3+ (February 6 2008)
2018.06.19 18:11:04 INFO  [main]: org.jivesoftware.database.SchemaManager - Found old database version 22 for openfire. Upgrading to version 26...
2018.06.19 18:11:05 ERROR [main]: org.jivesoftware.database.SchemaManager - SchemaManager: Failed to execute SQL:
 DROP INDEX IF EXISTS ofMucConvLog_msg_id
2018.06.19 18:11:05 ERROR [main]: org.jivesoftware.database.SchemaManager - ORA-00933: SQL command not properly ended

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:45)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:933)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1718)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1678)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:332)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
        at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
        at $java.sql.Wrapper$$EnhancerByProxool$$86c89640.execute(<generated>)
        at org.jivesoftware.database.SchemaManager.executeSQLScript(SchemaManager.java:383)
        at org.jivesoftware.database.SchemaManager.checkSchema(SchemaManager.java:280)
        at org.jivesoftware.database.SchemaManager.checkOpenfireSchema(SchemaManager.java:83)
        at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectionManager.java:602)
        at org.jivesoftware.database.DbConnectionManager.ensureConnectionProvider(DbConnectionManager.java:95)
        at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:117)
        at org.jivesoftware.util.JiveProperties.loadProperties(JiveProperties.java:413)
        at org.jivesoftware.util.JiveProperties.init(JiveProperties.java:89)
        at org.jivesoftware.util.JiveProperties.getInstance(JiveProperties.java:65)
        at org.jivesoftware.util.JiveGlobals.getProperty(JiveGlobals.java:544)
        at org.jivesoftware.util.cache.CacheFactory.<clinit>(CacheFactory.java:88)
        at org.jivesoftware.openfire.XMPPServer.initialize(XMPPServer.java:311)
        at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:535)
        at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:163)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at java.lang.Class.newInstance(Class.java:442)
        at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:102)
        at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:53)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.exe4j.runtime.LauncherEngine.launch(LauncherEngine.java:81)
        at com.install4j.runtime.launcher.UnixLauncher.main(UnixLauncher.java:65)
2018.06.19 18:11:05 ERROR [main]: org.jivesoftware.util.JiveProperties - ORA-00904: "ENCRYPTED": invalid identifier

java.sql.SQLSyntaxErrorException: ORA-00904: "ENCRYPTED": invalid identifier

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

[gdt]

Have a look in openfire/resources/database/upgrade/25/openfire_oracle.sql; Openfire is trying to run the contents of that file, and for whatever reason Oracle is rejecting it.

If you can fix the contents of that file for Oracle, next time you startup the upgrade should work. I don’t use Oracle locally, so can’t help - however if you can update this thread with the correct SQL we can at least update the file so future users don’t suffer the same problem.

Greg

[Spacegoose]

Thanks Greg, I’ll see if I can find out what the issue is within openfire/resources/database/upgrade/25/openfire_oracle.sql, and post back here if I do.

Meanwhile, if anyone else has already figured this out, please feel free to update this thread.