"AHD04115: miscellaneous database error--look at log" errors when adding new entries in parts of Service Desk (i.e. adding new members to a group). What is causing this?
Article ID: 54457
Updated On:
Products
Issue/Introduction
Description:
When the above error appears, the following error is seen the stdlog
SQL Execute failed: [Microsoft OLE DB Provider for SQL Server] [ SQL Code=3621 SQL State=01000] The statement has been terminated.; [Microsoft OLE DB Provider for SQL Server] [ SQL Code=2627 SQL State=23000] Violation of PRIMARY KEY constraint 'XPKxxxxxx'. Cannot insert duplicate key in object 'xxxxxx'.
In a specific case, I was adding a new member to a group, so the "xxxxxx" in the stdlog error message was "grpmem".
Immediately before this error started happening, we did a bulk load into this database table to automate the addition of lots of users to groups. This was done through DBMS tools, rather than Service Desk.
Environment
Release:
Service Desk Manager : All supported versions
Resolution
Solution:
A lot of database tables relating to Service Desk have an "id" field, which is incremented by one whenever a new entry is added to the table. In the above case, each time a new member is added to a group, a new entry is written to the grpmem table, including the UUID of the contact to be added and the UUID of the group. Each record has a unique "id". This counter is held internally in Service Desk.
If entries have been added to a database table which uses an incremental "id" field (e.g. the grpmem table) using DBMS utilities outside of Service Desk, the internal counter for the "id" field contains the last value which was added through Service Desk. This value may already have been used by the entries which were added outside Service Desk. This means that the addition of the new record causes an error because the "id" for the new record already exists in the database table.
Because of this problem, CA recommend that you only use Service Desk utilities (pdm_load) to add entries into Service Desk database tables such as the grpmem table.
If you find this problem, the solution is to re-sync the internal counter which Service Desk uses, to the data which exists in the database. The easiest way to do this is:
- Make sure that you have a backup of the MDB database.
- Extract all of the entries for the database table in question:
- pdm_extract Group_Member > grpmem_current.txt
- Delete all entries from the database table:
- pdm_load -rvf grpmem_current.txt
- Re-insert all of the database table entries:
- pdm_load -ivf grpmem_current.txt
This will cause Service Desk to re-load all of the database records, and to subsequently reset the counter used for the "id" field to the highest current value.
Feedback
