Ensure there is a working Discovery database backup before performing the below steps.1. Database cleanup.
If there are items other than DefaultEnvironment without a parent, they have to be removed from the CONFIGURATION_VALUE and CONFIGURATION_ITEM tables. Note that if the orphaned entry has a child in the CONFIGURATION_ITEMS_LINKS table, then the entire child tree needs to be removed.
select ID from CONFIGURATION_ITEM where ID<>'DefaultEnvironment' and ID not in (select distinct ITEM_ID from CONFIGURATION_ITEMS_LINKS) and ID not in (select distinct LINKED_ITEM_ID from CONFIGURATION_ITEMS_LINKS);
// Used ID's obtained above other than 'DefaultEnvionment' in the delete queries as in below example
// next 2 SQL commented due to provided better solution
// delete from CONFIGURATION_VALUE where ITEM_ID in ('id1','id2');
// delete from CONFIGURATION_ITEM where ID in ('id1','id2');
// or even better
delete from CONFIGURATION_VALUE where ITEM_ID in (select ID from CONFIGURATION_ITEM where ID <> 'DefaultEnvironment' and ID not in (select distinct ITEM_ID from CONFIGURATION_ITEMS_LINKS) and ID not in (select distinct LINKED_ITEM_ID from CONFIGURATION_ITEMS_LINKS));
delete from CONFIGURATION_ITEM where ID in (select ID from CONFIGURATION_ITEM where ID <> 'DefaultEnvironment' and ID not in (select distinct ITEM_ID from CONFIGURATION_ITEMS_LINKS) and ID not in (select distinct LINKED_ITEM_ID from CONFIGURATION_ITEMS_LINKS));
//after doing that (2 previous rows) next query should now return 0 rows
select ID from CONFIGURATION_ITEM where ID<>'DefaultEnvironment' and ID not in (select distinct ITEM_ID from CONFIGURATION_ITEMS_LINKS) and ID not in (select distinct LINKED_ITEM_ID from CONFIGURATION_ITEMS_LINKS);
// To find child tree in CONFIGURATION_ITEM_LINKS, use the id obtained in first query
select * from CONFIGURATION_ITEMS_LINKS where ITEM_ID='id1';
2. Hotfix (Discovery service)
a. The above queries will fix the existing Discovery database but since there are mappings which have been removed, these have to be synced from the Topology database. To prevent future incidents, apply a Discovery hotfix* to better handle large batch requests.
- For 8.1.1 - CD_8.1.1.4472 or later
- For 8.5 - CD_8.5.0.4472 or later
- For 9.5 - CD_11.5.0_21391 (latest as of Nov 2021)
See KB article below for instructions on how to download the hotfix.
Where can I find hotfixes for SDL Tridion Sites/Web?b. Add FlushInterval**="1000" attribute to <Storage....> configuration and restart the discovery service
<Storage Class="com.tridion.storage.persistence.JPADAOFactory".... FlushInterval="1000">
* Note that the FlushInterval may need to be increased further depending on the number of mappings, for example 5000 or 10000.
** This hotfix includes catching the general exception on datalayer to rollback if there is an unexpected error. It also includes closing the entity manager in case of standalone transaction and adding the missing commit on deleteLink method.
c. Run the Sync-TtmCdEnvironment command to resync from the Topology Manager database.
d. Recheck for any orphaned items in Discovery DB after the sync.
