Replication constraint error on ca_query_version table

Document ID : KB000103110
Last Modified Date : 22/06/2018
Show Technical Document Details
Issue:
The download replication task on a single domain manager that is a member of an enterprise fails with a constraint error and references ca_query_version table

In Engine trace the following message may also be seen:

Error 9001: update error, new version number less than the old version number, Microsoft SQL Server Native Client 11.0, (null)
Environment:
Any supported Domain Manager (DM) where DM is also a Member of an Enterprise Server (ES)
Cause:
This can happen when a Query is created on the Domain Manager and then exported and manually imported on the Enterprise level causing the same query to replicate down over the original which has an older creation date which causes a logic conflict

Trace errors similar to the below can be seen in the Engine log for the Engine that runs this replication task:

008696|00001dd0|TEMPEngine|CmdPtreImpl_Ado |CmdPtreImpl_Ado.cpp |001113|DETAIL | ExecuteCommandPointer sql: update ca_query_version set label=?,flag=1527082284,query_domain_uuid=?,creation_user=?,last_update_user=?,version_number=1527082230,domain_list=?,query_version_domain_uuid=? where query_version_uuid=0xD8C2CC11A8F544449F7069C23AD52535 and query_uuid=0xAEDF91396F0C36419E220013EC0C730E

008696|00001dd0|TEMPEngine|CmdPtreImpl_Ado |CmdPtreImpl_Ado.cpp |001127|DETAIL | ExecuteCommandPointer sqlwparam: update ca_query_version set label='xxxxxx xxxxx xxxx',flag=1527082284,query_domain_uuid=0x28e60fdb39272141a87eca756cca3982,creation_user=NULL,last_update_user=NULL,version_number=1527082230,domain_list='28e60fdb39272141a87eca756cca3982,4ff1b77122dc6d48bf9d94ddca374d8b;',query_version_domain_uuid=0x28e60fdb39272141a87eca756cca3982 where query_version_uuid=0xD8C2CC11A8F544449F7069C23AD52535 and query_uuid=0xAEDF91396F0C36419E220013EC0C730E

008696|00001dd0|TEMPEngine|DbImpl_Ado |DbImpl_Ado.cpp |002311|NOTIFY | evalSQLInfo sqlstate: 42000 native error: 50000 0xc350
008696|00001dd0|TEMPEngine|DbImplAdo |DbImpl_Ado.cpp |002361|NOTIFY | evalSQLInfo ss error info proc: p_integrity_version_number line: 8
008696|00001dd0|TEMPEngine|CDb |DbImpl_Ado.cpp |002584|NOTIFY | Error Message: ADO Version 6.3 - ExecuteCommandPointer -COM Error: ErrorCode:-2147217900,WordErrorCode:3092, IDispatch error #3092,

Error 9001: update error, new version number less than the old version number, Microsoft SQL Server Native Client 11.0, (null)

008696|00001dd0|TEMPEngine|amLog | |000000|ERROR | Exec command pointer failed
008696|00001dd0|TEMPEngine|amLog | |000000|ERROR | GetActionOnError: MS SQL dbErrCat=-3, dbDetailedError=9001, errorInfo=ADO Version 6.3 - ExecuteCommandPointer -COM Error: ErrorCode:-2147217900,WordErrorCode:3092, IDispatch error #3092, Error 9001: update error, new version number less than the old version number, Microsoft SQL Server Native Client 11.0, (null)

008696|00001dd0|TEMPEngine|amLog | |000000|ERROR | SetIntermediateStatus2: Download: A database constraint violation error occurred for table: ca_query_version
008696|00001dd0|TEMPEngine|amLogAndNotify |cAmoLog.h |000387|ERROR | Warning: A database constraint violation error occurred for table: ca_query_version
Resolution:
To resolve this issue, the custom query must be exported from the ES DSM Explorer and saved; then delete the item from the DSM Explorer and allow that deletion to replicate down to the DM and verify the query is gone there was well.

Finally, go back to the ES DSM Explorer and import the query again. Now it will replicate down, find no older version of itself and the the issue will be resolved.

Queries should always be created on the ES if they are to reside on the ES or a query exported from a DM should be deleted from that DM BEFORE it is imported into an Enterprise Server which will automatically replicate it down to all Domains.