Lessons Learned IBM Connections Upgrade 3.0.1 to 4.5

by Michael Urspringer - 19.05.2014

Here are some topics I learned during my last IBM Connections upgrade project from vesion 3.0.1 to 4.5.

Several errors while running the data migration

 

While running the homepage migration as decribed here we got several error messages. Below are some hints to solve them.

The database upgrade process from 3.0.1 to 4.5 is a two step approach. First you upgrade the data from version 3.0.1 to 4.0 and afterwards from 4.0 to 4.5. If you do that, make sure that you are using the correct version of the upgrade scripts. This means, use the scripts delivered with Connections 4.0 to upgrade from 3.0.1 to 4.0 and use the scripts provided with Connections 4.5 to upgrade from 4.0 to 4.5. This is important although the directory provided with 4.5 also contains all scripts to migrate from 3.0.1 to 4.0. However,  the provided JAR files and libraries are not working if you migrate from 3.0.1 to 4.0.

Even while using the correct scripts, we got a “null pointer exception” message while running the Homepage data migration from 3.0.1 to 4.0. This problem has been described already in this technote and we fixed it by running the following two SQL commands:

SQL   
-- FIX EMPINST.SNMSGV_ENTRY NULL VALUES
UPDATE EMPINST.SNMSGV_ENTRY SET CONTENT_TEXT = ' ' WHERE CONTENT_TEXT IS NULL;
-- FIX EMPINST.SNMSGV_COMMENT NULL VALUES
UPDATE EMPINST.SNMSGV_COMMENT SET CONTENT_TEXT = ' ' WHERE CONTENT_TEXT IS NULL;

However, afterwards we still got some SQL error messages like:

ORA-01400: cannot insert NULL into (“HOMEPAGE”.”BOARD_ENTRIES”.”CONTENT”)

The solution for that was an updated NEWS.MIGRATE.JAR which fixed the issue of handling NULLs, empty strings and strings with only spaces which causes these errors.You should be able to get that updated file from IBM Support if you open a PMR and ask for it.

 

Emoticons no longer displayed in Wikis

 

After migrating the data from 3.0.1 to 4.5 emoticons,which had been used within Wikis, were no longer displayed. You only get a red cross in the browser stating that the image file cannot be found. The reason for that is that the path, where the emoticon files are stored, has been changed from 3.0.1 to 4.x.

Unfortunately there is no built-in script which you can run to fix that.

So you have two different possibilities to fix it.

First, you can just add a redirect rule in your HTTPD.CONF to rewrite the  rong URL with the correct one. We did that as an immediate workaround. Here is the rule we used:

# Rewrite Rule to fix missing emoticons in wikis after upgrade from 3.0.1
RewriteEngine on
ReWriteRule ^(.*)/ckeditor/plugins/sametimeemoticons/images/(.*)$ /connections/resources/web/com.ibm.oneui.ckeditor/editor/plugins/sametimeemoticons/images/$2 [R,L]

Second, and that it the better solution as it really corrects the wrong data, you can change all references to the wrong URL with the correct one. The Wiki data is stored in the file system and not within the database so you can just replace it with a GREP command (if you are using Linux) or a Powershell command (if you are using Windows). Here is the Powershell command we used to correct the URLs in all Wiki entries:

(gci <shared-drive>\wikis\upload\files -rec | Select-String -SimpleMatch "<?xml version=" -context 0 -encoding UTF8 | % { Select-String -Path $_.path -pattern "server.acme.com/wikis/static/js/20130705.160059/ckeditor/plugins/sametimeemoticons/images/" -List}) | % {$a = ((Get-Content $_.path -encoding UTF8) -replace "server.acme.com/wikis/static/js/20130705.160059/ckeditor/plugins/sametimeemoticons/images/","server.acme.com/connections/resources/web/com.ibm.oneui.ckeditor/editor/plugins/sametimeemoticons/images/"); [System.IO.File]::WriteAllText($_.path, $a); echo $_.path}

You need to change the server name in the command (here: “server.acme.com”) to the name of your Connections server. Make sure you create also a backup of the “<shared-drive>\wikis\upload\files” folder in order something goes wrong!

 

Theme issues with the IBM Connections NextGen theme

 

The customer decided to go with the IBM Connections NextGen theme. How to do that is described in that wiki article. However, if you are using IBM Connections 4.5 CR3 or higher make sure that you do NOT use the JAR file described in that article (from Greenhouse) as it is an older version of the theme.

Starting with CR3 the NextGen theme is part of the Connections installation. You will find the JAR file in “<Connections shared drive>\provision\webresources” (file name: “com.ibm.social.gen4.theme_4.0.0.cr3.jar”). So you should use that one instead.

