This article is designed for administrators who understand your Mosaic installation, you may need to share this article with your local support team. For hosted customers please raise a new case online and reference the title of this article. Please provide the name or URL of the Mosaic instances this relates to.
Under unknown circumstance it has been possible to create an inconsistent report library structure. For example, a top-level folder will be linked to a sub folder and the same sub folder will also be linked to the top-level folder creating a recursive hierarchy. This will cause errors when accessing the reports library in Mosaic or the Admin Tool.
Also, because an infinite recursion in the report_classes table has occurred the query will not finish which will lead to the hogging of CPU on the database server. This will also have the knock-on effect of generating stuck threads in the application which will impact the performance of Mosaic in all areas of the system.
Run the SQL below to identify the problem records. When this returns zero rows, the issue is resolved.
;WITH RecursiveCTE
AS (
-- Get all parents:
-- Any record in report_classes table could be a Parent
-- We don't know yet which record can involve in an infinite recursion.
SELECT parent_class_id AS StartID,
ID,
CAST(description AS NVARCHAR(255)) AS [ParentChildRelationPath]
FROM report_classes
UNION ALL
-- Recursively try finding all the childrens of above parents
-- Keep on finding it until this child become parent of above parent.
-- keep in the StartID column in recursion
SELECT RecursiveCTE.StartID,
t.ID,
CAST(RecursiveCTE.[ParentChildRelationPath] + ' -> ' + t.description AS NVARCHAR(255)) AS [ParentChildRelationPath]
FROM RecursiveCTE
INNER JOIN report_classes AS t
ON t.parent_class_id = RecursiveCTE.ID
WHERE RecursiveCTE.StartID != RecursiveCTE.ID)
-- Find the ones which causes the infinite recursion
SELECT StartID,
[ParentChildRelationPath],
RecursiveCTE.ID
FROM RecursiveCTE
WHERE StartID = ID
OPTION (MAXRECURSION 0);Fix the data by setting the parent_class_id to NULL for the effected rows.
update report_classes set parent_class_id=NULL where id in (add id(s) returned from step 1 here)
Manually put the reports library structure back in the Admin Tool.