then we looked into logs and found the below error on TEMA log
Error on TEMA Log:
Java::ComMicrosoftSqlserverJdbc::SQLServerException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘scm.checks_persistent’ and the index name ‘scm_checks_persistent_checklist_id_remote_id_index’. The duplicate key value is (11, 00459be3-6680-9b04-e27e-89f3bafc0152).: CREATE UNIQUE INDEX [scm_checks_persistent_checklist_id_remote_id_index] ON [scm].[checks_persistent] ([checklist_id], [remote_id])
We are following the above Technote to resolve the issue
After removing all duplicate SCM_Id’s as suggested from Fixlets we are planning to rollback BigFix compliance to V1.8
here i do have few question
1)Do we need to run the 1.8 Exe file only by placing TEMA backup folder which was taken before change?
2)Do we need to place SSL certs ?
3)is there any another way to remove SCM_id’s from teamanalytics DB directly?
You could rollback to v1.8 first and then remove the duplicates or give them unique x-fixlet-scm-id’s. Working on the database directly is not recommended.
You do not need to do anything with SSL certs.
Yes we rolled back to v1.8 and then we modified SCM_id’s and the started import and it was succesful.
After modifying SCM_Ids,we tried again to Upgrade to V1.9 but it’s failed at Upgrading schema and the error on TEMA log is
[7/8/18 8:48:42:336 UTC] 0000003a com.ibm.ws.webcontainer.webapp I SRVE0292I: Servlet Message - [tema]:.[ERROR] Java::ComMicrosoftSqlserverJdbc::SQLServerException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘scm.checks_persistent’ and the index name ‘scm_checks_persistent_checklist_id_remote_id_index’. The duplicate key value is (11, 00459be3-6680-9b04-e27e-89f3bafc0152).: CREATE UNIQUE INDEX [scm_checks_persistent_checklist_id_remote_id_index] ON [scm].[checks_persistent] ([checklist_id], [remote_id])
4:26:41 PM: [7/6/18 9:53:52:266 UTC] 00000046 com.ibm.ws.webcontainer.webapp I SRVE0292I: Servlet Message - [tema]:.[ERROR] Java::ComMicrosoftSqlserverJdbc::SQLServerException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘scm.checks_persistent’ and the index name ‘scm_checks_persistent_checklist_id_remote_id_index’. The duplicate key value is (11, 00459be3-6680-9b04-e27e-89f3bafc0152).: CREATE UNIQUE INDEX [scm_checks_persistent_checklist_id_remote_id_index] ON [scm].[checks_persistent] ([checklist_id], [remote_id])
[7/6/18 9:53:52:290 UTC] 00000046 com.ibm.ws.webcontainer.webapp I SRVE0292I: Servlet Message - [tema]:.[WARN] The statement has been terminated.
Sequel::DatabaseError (Java::ComMicrosoftSqlserverJdbc::SQLServerException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘scm.checks_persistent’ and the index name ‘scm_checks_persistent_checklist_id_remote_id_index’. The duplicate key value is (11, 00459be3-6680-9b04-e27e-89f3bafc0152).):
ok did you make sure you are not getting any results when you run below query?
select ds.name as site_name,
ds.remote_id as site_id,
df.name fixlet_name,
df.remote_id as fixlet_bes_id,
checks.remote_id as scm_id,
checks.name as check_name
from scm.checks as checks
join (
select checklist_id, remote_id from scm.checks
group by checklist_id, remote_id
having COUNT(*) > 1 ) as duplicates on duplicates.checklist_id = checks.checklist_id
and duplicates.remote_id = checks.remote_id
join datasource_fixlets df on df.id = checks.datasource_fixlet_id
join datasource_sites ds on ds.id = df.datasource_site_id
order by ds.name
