[bug] unable to upgrade to 3.6.x from 3.5.x

[JM-1291] (svn revision 10158) renamed database tables, and XMPPServer.verifyDataSource() now looks for a table named ofID instead of jiveID. When upgrading from a version which has database version < 19, the table will be named jiveID and the upgrade will fail.

patch:

XMPPServer.verifyDataSource() should change

PreparedStatement stmt = conn.prepareStatement("SELECT count(*) FROM ofID");

to

PreparedStatement stmt = conn.prepareStatement("SELECT 1");

Hi,

I filed OF-73 for this

thanks!

daryl

Hi Matt,

thanks for your bug report. I have two problems with it:

  • First, I can’t reproduce your bug. Could you post more debug output or at least the used DBMS? I can’t understand why your patch should fix such a bug. I think Openfire first checks the database schema (org.jivesoftware.database.SchemaManager) and updates it if it’s necessary. So the table JiveID should already be renamed to ofID.
  • Your SQL query “SELECT 1” isn’t compatible with all supported DBMS. At least with the embedded DB it doesn’t work.
    Regards

Guenther,

My appologies on commiting the incomplete patch. Please revert it for in in SVN, as I am away from the interwebs at the moment.

When I looked into this, I thought I saw the bug Matt was suggesting as Openfire first tests the database connection information found in the configuration file before attempting migration.

I’ll more fully look into this as well.

sorry,

daryl

Hi Daryl,

no problem, I’ve reverted the patch. I think maybe there are race conditions but I think it will be good to have a more precisely look into this issue.

Guenther

Apologies for such an incomplete bug report. I also misinterpreted the results, so the bug is not as general as I originally thought. It may be isolated to an MS database. I mistakenly started the troubleshooting from the java.sql.SQLException: Invalid object name ‘ofID’. instead of from the java.sql.SQLException: The text, ntext, and image data types are invalid in this subquery or aggregate expression.

Steps to reproduce:

  1. Start from an unzipped 3.5.1 installation on Windows, with MSDE 2000sp4 as the database.

  2. Configure the database provider to use jtds, with sa username and password

  3. Run openfire.exe, confirm that it starts properly, then stop it

  4. Completely replace the 3.5.1 installation with a freshly unzipped 3.6.4 installation, preserving just the config.xml

  5. Run openfire.exe

Results:

In the console window, I get the following error message:

Found old database version 16 for openfire. Upgrading to version 20…

SchemaManager: Failed to execute SQL:

UPDATE mucService SET mucService.subdomain = ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = ‘xmpp.muc.service’ ) WHERE EXISTS ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = ‘xmpp.muc.service’ );

java.sql.SQLException: The text, ntext, and image data types are invalid in this subquery or aggregate expression.

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$$2f7987fe.execute(<generated>)

at org.jivesoftware.database.SchemaManager.executeSQLScript(SchemaManager.java:378 )

at org.jivesoftware.database.SchemaManager.checkSchema(SchemaManager.java:270)

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.<clinit>(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.<init>(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)

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$$fa262d44.executeQuery(<generated>)

at org.jivesoftware.util.JiveProperties.loadProperties(JiveProperties.java:337)

at org.jivesoftware.util.JiveProperties.init(JiveProperties.java:73)

at org.jivesoftware.util.JiveProperties$JivePropertyHolder.<clinit>(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.<init>(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)

Database setup or configuration error: Please verify your database settings and check the logs/error.log file for detailed error messages.

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$$fa262d44.executeQuery(<generated>)

at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:700)

at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:427)

at org.jivesoftware.openfire.XMPPServer.<init>(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)

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.<init>(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$$fa262d44.executeQuery(<generated>)

at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:700)

... 16 more

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.<init>(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.ENrrtoarv iestarting the server. Please check the log files for more information.

MethodAccessorImpl.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$$fa262d44.executeQuery(<generated>)

at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:700)

... 16 more

Server halted

This might be caused by a missing cast. I do not have access to a mssql database. Could someone verify that the first query fails, but the second query succeeds please?

Should fail:

UPDATE mucService SET mucService.subdomain =   ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = 'xmpp.muc.service' )
WHERE EXISTS   ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = 'xmpp.muc.service' );

Should succeed:

UPDATE mucService SET mucService.subdomain =   ( SELECT CONVERT(NVARCHAR(255),jiveProperty.propValue) FROM jiveProperty WHERE jiveProperty.name = 'xmpp.muc.service' )
WHERE EXISTS   ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = 'xmpp.muc.service' );

Message was edited by: Guus der Kinderen (corrected last query)

Hi Guus,

I’ve implemented such a test environment, applied your changes and got the attached error log.
error.log.zip (1300 Bytes)

I’ve played a little bit more on that issue and commited a fix which is based on Guus’ idea.

The attached patch fixes the problem for me.
mssqlupgrade.patch.zip (575 Bytes)

Sorry to confuse you. I didn’t intend to fix the issue with the SQL statement that I provided - I just wanted to check if that construct worked. As you’ve found, there was at least one other place where a similar fix was needed. I didn’t investigate further.

Thanks for creating the patch and fixing the issue. We’re getting closer and closer to a new release every day.