Urgent - Updating SQL database from 3.1.1 to 3.3.1

Openfire Openfire Support
1

Hi All,

We need to update our installation of Wildfire Enterpise 3.1.1 to Openfire Enterprise 3.3.1 ASAP.

We have built a new server for this, but want to keep the existing database info intact on the new server.

We are using Microsoft SQL 2005 for our DB.

I have tried copying the 3.1.1 DB to the new server, linking up an install of 3.3.1 to this DB & starting the server to perform the update to the DB.

The Openfire DB seems to update successfully, however the Enterprise DB fails.

I have had a look online for upgrade guides, and have found one that says Wildfire Enterprise should be at version 3.2 before updating to Openfire. I cannot find a download for Wildfire Enterprise 3.2 anymore, so i can’'t update our current DB for that version first…

Can you list the steps required to manually update our 3.1.1 SQL DB to 3.3.1?

Thanks

Jason

2

Jason,

You are right that you need to upgrade to WF 3.2.3 first. You can get it here:

http://www.igniterealtime.org/downloads/download-landing.jsp?file=wildfire/wildf ire_3_2_3.exe

Also, there are instructions for the manual steps you need to take to get from 3.1.1 to 3.2.3 and from 3.2.3 to 3.3.x.

You can find them here:

http://wiki.igniterealtime.org/display/WILDFIRE/WildfireUpgradeGuide

http://wiki.igniterealtime.org/display/WILDFIRE/WildfiretoOpenfireUpgradeGuide

Alternatively, you could do a fresh installation of 3.3.0 and manually migrate your data to the appropriate tables in the new instance (I wouldn’'t recommend this as this approach is not supported however).

The easiest approach is to just do a fresh install of 3.3.x if at all possible.

Hope this helps!

Kevin

3

Unfortunately we can’'t use a fresh 3.3.x install/DB as we need to keep our previous Fastpath transcriptions easily accessable for record keeping sake.

Does that download you have linked to include the 3.2 enterprise plugin?

Thanks

Jason

4

Managed to download Wildfire 3.2.4, and Enterprise Plugin 3.2.4…

Will try firing up 3.2.4 first to see what happens.

Another question…

Will our current webchat.war (running on external server under Resin) from 3.1.1 work with the 3.3.1 server version? Or will that need to be updated also?

If it needs to be updated, means more work as we have some customisation done do the files on webchat. doh…

5

Still having trouble…

When updating to Enterprise 3.2.4 im getting the following error logs…

On first startup of the 3.2.4 server:

2007.06.15 12:10:11 [org.jivesoftware.database.SchemaManager.checkSchema(SchemaManager.java:249)

]

java.sql.SQLException: There is already an object named ‘‘jive_liveGroupQueue_pk’’ in the database.

at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)

at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)

at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)

at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)

at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:664)

at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1114)

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

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

at org.jivesoftware.database.SchemaManager.checkPluginSchema(SchemaManager.java:10 9)

at org.jivesoftware.wildfire.container.PluginManager.loadPlugin(PluginManager.java :378)

at org.jivesoftware.wildfire.container.PluginManager.access$200(PluginManager.java :46)

at org.jivesoftware.wildfire.container.PluginManager$PluginMonitor.run(PluginManag er.java:920)

at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.util.concurrent.FutureTask$Sync.innerRunAndReset(Unknown Source)

at java.util.concurrent.FutureTask.runAndReset(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101 (Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodi c(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknow n Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

2007.06.15 12:10:11 [org.jivesoftware.wildfire.container.PluginManager.loadPlugin(PluginManager.jav a:380)

] enterprise - Database update failed. Please manually upgrade your database.

And on any subsequent startups of the server i get this now:

2007.06.15 12:25:20 [org.jivesoftware.database.SchemaManager.checkSchema(SchemaManager.java:249)

]

java.sql.SQLException: There is already an object named ‘‘fpWorkgroup’’ in the database.

at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)

at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)

at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)

at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)

at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:664)

at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1114)

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

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

at org.jivesoftware.database.SchemaManager.checkPluginSchema(SchemaManager.java:10 9)

at org.jivesoftware.wildfire.container.PluginManager.loadPlugin(PluginManager.java :378)

at org.jivesoftware.wildfire.container.PluginManager.access$200(PluginManager.java :46)

at org.jivesoftware.wildfire.container.PluginManager$PluginMonitor.run(PluginManag er.java:920)

at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.util.concurrent.FutureTask$Sync.innerRunAndReset(Unknown Source)

