After upgrading our corporate Blackberry Enterprise Server (BES) from 5.0 to 5.0SP1, all of our users started complaining that most if not all of their contacts were now missing.
From the Web Desktop Manager, and choosing the “Redirection Folders” section (where you configure which folders are redirected), the following error message was displayed at the top.
The personal redirection folders are unavailable. Contact your system administrator.
The Desktop Manager did not show any errors but also would not allow me to select any folders or my default private contact folder.
A glance through some of the BES logs showed messages like:
javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.SQLGrammarException: could not insert: [com.rim.bes.basplugin.synchronization.entity.Sync
[org.hibernate.util.JDBCExceptionReporter] [ERROR] Cannot insert duplicate key row in object 'dbo.SyncFolderList' with unique index 'IX_SyncFolderList_GUIDLoc'.
I happen to be a DBA, so I decided to dig right in.
First and foremost, before you perform any type of manual database manipulation against your BESMgmt database, TAKE A FULL BACKUP. Did I mention you should take a FULL BACKUP? Did you take a FULL backup? Ok good. Also, if you are not familiar with SQL or simply don’t want to risk bringing down your environment, I recommend you contact RIM support.
There are two database tables that control the bulk of this. [dbo].[SyncFolderUserList] and [dbo].[SyncFolderList]. The [dbo].[UserConfig] table also contains all of the users, and you will need to reference it for this operation.
First, I looked up my user id from the UserConfig table.
SELECT * FROM [dbo].[UserConfig]. I’m ID 47.
I then looked up my folder list from the SyncFolderUserList table.
SELECT * FROM [dbo].[SyncFolderUserList]
WHERE [UserConfigId] = 47
This yielded two records for me. Note the data in the SyncFolderListId table. For my scenario, I had two records, one with ID 60 and the other 147.
I then queried the SyncFolderList table for those rows.
SELECT * FROM [dbo].[SyncFolderList]
WHERE [Id] IN (60, 147)
While I was expecting two entries, to my surprise there was only one entry. Bingo, a rogue entry!
Seeing how it was only two folders, rather than deleting the rogue one and seeing if it worked, I opted to delete all of my data from both the SyncFolderList and the SyncFolderUserList tables.
To my expectation, I was now able to select the folders again from either Web Desktop Manager or the Desktop Manager.
As a number of other users in our environment were experiencing the same issue, I decided to delete these tables all together. All users reported they were now able to select folders to sync, and upon selecting them, their devices properly synchronized.