Fixing missing email addresses in the Connections SQL database

Posted on by

Issue:
A user can’t change her email preferences. She gets an error if she tries to change her personal settings

Explanation:
Every application has it’s own user table which uses the email address, next to the user’s GUID, to identify users. These are supposed to be synced from the Profile table. However, earlier versions of the Connections TDISOL assemblyline contained a bug which would cause users to have a missing email address in these tables. This would lead to problems for the user. This was supposed to be fixed in the CR3 release of Connections 5.5 (LO92040) and CR1 release of Connections 6.0 (LO93316) with a separate fix containing a new TDISOL assemblyline. The idea is that you copy your customisations to this new assemblyline. Problem at my customer however is that the default TDISOL line has been customised that much that it would take days to copy these customisations in the fixed assembyline. As I didn’t get budget for that, I looked for a temporary solution to fix these problems.

Solution:
Step one is to identify the users who have a problem. I created an SQL query to identify these users:

SELECT [PROF_GUID],[PROF_MAIL],[PROF_DISPLAY_NAME] FROM [PEOPLEDB].[EMPINST].[EMPLOYEE] 
WHERE [PEOPLEDB].[EMPINST].[EMPLOYEE].[PROF_STATE]='0' AND PROF_MAIL LIKE '%@%'
AND (PROF_GUID IN
(SELECT EXID FROM [OPNACT].[ACTIVITIES].[OA_MEMBERPROFILE] WHERE EMAIL is null AND NOT LASTLOGIN is null)
OR PROF_GUID IN
(SELECT DIRECTORY_UUID FROM [SNCOMM].[SNCOMM].[MEMBERPROFILE] WHERE EMAIL is null)
OR PROF_GUID IN
(SELECT DIRECTORY_ID FROM [WIKIS].[WIKIS].[USER] WHERE EMAIL='')
OR PROF_GUID IN
(SELECT extid FROM [BLOGS].[BLOGS].[rolleruser] WHERE emailaddress='')
OR PROF_GUID IN
(SELECT EXID FROM [FORUM].[FORUM].[DF_MEMBERPROFILE] WHERE EMAIL=' ' OR EMAIL is null)
OR PROF_GUID IN
(SELECT DIRECTORY_ID FROM [FILES].[FILES].[USER] WHERE EMAIL=' ')
OR PROF_GUID IN
(SELECT EXID FROM [HOMEPAGE].[HOMEPAGE].[PERSON] WHERE USER_MAIL is null))

Let’s have a look at the query. I start with grabbing the GUID, Email address and Display Name of the users. There’s a reason I take exactly these field as I need exactly those fields in the next step. I select all users with an active profile (PROF_STATE=0) and an email address. Then I limit if further to include only those users who have a missing email address in Activities, Communities, Wikis, Blogs, Forums, Files or the Homepage table. To give an idea of the problem. The number of rows I get back at this customer is between 5 and 10% of the total amount of active users.

Now we know the users who are affected. The next step is to solve the problem. I’ve found that the tip provided in the original APAR,  ProfilesService.publishUserData(String user_email_addr), doesn’t always do the trick. A safer way is to deactivate and reactivate the user. Then you get these commands:

ProfilesService.inactivateUser(<emailaddress>)
ProfilesService.activateUserByUserId(<GUID>,email=<emailaddress>, displayName=<displayName>)
ProfilesService.publishUserData(<emailaddress>)

This pushes the email addresses from the profiles database to the other databases. I’ve created a scheduled job in SQL to run he query and save the results to a csv file. This csv file is then copied to the deployment manager where it’s picked up by a scheduled jython script that does the deactivate and reactivation. Obviously it’s much preferable to just have a correct assemblyline, but as a workaround it works.