Updating xMatters 5.0 (patch 006 or earlier) fails on SQL Server deployments

Issue Summary

Some customers have reported encountering an error message and upgrade failure when attempting to apply xMatters 5.0 patch 007 or patch 008 to an xMatters 5.0 deployment prior to patch 007 using SQL Server.

Issue Details

When attempting to apply the database patches, the following error is logged in the AlarmPoint.txt log file:

ERROR - - Unable to update database

com.invoqsystems.apex.exceptions.AlarmPointDatabaseException: java.sql.SQLException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'xmatters5.AUDIT_EVS_ALL_ARC' and the index name 'AUDIT_EV_ARC_INSERT_ORDER'. The duplicate key value is ().

This error occurs because prior to patch 007, the AUDIT_EVS_ALL_ARC.insert_order column did not have a unique constraint applied to it, and was not included in the archive trigger. xMatters 5.0 patch 007 and patch 008 attempt to add a unique constraint to the column; this operation fails if the table contains any NULL data.

Workaround

You can work around this issue and successfully patch your system by fixing any current data before applying the patch.There are two options available to fix your data:

  • You can purge all runtime and archive data from your system. If there is no system data, the patch can be applied successfully as there  will be no duplication to cause the error. Note that this will remove all reporting and performance data from your system for Event Reports, Audit Reports, Performance Reports, etc. (For more information about purging your archive and runtime data, refer to the xMatters installation and administration guide.
  • If you are uncomfortable with removing your existing data, you can use the steps below to create, modify, and update the affected column in the database.

To fix the database data:

Step One

Run the following SQL statement to determine whether the insert_order column exists in the AUDIT_EVS_ALL_ARC table on your system:

SELECT 1 FROM information_schema.columns WHERE table_name = 'AUDIT_EVS_ALL_ARC' AND column_name = 'INSERT_ORDER'  

If the query does NOT return a result, skip to Step Three. If the query returns a result, proceed to Step Two.

Step Two

Run the following query to add the insert_order column to your AUDIT_EVS_ALL_ARC table: 

ALTER TABLE audit_evs_all_arc ADD insert_order NUMERIC(38)   

Step Three

Run the following query to update the insert_order column with unique data:

UPDATE audit_evs_all_arc  
   SET insert_order = audit_ev_id  
WHERE insert_order IN (SELECT insert_order FROM audit_evs_all_arc GROUP BY insert_order HAVING COUNT(*) > 1)  

Ensure that you commit the change to the database, and then run the patch installer.

Further information

This issue will be addressed in an upcoming patch; the fix is currently targeted for 5.0 patch 9.

xMatters 5.0 patch 007 Release Notes

xMatters 5.0 patch 008 Release Notes

xMatters Reference

DTN-3251, SUP-7161, JDN-4082

Originally created by Don Clark

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk