Tuesday, 22 June 2010

Ektron: Fix for Primary Key Constraint Error When Creating A User

We’ve recently had an issue with creating users in our Ektron 8.0.1 installation where we could no longer create users.  The error we got was:

Error cms_addUser: Error[cms_SetupDefaultNotificationPreferencesForUser]:

Violation of PRIMARY KEY constraint 'PK_notification_preference_default'.

Cannot insert duplicate key in object 'dbo.notification_preference_default'.


Scary Stuff.

Still, the Ektron provided fixes are simple enough (although by no means intuitive!):

Simple Fix

  1. Restart Ektron Windows Services
  2. Perform a full eSync (from Synchronisation screen)

Complex Fix

If the above doesn’t work try this:

  1. Backup database…just incase
  2. Remove any entries from [perform_action] table with an action_priority of –8
  3. Get the maximum PreferenceID from [notification_preferences_default]
    SELECT MAX(PreferenceID) FROM [notification_preferences_default]
  4. If the [max_table_number] value in [max_entries] table for [table_name] notification_preference_default is less than the maximum preference id then update it to the maximum value plus two.

To save any potential issues with this, I’ve written a quick SQL script:

   3: /* Rows that need deleting */
   4: SELECT * FROM [perform_action] WHERE [Action_priority]=-8
   6: DELETE FROM [perform_action] WHERE [Action_priority]=-8
   8: DECLARE @MaxPreferenceId BIGINT
  10: SELECT @MaxPreferenceId = MAX([PreferenceId])
  11: FROM [dbo].[notification_preference_default]
  13: /* Before! */
  14: SELECT [table_name], [max_table_number], @MaxPreferenceId AS MaxValue
  15: FROM [max_entries]
  16: WHERE [table_name]='notification_preference_default'
  18: UPDATE [max_entries] SET [max_table_number] = @MaxPreferenceId + 2
  19: WHERE [table_name]='notification_preference_default' AND [max_table_number]<@MaxPreferenceId
  21: /* After! */
  22: SELECT [table_name], [max_table_number], @MaxPreferenceId AS MaxValue
  23: FROM [max_entries]
  24: WHERE [table_name]='notification_preference_default'
  26: /*Change To COMMIT TRANSACTION when happy */  

NOTE: All the changes are performed in a transaction (which is rolled back), you’ll need to change the script so that it Commit the results when you’re happy.

1 comment:

  1. Thanks for posting this. Ektron support has mentioned that they have an update to one of the user create stored procs if this is a consistent problem. The fix should hopefully be in whatever release comes after 8.0.2.


Got something to say? Let it out then!
Comments are moderated, so it may take a while to for them to be displayed here!