If you are using the NextGen theme you should make sure that you reset all themes for existing  communities and all blogs, wikis, forums and activities within communities to the default theme. You can do that with the following SQL statement:

SQL   
-- Set all Communities to default theme
UPDATE "SNCOMM"."COMMUNITY" SET COMMUNITY_THEME = 'default';
-- Set all Community blogs to default theme
UPDATE "BLOGS"."WEBSITEASSOC" SET ASSOCTHEME = 'default' WHERE ASSOCTHEME IS NOT NULL;
-- Set all Community wikis to default theme
UPDATE "WIKIS"."LIBRARY" SET THEME_NAME = 'default' WHERE THEME_NAME IS NOT NULL;
-- Set all Community forums to default theme
UPDATE "FORUM"."DF_MEMBERPROFILE" SET THEMEID = 'default' WHERE THEMEID IS NOT NULL;
-- set all Community activities to default theme
UPDATE "ACTIVITIES"."OA_MEMBERPROFILE" SET THEMEID = 'default' WHERE THEMEID IS NOT NULL;

You also should switch back all customized blog themes back to default as they were no longer displayed correctly after the migration. This can be done with the following statement:

SQL   
-- Set all custom blog themes for standalone blogs to default blog theme
UPDATE "BLOGS"."WEBSITE" SET EDITORTHEME = 'blog' WHERE EDITORTHEME='custom';

 

Some user profiles could not be displayed

 

After the migration, some user profiles could no longer be displayed. The reason for that was that the field “PROF_TYPE” in the Profiles database was set to something different than NULL (we found profiles which contained either the string value “(null)” or “active” within that field. This was not a problem with Connections 3.0.1 but 4.5 seems to expect a real “NULL” value here.

We solved that by running the following SQL command:

SQL   
-- Fix Profile Types
UPDATE "EMPINST"."EMPLOYEE" SET PROF_TYPE = NULL;

 

HTTP 409 error while adding a files widget to a community

 

If we tried to add a files widget to a Community this was not possible and we got an HTTP error 409 while doing that. The root cause for that were two constraints which were not set correctly. We are ot 100% sure why these constraints have not been set correctly as we did not see any issues in the migration logs. However this problem could be solved with the following SQL statements:

SQL   
-- Correct wrong constraints after migration (Error 409 while adding files widget to community)
-- Files (first command might throw an error which you can ignore!)
ALTER TABLE "FILES"."COLLECTION" DROP CONSTRAINT "COL_LABEL_UKEY";
DROP INDEX "FILES"."COL_LABEL_UKEY";
ALTER TABLE "FILES"."COLLECTION" ADD CONSTRAINT "COL_LABEL_UKEY" UNIQUE ("TYPE", "LABEL");
-- Wikis (first command might throw an error which you can ignore!)
ALTER TABLE "WIKIS"."COLLECTION" DROP CONSTRAINT "COL_LABEL_UKEY";
DROP INDEX "WIKIS"."COL_LABEL_UKEY";
ALTER TABLE "WIKIS"."COLLECTION" ADD CONSTRAINT "COL_LABEL_UKEY" UNIQUE ("TYPE", "LABEL");

 

In some communities, the media gallery could not be opened

 

This nomally should not happen if you ran the SyncMember commands for all applications  as described here.  It seems that the SyncMember command for Communities did not run until the end (it took a long time, far more than 12h in our environment).  After we ran the command again and made sure it ran until the end, everything worked fine again:

wsadmin   
execfile("communitiesAdmin.py");
CommunitiesMemberService.syncAllMembersByExtId({"updateOnEmailLoginMatch": "true"});

There is also a manual workaround if you have that issue only for a few communities. You just need to add and delete a new owner to a certain community. This wsadmin script should does that from the command line:

wsadmin   
members= ["emailaddress@domain.com"]
CommunitiesService.addMembersToCommunityByEmail("Community Name",1,members)
CommunitiesService.removeMembersFromCommunityByEmail("Community Name",members)

 

SPNEGO problems with HTTP header size

 

The customer is using SPNEGO single-sign-on with Windows and some users were not able to login to Connections. This was true although we already set the maximum HTTP header size to 32k. This normally should be large enough. However it turned out that for this environment this was even too small for some users who were part of a large number of Active Directory groups. So we set the value to a much larger size with the following statement in the HTTPD.CONF (make sure that you add the statement also to a virtual host definition for SSL if you have configured that!):

LimitRequestFieldSize 131072
Tagged with:  

Leave a Reply

© 2000-2015 Michael Urspringer