at java.util.concurrent.FutureTask.runAndReset(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101 (Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodi c(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknow n Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

2007.06.15 12:25:20 [org.jivesoftware.wildfire.container.PluginManager.loadPlugin(PluginManager.jav a:380)

] enterprise - Database update failed. Please manually upgrade your database.

Message was edited by: JayMan

6

I think i have found the problem…

In the SQL db scripts is the following:

CREATE TABLE fpQueueAgent (

queueID INT NOT NULL,

objectID INT NOT NULL,

objectType INT NOT NULL,

administrator INT NULL,

CONSTRAINT jive_liveGroupQueue_pk PRIMARY KEY (queueID,objectID,objectType)

);

CREATE TABLE fpQueueGroup (

queueID INT NOT NULL,

groupName NVARCHAR(50) NOT NULL,

CONSTRAINT jive_fpQueueAgent_pk PRIMARY KEY (queueID,groupName)

);

I don’'t know how it should be… But “CONSTRAINT jive_liveGroupQueue_pk blah” under table fqQueueAgent just seems wrong… As does “CONSTRAINT jive_fpQueueAgent_pk blah” under table fqQueueGroup…

I’‘m guessing they should be “CONSTRAINT fpQueueAgent_pk blah” & “CONSTRAINT fpQueueGroup_pk blah” respectively… But i’'m neither an SQL or Java expert lol

The import script doesn’'t work for me either, appears to be a problem with the REPLACE command in the following:

INSERT INTO fpWorkgroupProp

(ownerID, name, propValue)

SELECT ownerID, name, REPLACE(propValue, ‘’.liveassistant.’’, ‘’.wildfire.fastpath.’’)

FROM jlaWorkgroupProp;

INSERT INTO fpAgentProp

(ownerID, name, propValue)

SELECT ownerID, name, REPLACE(propValue, ‘’.liveassistant.’’, ‘’.wildfire.fastpath.’’)

FROM jlaAgentProp;

Can you please check all the SQL (new, update & import) scripts for the Enterprise plugin & supply any corrections needed.

7

Bump… Still have an outstanding issue with this…

Has anybody got some feedback?

Thanks

Jason

8

From the stacktrace, the system is trying to install the plugin, and update the database, but is failing due to the tables already being there. I would check your database instance against what the scripts have. If they are the same, then you should be ok.

9

When launching Openfire, it starts to apply the scripts, but fails… It actually adds the first entries, but doesn’'t complete… Hence the different error from the 1st time launched (There is already an object named ‘‘jive_liveGroupQueue_pk’’ in the database… this primary key is already used in the pre 3.2 fastpath tables) & subsequent launches (There is already an object named ‘‘fpWorkgroup’’ in the database… this is because the update 4 script has partially been applied, but stopped when it encountered the 1st error about the primary key).

To go back over what my previous posts have said… the SQL script for enterprise update 4… and also the full SQL script look like they contain the same errors…

CREATE TABLE fpQueueAgent (

queueID INT NOT NULL,

objectID INT NOT NULL,

objectType INT NOT NULL,

administrator INT NULL,

CONSTRAINT jive_liveGroupQueue_pk PRIMARY KEY (queueID,objectID,objectType)

);

jive_liveGroupQueue_pk is an OLD primary key (from when things were still labelled as liveassistant), so you can’'t have a duplicate, and it also looks like its named wrong… (GroupQueue PK in the QueueAgent table??) … Looking at the rest of the script… I would assume this is meant to be fpQueueAgent_pk

The script fails at this point (all items before this are in the DB, but everything after is missing)…

There appears to be a 2nd error in the update 4 & full script files, which is this:

CREATE TABLE fpQueueGroup (

queueID INT NOT NULL,

groupName NVARCHAR(50) NOT NULL,

CONSTRAINT jive_fpQueueAgent_pk PRIMARY KEY (queueID,groupName)

);

Again… wrong naming for that table??? (QueueAgent PK in QueueGroup table) and also its got the ‘‘jive_’’ on it, which no other ‘‘new’’ key has (except the previous error). I presume this primary key should actually be fpQueueGroup_pk

Both of these error appear in the SQL script in the update 4 folder… and in the full SQL creation script…

Once the correct scripting for the update is sorted out… There still appears to be an issue with the import script (from the update 4 folder)… But for now, i’‘ll be happy getting the update working first… Once thats done successfully, i’'ll try the import again.

Thanks

Jason

10

Bump…

Any updates to this?

Thanks

Jason