Download replication error in deletion for table ca_query_def
Article ID: 270807
Updated On:
Products
CA Client Automation - IT Client Manager
CA Client Automation
Issue/Introduction
Donwload Replication Job is in error with message like :
2023-07-31 09:39:43 Table: ca_query_def (43 of 43), History deletion records to process: 29
2023-07-31 09:39:43 Warning: A database constraint violation error occurred for table: ca_query_def
2023-07-31 09:39:43 Table: ca_query_def (43 of 43), History deletion records processed: 11, Elapsed time: 00:00:00
...
2023-07-31 09:40:07 Download - Replication Job Done. Warning: though the job was completed, an error occurred: A database constraint violation error occurred for table: ca_query_def. Please examine the log file for more details
Problem occurs in Download Replication Job in deletion in table ca_query_def
In Engine logs we could see this error :
310723-09:39:43.8903446L|024160|00005560|SystemEngi|CDb |DbImpl_Ado.cpp |000649|INFO | CDb_Ado::ExecSQL() enter - delete from ca_query_def where query_uuid=0x978AF7AC725CD04692E73353E6441EDB and domain_uuid=0x19FD6D6EBCD27042A06C28E8DEAB303E
310723-09:39:43.8917159L|024160|00005560|SystemEngi|DbImplAdo |DbImpl_Ado.cpp |002345|NOTIFY | evalSQLInfo description: The DELETE statement conflicted with the REFERENCE constraint "$ca_gr_r0000089600000000". The conflict occurred in database "mdb", table "dbo.ca_group_def", column 'query_uuid'. source: Microsoft SQL Server Native Client 11.0
310723-09:39:43.8940990L|024160|00005560|SystemEngi|amLog | |000000|ERROR | Fewer records were deleted than expected for table: ca_query_def. Expected to delete: 29, actually deleted: 11
Environment
Client Automation - All Versions.
Cause
An AM query created at enterprise is deleted. Deletion should be replicated to the DOMAINs but it fails because the query is linked to a group.
Resolution
1- Find the query uuid in problem in Engine logs (TRC_<enginename>*.log
Example :
In below example, the query uuid in problem is 0x978AF7AC725CD04692E73353E6441EDB
310723-09:39:43.8923556L|024160|00005560|SystemEngi|CDb |DbImpl_Ado.cpp |002644|NOTIFY | Error Message: ADO Version 10.0 - COM Error: ErrorCode:-2147217873,WordErrorCode:3119, IDispatch error #3119, The DELETE statement conflicted with the REFERENCE constraint "$ca_gr_r0000089600000000". The conflict occurred in database "mdb", table "dbo.ca_group_def", column 'query_uuid'., Microsoft SQL Server Native Client 11.0, (null)
310723-09:39:43.8924885L|024160|00005560|SystemEngi|amLog | |000000|ERROR | GetActionOnError: MS SQL dbErrCat=-3, dbDetailedError=-6, errorInfo=ADO Version 10.0 - COM Error: ErrorCode:-2147217873,WordErrorCode:3119, IDispatch error #3119, The DELETE statement conflicted with the REFERENCE constraint "$ca_gr_r0000089600000000". The conflict occurred in database "mdb", table "dbo.ca_group_def", column 'query_uuid'., Microsoft SQL Server Native Client 11.0, (null)
310723-09:39:43.8927723L|024160|00005560|SystemEngi|amLog | |000000|ERROR | SetIntermediateStatus2: Download: A database constraint violation error occurred for table: ca_query_def
310723-09:39:43.8932647L|024160|00005560|SystemEngi|amLogAndNotify |cAmoLog.h |000387|ERROR | Warning: A database constraint violation error occurred for table: ca_query_def
310723-09:39:43.8932912L|024160|00005560|SystemEngi|amLog | |000000|ERROR | DoTableDelete - Delete on Target DB failed. sql=delete from ca_query_def where query_uuid=0x978AF7AC725CD04692E73353E6441EDB and domain_uuid=0x19FD6D6EBCD27042A06C28E8DEAB303E
310723-09:39:43.8935095L|024160|00005560|SystemEngi|amLog | |000000|ERROR | SetIntermediateStatus1: Download: A database error was encountered
2- With Microsoft SQL Server Management Studio execute this sql query on mdb database to find the name of query and the group names where the query is linked :
SELECT q.label 'Query Name', g.label 'Group Name'
FROM ca_query_def q
JOIN ca_group_def g ON q.query_uuid=g.query_uuid
where q.query_uuid=<uuid found in step 1>
FROM ca_query_def q
JOIN ca_group_def g ON q.query_uuid=g.query_uuid
where q.query_uuid=<uuid found in step 1>
Example :
SELECT q.label 'Query Name', g.label 'Group Name'
FROM P_DM_ca_query_def q
JOIN P_DM_ca_group_def g ON q.query_uuid=g.query_uuid
where q.query_uuid=0x978AF7AC725CD04692E73353E6441EDB
FROM P_DM_ca_query_def q
JOIN P_DM_ca_group_def g ON q.query_uuid=g.query_uuid
where q.query_uuid=0x978AF7AC725CD04692E73353E6441EDB
In this example the query "Asset by chassis type - Notebook" is linked to groups "Alle GH Notebooks" and "Alle Laptops"
3- In DSM Explorer, update the properties of groups found in step 3 and remove or change the Enterprise query.
Remark :
You could do a copy of the enterprise query which should be deleted and assign it to the groups.
Feedback
Yes
No
Powered by
