powered by Jive Software

Upgrade Openfire 4.6 ofVersion 31 SQL Error

SQL Server 2012
Upgrade Openfire to 4.6.1 with ofVersion for openfire less than 31.
distribution/src/database/upgrade/31/openfire_sqlserver.sql

When openfire starts and attempts to run the upgrade scripts, when it applies the script for version 31, the following error occurs:

2021.02.04 09:34:32 ERROR [main]: org.jivesoftware.database.SchemaManager - SchemaManager: Failed to execute SQL:
    provided that the maxPayloadSize still is at what Openfire used by default up to this point (5120).    OF-2062: Increase the maxPayloadSize in the default configuration for leaf nodes of PEP services (those with a serviceID that contain the '@' symbol,    which indicates that the serviceID matches a JID), provided that the maxPayloadSize still is at what Openfire used by default up to this point (5120). */ UPDATE ofPubsubDefaultConf SET persistItems = 1, maxPayloadSize = 10485760 WHERE serviceID LIKE '%@%' AND leaf = 1 AND maxPayloadSize = 5120;
2021.02.04 09:34:32 ERROR [main]: org.jivesoftware.database.SchemaManager - Incorrect syntax near 'the'.
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'the'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2935) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:744) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175) ~[commons-dbcp2-2.6.0.jar:2.6.0]
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175) ~[commons-dbcp2-2.6.0.jar:2.6.0]
	at org.jivesoftware.database.SchemaManager.executeSQLScript(SchemaManager.java:388) ~[xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.database.SchemaManager.checkSchema(SchemaManager.java:285) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.database.SchemaManager.checkOpenfireSchema(SchemaManager.java:85) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectionManager.java:658) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.database.DbConnectionManager.ensureConnectionProvider(DbConnectionManager.java:101) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:162) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.util.JiveProperties.loadProperties(JiveProperties.java:438) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.util.JiveProperties.init(JiveProperties.java:90) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.util.JiveProperties.getInstance(JiveProperties.java:66) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.util.JiveGlobals.getProperty(JiveGlobals.java:557) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.util.cache.CacheFactory.<clinit>(CacheFactory.java:105) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.openfire.XMPPServer.initialize(XMPPServer.java:431) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:710) [xmppserver-4.6.1.jar:4.6.1]
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:273) [xmppserver-4.6.1.jar:4.6.1]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_202]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) [?:1.8.0_202]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) [?:1.8.0_202]
	at java.lang.reflect.Constructor.newInstance(Unknown Source) [?:1.8.0_202]
	at java.lang.Class.newInstance(Unknown Source) [?:1.8.0_202]
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:92) [startup.jar:4.6.1]
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:56) [startup.jar:4.6.1]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_202]
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_202]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_202]
	at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.8.0_202]
	at com.exe4j.runtime.LauncherEngine.launch(LauncherEngine.java:65) [i4jruntime.jar:?]
	at com.exe4j.runtime.WinLauncher$2.run(WinLauncher.java:96) [i4jruntime.jar:?]

Hi Peter,

Thanks for reporting this. We’re in process of discussing this issue here: https://discourse.igniterealtime.org/t/openfire-upgrade-to-4-6-causes-loss-of-group-chat-rooms

I suggest we focus the discussion around this in that thread.

For future reference, we’re tracking this issue as https://igniterealtime.atlassian.net/browse/OF-2197