Upgrade from 3.5.1 to 3.6.0a Fails for SQL Server Error

Openfire Openfire Support
1

I tried a test upgrade for 3.5.1 to 3.6.0a. Have 3.5.1 running on Win 2003 Svr with LDAP (AD) and SQL Server DB. The following is what appeared in the error log:

2008.08.29 09:38:49 [org.jivesoftware.database.SchemaManager.executeSQLScript(SchemaManager.java:37 9)
] SchemaManager: Failed to execute SQL:
ALTER TABLE mucRoom DROP CONSTRAINT mucRoom_pk;
2008.08.29 09:38:49 [org.jivesoftware.database.SchemaManager.checkSchema(SchemaManager.java:269)
]
java.sql.SQLException: ‘mucRoom_pk’ is not a constraint.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1157)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.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$$86324868.execute()
at org.jivesoftware.database.SchemaManager.executeSQLScript(SchemaManager.java:374 )
at org.jivesoftware.database.SchemaManager.checkSchema(SchemaManager.java:266)
at org.jivesoftware.database.SchemaManager.checkOpenfireSchema(SchemaManager.java: 71)
at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectio nManager.java:498)
at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager .java:79)
at org.jivesoftware.util.JiveProperties.loadProperties(JiveProperties.java:335)
at org.jivesoftware.util.JiveProperties.init(JiveProperties.java:73)
at org.jivesoftware.util.JiveProperties$JivePropertyHolder.(JiveProperties .java:40)
at org.jivesoftware.util.JiveProperties.getInstance(JiveProperties.java:52)
at org.jivesoftware.util.JiveGlobals.getProperty(JiveGlobals.java:532)
at org.jivesoftware.openfire.XMPPServer.initialize(XMPPServer.java:298)
at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:415)
at org.jivesoftware.openfire.XMPPServer.(XMPPServer.java:161)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at java.lang.Class.newInstance0(Unknown Source)
at java.lang.Class.newInstance(Unknown Source)
at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:106)
at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.exe4j.runtime.LauncherEngine.launch(Unknown Source)
at com.exe4j.runtime.WinLauncher.main(Unknown Source)
2008.08.29 09:38:49 [org.jivesoftware.util.JiveProperties.loadProperties(JiveProperties.java:346)
]
java.sql.SQLException: Invalid object name ‘ofProperty’.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatem ent.java:777)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.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$$50deedae.executeQuery()
at org.jivesoftware.util.JiveProperties.loadProperties(JiveProperties.java:337)
at org.jivesoftware.util.JiveProperties.init(JiveProperties.java:73)
at org.jivesoftware.util.JiveProperties$JivePropertyHolder.(JiveProperties .java:40)
at org.jivesoftware.util.JiveProperties.getInstance(JiveProperties.java:52)
at org.jivesoftware.util.JiveGlobals.getProperty(JiveGlobals.java:532)
at org.jivesoftware.openfire.XMPPServer.initialize(XMPPServer.java:298)
at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:415)
at org.jivesoftware.openfire.XMPPServer.(XMPPServer.java:161)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at java.lang.Class.newInstance0(Unknown Source)
at java.lang.Class.newInstance(Unknown Source)
at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:106)
at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.exe4j.runtime.LauncherEngine.launch(Unknown Source)
at com.exe4j.runtime.WinLauncher.main(Unknown Source)
2008.08.29 09:38:50 [org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:709)
] Database could not be accessed
java.sql.SQLException: Invalid object name ‘ofID’.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatem ent.java:777)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.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$$50deedae.executeQuery()
at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:700)
at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:427)
at org.jivesoftware.openfire.XMPPServer.(XMPPServer.java:161)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at java.lang.Class.newInstance0(Unknown Source)
at java.lang.Class.newInstance(Unknown Source)
at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:106)
at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.exe4j.runtime.LauncherEngine.launch(Unknown Source)
at com.exe4j.runtime.WinLauncher.main(Unknown Source)
2008.08.29 09:38:50 [org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:457)
]
java.lang.IllegalArgumentException: java.sql.SQLException: Invalid object name ‘ofID’.
at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:710)
at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:427)
at org.jivesoftware.openfire.XMPPServer.(XMPPServer.java:161)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at java.lang.Class.newInstance0(Unknown Source)
at java.lang.Class.newInstance(Unknown Source)
at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:106)
at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.exe4j.runtime.LauncherEngine.launch(Unknown Source)
at com.exe4j.runtime.WinLauncher.main(Unknown Source)
Caused by: java.sql.SQLException: Invalid object name ‘ofID’.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatem ent.java:777)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.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$$50deedae.executeQuery()
at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:700)
… 16 more

Looks like the SQL Server upgrade script needs some work.

2

I found the problem. Whoever built the SQL Server database upgrade scripts has not been consistent in the use of underscores. In some cases for the muc tables, the primary keys have double underscores in their names instead of single underscores like all the rest of the tables. For example mucRoom__pk instead of mucRoom_pk. Also the index for ofRoster referenced a completely different table. The files that need fixing are the version 17 and version 19 upgrade files. I have attached fixed files for these.
openfire_sqlserver_17fix.sql (7551 Bytes)
openfire_sqlserver_19fix.sql (9805 Bytes)

3

I replaced the files in resources\database\upgrade with the ones you made, and I’m still getting the same exact errors as the OP. This is getting pretty ridiculous…

4

Hi,

What version is your database at? select version from jiveVersion where name = ‘openfire’

At this point, it may be a good idea to manually try to apply each script and fix each as we go. Are you comfortable with such an approach? For example, lets run the 17 script manually and fix the bugs as they come first. First up, you can comment out that drop constraint for now to get past that error or try to figure out what the name of the constraint is.

daryl