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.
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.
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:
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.
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)
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.
This issue will be addressed in an upcoming patch; the fix is currently targeted for 5.0 patch 9.
DTN-3251, SUP-7161, JDN-4082
Originally created by Don Clark